mysqli_fetch_assoc() performance PHP5.4 vs PHP7.0

Question

I have large MySQL query (1.8M rows, 25 columns) and I need to make 2 dimensional array from it (memory table based on primary key).

Code works as expected, but $table creation takes a long time in PHP7.0.

What is the reason why PHP7.0 performs so much worse? My primary interest is in mysqli.

Thank you for any insights - PHP7 would save me much memory if I can fix performance.

mysqli code snippet

$start = microtime(true);

$vysledek = cluster::query("SELECT * FROM `table` WHERE 1");
$query_time = (microtime(true) - $start);
$start_fetch = microtime(true);
while($zaznam = mysqli_fetch_assoc ( $vysledek )){
  $fetch_time+= (microtime(true) - $start_fetch);
  $start_assign = microtime(true);
  $table[$zaznam['prikey']] = $zaznam;
  $assign_time+= (microtime(true) - $start_assign);
  $start_fetch = microtime(true);
}
$total_time+= (microtime(true) - $start);
echo round($assign_time, 2).' seconds to set the array values\n';
echo round($query_time, 2).' seconds to execute the query\n';
echo round($fetch_time, 2).' seconds to fetch data\n';
echo round($total_time, 2).' seconds to execute whole script\n';
echo "Peak Memory Usage:".round(memory_get_peak_usage(true)/(1024 * 1024), 2)." MB\n";

mysqli results

Deb 7 PHP 5.4 mysqlnd 5.0.10
1.8 seconds to set the array values
8.37 seconds to execute the query
13.49 seconds to fetch data
24.42 seconds to execute whole script
Peak Memory Usage:8426.75 MB

Deb 8 PHP 5.6 mysqlnd 5.0.11-dev
1.7 seconds to set the array values
8.58 seconds to execute the query
12.55 seconds to fetch data
23.6 seconds to execute whole script
Peak Memory Usage: 8426.75 MB

Deb 8 PHP 7.0 mysqlnd 5.0.12-dev
0.73 seconds to set the array values
8.63 seconds to execute the query
126.71 seconds to fetch data
136.46 seconds to execute whole script
Peak Memory Usage:7394.27 MB

Deb 8 PHP 7.0 mysqlnd 5.0.12-dev extended benchmarking

I have extended benchmarking for section fetch to report every 100k lines with following results:

Lines fetched 100000 in 1.87s
Lines fetched 300000 in 5.24s
Lines fetched 500000 in 10.97s
Lines fetched 700000 in 19.17s
Lines fetched 900000 in 29.96s
Lines fetched 1100000 in 43.03s
Lines fetched 1300000 in 58.48s
Lines fetched 1500000 in 76.47s
Lines fetched 1700000 in 96.73s
Lines fetched 1800000 in 107.78s

DEB8 PHP7.1.0-dev libclient 5.5.50

1.56 seconds to set the array values
8.38 seconds to execute the query
456.52 seconds to fetch data
467.68 seconds to execute whole script
Peak Memory Usage:8916 MB

DEB8 PHP7.1.0-dev libclient 5.5.50 extended benchmarking

Lines fetched 100000 in 2.72s
Lines fetched 300000 in 15.7s
Lines fetched 500000 in 38.7s
Lines fetched 700000 in 71.69s
Lines fetched 900000 in 114.8s
Lines fetched 1100000 in 168.18s
Lines fetched 1300000 in 231.69s
Lines fetched 1500000 in 305.36s
Lines fetched 1700000 in 389.05s
Lines fetched 1800000 in 434.71s

DEB8 PHP7.1.0-dev mysqlnd 5.0.12-dev

1.51 seconds to set the array values
9.16 seconds to execute the query
261.72 seconds to fetch data
273.61 seconds to execute whole script
Peak Memory Usage:8984.27 MB

DEB8 PHP7.1.0-dev mysqlnd 5.0.12-dev extended benchmarking

Lines fetched 100000 in 3.3s
Lines fetched 300000 in 13.63s
Lines fetched 500000 in 29.02s
Lines fetched 700000 in 49.21s
Lines fetched 900000 in 74.56s
Lines fetched 1100000 in 104.97s
Lines fetched 1300000 in 140.03s
Lines fetched 1500000 in 180.42s
Lines fetched 1700000 in 225.72s
Lines fetched 1800000 in 250.01s

PDO code snippet

$start = microtime(true);
$sql = "SELECT * FROM `table` WHERE 1";
$vysledek = $dbh->query($sql, PDO::FETCH_ASSOC);
$query_time = (microtime(true) - $start);
$start_fetch = microtime(true);
foreach($vysledek as $zaznam){
  $fetch_time+= (microtime(true) - $start_fetch);
  $start_assign = microtime(true);
  $table[$zaznam['prikey']] = $zaznam;
  $assign_time+= (microtime(true) - $start_assign);
  $start_fetch = microtime(true);
}
$total_time+= (microtime(true) - $start);
echo round($assign_time, 2).' seconds to set the array values\n';
echo round($query_time, 2).' seconds to execute the query\n';
echo round($fetch_time, 2).' seconds to fetch data\n';
echo round($total_time, 2).' seconds to execute whole script\n';
echo "Peak Memory Usage:".round(memory_get_peak_usage(true)/(1024 * 1024), 2)." MB\n";

PDO Results

Deb 7 PHP 5.4 mysqlnd 5.0.10
1.85 seconds to set the array values
12.51 seconds to execute the query
16.75 seconds to fetch data
31.82 seconds to execute whole script
Peak Memory Usage:11417.5 MB

Deb 8 PHP 5.6 mysqlnd 5.0.11-dev
1.75 seconds to set the array values
12.16 seconds to execute the query
15.72 seconds to fetch data
30.39 seconds to execute whole script
Peak Memory Usage:11417.75 MB

Deb 8 PHP 7.0 mysqlnd 5.0.12-dev
0.71 seconds to set the array values
35.93 seconds to execute the query
114.16 seconds to fetch data
151.19 seconds to execute whole script
Peak Memory Usage:6620.29 MB

Baseline comparison code

 $start_query = microtime(true);
 exec("mysql --user=foo --host=1.2.3.4 --password=bar -e'SELECT * FROM `profile`.`table`' > /tmp/out.csv");
 $query_time = (microtime(true) - $start_query);
 echo round($query_time, 2).' seconds to execute the query \n';

Execution time is similar for all systems at 19 seconds +-1 second variation.

Based on above observations I would say that PHP 5.X is reasonable as there is a bit more work executed than just dumping to the file.

• all 3 servers are on same host (source and both test servers)
• tests are consistent when repeated
• there is already similar variable in memory ,I need to do it for comparison removed for testing, is not related to the problem
• CPU is at 100% whole time
• Both servers have 32G RAM and swappiness set to 1, goal is to perform it as memory operation
• test server is dedicated, there is nothing else running
• php.ini changed between major versions but all options relating to mysqli/PDO seems to be the same

• Deb8 machine was downgraded to PHP5.6 and issue disappeared, after reinstalling PHP7 its back

• Reported a bug at php.net - ID 72736 since I belive that it was proven that problem is in PHP and not in the system or any other configuration

Edit 1 : Added PDO Comparison

Edit 2 : Added benchmarking markers, edited PDO results as there was benchmarking error

Edit 3 : Major cleanup in original question, rebuild of code snipets for better indication of the error

Edit 4 : added point about Downgrade and upgrade of PHP

Edit 5 : added extended benchmarking for DEB8 PHP7.0

Edit 6 : included php7 config

Edit 7 : performance measurement for PHP 7.1 dev with both libraries- compiled with configs from bishop, removed my php-config

Edit 8 : added comparison against CLI command, minor clean-ups

Answer 1

For cross-reference: With the release of PHP 7.1 on 1st Dec 2016 this issue should be resolved (in PHP 7.1).

PHP 7.0: Even in the ticket it's written that PHP-7.0 has been patched, I've not yet seen in the recent change-log (7.0.13 on 10 Nov 2016, since patch incorporation date) that this is part of the current PHP 7.0.x release. Perhaps with the next release.

The bug is tracked upstream (thanks to OP's report): Bug #72736 - Slow performance when fetching large dataset with mysqli / PDO (bugs.php.net; Aug 2016).

Answer 2

As the problem appears to be in the fetch (not the array creation), and we know the driver is running mysqlnd (which is a driver library independently written by the PHP team, not provided by MySQL AB aka Oracle), then recompiling PHP using libmysqlclient (which is the MySQL AB aka Oracle provided interface) may improve the situation (or at least narrow the problem space).

First thing I'd suggest is writing a small script that can be run from the CLI that demonstrates the problem. This will help to eliminate any other variables (web server modules, opcache, etc).

Then, I'd suggest rebuilding PHP with libmysqlclient to see if performance improves. Quick guide to rebuilding PHP (for the technically competent):

1. Download the source for the PHP version you want
2. Decompress and go into the PHP code directory
3. Run ./buildconf
4. Run ./configure --prefix=/usr --with-config-file-path=/etc/php5/apache2 --with-config-file-scan-dir=/etc/php5/apache2/conf.d --build=x86_64-linux-gnu --host=x86_64-linux-gnu --sysconfdir=/etc --localstatedir=/var --mandir=/usr/share/man --enable-debug --disable-static --with-pic --with-layout=GNU --with-pear=/usr/share/php --with-libxml-dir=/usr --with-mysql-sock=/var/run/mysqld/mysqld.sock --enable-dtrace --without-mm --with-mysql=shared,/usr --with-mysqli=shared,/usr/bin/mysql_config --enable-pdo=shared --without-pdo-dblib --with-pdo-mysql=shared,/usr CFLAGS="-g -O2 -fstack-protector-strong -Wformat -Werror=format-security -O2 -Wall -fsigned-char -fno-strict-aliasing -g" LDFLAGS="-Wl,-z,relro" CPPFLAGS="-D_FORTIFY_SOURCE=2" CXXFLAGS="-g -O2 -fstack-protector-strong -Wformat -Werror=format-security"
5. Run make && make test
6. Walk away
7. Run sapi/cli/php -i and confirm the version and presence of libmysqlclient

Run your test again. Any better?