Description:
------------
I am trying to update an old application that uses mysql_* functions but I am running into memory problems when I really shouldn't.

I've tried mysqli (OOP) and PDO, buffered and unbuffered queries, USE_RESULT, free()/close()/=null, and everything else I can think of or find online. I hope I missed something that will work. There is a text column in a join in some queries, the actual data in that field is ~30 KB (~13k chars for the longest row). The result sets (rows) the site is fetching are small. However, it does do a large number of queries (500+).

The current site's Apache processes (using mod_php) never go above 50 MB of memory usage when loading the test page. It inflates by 5-15 MB or so while loading, then drops back down again. This is with the mysql_* functions. With PDO or mysqli (with MYSQLI_USE_RESULT), it increases quickly to the memory_limit and dies. I had a 128 MB limit but raising that to 256 MB made no difference--it hit the limit in a few seconds.

With a 768 MB limit, all three types transfer about 2 MB (inbound/download) over the wire, however with a 128 MB limit, for some reason PDO transferred a whopping 45 MB over the wire. No idea what happened there.

With a 768 MB limit, using PDO and watching vmstat, it uses 340 MB or more to load the test page in ~5.25sec.

With mysqli with MYSQLI_USE_RESULT, 380 MB used and a 4.75sec load time.
With mysqli without MYSQLI_USE_RESULT, it was almost the same.

With mysql_*, 20 MB used and a 4.25sec load time.

This is system memory usage, so I would think whether you buffer in C or PHP wouldn't matter much, as mysqli seems to indicate. However, there is a staggeringly large difference (I'm guessing) between libmysqlclient (or whatever mysql_* uses) and mysqlnd, and presumably different code paths within PHP.

The DB layer should only be creating one instance of mysqli or PDO. The queries haven't changed at all, and the DB layer assigns the full result set to an array and that array to a result object before returning the object to the code that is making the query. I've tried looping through with fetch() or its equivalents and I've tried simply assigning the return of fetchAll() or its equivalents and neither made a difference. For example:

	$data = Array();
	$i = 0;
	while ($r = $result->fetch(PDO::FETCH_ASSOC)) {
		$data[$i] = $r;
		$i++;
	}
	$query = new stdClass();
	$query->num_rows = $i;
	$query->row = isset($data[0]) ? $data[0] : Array();
	$query->rows = $data;
	$result = null;
	return $query;

Only the while statement is different between the three DB engines (except for e.g. fetchAll). I would expect similar memory usage in all cases given this design, since it always buffers everything into a PHP object/array one way or another, or even twice as much if it's copying data, but it is somehow seemingly using ten times the amount of memory (or more) with mysqli or PDO (or mysqlnd) over mysql_*.

This behavior seems like a bug to me, or GC isn't happening, or...something is definitely wrong. I know the actual data being queried is small. Even using the recommendations on dealing with large result sets doesn't work.

At this point I have no idea what to do. I can't feasibly rewrite all the queries to use enumerators (though I don't know if that would actually help), and in many cases I can't do that anyway because it needs the whole result set.