Learn how the parallel index rebuild feature in MySQL 8.0.31 improves performance by optimizing index and column additions. Explore the multithreaded insert phase, sort index build process, and system configurations for enhanced efficiency.

1. Understanding Sorted Index Build
   1. When does InnoDB use a sort index build approach?
2. Phases of Sort Index Build
   1. Multithreaded Insert Phase in MySQL 8.0.31
3. System Configurations and Performance Testing
4. Leveraging Parallel Index Rebuild for Performance Optimization

In this blog, we’ll explore the parallel index rebuild feature in MySQL 8.0.31. If you’ve ever encountered slow index or column additions, this feature can significantly speed up the process. We’ll discuss the multithreaded insert phase and the sort index build method. Additionally, we’ll cover system configurations and performance testing to help you optimize your database’s efficiency. By leveraging parallel index rebuild, you can maximize the performance of your MySQL 8.0.31 database. Let’s dive in and enhance your database performance together.

Understanding Sorted Index Build

Starting from version 5.7, MySQL utilizes the sorted index build method when creating secondary indexes. It involves sorting the data based on the index key before building the index structure. This sorting process ensures that the index entries are organized in the desired order, enabling efficient data retrieval and query performance.

When does InnoDB use a sort index build approach?

The sort index build approach is employed by InnoDB in the following scenarios:

• Adding a new index to an existing table: When you add a new index to a table in MySQL, InnoDB utilizes the sort index build approach. This ensures that the index entries are sorted appropriately, allowing for efficient data retrieval and improved query performance.
• Adding a new column using the ALGORITHM=INPLACE option: When you add a new column to a table using the ALGORITHM=INPLACE option, InnoDB employs the sort index build method. This ensures that the index for the new column is built in the desired order, enabling seamless integration with the existing table structure.
• Executing the OPTIMIZE TABLE command: When you execute the OPTIMIZE TABLE command on a table, InnoDB performs a rebuild of the table’s structure. This includes the index rebuild process using the sort index build approach. By rebuilding the indexes, InnoDB optimizes the table’s performance and storage efficiency.

Phases of Sort Index Build

The Sort Index Build process in InnoDB consists of three phases:

• Read/Run Phase: In this phase, InnoDB appends the primary key field to the secondary index.
• Merge Sort Phase: All index entries undergo a merge sort operation.
• Insert Phase: The sorted entries are loaded into the B-tree, which has become multithreaded since MySQL version 8.0.31. This phase marks the final step of the Sort Index Build approach.

Multithreaded Insert Phase in MySQL 8.0.31

The insert phase, which is the final step in the Sort Index Build approach, has been enhanced in MySQL 8.0.31 with multithreading capabilities. Previously, the insert phase was a single-threaded process, which could result in longer execution times for larger datasets. However, with the introduction of multithreading in MySQL 8.0.31, multiple threads can now work simultaneously to load the sorted index entries into the B-tree structure. This improvement allows for faster and more efficient index creation in InnoDB.

With the multithreaded insert phase, MySQL 8.0.31 provides an optimized and efficient approach to building sorted indexes, resulting in improved scalability and reduced index creation times.

System Configurations and Performance Testing

To evaluate the impact of the multithreaded insert phase and assess the performance improvements in MySQL 8.0.31, a series of performance tests were conducted using different system configurations.

Several variables introduced in MySQL 8.0.27 were adjusted during the tests to observe their impact on performance. These variables include:

• innodb_ddl_threads: This variable determines the number of threads used in online DDL operations for creating or rebuilding secondary indexes.

• innodb_ddl_buffer_size: It defines the maximum buffer size for DDL operations.

• innodb_parallel_read_threads: This variable controls the number of threads that can be utilized for parallel clustered index reads.

Version 8.0.31

 
Table size :
+----------+---------+---------------+
| Database | Table   | Table size GB |
+----------+---------+---------------+
| sbtest   | sbtest1 |         10.00 |
+----------+---------+---------------+

mysql> select @@version;
+-------------------------+
| @@version               |
+-------------------------+
| 8.0.31-0ubuntu0.22.04.1 |
+-------------------------+

mysql> set global innodb_ddl_threads=4;Query OK, 0 rows affected (0.00 sec)
mysql> set global innodb_parallel_read_threads=4;Query OK, 0 rows affected (0.00 sec)
mysql> set global innodb_ddl_buffer_size=1048576;Query OK, 0 rows affected (0.00 sec)
mysql> alter table sbtest1 add index idx_c(c);
	

While the ALTER query is being executed, you can run the following queries in a separate window concurrently to monitor the progress of the ALTER operation.

 
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) |         908306 |        1969933 |
| stage/innodb/alter table (merge sort)                |        1263170 |        2035529 |
+------------------------------------------------------+----------------+----------------+
2 rows in set (0.00 sec)

mysql> select THREAD_ID,NAME,TYPE from performance_schema.threads where type ='Background' and name like '%parallel%'\G
*************************** 1. row ***************************
THREAD_ID: 54
     NAME: thread/innodb/parallel_read_thread
     TYPE: BACKGROUND
*************************** 2. row ***************************
THREAD_ID: 55
     NAME: thread/innodb/parallel_read_thread
     TYPE: BACKGROUND
*************************** 3. row ***************************
THREAD_ID: 56
     NAME: thread/innodb/parallel_read_thread
     TYPE: BACKGROUND
*************************** 4. row ***************************
THREAD_ID: 57
     NAME: thread/innodb/parallel_read_thread
     TYPE: BACKGROUND
4 rows in set (0.00 sec)
	

Since the final insert phase in the sort index build is now multithreaded, we can observe that the innodb_ddl_threads are actively utilized in the background during the execution of the insert phase.

 
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) |         908306 |        1969933 |
| stage/innodb/alter table (insert)                    |        1734178 |        2035529 |
+------------------------------------------------------+----------------+----------------+

mysql> select THREAD_ID,NAME,TYPE from performance_schema.threads where type ='Background' and name like '%ddl%';
+-----------+--------------------------+------------+
| THREAD_ID | NAME                     | TYPE       |
+-----------+--------------------------+------------+
|        58 | thread/innodb/ddl_thread | BACKGROUND |
|        59 | thread/innodb/ddl_thread | BACKGROUND |
|        60 | thread/innodb/ddl_thread | BACKGROUND |
+-----------+--------------------------+------------+

	

The table below provides a summary of the various scenarios tested in MySQL versions 8.0.31 and 8.0.30. These tests involved modifying the values of Innodb parallel threads for Online DDL operations.

With the introduction of a multithreaded final insert phase in the sort index build, the execution time for creating secondary indexes in MySQL 8.0.31 has significantly improved. This enhancement allows for faster and more efficient creation of secondary indexes compared to previous versions.

Based on our system configuration and the data we tested, we observed that optimizing the performance of the InnoDB DDL threads in MySQL 8.0.31 can be achieved by increasing the buffer size (innodb_ddl_buffer_size). By allocating a larger buffer size, the sort index build process benefits from improved memory utilization, resulting in enhanced performance during secondary index creation.

Leveraging Parallel Index Rebuild for Performance Optimization

The parallel index rebuild feature in MySQL 8.0.31 offers benefits such as reduced execution time and improved resource utilization.

To optimize performance,

• Allocate sufficient resources,
• Adjust relevant variables like innodb_ddl_threads and innodb_ddl_buffer_size, monitor progress, and
• Conduct performance testing for fine-tuning.

This updated approach in MySQL 8.0.31 for handling the sort index build demonstrates a significant improvement in efficiency and performance compared to previous versions. The ability to optimize the buffer size for InnoDB DDL threads allows for better utilization of system resources, resulting in faster and more streamlined secondary index creation. It’s a noteworthy enhancement that contributes to a more efficient database management experience.

For more insightful discussions and further learning opportunities, stay tuned for upcoming posts. Visit our website to explore a wide range of informative blogs covering various topics in database consulting, support, and technology. If you found this blog post helpful, you’ll likely find other articles that spark your interest and offer valuable insights for your business or career.

If you need expert assistance in managing your database, don’t hesitate to reach out to us. Our highly skilled professionals are dedicated to providing tailored solutions that meet your specific needs, ensuring the security, optimization, and accessibility of your data. Let us help you unleash the full potential of your data.

{{cta}}