Optimize MySQL tmp_table_size

Optimize MySQL tmp_table_size

MySQL tmp_table_size is  the maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. The on-disk table is expensive and affects your database performance. The temporary table may be created when you do many advanced GROUP BY queries, multiple table joining without proper indexing. The default size is enough for most of the cases.  However, to find out the tuned value follow the steps:
1. Find out current value of tmp_table_size

mysql> show global variables like 'tmp_table_size';
+----------------+-----------+
| Variable_name  | Value     |
+----------------+-----------+
| tmp_table_size | 268435456 |
+----------------+-----------+
1 row in set (0.00 sec)

2. Find out percentage of tables created on disk

mysql> show global status like 'created_tmp_disk_tables';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 278571 |
+-------------------------+--------+
1 row in set (0.00 sec)

mysql> show global status like 'created_tmp_tables';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| Created_tmp_tables | 1617536 |
+--------------------+---------+
1 row in set (0.00 sec)

Tmp_disk_tables=((created_tmp_disk_tables*100/(created_tmp_tables+created_tmp_disk_tables))
= ((278571*100/(1617536 + 278571))
= 14.69%

Created disk tmp tables ratio seems fine. If it exceeds 25% you may consider to increase tmp_table_size. If it is required set it by the command

mysql> set global tmp_table_size=268435456;
Query OK, 0 rows affected (0.00 sec)

It is to be mentioned that the tmp_table_size and max_heap_table_size should be the same value. The overall goal to optimize MySQL tmp_table_size should be to prevent temp table creation as much as possible. Simply increasing tmp_table_size and max_heap_table_size lets inefficient queries and tables that lack proper indexing run amok. If percentage of tables created on disk exceeds 50% then first check your database is indexed properly specially for joining and group by columns. After tuning index, observe some days and adjust tmp_table_size.