Asynchronous Query Execution with MySQL 5.7 X Plugin

In this blog, we will discuss MySQL 5.7 asynchronous query execution using the X Plugin.

Overview

MySQL 5.7 supports X Plugin / X Protocol, which allows (if the library supports it) asynchronous query execution. In 2014, I published a blog on how to increase a slow query performance with the parallel query execution. There, I created a prototype in the bash shell. Here, I’ve tried a similar idea with NodeJS + mysqlx library (which uses MySQL X Plugin).

TL;DR version: By using the MySQL X Plugin with NodeJS I was able to increase query performance 10x (some query rewrite required).

X Protocol and NodeJS

Here are the steps required:

1. First, we will need to enable X Plugin in MySQL 5.7.12+, which will use a different port (33060 by default).
2. Second, download and install NodeJS (>4.2) and mysql-connector-nodejs-1.0.2.tar.gz (follow Getting Started with Connector/Node.JS guide).
   
   Shell

   # node --version<br>v4.4.4<br># wget https://dev.mysql.com/get/Downloads/Connector-Nodejs/mysql-connector-nodejs-1.0.2.tar.gz<br># npm install mysql-connector-nodejs-1.0.2.tar.gz

   1	# node --version<br>v4.4.4<br># wget https://dev.mysql.com/get/Downloads/Connector-Nodejs/mysql-connector-nodejs-1.0.2.tar.gz<br># npm install mysql-connector-nodejs-1.0.2.tar.gz

   
   Please note: on older systems, you will probably need to upgrade the nodejs version. Follow the Installing Node.js via package manager guide.
3. All set! Now we can use the asynchronous queries feature.

Test data 

I’m using the same Wikipedia Page Counts dataset (wikistats) I’ve used for my Apache Spark and MySQL example. Let’s imagine we want to compare the popularity of MySQL versus PostgeSQL in January 2008 (comparing the total page views). Here are the sample queries:

The table size only holds data for English Wikipedia for January 2008, but still has ~200M rows and ~16G in size. Both queries run for ~5 minutes each, and utilize only one CPU core (one connection = one CPU core). The box has 24 CPU cores, Intel(R) Xeon(R) CPU L5639 @ 2.13GHz. Can we run the query in parallel, utilizing all cores?

That is possible now with NodeJS and X Plugin, but require some preparation:

1. Partition the table using hash, 24 partitions:
   
   MySQL

   CREATE TABLE `wikistats_by_day_spark_part` (<br> `id` int(11) NOT NULL AUTO_INCREMENT,<br> `mydate` date NOT NULL,<br> `url` text,<br> `cnt` bigint(20) NOT NULL,<br> `tot_visits` bigint(20) DEFAULT NULL,<br> PRIMARY KEY (`id`)<br>) ENGINE=InnoDB AUTO_INCREMENT=239863472 DEFAULT CHARSET=latin1<br>/*!50100 PARTITION BY HASH (id)<br>PARTITIONS 24 */

   1

   CREATE TABLE `wikistats_by_day_spark_part` (<br>  `id` int(11) NOT NULL AUTO_INCREMENT,<br>  `mydate` date NOT NULL,<br>  `url` text,<br>  `cnt` bigint(20) NOT NULL,<br>  `tot_visits` bigint(20) DEFAULT NULL,<br>  PRIMARY KEY (`id`)<br>) ENGINE=InnoDB AUTO_INCREMENT=239863472 DEFAULT CHARSET=latin1<br>/*!50100 PARTITION BY HASH (id)<br>PARTITIONS 24 */

2. Rewrite the query running one connection (= one thread) per each partition, choosing its own partition for each thread:
   
   MySQL

   select sum(tot_visits) from wikistats_by_day_spark_part partition (p<N>) where url like '%mysql%';

   1	select sum(tot_visits) from wikistats_by_day_spark_part partition (p<N>) where url like '%mysql%';

3. Wrap it up inside the NodeJS Callback functions / Promises.

The code

The explanation

The idea here is rather simple:

1. Find all the partitions for the table by using “select partition_name from information_schema.partitions”
2. For each partition, run the query in parallel: create a connection, run the query with a specific partition name, define the callback function, then close the connection.
3. As the callback function is used, the code will not be blocked, but rather proceed to the next iteration. When the query is finished, the callback function will be executed.
4. Inside the callback function, I’m saving the result into an array and also calculating the total (actually I only need a total in this example).
   
   JavaScript

   .execute(function (row) {<br> console.log(p + ":" + row);<br> res.push(row);<br> total = Number(total) + Number(row); ...

   1	.execute(function (row) {<br>                         console.log(p + ":" + row);<br>                         res.push(row);<br>                         total = Number(total) + Number(row); ...

Asynchronous Salad: tomacucumtoes,bersmayonn,aise *

This may blow your mind: because everything is running asynchronously, the callback functions will return when ready. Here is the result of the above script:

… here the script will wait for the async calls to return, and they will return when ready – the order is not defined.

Meanwhile, we can watch MySQL processlist:

And CPU utilization:

Now, here is our “salad”:

As we can see, all partitions are in random order. If needed, we can even sort the result array (which isn’t needed for this example as we only care about the total). Finally our result and timing:

Timing and Results

• Original query, single thread: 5 minutes
• Modified query, 24 threads in Node JS: 30 seconds
• Performance increase: 10x

If you are interested in the original question (MySQL versus PostgreSQL, Jan 2008):

• MySQL, total visits: 88935
• PostgreSQL total visits: 17753

Further Reading:

• NodeJS connector for MySQL: Getting Started
• X Plugin overview
• Connector/Node.js example in MySQL 5.7
• Callback functions concept

PS: Original Asynchronous Salad Joke, by Vlad @Crazy_Owl (in Russian)