Other day I noticed a case where loading the same set of data to InnoDB took only 10 minutes where as loading it to MyISAM took ~2 hours.

Digging it further found that it is all because of well known Repair with keycache issue. But for some reason, it took me a while to get to the root cause of the issue as it was working fine until few days. When MyISAM needs to repair the table (REPAIR, ALTER or LOAD or ENABLE KEYS); it uses two modes for repair:

  • repair by sorting
  • repair using keycache (falls to this mode by default if repair by sort fails for any reason)

first it tests if the table can be repaired by sorting provided it meets the following requirements:

  • table at least has one key
  • total size needed for individual key is less than myisam_max_sort_file_size

If it meets the above requirements, then it uses either regular sorting if myisam_repair_threads  = 1 (default) by building each key at a time or in parallel if myisam_repair_threads > 1 by using ‘n’ threads in parallel (n = total keys in the table). If you have a table with more than one key and table needs a frequent key rebuild, then setting myisam_repair_threads = 2 can speedup the repair/alter process.

If it fails to satisfy the above conditions, then it falls to expensive keycache repair mode.

Lets consider the following simple example (lengthy index) where one uses ‘Repair by sort’ and another uses ‘Repair by keycache’ when rebuilding 2M rows; the only difference between the two cases is difference in myisam_max_sort_file_size 

mysql> create table test_repair_cache (s_id bigint unsigned not null, 
                                       s_key VARCHAR(255) NOT NULL, 
                                       s_value text, 
                                       PRIMARY KEY(s_id, s_key), INDEX(s_key, s_id)
        )Engine=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;
Query OK, 0 rows affected (0.00 sec)

with myisam_max_sort_file_size=1.5G with myisam_max_sort_file_size=512M 
mysql> show global variables like 'myisam_max_sort_file_size';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| myisam_max_sort_file_size | 1610612736 | 
+---------------------------+------------+
1 row in set (0.00 sec)
 
mysql> alter table test_repair_cache disable keys;
Query OK, 0 rows affected (0.00 sec)
 
mysql> load data local infile 'x' into table test_repair_cache  ;
Query OK, 2000000 rows affected (2 min 28.67 sec)
Records: 2000000  Deleted: 0  Skipped: 0  Warnings: 0
 
mysql> alter table test_repair_cache enable keys;
Query OK, 0 rows affected (13.96 sec)
 
mysql>
 
mysql> show global variables like 'myisam_max_sort_file_size';
+---------------------------+-----------+
| Variable_name             | Value     |
+---------------------------+-----------+
| myisam_max_sort_file_size | 536870912 | 
+---------------------------+-----------+
1 row in set (0.00 sec)
 
mysql> alter table test_repair_cache disable keys;
Query OK, 0 rows affected (0.00 sec)
 
mysql> load data local infile 'x' into table test_repair_cache  ;
Query OK, 2000000 rows affected (2 min 28.95 sec)
Records: 2000000  Deleted: 0  Skipped: 0  Warnings: 0
 
mysql> alter table test_repair_cache enable keys;
Query OK, 0 rows affected (6 min 47.92 sec)
 
mysql>

As you can see, it took only 13 secs when there is a sufficient myisam_max_sort_file_size to sort 2M rows and ~6m  in case of keycache mode. For larger data sets, this can take hours and sometimes even a day or two.

Here is the formula to find the right sort size for the above case (~1.5G) 

mysql> select (2000000 * (255 * 3) + 8) / (1024 * 1024 * 1024) as SizeInGig;
+-----------+
| SizeInGig |
+-----------+
|    1.4249 | 
+-----------+
1 row in set (0.00 sec)

SHOW PROCESSLIST thread state will indicate if its using sort or keycache for repairing the keys, for example, here is the show processlist state for all three repair modes

mysql> show processlist;
+-------+---------+-----------+------+---------+------+----------------------+-------------------------------------------+
| Id    | User    | Host      | db   | Command | Time | State                | Info                                      |
+-------+---------+-----------+------+---------+------+----------------------+-------------------------------------------+
| 23735 | testing | localhost | test | Query   |  248 | Repair with keycache | alter table test_repair_cache enable keys | 
+-------+---------+-----------+------+---------+------+----------------------+-------------------------------------------+
 
+-------+---------+-----------+------+---------+------+-----------------------+-------------------------------------------+
| Id    | User    | Host      | db   | Command | Time | State                 | Info                                      |
+-------+---------+-----------+------+---------+------+-----------------------+-------------------------------------------+
| 23740 | testing | localhost | test | Query   |    4 | Repair with 2 threads | alter table test_repair_cache enable keys | 
+-------+---------+-----------+------+---------+------+-----------------------+-------------------------------------------+
 
+-------+---------+-----------+------+---------+------+-------------------+-------------------------------------------+
| Id    | User    | Host      | db   | Command | Time | State             | Info                                      |
+-------+---------+-----------+------+---------+------+-------------------+-------------------------------------------+
| 23743 | testing | localhost | test | Query   |    5 | Repair by sorting | alter table test_repair_cache enable keys | 
+-------+---------+-----------+------+---------+------+-------------------+-------------------------------------------+
  • Print
  • Twitter
  • Facebook
  • PDF
  • Google Bookmarks

Related posts:

  1. LOAD DATA INFILE – performance case study
  2. INT and String data comparison, difference in performance because of quotes
  3. MyISAM table incompatibility when upgrading to 5.1
  4. ON DUPLICATE KEY With NULL Validation