road to MySQL parallel query

road to MySQL parallel queryFor 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; 
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:

Unfortunately it only works for count(*) from table without a “where” condition.

Conclusion: although this feature is currently limited it is a great start for MySQL and opens a road to real parallel query executions.


Photo by Vidar Nordli-Mathisen on Unsplash

Leave a Reply

Your email address will not be published. Required fields are marked *