Feature Request: VReplication Defer Secondary Index Creation

[mattlord]

Feature Description

For MoveTables and Reshard , VReplication should defer secondary index creation for a table until after the table has been copied.

As a quick example, you can see ~ 20% improvement for a small dataset this way:

cd /tmp
mkdir /tmp/myidxtestdata
git clone https://github.com/mattlord/test_db.git
cd test_db

# Install MySQL 8.0.31: https://dev.mysql.com/downloads/mysql/
mysqld --initialize-insecure --datadir=/tmp/myidxtestdata
mysqld --no-defaults --datadir=/tmp/myidxtestdata &

# Load data with secondary indexes
time mysql -u root < ./employees.sql
# ~ 25 seconds on avg
mysql -u root -e "drop database employees"

# Load data without secondary indexes and add at the end
time mysql -u root < ./employees_deferred_keys.sql
# ~ 20 seconds on avg
mysql -u root -e "drop database employees"

mysqladmin -u root shutdown
rm -rf /tmp/myidxtestdata

In practice we've seen upwards of 10x improvement when importing tables that are hundreds of GiBs when creating the secondary indexes after the tables have been copied during a MoveTables.

The one-shot secondary index creation after table copy should be even faster with MySQL 8.0.31+ due to InnoDB support for parallel index builds:

InnoDB: InnoDB now supports parallel index builds, which improves index build performance. In particular, loading sorted index entries into a B-tree is now multithreaded. Previously, this action was performed by a single thread.

Use Case(s)

• Moving/Importing very large tables
• Resharding very large tables

Note: The value of doing this becomes exponential as the table size and number of secondary indexes grow. We've seen some cases where the time to copy a very large table goes from days to hours.