Repair with keycache Performance Issues

If you've ever had to alter a large table or even rebuild a table and encountered an extremily long load process then it could be because MySQL is creating the table indexes in a sub-optimal way.

If the total size of the indexes required by the new table exceeds the myisam_max_sort_file_size parameter, or if there is insufficient temporary space available on the tmp directory, then MySQL will elect to "Repair with keycache" rather than the more performant "Repair by sorting".

For example:

I have a table called statistics which is currently has 94 MB of data (i.e. the size of the MyISAM statistics.MYD file) and 129MB of indexes (i.e. the size of the statistics.MYI file).

If I want to populate a new table (called stat_test in the below example) with the contents of this table, MySQL will first create the data file, and then create the necessary indexes. If the myisam_max_sort_file_size is configured too low, then MySQL will opt for the Repair with keycache method.

Note: In this case we will force myisam_max_sort_file_size to be just 10MB. In reality your default setting could be 2GB or more, and this issue would only affect very large tables.

mysql> truncate table stat_test;

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

mysql> insert into stat_test select * from statistics;
Query OK, 3258166 rows affected (3 min 0.71 sec)
Records: 3258166 Duplicates: 0 Warnings: 0

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

mysql> truncate table stat_test;

mysql> insert into stat_test select * from statistics;
Query OK, 3258166 rows affected (1 min 50.43 sec)
Records: 3258166 Duplicates: 0 Warnings: 0

The Repair by sorting method is over a minute faster in this case, or 64% Faster.

AppDynamics for Databases can display both executions, and also the time spent in each wait state.