Bug #48343 [Com]: Mysqlnd memory leak when fetching rows

Edit report at http://bugs.php.net/bug.php?id=48343&edit=1 ID: 48343 Comment by: rick at igabe dot us Reported by: mikec at mikenz dot geek dot nz Summary: Mysqlnd memory leak when fetching rows Status: Bogus Type: Bug Package: MySQLi related Operating System: * PHP Version: 5.3CVS-2009-05-20 (snap) New Comment: This does seem to be related to mysqlnd only. However, it can become a large problem if many results are returned from a query. Each call to mysql_fetch_assoc() for a single result set increases the memory used. If the memory limit is reached, the script dies with a fatal error. Switching to ext/mysql does not produce the problem. Is this really intended behavior? Previous Comments: ------------------------------------------------------------------------ [2009-05-24 18:08:57] johannes@php.net Yes the memory calculation is now more precise and mysqlnd uses some own caching so it will re use some of the memory on your next query. ------------------------------------------------------------------------ [2009-05-21 13:02:49] mikec at mikenz dot geek dot nz Most of the memory is available again once it is destroyed. Very similar between both versions. The problem is that code which loops over large number of rows in php 5.2 with near constant memory usage now increase in memory usage as it loops through the rows in 5.3 causing termination when they hit the memory limit. Reported memory usage for a script in php 5.3 is a lot higher than in php 5.2. This bug may be bogus as mysqlnd uses php memory allocation so its memory usage is now included in memory usage checks where libmysql's memory usage in 5.2 was independent and uncounted. Can a developer confirm? If so this should be noted in the PHP 5.3 upgrading notes or migration information as it is unexpected at the moment. ------------------------------------------------------------------------ [2009-05-21 11:36:58] ninzya at inbox dot lv Try destroying mysqli object before calculating estimated memory usage. See what are results. ------------------------------------------------------------------------ [2009-05-20 05:56:05] mikec at mikenz dot geek dot nz Description: ------------ A simple loop fetching rows from a database uses a lot more memory of php5.3 when compared to php5.2. First noticed on a Debian machine. Confirmed on a Windows machine with latest snaps (20-May-2009). php.ini based on php.ini-dist/development only change is to enable mysqli. Reproduce code: --------------- <?php /** Mysql test table: CREATE TABLE test ( id int(11) NOT NULL auto_increment, value int(11) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO test VALUES (1, 910044366),(2, 598209383),(3, 1057704306),(4, 877739287),(5, 260395243),(6, 1154353180),(7, 19390630),(8, 362285832),(9, 510459913),(10, 222644712),(11, 824641253),(12, 969677378),(13, 1131665776),(14, 263701992),(15, 409107462); */ $before = memory_get_usage(); echo "Memory Usage (in bytes)"; echo "\nBefore: " . $before; $db = new mysqli("127.0.0.1", "test", "testpasswd", "test"); $result = $db->query('SELECT * FROM test'); $rows = 0; while($result->fetch_array(MYSQLI_ASSOC)) {$rows++;} $after = memory_get_usage(); echo "\nAfter: " . $after; echo "\nDelta: " . ($after - $before); echo "\nDelta per row: " . round(($after - $before)/$rows) . ' bytes per row'; Expected result: ---------------- C:\test\php-5.2-nts-win32-VC6-x86-latest>php -v PHP 5.2.10-dev (cli) (built: May 19 2009 11:46:39) Copyright (c) 1997-2009 The PHP Group Zend Engine v2.2.0, Copyright (c) 1998-2009 Zend Technologies C:\test\php-5.2-nts-win32-VC6-x86-latest>php r:\dbmem.php Memory Usage (in bytes) Before: 55216 After: 56080 Delta: 864 Delta per row: 58 bytes per row Actual result: -------------- C:\test\php-5.3-nts-win32-VC9-x86-latest>php -v PHP 5.3.0RC3-dev (cli) (built: May 19 2009 12:57:00) Copyright (c) 1997-2009 The PHP Group Zend Engine v2.3.0, Copyright (c) 1998-2009 Zend Technologies C:\test\php-5.3-nts-win32-VC9-x86-latest>php r:\dbmem.php Memory Usage (in bytes) Before: 321328 After: 337384 Delta: 16056 Delta per row: 1070 bytes per row Would expect the Delta per row to be the similar for PHP 5.3 but this is no where near. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/bug.php?id=48343&edit=1