php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #48343 Mysqlnd memory leak when fetching rows
Submitted: 2009-05-20 05:56 UTC Modified: 2009-05-24 18:08 UTC
From: mikec at mikenz dot geek dot nz Assigned:
Status: Not a bug Package: MySQLi related
PHP Version: 5.3CVS-2009-05-20 (snap) OS: *
Private report: No CVE-ID: None
 [2009-05-20 05:56 UTC] 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.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-05-21 11:36 UTC] ninzya at inbox dot lv
Try destroying mysqli object before calculating estimated memory usage. See what are results.
 [2009-05-21 13:02 UTC] 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-24 18:08 UTC] 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.
 [2010-03-16 23:59 UTC] rick at igabe dot us
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?
 [2013-05-15 12:00 UTC] m4t1k at interia dot pl
This is a bug and still present.
Are you serious guys? 4 years?
Back to libmysql in my enviroment :/
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Mon Oct 07 04:01:27 2024 UTC