I'm trying to improve the performance of our primary MySQL server. It's a Dual Quadcore Intel X5450, with 8GB of RAM. It's a dedicated MySQL box. I'm only in the beginnings of configuring it, so I'm starting relatively fresh.

I noticed that Created_tmp_disk_tables was awfully high, at 330k. Created_tmp_tables is only at 380k. This might not be too bad, except that MySQL was restarted less than 4 hours ago.

I've set the following:

15:31:15 (9) > show global variables like '%table_size%';
| Variable_name       | Value      |
| max_heap_table_size | 1073741824 | 
| tmp_table_size      | 1073741824 | 

This page suggests that the smaller of the two values determines when a temporary table becomes a disk-based table. I would think (personally) that 1GB would be more than large enough to handle any sized temporary table that we'd be creating, but apparently, I'm incorrect.

Can anyone suggest other methods to keep the number of disk-based temporary tables down?


If temporary tables are over utilized, my first thought is that the application using the database is in need of query optimization.

  • Magento is one such application that causes this, but this seems to be a bug in MySQL.. I went all the way to 2.4GB for tmp and max_heap table_size and it din't make a difference. My entire database is less than 2.4GB! – ColinM Aug 2 '12 at 2:04
  • 1
    @ColinM cartesian queries can produce more data than is stored in the database. – Nitrodist Jan 30 '13 at 22:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.