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:
- Query processing must stop
- Create a temp table on disk
- Transfer the contents of the memory-based temp table into the disk-based temp table
- Drop in the memory-based temp table
- 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.
