In my previous blog post, I explained the internals of the sorted index build process. The blog ended with saying “there is one disadvantage.”
Beginning in MySQL 5.6, many DDLs including ALTER TABLE ADD INDEX became “ONLINE”. Meaning, when the ALTER is in progress, there can be concurrent SELECTS and DMLs. See the MySQL documentation for online DDL. From the documentation, we can see that ALTER TABLE ADD INDEX DDL permits concurrent DML.
The main disadvantage with Sorted Index Builds introduced in 5.7 is the reduced insert performance when ALTER is in progress. In this blog, we specifically talk about single thread insert performance on a table with ALTER ADD INDEX in progress.
If the table is huge, let’s say around 600 million rows or more, the inserts can even crash the server. This is especially true for ALTERs that run for hours and the concurrent insert waits for more than 600 seconds. InnoDB’s monitor thread crashes the server, stating that the INSERT waited for latch more than 600 seconds. It is reported as MySQL Bug#82940
Is it fixed?
The problem has existed since 5.7 GA and it is fixed in the latest release of Percona Server for MySQL 5.7.26-29 and 8.0.15-6 as part of PS-3410 bug fix. The number of inserts completed depends on whether the table is compressed or uncompressed, as well as the page size.
Percona’s fix is provided to upstream (Oracle MySQL) https://github.com/mysql/mysql-server/pull/268 but has yet to be included. We hope Oracle will include the fix in their next 5.7 release for the benefit of the MySQL community.
If you cannot upgrade to PS-5.7.26, one reasonable workaround would be to use pt-online-schema-change. With this tool, ensure that you have disk space at least equal to the original tablespace size.
How much is the improvement?
The % of improvement depends on the test scenario, machine configuration, etc. See details below.
For uncompressed tables, compared to 5.7.25 (baseline), with the fix version (5.7.26), 58% more inserts finished when ALTER ADD INDEX is running.
For compressed tables, 322% more inserts finished when ALTER ADD INDEX is running.
How does it compare to 5.6?
After the fix, for uncompressed tables, the number of inserts (from a single connection) completed during ALTER ADD INDEX is the same as 5.6.
For compressed tables, the number of inserts completed with 5.6.44 is 43% more than 5.7.26 (which has a fix). This is a bit surprising, and more analysis has to be done to find the reason. A topic for another day.
The problem from a design perspective
As part of sorted index builds, on the index being built, index->lock is acquired in X (exclusive) mode. This lock is held for the entire duration of the sorted index build. Yes, you read it right, for the entire duration. See PS-3410 for complete details.
Concurrent inserts will be able to see that there is a ‘new index’ being built. For such indexes, inserts go into Online ALTER logs, which are later executed at the end of ALTER. As part of this, INSERT tries to acquire index->lock in S (shared) mode to see if the index is in an online or aborted state.
Since the sorted index build process holds index->lock in X mode for the entire duration, concurrent insert waits for this latch. If the index is huge, the wait by insert thread crosses 600 seconds, and it will crash the server.
Fix
The fix is rather simple. Sorted index builds do not need to acquire index->lock in X mode. At this stage, there are no concurrent reads on this uncommitted index. Concurrent inserts do not interfere with the sorted index build. They go to online ALTER logs. So it is safe to not acquire the index->lock of the index being built.
Test Case
The following MTR test case is written to show the number of inserts that are concurrently executed while ALTER is running. Note that there is only one connection that does the inserts.
The test is run with innodb_buffer_pool_size = 1G for all versions. Two versions of the table are used. One with regular 16K page size and the other a compressed table with 4K page size.
The data directory is stored in RAM for all tests. You can save the below file (for example as mysql-test/t/alter_insert_concurrency.test) and run the MTR test case as:
1 | ./mtr --mem main.alter_insert_concurrency --mysqld=--innodb_buffer_pool_size=1073741824 |
The test inserts 10 million rows to the table and does CREATE INDEX (same as ALTER TABLE t1 ADD INDEX) and in another connection, INSERTS are executed one by one until ALTER finishes.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 | --source include/have_innodb.inc --source include/count_sessions.inc connect (con1,localhost,root,,); CREATE TABLE t1( class INT, id INT, title VARCHAR(100), title2 VARCHAR(100) ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; DELIMITER |; CREATE PROCEDURE populate_t1() BEGIN DECLARE i int DEFAULT 1; START TRANSACTION; WHILE (i <= 1000000) DO INSERT INTO t1 VALUES (i, i, uuid(), uuid()); SET i = i + 1; END WHILE; COMMIT; END| CREATE PROCEDURE conc_insert_t1() BEGIN DECLARE i int DEFAULT 1; SELECT COUNT(*) INTO @val FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID != CONNECTION_ID() AND info LIKE "CREATE INDEX%"; IF @val > 0 THEN SELECT "ALTER STARTED"; END IF; WHILE (@val > 0) DO INSERT INTO t1 VALUES (i, i, uuid(), uuid()); SET i = i + 1; SELECT COUNT(*) INTO @val FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID != CONNECTION_ID() AND info LIKE "CREATE INDEX%"; END WHILE; SELECT concat('Total number of inserts is ', i); END| DELIMITER ;| --disable_query_log CALL populate_t1(); --enable_query_log --connection con1 --send CREATE INDEX idx_title ON t1(title, title2); --connection default --sleep 1 --send CALL conc_insert_t1(); --connection con1 --reap --connection default --reap --disconnect con1 DROP TABLE t1; DROP PROCEDURE populate_t1; DROP PROCEDURE conc_insert_t1; --source include/wait_until_count_sessions.inc |
Numbers
1 2 3 4 5 6 7 8 9 10 11 12 | compressed 4k : number of concurrent inserts (Avg of 6 runs) ============== ============================ PS 5.7.25 (and earlier) : 2315 PS 5.7.26 (fix version) : 9785.66 (322% improvement compared to 5.7.25) (43% worse compared to 5.6) PS 5.6 : 17341 16K page size ============= PS 5.7.25 (and earlier) : 3007 PS 5.7.26 (fix version) : 4768.33 (58.5% improvement compared to 5.7.25) (3.4% worse compared to 5.6) PS 5.6 : 4939 |
