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?

  1. 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
mysql
Edit
Report