[PHP-BUG] Bug #74304 [NEW]: Can't "upgrade" from mysql_* functions due to memory allocation problems

From:             josh at endries dot org
Operating system: FreeBSD
PHP version:      5.6.30
Package:          *Database Functions
Bug Type:         Bug
Bug description:Can't "upgrade" from mysql_* functions due to memory allocation problems

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.



-- 
Edit bug report at https://bugs.php.net/bug.php?id=74304&edit=1
-- 
Try a snapshot (PHP 5.4):   https://bugs.php.net/fix.php?id=74304&r=trysnapshot54
Try a snapshot (PHP 5.5):   https://bugs.php.net/fix.php?id=74304&r=trysnapshot55
Try a snapshot (trunk):     https://bugs.php.net/fix.php?id=74304&r=trysnapshottrunk
Fixed in SVN:               https://bugs.php.net/fix.php?id=74304&r=fixed
Fixed in release:           https://bugs.php.net/fix.php?id=74304&r=alreadyfixed
Need backtrace:             https://bugs.php.net/fix.php?id=74304&r=needtrace
Need Reproduce Script:      https://bugs.php.net/fix.php?id=74304&r=needscript
Try newer version:          https://bugs.php.net/fix.php?id=74304&r=oldversion
Not developer issue:        https://bugs.php.net/fix.php?id=74304&r=support
Expected behavior:          https://bugs.php.net/fix.php?id=74304&r=notwrong
Not enough info:            https://bugs.php.net/fix.php?id=74304&r=notenoughinfo
Submitted twice:            https://bugs.php.net/fix.php?id=74304&r=submittedtwice
register_globals:           https://bugs.php.net/fix.php?id=74304&r=globals
PHP 4 support discontinued: https://bugs.php.net/fix.php?id=74304&r=php4
Daylight Savings:           https://bugs.php.net/fix.php?id=74304&r=dst
IIS Stability:              https://bugs.php.net/fix.php?id=74304&r=isapi
Install GNU Sed:            https://bugs.php.net/fix.php?id=74304&r=gnused
Floating point limitations: https://bugs.php.net/fix.php?id=74304&r=float
No Zend Extensions:         https://bugs.php.net/fix.php?id=74304&r=nozend
MySQL Configuration Error:  https://bugs.php.net/fix.php?id=74304&r=mysqlcfg