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

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

PREVIOUS POST
NEXT POST

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):

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

PREVIOUS POST
NEXT POST

Share this post

Comments (3)

  • Adam Mulla Reply

    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.

    January 23, 2019 at 1:23 pm
  • Abbas Ahmed Reply

    Well, I would say it’s not bad for a start. Baby steps 🙂

    January 24, 2019 at 8:54 am
  • Jacob Reply

    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?

    January 24, 2019 at 5:23 pm

Leave a Reply