I have a program that calls "alter table ... enable keys". The strange things is, every once in a while---but not always, it resulted in the dreaded "repair with keycache" operation. And I am pretty sure it is not because of the myisam_max_sort_file_size variable is too small, or the /tmp directory is full, or it is not data-induced.
The program loads external data to an existing table (MyISAM table). In order to improve performance, I disable indexes on that table first, do bulk inserts, then re-enable the indexes.
Most of the time, re-enabling works fine. The "show processlist" shows that the table is using "Repair with sort". But every once in a while (no clear pattern at all), the re-enabling will go into "repair with keycache" mode.
When this happened, I looked into the key variable that matters: myisam_max_sort_file_size, it shows:
mysql> show variables like 'myisam%max%';
+---------------------------+---------------------+
| Variable_name | Value |
+---------------------------+---------------------+
| myisam_max_sort_file_size | 9223372036854775807 |
+---------------------------+---------------------+
And the tmp directory uses /tmp which had more than enough space for the table involved.
Then if I stop the MySQL server, do a MyISAMchk on that table and run the same program using the same data set, then the problem goes away. So I know it is NOT data-induced.
Did I miss something? What else should I check/verify?
Bing
Edited 1 time(s). Last edit at 07/09/2010 04:10PM by Bing Wu.