php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #69938 mysqlnd 50% slower to fetch all results than mysqli
Submitted: 2015-06-26 09:40 UTC Modified: 2023-01-15 21:43 UTC
Votes:1
Avg. Score:4.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:0 (0.0%)
From: turneliusz at gmail dot com Assigned: dharman (profile)
Status: Closed Package: PDO MySQL
PHP Version: 5.6.10 OS: Ubuntu
Private report: No CVE-ID: None
View Developer Edit
 [2015-06-26 09:40 UTC] 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)

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2023-01-15 21:14 UTC] dharman@php.net
-Status: Open +Status: Verified
 [2023-01-15 21:14 UTC] 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.
 [2023-01-15 21:43 UTC] dharman@php.net
-Status: Verified +Status: Closed -Assigned To: +Assigned To: dharman
 [2023-01-15 21:43 UTC] dharman@php.net 
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.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Thu Dec 26 10:01:29 2024 UTC