Dual Quad Core Xeon CPU but still high load

[Ali Kiyani]

Hi,

I have a dedicated server with following specs:

1. Two Intel Xeon-Harpertown 5430-Quadcore[2.66 Ghz]
2. 4GB DDRII RAM
3. 500GB SATAII HD
4. CentOS 5.5 64-bit

Problem is that even with such specs MySQL still takes high CPU usage. It almost remain above 150% all the time and most of the time goes above 300%. I came to know about this after running "top" command.
Now the thing is as soon as I run "watch mysqladmin pr" to see what's going on then I don't see any problem. Although there are queries running but they are not like some very heavy queries except may be one or two.

I ran "mysqltunner.pl" and it showed me the following:


-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 362M (Tables: 255)
[--] Data in InnoDB tables: 880K (Tables: 55)
[!!] Total fragmented tables: 2

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3h 26m 51s (1M q [138.122 qps], 43K conn, TX: 3B, RX: 246M)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 830.0M global + 3.9M per thread (300 max threads)
[OK] Maximum possible memory usage: 1.9G (50% of installed RAM)
[OK] Slow queries: 0% (47/1M)
[OK] Highest usage of available connections: 6% (18/300)
[OK] Key buffer size / total MyISAM indexes: 256.0M/169.9M
[OK] Key buffer hit rate: 100.0% (5B cached / 36K reads)
[OK] Query cache efficiency: 84.2% (1M cached / 1M selects)
[!!] Query cache prunes per day: 338346
[OK] Sorts requiring temporary tables: 0% (989 temp sorts / 242K sorts)
[!!] Temporary tables created on disk: 38% (160K on disk / 420K total)
[OK] Thread cache hit rate: 99% (18 created / 43K connections)
[OK] Table cache hit rate: 98% (446 open / 452 opened)
[OK] Open file limit used: 1% (663/65K)
[OK] Table locks acquired immediately: 99% (684K immediate / 684K locks)
[OK] InnoDB data size / buffer pool: 880.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query_cache_size (> 64M)
------------------------------------------------------------------------------


As you can see most of the parameters are correct except a few like 2 defrag tables and small query cache size. Even if I fix this I don't see any noticeable performance increase. So now I have turned my attention to this "Temporary tables created on disk 38%"
Do you think it is because of this MySQL is taking too much CPU time? How can I improve it? Or do you think there is something else after look at above result?

Currently my setting regarding temporary tables in MySQL config file are:

tmp_table_size=1000M
max_heap_table_size=500M

Even if I increase these values, MySQL still created temporary tables on disk. How do I fix it? I can see mysqltuner.pl also says that I need reduce my result set but if I give it plenty of RAM to create temp tables shouldn't the problem go away?


Thanks