MySQL 8.0 – InnoDB Parallel Threads for Online DDL Operations

MySQL 8.0.27 introduced a new variable to control the maximum of parallel threads InnoDB can use for creating (sorting and building) secondary indexes: innodb_ddl_threads.

This new variable is coupled with another new variable: innodb_ddl_buffer_size.

If you have fast storage and multiple CPU cores, tuning these variables can speed up secondary index creation.

For this example, I used the airportdb database, and I added a secondary index to the largest table, booking.

Let’s start with the default settings:

 SQL  alter table booking 
       add index idx_2(flight_id, seat, passenger_id);
Query OK, 0 rows affected (9 min 0.6838 sec)

The default settings in MySQL 8.0.27 are:

innodb_ddl_threads = 4
innodb_ddl_buffer_size = 1048576
innodb_parallel_read_threads = 4

The innodb_ddl_buffer_size is shared between all innodb_ddl_threads defined. If you increase the amount of threads, I recommend that you also increase the buffer size.

To find the best values for these variables, let’s have a look at the amount of CPU cores:

 SQL  select count from information_schema.INNODB_METRICS 
       where name = 'cpu_n';
+-------+
| count |
+-------+
|    16 |
+-------+

We have then 16 cores to share. As my machine as plenty of memory, I will allocate 1GB for the InnoDB DDL buffer.

 SQL  SET innodb_ddl_threads = 8;
 SQL  SET innodb_parallel_read_threads = 8;
 SQL  SET innodb_ddl_buffer_size = 1048576000;

 SQL  alter table booking add index idx_2(flight_id, seat, passenger_id);
Query OK, 0 rows affected (3 min 9.1862 sec)

Wow ! This is a nice improvement !

This is a table summary to see the impact of these variables:

ddl buffer sizeddl threadsparallel read theadsexecution time
1048576449 min 0.6838 sec
104857600884 min 8.3601 sec
1048576000883 min 9.1862 sec
104857600016163 min 7.4079 sec
10485760001683 min 4.1161 sec
10485760001243 min 8.7854 sec
10485760004123 min 5.9497 sec
1048576000443 min 12.2435 sec
2097152000442 min 43.6280 sec
2097152000882 min 44.6516 sec

We can see that with our system and our data, the best performance is achieved by increasing the buffer size used for the InnoDB DDL threads.

Does that mean only the buffer size matter ? Not really. Of course it depends of your data, with only one thread but with a buffer of 2G, the execution time is 3 min 22.9617 sec.

Also keeping increasing the buffer size won’t necessary improve the performance 😉

Please note that this feature also provides the possibility to build multiple indexes for a table in the same alter statement in parallel:

SQL  alter table booking add index idx_2(flight_id, seat, passenger_id), 
      add index idx_3(price, passenger_id), add index idx_4(seat,price);
Query OK, 0 rows affected (10 min 17.8435 sec)

With the default settings it takes 28 min 46.9949 sec.

But the same query on a MySQL Database Service instance on OCI with 32 cores is executed in 2 min 11.6700 sec with a buffer size of 2G !

This new way of dealing with online DDL in InnoDB is a nice improvement, congrats InnoDB team !

And as usual, enjoy MySQL !

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

2 Comments

  1. Thanks for the post LeFred, good to see MySQL getting better at using many cores for what was single-threaded operations in the past.

    From what I understand, index creation was single threaded in the past, so could only use one CPU. With the default of innodb_ddl_threads being 4 threads, an index creation could use 100% of 4 CPU. With more and more people running MySQLin the Cloud and in Containers, many MySQL instances in production have 4 CPU or less. On such systems, the default value of innodb_ddl_threads to 4 might be too big as an index creation could starve CPU for other operations. Can you share some rationale behind the default being 4 (I would have put 2 or even 1) ?

    And I know the default for innodb_page_cleaners is 4, but the page-cleaner threads are not as CPU intensive as a thread doing sorting. A default of 4 for innodb_page_cleaners might be good even on a “small” MySQL instance, while a default of 4 for innodb_ddl_threads might be catastrophic, especially in a Kubernetes / Container infrastructure where we might have CPU Throttling.

    Thanks in advance for the details, Jean-François Gagné

    • Hi Jean-François,
      Thank you for your comment.
      innodb_ddl_threads defines the MAXIMUM threads that can be used. So this doesn’t mean 4 threads will be used. I need to check the code to see what kind of control is made.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.