Mysql tmp_table_size max_heap_table_size

On my server 2 days ago my tmp_table_size = max_heap_table_size(16M).

I made a cron job that runs once an hour and generates a report starting from : created_tmp_disk_tables, created_tmp_files, created_tmp_tables

In my report : created_tmp_disk_tables+created_tmp_files+created_tmp_tables=100% of my temporary data

With that :

1. with tmp_table_size=max_heap_table_size=16M the report showed me the next average report:

• 27.37% (created_tmp_disk_tables)
• 1.16% (created_tmp_files)
• 71.48% (created_tmp_tables)

How can I optimize these results?

2. with tmp_table_size=max_heap_table_size=20M in the first hour :

• 23.48% (created_tmp_disk_tables)
• 32.44% (created_tmp_files)
• 44.07% (created_tmp_tables)

After 7 hours (from restart):

• 21.70% (created_tmp_disk_tables)
• 33.75% (created_tmp_files)
• 44.55% (created_tmp_tables)

It's not what I expected.

• disk tables decreased from 27.37% to 21.70% -> expected much more
• temporary files rise form 1.16% to 33.75% -> why ?
• memory tables decreased from 71.48% to 44.55% -> strange; expected to rise

Whenever you increase tmp_table_size and max_heap_table_size, keep in mind that setting these does not make queries behave better. It actually make inefficient queries behave worse than before. Under what circumstances?

When a query performs a join or sort (via ORDER BY) without the benefit of an index, a temp table has to be formed in memory. This would increment Created_tmp_tables.

What if the temp table grows to the number of bytes in tmp_table_size and needs more space? The following sequence of events happens:

1. Query processing must stop
2. Create a temp table on disk
3. Transfer the contents of the memory-based temp table into the disk-based temp table
4. Drop in the memory-based temp table
5. Query processing continue using the disk-based temp table

This process increments Created_tmp_disk_tables

Knowing these mechanisms, let's explore what happened in each instance

> disk tables decreased from 27.37% to 21.70% -> expected much more

This could easily happen if the queries that ran before have cached results remaining in RAM. This would eliminate the need to process the query from the beginning and not recreate the same large temp tables.

> temporary files rise form 1.16% to 33.75% -> why ?

This is not surprising. This simply brings out the fact that there are queries that require temp tables. They were created in RAM first. This just indicates the presence of queries that do not join well (maybe join_buffer_size is too small) or ORDER BY non-indexed columns or columns with a temp table (maybe sort_buffer_size is too small).

> memory tables decreased from 71.48% to 44.55% -> strange; expected to rise

This is not surprising either. If there are enough calls for the same query with the same values, sorts and joins may be preempted by the fulfillment of queries from previously cached results.

#RECOMMENDATION

In light of these things, here is what could be adjusted:

• join_buffer_size
• sort_buffer_size
• Create indexes that would be used
• in joins via eq_ref
• in sorts via index scans in order

The overall goal should be to prevent temp table creation as much as possible. Simply increasing tmp_table_size and max_heap_table_size lets inefficient queries and tables that lack proper indexing run amok.