There are some rare occasions when mysqld processes
ALTER TABLE ... ENABLE KEYS; and you get
Repair by keycache. The real question to ask is Under what circumstances does mysqld perform
Repair by keycache ?
According to MySQL Documentation on General Thread States
Repair with keycache
The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.
There are one or more server variables in the session that could potentially affect this behavior
- Independent of Storage Engine
- The default is 256K
- If this is set to 2M and mysqld needs more that 2M to process a sort operation, the session dumps the 2M it has in the sort buffer to a temporary MyISAM table, then continue processing from there.
- Documentation says three buffers of this size are allocated for an ALTER TABLE or CREATE TABLE statement that creates an index.
- Default value is 8M.
- Definition : The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
- If the sort operation needs more than 8M, there is where mysqld will make the session stop doing
Repair by sorting and switch to
Repair by keycache.
Based on the MySQL Documentation on sort_buffer_size
As of MySQL 5.6.4, the optimizer tries to work out how much space is needed but can allocate more, up to the limit. Before MySQL 5.6.4, the optimizer allocates the entire buffer even if it is not all needed. In either case, setting it larger than required globally will slow down most queries that sort. It is best to increase it as a session setting, and only for the sessions that need a larger size. On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values. Experiment to find the best value for your workload. See Section B.5.4.4, “Where MySQL Stores Temporary Files”.
Only sort_buffer_size and myisam_sort_buffer_size can be changed for the session.
If you don't have a lot of memory, change myisam_sort_buffer_size so that a larger temp table can be formed. To set it to 1G in your session, do one of the following before executing a large reindexing:
SET myisam_sort_buffer_size = 1024 * 1024 * 1024;
SET SESSION myisam_sort_buffer_size = 1024 * 1024 * 1024;
That way, the temp table on disk will be allowed to grow to 1G. Make sure the folder mapped to tmpdir can hold large temp tables.
If you want 1G as the permanent setting for myisam_sort_buffer_size going forward, do this
STEP #1 : Add this to
This will allow connections to have this setting after mysqld is restarted.
STEP #2 : Login to mysql as
root@localhost and run
SET GLOBAL myisam_sort_buffer_size = 1024 * 1024 * 1024;
This will enabled new connections to have this setting. Current connections would have to be closed and reopened (perhaps restarting
apache/tomcat/Hibernate or whatever web server you are using).
MY OTHER POSTS ON
Repair by keycache