MySQL - 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 anyway 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

Thanks Mark, Yes that is exactly what I ended up trying and am seeing from the logs that that's the reason it switched to "Repair with keycache", was an out of space error.

This is what I did to get my solution in place as I will not go through the fact that it was pointing to /tmp/mysqltmp/, which only had a max of 2MB.

So I did this:

mkdir /home/mysqltmp

chown mysql:mysql /home/mysqltmp

changed my tmp dir in my.conf to tmpdir=/home/mysqltmp/

Now if I use, df -h /home/mysqltmp what I see is that dir has 285 GB available, so that really was a nice sight to see, had plenty of free space, plus I could see mysql was wanting 20GB easily. So what was taking me 12 hours before now is complete in 20 minutes, that is over 3 million records insert to index.

Answer 3

Thanks for the update! The fix helped my Repair with keycache issues. Thanks God for guys like you who comes back to update their post. Thanks!