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

Edit report at https://bugs.php.net/bug.php?id=74304&edit=1

 ID:                 74304
 Comment by:         spam2 at rhsoft dot net
 Reported by:        josh at endries dot org
 Summary:            Can't "upgrade" from mysql_* functions due to memory
                     allocation problems
 Status:             Open
 Type:               Bug
 Package:            *Database Functions
 Operating System:   FreeBSD
 PHP Version:        5.6.30
 Block user comment: N
 Private report:     N

 New Comment:

i can assure you that this is not normal - i replaced mysql_ to mysqli_ in our database layer many years ago and never saw such issues with 5.3, 5.4, 5.5, 5.6, 7.0 or 7.1 - sounds like you hit a memory leak

maybe you can look at https://bugs.php.net/bug.php?id=73889 where my problem is that i get the valrgind leak but i am unable to isolate which of the thousands automated requests and which piece of code triggers it

you need a debug build and valgrind


Previous Comments:
------------------------------------------------------------------------
[2017-03-24 02:02:57] josh at endries dot org

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 this bug report at https://bugs.php.net/bug.php?id=74304&edit=1