MYSQL myisam Repair with keycache

Question

I'm disabling keys; bulk load data; and then re build the index on a myisam table. however, the last part takes forever because it is

Repair with keycache     

instead of repair by sort
here are my variables, I have about 100 million records in the table, each row has one integer primary key, one smallint column, and one varchar(256) column. my myisam_max_sort_file_size is 500G, I doubt that the thereotical max size of the indices will be anywhere close to that.

mysql> show variables where variable_name like '%myisam%';
+---------------------------+----------------------+
| Variable_name             | Value                |
+---------------------------+----------------------+
| myisam_data_pointer_size  | 6                    |
| myisam_max_sort_file_size | 536870912000         |
| myisam_mmap_size          | 18446744073709551615 |
| myisam_recover_options    | OFF                  |
| myisam_repair_threads     | 1                    |
| myisam_sort_buffer_size   | 4294967296           |
| myisam_stats_method       | nulls_unequal        |
| myisam_use_mmap           | OFF                  |
+---------------------------+----------------------+

my box has 8 GB of RAM and 250GB of storage, why doesn't MYSQL use repair with sort?

Answer 1

apparently myisam_sort_buffer_size = 4G is not enough, I needed to set it to 10G, then enable keys will use repair with sort. It baffles me!

Answer 2

For anyone else finding this, I discovered recently my MySQL 5.1 server does not like myisam_sort_buffer_size to be an exact multiple of 4G. I discovered this after a major RAM upgrade leaving me with Repair from Keycache.

I tested a 200MB table ENABLE KEYS with different values for myisam_sort_buffer_size

4G - repair with keycache
5G - repair with sort
6G - repair with sort
7G - repair with sort
8G - repair with keycache

...

Not sure if the 4G multiple was specific to my config, but its something to look out for. Took me a long time to discover that after trying setting to 8G, 16G, even 64GB.