Bug #69938 [Ver->Csd]: mysqlnd 50% slower to fetch all results than mysqli
Edit report at https://bugs.php.net/bug.php?id=69938&edit=1
ID: 69938
Updated by: dharman@php.net
Reported by: turneliusz at gmail dot com
Summary: mysqlnd 50% slower to fetch all results than mysqli
-Status: Verified
+Status: Closed
Type: Bug
Package: PDO MySQL
Operating System: Ubuntu
PHP Version: 5.6.10
-Assigned To:
+Assigned To: dharman
Block user comment: N
Private report: N
New Comment:
Actually, I made a huge mistake. I was testing using DEBUG build. I redid it using RELEASE and these
are the results:
LIMIT 100
mysqlnd 0.0001512
libmysql 0.0001556
SELECT name
mysqlnd 0.0010228
libmysql 0.0009312
SELECT *
mysqlnd 0.0018528
libmysql 0.0018880
All measurements fall within margin of error. Therefore, there is no difference. The main reason why
we see the difference in DEBUG build is because mysqlnd contains a lot of debug statements. They are
unnecessary but they are executed in DEBUG mode and noop in RELEASE.
Therefore, I am closing this bug report as not reproducible. If someone can reproduce bad
performance with mysqlnd, please raise a detailed issue on GH.
Previous Comments:
------------------------------------------------------------------------
[2023-01-15 21:14:25] dharman@php.net
Tested with SELECT * FROM world.city:
- libmysql 0.0044s / execution
- mysqlnd 0.0050s / execution
Selecting only the name column:
- libmysql 0.0019s
- mysqlnd 0.0025s
This suggests that there's some static overhead when using mysqlnd. Connection time is not
included. Only prepare/execute/fetchAll is measured.
Limiting to 100 rows:
- libmysql 0.000226s
- mysqlnd 0.000235s
At this scale, the numbers vary too much for an accurate reading. This is an average of 1000 runs,
but still the difference is within a margin of error.
Tested on git.master as of 2023-01-14.
The benchmark shows that there is a considerable performance loss when using mysqlnd. In any real
world application this would be unnoticeable. Given that the difference doesn't scale with the
number of columns I am doubtful if the difference would ever be 50% as the ticket claims. I'd
rather blame it on inaccurate measurements.
So the ticket is confirmed. But the question is what's causing worse performance for mysqlnd
and can we fix it? I would expect mysqlnd to have a better performance than libmysql.
------------------------------------------------------------------------
[2015-06-26 09:40:21] turneliusz at gmail dot com
Description:
------------
I'm just switching to mysqlnd to be able to use http://php.net/manual/en/book.mysqlnd-ms.php
but I see some serious performance degradation compared to libmysql. That degradation is not
documented and PHP documentation states that mysqlnd is dropped in replacement when in fact.
NewRewlic for my application shows more 100ms spend in PHP after switching to mysqlnd https://db.tt/68r9RfhJ
I've created a benchmark to reproduce to problem, it's quite simple (ignore the fact
I'm creating prepared statement in every iteration; it's just for testing only):
Orders table contains 91 columns.
$dbh = new PDO("mysql:host=$host; dbname=$dbname;", $user, $pass);
for ($i = 0; $i < 1000; $i++) {
$sth = $dbh->prepare("SELECT * FROM Orders LIMIT 100");
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_ASSOC);
}
Execution time:
libmysql - 2 seconds
mysqlnd - 3 seconds (50% more)
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=69938&edit=1
Thread (3 messages)