MySQL 8.0.14: A Road to Parallel Query Execution is Wide Open!

January 23, 2019

Author

Alexander Rubin

Insight for DBAs

MySQL

Share this Post:

For a very long time – since when multiple CPU cores were commonly available – I dreamed about MySQL having the ability to execute queries in parallel. This feature was lacking from MySQL, and I wrote a lots of posts on how to emulate parallel queries in MySQL using different methods: from simple parallel bash script to using Apache Spark to using ClickHouse together with MySQL. I have watched parallelism coming to PostgreSQL, to new databases like TiDB, to Amazon Aurora… And finally: MySQL 8.0.14 has (for now limited) an ability to perform parallel query execution. At the time of writing it is limited to select count(*) from table queries as well as check table queries.

MySQL 8.0.14 contains this in the release notes: “As of MySQL 8.0.14, InnoDB supports parallel clustered index reads, which can improve CHECK TABLE performance.” Actually parallel clustered index reads also works for simple count(*) (without a “where” condition). You can control the parallel threads with the innodb_parallel_read_threads parameter.

Here is the simple test (machine has 32 cpu cores):

mysql> set local innodb_parallel_read_threads=1;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> select count(*) from ontime;<br>+-----------+<br>| count(*)  |<br>+-----------+<br>| 177920306 |<br>+-----------+<br>1 row in set (2 min 33.93 sec)<br><br>mysql> set local innodb_parallel_read_threads=DEFAULT; -- 4 is default<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> select count(*) from ontime;<br>+-----------+<br>| count(*)  |<br>+-----------+<br>| 177920306 |<br>+-----------+<br>1 row in set (21.85 sec)<br><br>mysql> set local innodb_parallel_read_threads=32;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> select count(*) from ontime;<br>+-----------+<br>| count(*)  |<br>+-----------+<br>| 177920306 |<br>+-----------+<br>1 row in set (5.35 sec)<br>

1

mysql> set local innodb_parallel_read_threads=1; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from ontime; +-----------+ | count(*) | +-----------+ | 177920306 | +-----------+ 1 row in set (2 min 33.93 sec) mysql> set local innodb_parallel_read_threads=DEFAULT; -- 4 is default Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from ontime; +-----------+ | count(*) | +-----------+ | 177920306 | +-----------+ 1 row in set (21.85 sec) mysql> set local innodb_parallel_read_threads=32; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from ontime; +-----------+ | count(*) | +-----------+ | 177920306 | +-----------+ 1 row in set (5.35 sec)

The following graph shows CPU utilization during the execution with 4 threads and 32 threads: