Default index_cache_size causes very slow alter table ... add index

[Andrew Garner]

Using the default index_cache_size (32M) against a relatively small (~200M) table, adding an index taking >43 minutes on my hardware. Watching disk io from the otherwise idle MySQL process shows several gigs of data written (at 130+M/s), while the entire database is only ~350M. Through some automated trial-and-error, I found that raising index_cache_size to 128M, while still leaving all other parameters at their defaults allows this process to complete in <2 minutes.

For reference, I used the 'test-db' database for testing from http://launchpad.net/test-db. I ran:
USE employees;
SET SQL_LOG_BIN = 0;
ALTER TABLE salaries ADD INDEX date_range_idx (from_date, to_date);

[Vladimir Kolesnikov (vkolesnikov)]

Thanks for the report. Running the ALTER with default memory parameters is very slow indeed

[Paul McCullagh (paul-mccullagh)]

I have some ideas of how we can fix this. In particular, we are dealing with a temporary table here (the original table is being copied into a temporary table on alter table). The thing is, temporary tables do not need to be durable. So if we mark a table as temporary, we can do certain optimizations. For example the index need not be flushed consistently.

[Paul McCullagh (paul-mccullagh)]

This temporary table has been implemented in PBXT 1.1 (branch attached).

There is a second aspect that also need to be implemented. Currently, the entire import is done in one transaction. These means the sweeper is suspended during this time.

Then, at the end of the import, then sweeper needs to complete cleaning the transaction before the ALTER TABLE can complete.

[Paul McCullagh (paul-mccullagh)]

This operation now takes 2 min 17.44 sec on my Mac. Besides what has previously been mentioned, one of the main problems here turned out to be the FK definition. On ALTER TABLE the foreign keys references were being checked. This is unnecessary because the contents of the table are not changed.