How To Avoid Repair With Keycache?

Question

I have had some experience with optimizing the my.cnf file but my database has around 4 million records (MyISAM). I am trying to restore from a mysqldump but every time I do I eventually get the dreaded "Repair With Keycache", that may take days. Is there any way to get past this and let it roll as "Repair By Sorting"?

I have 2GB RAM, Dual Cores, lots of extra hard-drive space.

Snip out of my.cnf:

set-variable = max_connections=650 set-variable = key_buffer=256M set-variable = myisam_sort_buffer_size=64M set-variable = join_buffer=1M set-variable = record_buffer=1M set-variable = sort_buffer_size=2M set-variable = read_buffer_size=2M set-variable = query_cache_size=32M set-variable = table_cache=1024 set-variable = thread_cache_size=256 set-variable = wait_timeout=7200 set-variable = connect_timeout=10 set-variable = max_allowed_packet=16M set-variable = max_connect_errors=10 set-variable = thread_concurrency=8 

Answer 1

"Repair by sorting" uses the filesort routine, which in turn creates several temporary files (usually) in your tmpdir.

If your tmpdir does not have enough space for them, it will revert to "Repair by keycache". This is extremely bad as it's much slower AND creates less optimal indexes.

There are some other conditions but I haven't identified them.

Working out the size of tmpdir you need for filesort() is nontrivial; the format data are stored in the filesort buffer is not the same as MYD files, it typically uses a lot more space.

So if your tmpdir points at a small /tmp (or tmpfs), you might want to change it to a larger /var/tmp - if that exists.

Answer 2

MySQL will use repair by keycache for MyISAM tables whenever the maximum possible size of the tables indexes are greater than the value for the variable myisam_max_sort_file_size.

You can calculate the maximum size of the index by adding up the byte size values for all keys in all the indexes and multiplying that by the number of rows in your table.

Increase the myisam_max_sort_file_size and your index will be rebuilt using sorting on disk, rather than with the slow keycache method.