Optimal MySQL temporary tables (memory tables) configuration?

Question

First of all, I am new to optimizing mysql. The fact is that I have in my web application (around 400 queries per second), a query that uses a GROUP BY that i can´t avoid and that is the cause of creating temporary tables. My configuration was:

max_heap_table_size = 16M  
tmp_table_size = 32M  

The result: temp table to disk percent + - 12.5%

Then I changed my settings, according to this post

max_heap_table_size = 128M  
tmp_table_size = 128M

The result: temp table to disk percent + - 18%

The results were not expected, do not understand why.

It is wrong tmp_table_size = max_heap_table_size? Should not increase the size?

Query

SELECT images, id  
FROM classifieds_ads   
WHERE  parent_category = '1' AND published='1' AND outdated='0'
GROUP BY aux_order  
ORDER BY date_lastmodified DESC  
LIMIT 0, 100;

EXPLAIN

| 1 |SIMPLE|classifieds_ads | ref |parent_category, published, combined_parent_oudated_published, oudated | combined_parent_oudated_published | 7 | const,const,const | 67552 | Using where; Using temporary; Using filesort |

Answer 1

"Using temporary" in the EXPLAIN report does not tell us that the temp table was on disk. It only tells us that the query expects to create a temp table.

The temp table will stay in memory if its size is less than tmp_table_size and less than max_heap_table_size.

Max_heap_table_size is the largest a table can be in the MEMORY storage engine, whether that table is a temp table or non-temp table.

Tmp_table_size is the largest a table can be in memory when it is created automatically by a query. But this can't be larger than max_heap_table_size anyway. So there's no benefit to setting tmp_table_size greater than max_heap_table_size. It's common to set these two config variables to the same value.

You can monitor how many temp tables were created, and how many on disk like this:

mysql> show global status like 'Created%'; 
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 20    |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 43    |
+-------------------------+-------+

Note in this example, 43 temp tables were created, but only 20 of those were on disk.

When you increase the limits of tmp_table_size and max_heap_table_size, you allow larger temp tables to exist in memory.

You may ask, how large do you need to make it? You don't necessarily need to make it large enough for every single temp table to fit in memory. You might want 95% of your temp tables to fit in memory and only the remaining rare tables go on disk. Those last 5% might be very large -- a lot larger than the amount of memory you want to use for that.

So my practice is to increase tmp_table_size and max_heap_table_size conservatively. Then watch the ratio of Created_tmp_disk_tables to Created_tmp_tables to see if I have met my goal of making 95% of them stay in memory (or whatever ratio I want to see).

Unfortunately, MySQL doesn't have a good way to tell you exactly how large the temp tables were. That will vary per query, so the status variables can't show that, they can only show you a count of how many times it has occurred. And EXPLAIN doesn't actually execute the query so it can't predict exactly how much data it will match.

An alternative is Percona Server, which is a distribution of MySQL with improvements. One of these is to log extra information in the slow-query log. Included in the extra fields is the size of any temp tables created by a given query.