Mysql – Why does a thesql query, that requires copying to tmp table, randomly sometimes consume 100% CPU during several seconds

cpu-usagehard drivememory usageMySQLsql

I have some crazy SQL queries in my PHP scripts which badly need some optimization. However the question is NOT about how to optimize them.

These queries have a lot of "join"s and some bizarre "order by", and are done against tables with several tens of thousands of records. The lots_of_joins+crazy_order_by thing makes them need a "copy to tmp table" step.

Now the strange thing is that the very same query sometimes runs fast (less than a second), and sometimes takes ages (tens of seconds). In BOTH cases, "explain" and profiles show a "copy to tmp table" step. When the query takes ages, 99% of the time is spent in the "copy to tmp table" phase. The STRANGE THING is that during that time, mysql consumes almost 100% CPU.

So, I understand that the tmp table sometimes is kept in memory and sometimes is written to disk (depending on current memory availability). So that would perfectly explain why the same query can sometimes be fast and sometimes take long. However, there are two things that I don't understand.

  1. If the bottleneck is in writing the temporary table to disk, that should mean a lot of time spent in I/O, but the avarage cpu load during that time should be relatively low, certainly far from 100%. How can the CPU be so busy when doing so much I/O?

  2. I have increased in my.cnf:

     max_heap_table_size = 1024M
 tmp_table_size = 1024M

(I think the defaults were 16M)

and I really don't believe the tmp table is requiring more than that amount of RAM.

As far as I understand, a tmp table is written to disk rather than memory either:
a- if the query and the table require it because they don't satisfy certain criteria
b- if it exceeds the minimum between max_heap_table_size and tmp_table_size

If (a) was the case, then it would happen always, not from time to time.
On the other hand it seems to me unlikely that it is (b), because I have increased the above parameters a lot without any noticeable change. The size of the needed table should not change very much among occurrencies of the same query (data being almost the same). So if prior to increasing the memory sizes it happened every once in a while (it would mean that the size of the required tmp table was around just the maximum) then the erratic behavior should have disappeared completely after such a drastic increase.

So my questions basically are:

  1. Is creating-disk-table really the only cause that can make the copying-to-tmp-table step take very long? (Or can it take long even though it is done in memory? and if so why, and why randomly?)

  2. If it is, then

    • how can it possibly use a lot of CPU?
    • why can a tmp table be written to disk even if it is not bigger than min(tmp_table_size,max_heap_table_size) ?

I really don't think the example query and the profiles are needed, but I can post them if they are.

Best Answer

I think your problem is not so much with tmp_table_size as it is with getting your "ugly" query (resultset) cached via query_cache. If your query is type SELECT, use SQL_NO_CACHE. When cached, it serves fast, but it can overload the cache and mysql had known issues with reorganizing cache internally. Also, check your query with EXPLAIN and use force indexes on joins if necessary.

This would be more appropriate as comment but my current rep is too low.

Related Topic