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.

  • I wonder why on earth the code formatting is not working. – matteo Apr 3 '13 at 21:03
  • 5
    Markdown hates you and wants you to be miserable. – Michael Hampton Apr 3 '13 at 21:05
  • The "code" button in the editor adds 4 spaces at the beginning of each line. I notice you have added 5 more and it now works. So why doesn't the editor add 9 spaces if that is what is needed in order to have the text be formatted as code? that doesn't make any sense – matteo Apr 4 '13 at 6:39

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.

  • The "show profile" does indicate the time is spent in copying to tmp table (however I don't know whether that may include time spent in caching-related stuff at that stage). Anyway, cache was disabled when I was observing this (the first thing i thought was: if it's sometimes fast and sometimes slow, it must be because it is sometimes served from cache and sometimes not - but that is NOT the case because caching was not enabled at all). Then I enabled cache and there is no difference: neither improvement nor worsening... – matteo Aug 25 '13 at 11:20
  • However the force indexes thing sounds interesting, I'll try to check that out – matteo Aug 25 '13 at 11:22

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.