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 php.net/manual/en/function.mysql-unbuffered-query.php — Jon Black
– Jon Black, Commented Dec 7, 2011 at 1: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
– Mike Purcell, Commented Dec 7, 2011 at 4:08
@Galled -- I'm dumping this data to CSV, so pagination isn't really an option. — Lou Kosak
– Lou Kosak, Commented Dec 8, 2011 at 2:38

goat · Accepted Answer · 2011-12-07 03:57:11Z

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 7, 2011 at 3:57

goat

31.9k7 gold badges76 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lou Kosak Over a year ago

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.

Community · Accepted Answer · 2017-05-23 12:32:52Z

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, 2017 at 12:32

CommunityBot

11 silver badge

answered Dec 7, 2011 at 1:38

SuperTron

4,2537 gold badges40 silver badges62 bronze badges

Comments

Luke · Accepted Answer · 2011-12-07 02:03:59Z

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.

Collectives™ on Stack Overflow

Memory leak in PHP when fetching large dataset from MySQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related