MySQL Forums
Forum List  »  Performance

Dual Quad Core Xeon CPU but still high load
Posted by: Ali Kiyani
Date: February 17, 2011 10:32AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Dual Quad Core Xeon CPU but still high load
3693
February 17, 2011 10:32AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.