Mysql – ENABLE/DISABLE KEYS lag: how to optimize

myisamMySQL

I've got 32 GB of RAM and a mysql table with varying size of rows: 100m – 500m rows (each row is around 30 bytes). Every hour a PHP script DISABLEs KEYS of the table, LOADs a couple of millions of new rows from a CSV file, and ENABLEs KEYS again. This works well (done within 2 mins) as long as the table has less than 200m rows. Once it grows further, extreme lagging is experienced: the disable-enable keys procedure suddenly lasts longer than an hour.

The table is trimmed frequently back to 100m rows (let's say once every day).

For all I care, I am ready to devote 90% of all resources of my server for this script only (only during the execution of the script, naturally).

QUESTIONS

  • Why this lagging?
  • What system variables could I define in my PHP script to try to overcome the lagging?

Best Answer

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

  • sort_buffer_size
    • 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.
  • innodb_sort_buffer_size
    • Default: 1M, MinValue: 64K, MaxValue: 64M
    • Documentation says three buffers of this size are allocated for an ALTER TABLE or CREATE TABLE statement that creates an index.
  • myisam_sort_buffer_size
    • 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.

SUGGESTION

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;

or

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 /etc/my.cnf

[mysqld]
myisam_sort_buffer_size=1G

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

Related Question