Edit report at https://bugs.php.net/bug.php?id=54864&edit=1
ID: 54864
Updated by: nikic@php.net
Reported by: jas at rephunter dot net
Summary: Memory leak associated with mysql connector
-Status: Re-Opened
+Status: Closed
Type: Bug
Package: MySQLi related
Operating System: FreeBSD
PHP Version: 5.3.6
-Assigned To:
+Assigned To: nikic
Block user comment: N
Private report: N
New Comment:
By default, mysqli uses buffered result sets, so your entire result set will be kept in memory.
Disabling this is very simple: Pass MYSQLI_USE_RESULT to mysqli_query(). In this case, I have
confirmed that memory usage for the provided test case stays constant.
Previous Comments:
------------------------------------------------------------------------
[2015-09-10 14:07:38] jas at rephunter dot net
Please note that in my above post with the simplified code, the flush() and ob_flush() are not
directly related to the issue afaik, but have to do with attempts to display output in a browser.
------------------------------------------------------------------------
[2015-09-10 13:51:52] jas at rephunter dot net
The problem seems to be worse than previously. The following results are on PHP 5.5.27.
On every loop while reading a table, memory is consumed, regardless of using gc_enable, unset, or
mysqli_free_memory.
I started by dusting off the script that I originally provided in this bug ticket on 2011-07-05.
However that complexity is not needed, and a very simple query of "SELECT * from user"
gives the same result.
Here is the main loop of the simplified version, where the user table has about 70,000 rows:
$rs = mysqli_query($link, 'SELECT * FROM user');
if ($rs)
{
// main loop
$cnt = 0;
echo 'after SQL mem=', memory_get_usage(true), LF;
while($row = mysqli_fetch_row($rs))
{
if (++$cnt % 3000 == 0)
{
echo ' id=', $row[$userid_ix], ' mem=', memory_get_usage(true), LF;
gc_collect_cycles();
flush();
ob_flush(); // documentation is inconclusive whether this is also needed
}
unset($row);
// $row = null;
}
}
Here is the output of the above script (please note that the echo statements are issued once for
every 3000 rows):
Test Autoemail Memory Leak
Using mysqli_connect
start run mem=524288
Query
SELECT * FROM user
after SQL mem=24117248
id=3043 mem=29884416
id=6066 mem=35913728
id=9041 mem=41680896
id=12031 mem=47710208
id=15015 mem=53477376
id=17986 mem=59506688
id=20984 mem=65273856
id=24004 mem=71303168
id=27065 mem=77070336
id=30076 mem=83099648
id=33115 mem=88866816
id=36201 mem=94896128
id=39095 mem=100663296
id=42106 mem=106692608
id=45112 mem=112459776
id=48104 mem=118489088
id=51085 mem=124518400
id=54082 mem=130285568
PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes) in
/Users/jas/Websites/RepHunter/current/test-autoemail-memory.php on line 44
The various attempts to reclaim memory (gc_enable(), unset(), gc_reclaim_cycles, and setting the
$row to null, all have no effect.
At this point, the workaround is to simply set memory large enough, and hope the application
completes before exhausting memory. At present I am using ini_set('memory_limit',
'384M');
The problem (on line 44) is while($row = mysqli_fetch_row($rs)).
Has anybody come up with an alternative to that construct that does not continue to consume memory?
------------------------------------------------------------------------
[2015-09-03 17:18:42] jas at rephunter dot net
I am the OP for this ticket. Our system, now at PHP 5.6.9, is once again experiencing a memory leak,
and in fact it is in the same script as in the original post.
I am planning to do some testing to see if unsetting things helps. But per the post from yesterday
from supraguy, that does not work.
------------------------------------------------------------------------
[2015-09-02 20:53:41] supraguy at yandex dot com
I run into the same problem as well. I use mysqli_query() and on each execution it increases by 50
or so bytes. I unset the crap out of everything and do mysqli_free_result() but still the number
keeps rising.
------------------------------------------------------------------------
[2015-05-17 19:26:24] pr0ger at free dot fr
Note : same leakage using mysql_fetch_row() or mysql_fetch_array()
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
https://bugs.php.net/bug.php?id=54864
--
Edit this bug report at https://bugs.php.net/bug.php?id=54864&edit=1