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 comparisonremoved 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
