Bug #72736 [Com]: Slow performance when fetching large dataset with mysqli / PDO

Edit report at https://bugs.php.net/bug.php?id=72736&edit=1 ID: 72736 Comment by: jim dot hofer at gmail dot com Reported by: petr dot hrabal at gmail dot com Summary: Slow performance when fetching large dataset with mysqli / PDO Status: Verified Type: Bug Package: *Database Functions Operating System: Debian 8 PHP Version: 7.0.9, 7.1.0-dev Assigned To: dmitry Block user comment: N Private report: N New Comment: Just wanted to add this bug also affects Centos 6 & 7 and still present in PHP 7.0.12. USE_ZEND_ALLOC=0 appears to be a good temporary alternative, but also leads to other weird behavior. Like this bug https://bugs.php.net/bug.php?id=73370 Previous Comments: ------------------------------------------------------------------------ [2016-08-06 15:54:37] nikic@php.net It's not really necessary to bring mysql into this. Just allocating a bunch of strings without deallocating them in between is enough: <?php $n = 50000000; $s = "aaaaaaaaaaaaaaaaaaaaa"; $a = []; $t = microtime(true); for ($i = 0; $i < $n; $i++) { $s++; $a[] = $s; } var_dump(microtime(true) - $t); This gives me the following results with and without ZMM: nikic@saturn:~/php-src-fast$ sapi/cli/php -d memory_limit=-1 t043.php float(30.064018011093) nikic@saturn:~/php-src-fast$ USE_ZEND_ALLOC=0 sapi/cli/php -d memory_limit=-1 t043.php float(3.7853488922119) Generally the ZMM is somewhat faster than the system allocator, not 20x slower. Assigning to dmitry... ------------------------------------------------------------------------ [2016-08-06 15:32:25] nikic@php.net Can confirm this is the case. Some further observations: * In PHP 7 the time increases quadratically with the number of rows. In PHP 5 it increases linearly. * PHP 7 goes back to reasonable performance with USE_ZEND_ALLOC=0 * callgrind with --cache-sim suggests that we're seeing a crazy number of LL data read misses in zend_mm_alloc_pages (this line: https://github.com/php/php-src/blob/master/Zend/zend_alloc.c#L856) This being callgrind, I don't know whether this is true My wild guess: We're going through the list of chunks where the first N are all full until we hit one which isn't. At each chunk we experience an LL miss due to aligned address conflicts. ------------------------------------------------------------------------ [2016-08-02 16:10:49] petr dot hrabal at gmail dot com Expected resaul is that performance should be at least comparable and not dramaticaly worse ------------------------------------------------------------------------ [2016-08-02 14:55:57] petr dot hrabal at gmail dot com Description: ------------ I have large MySQL query (1.8M rows, 25 columns - all string between 20 and 256 chars) 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.9 or 7.1.0-dev same proble apears when PDO is used more extensive info/diagnostics at http://stackoverflow.com/questions/38614982/mysqli-fetch-assoc-performance-php5-4-vs-php7-0 with tested dataset PHP5.x performs 10 times faster Test script: --------------- $vysledek = mysqli_query ( $conn,"SELECT * FROM table WHERE 1"); while($zaznam = mysqli_fetch_assoc ( $vysledek )){ $table[$zaznam['prikey']] = $zaznam; } ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=72736&edit=1