show global status like '%tmp%'

will show output like:

# Variable_name, Value
Created_tmp_disk_tables, 5939
Created_tmp_files, 6
Created_tmp_tables, 97525

If “Created_tmp_disk_tables” is too high it means system tmp tables do not fit into memory and MySQL must store them on disk. (check percentage “Created_tmp_disk_tables / Created_tmp_tables * 100”

Therefore check limits on your db:

  • tmp_table_size
  • max_heap_table_size

I was able to decrease “Created_tmp_tables” by setting higher limits but still I have around 6% of tmp tables being stored on disk