Slow MySQL on idle server

MySQL & MariaDB Other MySQL® Questions
1

We have new server with 4-core CPU and 3 GB RAM running mysql 5.0, disks are almost idle (iowait 1-5%). /var/lib/mysql has 2.9 GB used, but most used tables fit in memory.
But some queries are still taking almost 1.5 seconds. What can be the reason? CPU usage is about 15-25% only. What can be limiting MySQL if it is not CPU and not DISK?
From slow.log I see for example this:

Count : 941 (48.06%)Time : 1396.313552 s total, 1.483861 s avg, 1.319578 s to 4.09349 s max (35.55%) 95% of Time : 1280.66036 s total, 1.43411 s avg, 1.319578 s to 1.710587 s maxLock Time : 102.633 ms total, 109 ľs avg, 44 ľs to 16.016 ms max (3.00%) 95% of Lock : 74.149 ms total, 83 ľs avg, 44 ľs to 131 ľs maxRows sent : 10 avg, 10 to 10 max (0.34%)Rows examined : 69.90k avg, 69.75k to 70.14k max (45.74%)QC hit : 0% (0)Full scan : 0% (0)Full join : 0% (0)Tmp table : 100% (941)Disk tmp table: 100% (941)Filesort : 100% (941)Disk filesort : 0% (0)Merge passes : 0 total, 0 avg, 0 to 0 max

So it should not be locking issues. Most tables are myisam, some are InnoDB. Some myisam are because of FULLTEXT indexes, others are mysaim only for historical reasons. I don’t know if converting them to InnoDB would have much effect. They are not written too much.

If I understand it well, mysql is multi-threaded, but each query uses only 1 CPU. But I don’t believe that this quite simple query would require entire 1 CPU for 1.5 seconds…

2

Tmp table : 100% (941)
Disk tmp table: 100% (941)

MySQL creates disk based temporary tables for your queries - either because you are using text/blob fields which the MEMORY storage engine can’t handle - or because tmp_table_size/max_heap_table_size are to low.

  • Try to avoid text/blob fields.
  • If you are using no text/blob fields in your query, increase tmp_table_size/max_heap_table_size.
  • If you need to use text/blob fields in your query, set tmpdir to a ramdisk.

Regards

Pascal

3

Hi,

Thanks for your explanation. But are these on-disk tmp tables so slow on idle machine? I suppose that they should be fully cached by OS (linux kernel 2.6) and they exist only for 1-2 seconds, so won’t be flushed to HDD. I will test the tmpdir on ramdisk next time the mysql will be restarted (or can tmpdir be changed online?)

Yes I know it would be best to avoid having tmp tables, but developers are just too lazy to rewrite the queries…