I have a dedicated server with following specs:

  1. Two Intel Xeon-Harpertown 5430-Quadcore[2.66 Ghz]
  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?



You may be addressing the symptom and not the problem. The place to start is by looking at what is making your load so high. Is it kernel mode activity? Is it userland activity? If it is kernel mode, my guess is you are having issues writing to disk fast enough and the io is in a wait state. Look at tools like top, iostat, vmstat, etc to start narrowing down your problem.

With a load that high, you are likely to be seeing some sort of wait that is causing the kernel to queue requests.

  • Ok but what exactly I look for in iostat, vmstat? May be I will post the results here. I am new to Linux so I have no idea what to look for. – Ali Feb 17 '11 at 17:22
  • Agreed, that it sounds like an I/O problem. @Ali, you're not storing MySQL tables on NFS mounts or anything like that, are you? – pboin Feb 17 '11 at 17:34
  • Here are iostat and vmstat results. At this moment MySQL is using around 400% CPU time. rapidshare.com/files/448538913/results.txt It's a small TXT file. – Ali Feb 18 '11 at 4:27
  • @pobin I have no idea if that is the case. How do I find out? There is one network drive mounted on server but it is not in use. – Ali Feb 18 '11 at 4:28
  • So guys what should I do now? – Ali Feb 19 '11 at 6:58

Please check several things:

1) join_buffer_size

2) sort_buffer_size

3) Temporary File Creation

4) SELECT queries that have sub-SELECTs

You probably have many queries that do slow joins because of the presence of tables without needed columns being indexed.

If your join buffer/sort buffer in a given DB Connection is too small to hold temporary results, the join buffer and/or sort buffer would have to page out to disk as a temp table.

  • Here are the results: 1. join_buffer = 1M 2. sort_buffer_size = 1M 3. Temporary files are being created in /var/tmp 4. How do I do that? Is there a command or query? Because there may be lots of them and I will have to check them all manually. – Ali Feb 18 '11 at 4:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.