洛克的猜想

Note: This tip only applies to MyISAM table

Usually when repairing table, mysql will use "repair with keycache" mechanism, it's fast when table is small, but extremely slow when table grows big.

Mysql has another repair mechanism called "repair by sorting", which is 100x faster than keycache mode according to experience.

To tell mysql use sorting mode, you have to specify two variables

• myisam_max_sort_file_size
• the max disk size mysql can used to do sorting, set it larger than the row number * key size. If you have 100Million data and key is varchar(32), then this variable must be bigger than 3.2G
  
• myisam_sort_buffer_size
• the max memory size mysql can used to do sorting, set it as large as your machine can afford.

You can set them by set command

• set global myisam_max_sort_file_size = 3200000000;
• set global myisam_sort_buffer_size = 128000000;
  

Note: the max disk size above means the available size of disk which TMPDIR is located

You can get the TMPDIR information by mysql command

• show variables like "%tmpdir%";

And you can set this parameter by

• --tmpdir argument when starting mysqld
• or configure the environment variable TMPDIR when starting mysql

For any further information, please refer to Mysql document.