[MDEV-20830] Performance difference when executing multiple queries in 10.1 vs 10.3 and 10.4 Created: 2019-10-14  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Protocol, Server
Affects Version/s: 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Andre Nathan Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 18.04 VM, mariadb-server packages from MariaDB's Debian repository.


 Description   

I have a trivial PHP script using mysqli::multi_query (error handling ommited):

$queries = "...";
$db = new mysqli("localhost", $user, $pass, $database);
$db->multi_query($queries);
$db->close();

Here $queries refers to a large number (hundreds) of CREATE TABLE, ALTER TABLE and INSERT queries derived from a database migration system. I can provide them privately if needed.

When I run this script against MariaDB 10.1, it returns immediately, and a SHOW PROCESSLIST on the MariaDB console shows tables being created after the script has finished running. In other words, it's as if the queries run asynchronously.

If I upgrade to 10.3 or 10.4, the behavior changes and becomes "synchronous": the script takes minutes to run and only finishes once every query has run.

Is this a known behavior? Is there a configuration setting that can restore the behavior from 10.1?

I've tested this on an Ubuntu 18.04 VM with packages installed from MariaDB's APT repositories, using default configurations.

 Comments   
Comment by Elena Stepanova [ 2019-10-28 ]

Thanks for the report.

I can reproduce it, the behavior changed in 10.2 and up; but I can't tell whether it was an intentional change or not. The difference is that 10.1 doesn't wait for all the queries to be executed, unless you further fetch results of the queries, while 10.2+ waits in any case. Both might make sense, depending how you look at it. I can't find any clear indication in PHP documentation that multi_query is supposed to be asynchronous, but some loosely related discussions online suggest that it might be expected.

I'll leave it to the protocol experts to decide on this.

Here is an example of the difference.
The test below runs 100 INSERT statements and fetches all "results" of the multi_query before it proceeds with checking the final count. It returns 100 final rows on all versions, and the timing is the same on all versions, give or take.

Note: If you want to run the same script, provide port as the first and only command-line argument. Also, I'm using PHP 7.0. There is no guarantee the same script will work the same on other versions, it's changing significantly at times.

<?php
 
 
 
$queries = "DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2)";
 
 
 
$db1 = new mysqli("127.0.0.1:$argv[1]", 'root', '', 'test');
 
$db2 = new mysqli("127.0.0.1:$argv[1]", 'root', '', 'test');
 
 
 
$db1->multi_query($queries);
 
 
 
while ($db1->next_result()) {;}
 
 
 
$res= $db2->query("SELECT COUNT(*) AS c FROM t1")->fetch_object()->c;
 
echo "Number of rows: $res\n";
 
 
 
$db1->close();
 
$db2->close();

10.3 803d0521

 
Number of rows: 100
 
 
 
real	0m5.564s
 
user	0m0.012s
 
sys	0m0.000s

10.1 a41d4297

 
Number of rows: 100
 
 
 
real	0m5.100s
 
user	0m0.016s
 
sys	0m0.004s

The very same test, but without fetching "results" of the multi_query finishes immediately on 10.1 and returns a number of rows close to zero, while on 10.2+ it behaves as the first test and returns full count:

<?php
 
 
 
$queries = "DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2)";
 
 
 
$db1 = new mysqli("127.0.0.1:$argv[1]", 'root', '', 'test');
 
$db2 = new mysqli("127.0.0.1:$argv[1]", 'root', '', 'test');
 
 
 
$db1->multi_query($queries);
 
 
 
//while ($db1->next_result()) {;}
 
 
 
$res= $db2->query("SELECT COUNT(*) AS c FROM t1")->fetch_object()->c;
 
echo "Number of rows: $res\n";
 
 
 
$db1->close();
 
$db2->close();

10.3 803d0521

 
Number of rows: 100
 
 
 
real	0m5.663s
 
user	0m0.012s
 
sys	0m0.000s

10.1 a41d4297

 
Number of rows: 0
 
 
 
real	0m0.464s
 
user	0m0.012s
 
sys	0m0.000s

MySQL 5.6, 5.7, 8.0 behave as 10.1 above.

Generated at Thu Feb 08 09:02:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.