Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer
    
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
    
@ColinM cartesian queries can produce more data than is stored in the database. –  Nitrodist Jan 30 '13 at 22:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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