Magento 2 Asynchronous MySQL query processing with Mage/DB2

Yegor Shytikov
5 min readDec 13, 2024

--

A new feature of the Mage/DB2 package. Now, you can send async queries to the database async. Asynchronous queries in a Magento application can significantly improve performance and scalability.

Improved Magento Performance with Parallel Query Execution

Magento often executes multiple database queries in sequence during page generation, such as fetching product data, customer information, and pricing rules. Asynchronous queries enable Magento to send multiple queries simultaneously, reducing the overall execution time by parallelizing database operations.

Reduced TTFB Latency

Magento storefronts often deal with high traffic, requiring fast response times to maintain user satisfaction. Asynchronous queries avoid blocking operations, allowing the application to handle other tasks while waiting for database responses.

Scalability for Large Data Sets

Magento 2 and Asobe commerce frequently processes large datasets, such as bulk product updates, order processing, or customer data synchronization. Asynchronous queries reduce the time required to fetch or update large volumes of data, making the system more scalable.

Better Resource Utilization

Magento’s PHP threads often wait idle for database responses, wasting computational resources. Asynchronous queries free up PHP threads to execute other tasks, optimizing server utilization and improving throughput.

Tests

500 products insert in 10 threads:`

View SQL Insert Time :0.21748018264771
View SQL Insert Time :0.24501204490662
View SQL Insert Time :0.18011999130249
View SQL Insert Time :0.18140506744385
View SQL Insert Time :0.27077198028564
View SQL Insert Time :0.17856001853943
View SQL Insert Time :0.1782751083374
View SQL Insert Time :0.18860292434692

500 products insert in 1 thread:

Error:   SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders  

200 products 10 threads await

View SQL Insert Time :0.11653995513916
View SQL Insert Time :0.092501163482666
View SQL Insert Time :0.093584060668945
View SQL Insert Time :0.093992948532104
View SQL Insert Time :0.094542980194092
View SQL Insert Time :0.095008134841919
View SQL Insert Time :0.095669984817505
View SQL Insert Time :0.098028898239136
View SQL Insert Time :0.11339998245239
View SQL Insert Time :0.093953847885132
View SQL Insert Time :0.095566034317017
View SQL Insert Time :0.093995094299316

200 products insert in 5 threads await


View SQL Insert Time :0.13209891319275
View SQL Insert Time :0.10974884033203
View SQL Insert Time :0.10141015052795
View SQL Insert Time :0.10025882720947
View SQL Insert Time :0.10012912750244
View SQL Insert Time :0.10411214828491
View SQL Insert Time :0.10366082191467
View SQL Insert Time :0.10421204566956

200 products insert in 1 thread

View SQL Insert Time :0.24119591712952
View SQL Insert Time :0.19277191162109
View SQL Insert Time :0.19348692893982
View SQL Insert Time :0.23045802116394
View SQL Insert Time :0.22797894477844
View SQL Insert Time :0.19504189491272
View SQL Insert Time :0.19307494163513

However, that is not all. We have await/async functionality. So we can send queries and do other tasks. Let's do next. We are selecting 200 products, sending insert async, and sending a query to get the next batch of the products while inserting async. When we have selected a new batch, we are waiting (awaiting) for the stuff we requested before. We can send it without waiting, but we have removed this opportunity to avoid issues.

200 products insert in 5 threads async

SQL query to batch: 0.10768413543701 ms

Send Async Time :0.0010461807250977
Async aWait Time :0.065101146697998
Async aWait Time :0.065010070800781
Async aWait Time :0.063712120056152
Async aWait Time :0.066519021987915
Async aWait Time :0.062978982925415
Async aWait Time :0.064253091812134
Async aWait Time :0.063701152801514
Async aWait Time :0.065937995910645
Async aWait Time :0.069861888885498
Async aWait Time :0.078459024429321

200 products insert in 10 threads async

Async aWait Time :0.059938907623291
Async aWait Time :0.060409069061279
Async aWait Time :0.066435098648071
Async aWait Time :0.046823024749756
Async aWait Time :0.043323993682861
Async aWait Time :0.068089008331299
Async aWait Time :0.050564050674438
Async aWait Time :0.054868936538696
Async aWait Time :0.057026863098145
Async aWait Time :0.051629066467285
Async aWait Time :0.052289962768555
Async aWait Time :0.060111999511719
Async aWait Time :0.04675817489624
Async aWait Time :0.045651912689209
Async aWait Time :0.063713073730469
Async aWait Time :0.050109148025513
Async aWait Time :0.05135703086853

Let's add 1 second sleep before we await:

Async aWait Time :0.0011608600616455
Async aWait Time :0.0011579990386963
Async aWait Time :0.0011529922485352
Async aWait Time :0.0011558532714844
Async aWait Time :0.0011579990386963
Async aWait Time :0.0011551380157471
Async aWait Time :0.0011539459228516
Async aWait Time :0.0011558532714844

Let's do small queries like insert 10 products 10 threads

Async aWait Time :0.0010898113250732
Async aWait Time :0.0010900497436523
Async aWait Time :0.0010881423950195
Async aWait Time :0.0010988712310791
Async aWait Time :0.0010900497436523
Async aWait Time :0.001086950302124
Async aWait Time :0.0010871887207031
Async aWait Time :0.0010929107666016
Async aWait Time :0.0010931491851807

Let’s do small queries like insert 10 products 10 threads


View SQL Insert Time :0.010526895523071
View SQL Insert Time :0.01154613494873
View SQL Insert Time :0.010470867156982
View SQL Insert Time :0.010484933853149
View SQL Insert Time :0.0094289779663086
View SQL Insert Time :0.01050877571106
View SQL Insert Time :0.011559963226318

So, async SQL is better for Magento for slow and big queries when preparing the next batch of SQL queries.

ASYNC BIg Select performance

Select 1000 Product Sync:

Select Big SQL Time :0.083328008651733
Select Big SQL Time :0.083254098892212

Let's divide on 5 SQL 200 products per thread

Select Big SQL Time :0.1721818447113
Select Big SQL Time :0.1721818447113
Select Big SQL Time :0.1721818447113

Let’s divide on 5 SQL 200 products per thread async

Await Time: 0.13724684715271

What the issue here. The data upload still happens synchronously. And we are waiting for the data upload time…. mysqli_reap_async_query — gets SQL data over the network, but the query is slow. The best way here will be to use it for really slow queries or insert or delete when you don’t need to wait and no huge data chanks are returned. It can also be connected with limited Mysql REsources for multi-query. I have just 8 CPUS

What's interesting there? When we select 100 products, data by ten products in 5 queries:

        $sql[] = DB::table(myTable)->selectRaw('SQL_NO_CACHE *')->orderBy('entity_id')->limit(10)->toSql();
$sql[] = DB::table(myTable)->selectRaw('SQL_NO_CACHE *')->orderBy('entity_id')->skip(20)->take(40)->toSql();
$sql[] = DB::table(myTable)->selectRaw('SQL_NO_CACHE *')->orderBy('entity_id')->skip(40)->take(60)->toSql();
$sql[] = DB::table(myTable)->selectRaw('SQL_NO_CACHE *')->orderBy('entity_id')->skip(60)->take(80)->toSql();
$sql[] = DB::table(myTable)->selectRaw('SQL_NO_CACHE *')->orderBy('entity_id')->skip(80)->take(100)->toSql();

$result = Async::instance()->sendAsync($sql, debug:false, await:false);

We have better results:

Await Time: 0.009840011596679
and to send data
Select Smal SQL Async 5/20 Time: 0.00040507316589355

So we can make small queries faster!

We have 10ms vs 60ms+ one query

One query has a big result

Select Big SQL Single Query Time: 0.060435056686401

there is a Video example of How Async SQL works. Async MySQL Magento 2 resolves Mysql queries asynchronously in different MySQL threads in one PHP process.

Video URL : https://www.linkedin.com/posts/yehorshytikov_async-mysql-magento-2-example-you-can-resolve-activity-7274887250176274432-uOT8

--

--

Yegor Shytikov
Yegor Shytikov

Written by Yegor Shytikov

True Stories about Magento 2. Melting down metal server infrastructure into cloud solutions.

No responses yet