[MDEV-16281] Implement parallel CREATE INDEX, ALTER TABLE, or bulk load Created: 2018-05-24  Updated: 2024-01-18

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Fix Version/s: 11.5

Type: Task Priority: Major
Reporter: Marko Mäkelä Assignee: Thirunarayanan Balathandayuthapani
Resolution: Unresolved Votes: 2
Labels: ddl, performance

Issue Links:
Blocks
is blocked by MDEV-16264 Implement a common work queue for Inn... Closed
Duplicate
duplicates MDEV-18508 create index cannot be done in parallel Closed
Relates
relates to MDEV-26740 Inplace alter rebuild increases file ... Closed
relates to MDEV-5171 Add support for --innodb-optimize-ke... Closed
relates to MDEV-11675 Lag Free Alter On Slave Closed
relates to MDEV-16223 Background ADD INDEX Closed
relates to MDEV-24621 In bulk insert, pre-sort and build in... Closed
relates to MDEV-32174 ROW_FORMAT=COMPRESSED table corruptio... Confirmed

 Description   

ALTER TABLE in InnoDB is single-threaded, except for the special case ADD FULLTEXT INDEX, which creates additional threads to create the partitioned inverted index in shorter time.

We should refactor ADD FULLTEXT INDEX to make use of the MDEV-16264 work queue and make ADD INDEX use of the background threads as well. To avoid disparity with smaller items in the work queue, the work should be split into smaller items, such as "merge sort 2 blocks" or "copy a sorted block to index pages".

 Comments   
Comment by Marko Mäkelä [ 2019-08-15 ]

When MDEV-11675 is implemented, we should ensure reasonable scheduling of resources on the replication slave.

Comment by Marko Mäkelä [ 2022-01-18 ]

This feature has been implemented in MySQL 8.0. If we used that for reference, we should also look for follow-up fixes, such as this one.

Comment by Marko Mäkelä [ 2022-12-09 ]

Starting with MDEV-24621, this would speed up loading data into initially empty tables as well.

Comment by Marko Mäkelä [ 2023-01-17 ]

As noted in MDEV-26740, ALTER TABLE…ALGORITHM=COPY does not currently use any bulk load mechanism. We merely disable row-level undo logging (MDEV-11415).

Comment by Marko Mäkelä [ 2023-05-22 ]

For the record, MySQL 8.0.33 is "removing bulk load component and multi-threaded index build feature."

Comment by Marko Mäkelä [ 2023-10-31 ]

MySQL 8.2.0 includes WL#14717 - InnoDB: Bulk Ingest Feature V1, which could serve as a useful reference.

Generated at Thu Feb 08 08:27:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.