1

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?
3
  • You never mentioned if the target table for the load was InnoDB or MyISAM. Commented Sep 14, 2015 at 17:44
  • What criteria is used for trimming back to 100M?
    – Rick James
    Commented Sep 14, 2015 at 23:33
  • The engine is MyISAM. Trimming criterion: when there are more than 12 rows per each sorted group (defined by 3 columns), remove the extra rows. Commented Sep 15, 2015 at 15:54

1 Answer 1

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.