Avoiding "Repair with keycache"
First, I know little about MySQL: let's get that up front.
I have a db with one table, 2,787,003 rows of average length 1,527. It loaded very smoothly, 12 to 13 minutes, with keys disabled. When I went to index it by enabling keys, it went into snail mode--it has been running for days now and has just gotten to the .TMM-file point (which it would be nice to see documented somewhere).
A SHOW PROCESSLIST revealed, not surprisingly, the dreaded "Repair with keycache", which seems to multiply indexing time by a factor of 20 to 30. My problem is how to get it to index using sort.
My full "my.cnf" is this:
[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.sock
log = /usr/local/var/logs/access_log
log-slow-queries= /usr/local/var/logs/slow_log
tmpdir = /usr/tmp
enable-locking
skip-name-resolve
skip-host-cache
skip-innodb
back_log=128
connect_timeout=10
ft_min_word_len=3
key_buffer_size=512M
max_allowed_packet=1024M
max_connections=200
max_connect_errors=100
myisam_sort_buffer_size=128M
query_cache_size=32M
read_buffer_size=2M
sort_buffer_size=2M
thread_cache=32
table_cache=512
wait_timeout=15
myisam_max_sort_file_size=21474836480
The drive has about 40 GB spare, but allowing for this and that I assumed a value of 20 GB available, and have so set the myisam_max_sort_file_size parameter. I know little or nothing about the myisam_max_extra_sort_file_size parameter, and have not set it; but I understand that the default there is 2 GB, and that seems to be the threshold for problems (tables only slightly smaller index in a few hours). Is that the problem? I have no idea.
The MySQL is v. 4.0.21. The RAM is, I believe (I have minimal control over my server and host) 1 GB. I am unsure of the CPU, but it does everything fast enough when the index is under 2 GB.
I have spent literally days combing the web for advice, and what little I find is confusing and often contradictory. Can someone *please* give me a brief, clear idea of what parameters work in what ways to determine which method MySQL chooses for repairing indices? I cannot take several days every time I need to update that table (typically weekly). I should add that the table is used only for reading: it is never updated save all at once by dropping and remaking with new data.
TIA.
Cordially,
Eric Walker
Owlcroft House