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.