php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Doc Bug #61060 MySQL memory leak
Submitted: 2012-02-12 03:50 UTC Modified: 2012-06-01 06:48 UTC
Votes:2
Avg. Score:4.5 ± 0.5
Reproduced:2 of 2 (100.0%)
Same Version:2 (100.0%)
Same OS:0 (0.0%)
From: trusty_jim at hotmail dot com Assigned: philip (profile)
Status: Closed Package: MySQL related
PHP Version: 5.3.10 OS: Windows Vista
Private report: No CVE-ID: None
View Add Comment Developer Edit
 [2012-02-12 03:50 UTC] trusty_jim at hotmail dot com 
Description:
------------
Fetching data through mysql_fetch_assoc() causes php memory usage to go up after 
each query. 

This occurs even after unsetting the retrieved data.

Test script:
---------------
<?php
$db = mysql_connect(DB_DOMAIN, DB_USERNAME, DB_PASS, true);
echo "Start Memory : ".memory_get_usage()."\n";
$result = mysql_query('SELECT * FROM addr', $db);

for ($i = 0; $i < 10; $i++) {
	$row = mysql_fetch_assoc($result);
	if ($row === false) break;
	unset($row);
	gc_collect_cycles();	
	echo "  ".memory_get_usage()."\n";
}
mysql_free_result($result);

echo "End Memory : ".memory_get_usage()."\n";
?>

Expected result:
----------------
Memory usage should be similar after each execution

Start Memory : 31108008
  65407576
  65407576
  65407576
  65407576
  65407576
  65407576
  65407576
  65407576
  65407576
  65407576
End Memory : 31108160


Actual result:
--------------
Memory usage increases significantly after each execution

Start Memory : 31108008
  65407576
  65408120
  65408664
  65409208
  65409752
  65410296
  65410840
  65411384
  65411944
  65412488
End Memory : 31108160

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2012-04-10 16:59 UTC] jim at bladehq dot com 
I have experienced this issue too. It appears to only leak memory when the query is SELECT *. This has caused our application to run out of memory when iterating over large result sets.
 [2012-04-11 14:35 UTC] rasmus@php.net
-Status: Open +Status: Not a bug
 [2012-04-11 14:35 UTC] rasmus@php.net 
This will depend on your mysql client library, and it isn't actually a memory 
leak.

Running your script on my Ubuntu box gives me:

Start Memory : 227784
  228240
  228240
  228240
  228240
  228240
  228240
  228240
  228240
  228240
  228240
End Memory : 228120

On both PHP 5.3.11 and PHP 5.4.1 using the old outdated mysql extension built 
against libmysqlclient-dev-5.1.61-0ubuntu0.11.10.1

Now, if however you are using the mysqlnd library, keep in mind that this 
library uses PHP native memory management system, so anything allocated by the 
library shows up in memory_get_usage(). So, using mysqli linked against mysqlnd 
I get:

Start Memory : 235440
  9969808
  9970720
  9971632
  9972544
  9973456
  9974368
  9975280
  9976192
  9977104
  9978016
End Memory : 295280

But this makes perfect sense. Each time you fetch a row from the MySQL server 
the client library needs to allocate memory to store that row. This happens in 
the non-mysqlnd case as well, but it uses a straight system malloc() call so the 
memory doesn't show up when you call memory_get_usage(). It is still eating 
memory, and it isn't actually a leak, that's how the library works. You get your 
memory back when you free the result.

Now, what this bug report is really asking is how do we tell the client library 
not to buffer rows as we read them from the server. That's what unbuffered 
queries are all about. In mysqli this see the use_result() method. For the old 
deprecated mysql extension, see the mysql_unbuffered_query() function.

So, this is not actually a bug.
 [2012-04-18 20:56 UTC] philip@php.net 
Marking as a doc bug, as suggested by Rasmus. Assigning to self.
 [2012-04-18 20:56 UTC] philip@php.net
-Status: Not a bug +Status: Re-Opened -Type: Bug +Type: Documentation Problem -Assigned To: +Assigned To: philip
 [2012-04-18 21:03 UTC] philip@php.net 
Automatic comment from SVN on behalf of philip
Revision: http://svn.php.net/viewvc/?view=revision&amp;revision=325318
Log: Partially document PHP bug #61060, with more to come. Help clarify memory memory reporting with mysqlnd vs libmysql
 [2012-04-18 21:19 UTC] philip@php.net 
Automatic comment from SVN on behalf of philip
Revision: http://svn.php.net/viewvc/?view=revision&amp;revision=325320
Log: Added concepts section for MySQL docs. And added basic info about buffered vs unbuffered. Incomplete but should be helpful for discussion and/or lure others into adding content. :) That part deals with PHP Bug #61060
 [2012-06-01 06:48 UTC] philip@php.net
-Status: Re-Opened +Status: Closed
 [2012-06-01 06:48 UTC] philip@php.net 
This is now documented, thank you for the report.
 [2012-12-07 10:01 UTC] keith at sauvant dot de 
One additional question/comment (regarding PHP on Windows in this case): When the effect described above eats to much memory the PHP process crashes. But: You will not find anything about that in the PHP error log nor in the Windows event log or anywhere else.

Configuration issue? Bug? Desired behaviour?

Best regards
Keith
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Sun May 05 10:01:31 2024 UTC