Sizing MySQL on TEMP Tables

=================================================

MySQL Configuration Changes

 =================================================

This document is intended to explain how MySQL creates Temporary Tables and how to tune the MySQL Configuration file to improve query performance for Power Users.  MySQL will create a Temporary Table when a user issues a Group By or Order By command.  

MySQL will first create the Temporary Table in Memory, if the query exceeds the value of certain parameters in the my.cnf file, MySQL will use the MyISAM Storage Engine creating *.MYI and *.MYD files in the /tmp directory.  When MySQL has to create the *.MYI and *.MTD files the query performance will be slower than creating the Temporary Tables in Memory.

I created a Test on dl4dev to illustrate how MySQL creates Temporary Tables and how changing the MySQL Configuration file will improve performance.  My plan was to Configure MySQL to the lowest possible settings to force MySQL to create Temporary Tables when a Group By query was issued against the database.

mysql> \. small_parameters.sql

set global tmp_table_size = 83;

set global key_buffer_size = 83;

set global max_heap_table_size = 83;

set global query_cache_size = 0;

set global query_cache_limit = 0;

mysql>

Then I issued the following query, which completed in 29.45 seconds:

select GLDetailID,Year_Period,count(GLAccount) from GL_Detail where GLDetailID > 50000 group by GLAccount order by 3;

+------------+-------------+------------------+

544 rows in set (29.45 sec)

While the query was running, I checked the Global Status, looking specifically at the Key_writes, which means that MySQL wrote to a MyISAM Table.  At the same time, I was checking the /tmp directory to see if MySQL created the *.MYI and *.MYD files.

mysql> show global status like 'Key%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| Key_blocks_not_flushed | 0     |

| Key_blocks_unused      | 0     |

| Key_blocks_used        | 0     |

| Key_read_requests      | 0     |

| Key_reads              | 0     |

| Key_write_requests     | 0     |

| Key_writes             | 0     |

+------------------------+-------+

7 rows in set (0.00 sec)

The above output shows there were no reads or writes prior to the Test.  While the below output shows there were 444 Key_writes after the Test.  At the same time I verified if MySQL created the Temporary Tables.

mysql> show global status like 'Key%';

+------------------------+---------+

| Variable_name          | Value   |

+------------------------+---------+

| Key_blocks_not_flushed | 0       |

| Key_blocks_unused      | 0       |

| Key_blocks_used        | 0       |

| Key_read_requests      | 3326750 |

| Key_reads              | 3326750 |

| Key_write_requests     | 444     |

| Key_writes             | 444     |

+------------------------+---------+

7 rows in set (0.00 sec)

And sure enough, checking the /tmp directory, MySQL indeed created the *.MYI and *.MYD files, which are automatically cleaned up by MySQL once the query completes.

ls –ltr  /tmp

-rw-rw---- 1 mysql       mysql        11264 Mar 30 13:30 #sql_394d_0.MYI

-rw-rw---- 1 mysql       mysql        18410 Mar 30 13:30 #sql_394d_0.MYD

Next I increased the values of the following parameters and ran the previous query.

mysql> \. new_parameters.sql

set global tmp_table_size = 536870912;

set global key_buffer_size = 268435456;

set global max_heap_table_size = 536870912;

set global query_cache_size = 16777216;

set global query_cache_limit = 2097152;

set global table_open_cache = 256;

mysql>

I flushed the Key_writes buffer and issued the Group By query.

mysql> show global status like 'Key%';

+------------------------+--------+

| Variable_name          | Value  |

+------------------------+--------+

| Key_blocks_not_flushed | 0      |

| Key_blocks_unused      | 214342 |

| Key_blocks_used        | 0      |

| Key_read_requests      | 0      |

| Key_reads              | 0      |

| Key_write_requests     | 0      |

| Key_writes             | 0      |

+------------------------+--------+

7 rows in set (0.00 sec)

mysql>select GLDetailID,Year_Period,count(GLAccount) from GL_Detail where GLDetailID > 50000 group by GLAccount order by 3;

544 rows in set (6.19 sec)

As you can see the query completed in 6.19 seconds instead of 29.45 seconds, and didn’t write to disk, as the Key_writes value is zero.

mysql> show global status like 'Key%';

+------------------------+--------+

| Variable_name          | Value  |

+------------------------+--------+

| Key_blocks_not_flushed | 0      |

| Key_blocks_unused      | 214342 |

| Key_blocks_used        | 0      |

| Key_read_requests      | 0      |

| Key_reads              | 0      |

| Key_write_requests     | 0      |

| Key_writes             | 0      |

+------------------------+--------+

7 rows in set (0.01 sec)

Conclusion

Increasing the following parameters in the MySQL Configuration file will increase performance when Group By or Order By commands are issued against the MySQL database:

tmp_table_size = 536870912

key_buffer_size = 268435456

max_heap_table_size = 536870912

query_cache_size = 16777216

query_cache_limit = 2097152

table_open_cache = 256