3

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.

NewRewlic shows more 100ms spend in PHP after switching to mysqlnd https://db.tt/68r9RfhJ

I've created a benchmark to reproduce the problem, it's quite simple:

$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)

Version information:

  • PHP Version: PHP 5.5.9-1ubuntu4.9
  • Client API version => mysqlnd 5.0.11-dev - 20120503 - $Id: bf9ad53b11c9a57efdb1057292d73b928b8c5c77 $
  • Client API library version => mysqlnd 5.0.11-dev - 20120503 - $Id: bf9ad53b11c9a57efdb1057292d73b928b8c5c77 $

Can anybody explain me what's the reason behind it and how to solve it?

  1. Slowness occurs for PDO mainly.
  2. mysqli doesn't seem to be that much affected.
  3. I cannot switch to mysqli due to usage of Doctrine.
  4. Only way to catch up libmysql speed is to disable buffering from queries for MySQL (setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false)) which I cannot do easily on production because of character of change and possible side-effects.
Tuner
  • 69
  • 7

1 Answers1

0

mysqlnd isn't slower. It's just more efficient on memory. Instead of fetching your results from mysql by copying all of the data over, PHP becomes the same process that talks to mysql and stores the data in memory. So you're ultimately using half the memory, but the added computational cost might be in having to allocate that memory within the PHP process. You likely are just seeing that shift from mysql to PHP in newrelic. I highly doubt you have anything to be concerned about in terms of performance here.

Sherif
  • 11,786
  • 3
  • 32
  • 57
  • I'm sure that is not about shifting costs from MySQL to PHP as PDO is still buffering the whole query result as "PDO::MYSQL_ATTR_USE_BUFFERED_QUERY" is enabled by default. So installing mysqlnd shouldn't magically overridden that behaviour. libmysql is having 2 copies of the data while mysqlnd should have only one and that should lower the computation needs (copying the data). – Tuner Jun 17 '15 at 15:14
  • As long as you're using `PDO::fetchAll()` throughout your code you'd be fine to turn of `PDO::MYSQL_ATTR_USE_BUFFERED_QUERY`. In fact, that's what is [recommended in the manual for PDO MySQL driver](http://php.net/manual/en/ref.pdo-mysql.php). – Sherif Jun 17 '15 at 16:52
  • Also, FWIW, your benchmark is a pretty bad one. You're preparing the statement with every iteration of the loop. You would never do that in practice, as such you're just bench marking the wrong thing. – Sherif Jun 17 '15 at 16:57
  • I'm trying to benchmark multiple completely separate queries, so my I feel that the benchmark actually shows properly the slowdown. About buffered queries, I'm pretty afraid of doing that on production as "This uses less memory on the PHP-side, but can increase the load on the server. Unless the full result set was fetched from the server no further queries can be sent over the same connection". I have a doubt how that will behave with persistent connection. Most of all, I'm quite surprised how just switching the driver is causing so noticeable slowdown. – Tuner Jun 18 '15 at 07:55