MySQL Forums
Forum List  »  MyISAM

"Alter table enable keys" randomly using "Repair with keycache"
Posted by: Bing Wu
Date: July 09, 2010 04:03PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
"Alter table enable keys" randomly using "Repair with keycache"
6267
July 09, 2010 04:03PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.