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):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | 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
Thanks for your post. I will try it. I was also thinking why mysql don’t have parallel query execution like in Oracle ,MSSQL and PostgreSQL.
Well, I would say it’s not bad for a start. Baby steps 🙂
Hi Alexander,
Thank you for the post – it is very good news. It means that Oracle/MySQL team is starting to address real urgent needs of MySQL users. However, talking about MySQL parallel query implementation we should mention Justin Swanhart who
released the first implementation for MySQL parallel queries in 2014 – five years ago. Unfortunately, it was quite difficult to install.
What surprises me that if this is such important and fundamental feature (and I think, it is), why it was mentioned so briefly in corresponding documentation on MySQL site? Does it mean that Sunny Bains (its implementor according to
blog https://mysqlserverteam.com/the-mysql-8-0-14-maintenance-release-is-generally-available/) just a little bit shy of his achievement? Or this parallel query execution is just hack-like use of new functionality? If this is just a hack, it could be
that we have to wait quite a long time for the second step in MySQL parallel query implementation.
I am also not sure how innodb-parallel-read-threads flag works in case of MySQL server running in virtual machine. According to the article, the higher number of threads leads to improved performance. It means that increasing the number of virtual CPUs should be beneficial. However, we also know (https://theithollow.com/2013/01/21/the-effect-of-too-many-virtual-cpus/) that too many virtual CPUs have decreasing effect on server performance. I wonder how this contradiction could be resolved? Is it possible to resolve it at all?
Any further updates on when MySQL team will take this to the next step?