Memory leak in PHP when fetching large dataset from MySQL

Question

When I execute the following code for a user table of about 60,000 records:

mysql_connect("localhost", "root", "");
mysql_select_db("test");

$result = mysql_query("select * from users");

while ($row = mysql_fetch_object($result)) {
  echo(convert(memory_get_usage(true))."\n");
}


function convert($size) {
  $unit=array('b','kb','mb','gb','tb','pb');
  return @round($size/pow(1024,($i=floor(log($size,1024)))),2).' '.$unit[$i];
}

I get the following error:

PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes)

Any thoughts on how to avoid having the script take up additional memory with each pass through the loop? In my actual code I'm trying to provide a CSV download for a large dataset, with a little PHP pre-processing.

Please don't recommend increasing PHP's memory limit--it's a bad idea and, more importantly, will still create an upward bound on how large a dataset can be processed with this technique.

try using http://www.php.net/manual/en/function.mysql-unbuffered-query.php — Jon Black, Dec 07 '11 at 01:46
The title of this post is misleading. It's not a memory leak, you are working with a huge result in a way that goes beyond a preset memory limit. — Mike Purcell, Dec 07 '11 at 04:08
@Galled -- I'm dumping this data to CSV, so pagination isn't really an option. — Lou Kosak, Dec 08 '11 at 02:38

score 2 · Accepted Answer · answered Dec 07 '11 at 03:57

2

mysql_query buffers the entire result set into php memory. This is convenient and generally very fast, but you're experiencing a drawback to it.

mysql_unbuffered_query() exists. It doesn't grab the entire result set all at once. It grabs little pieces at a time when you fetch rows from the result set.

answered Dec 07 '11 at 03:57

goat

31,486
7
73
96

Thanks chris, this is exactly what I was looking for. It seems very odd that the default behavior of MySQL is to tie up additional memory for each row being fetched (even when fetching sequentially and not explicitly storing the data in memory), but there you have it. – Lou Kosak Dec 08 '11 at 02:34

score 1 · Answer 2 · edited May 23 '17 at 12:32

1

I'm not 100% sure if this will solve your problem, but have you considered using PDO? It has several advantages; you can read more about them here. If you do go in that direction, there is a similar question about memory usage here.

edited May 23 '17 at 12:32

Community

1
1

answered Dec 07 '11 at 01:38

SuperTron

4,203
6
35
62

score 0 · Answer 3 · answered Dec 07 '11 at 02:03

I have had a similar problem. What I did to get it to work was to create a temporary file (you can use hash or something similar to keep a record of the name).

Pull 10,000 rows and put them into a file (temp). Put it into a temp csv file.
Reload page (using header with specific parameters / session)
Pull another 10,000 rows and append it to a file.
When you reach end of the table - buffer file to user.

Go like that in circles until you got it all. I had to do this work around for two reasons,

Timeout
Out of memory error.

Drawbacks of this method is that it requires many HTTP calls to get data. Also in the mean time there could be rows that have changed etc. It is a pretty "dirty" way of doing it. I'm yet to find something that works better. Hope that helps.

Memory leak in PHP when fetching large dataset from MySQL

3 Answers3

Linked