php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #65741 High memory usage from mysqli:query()
Submitted: 2013-09-23 13:03 UTC Modified: 2013-09-23 15:06 UTC
From: cduncan at regatta dot com Assigned:
Status: Not a bug Package: MySQLi related
PHP Version: 5.5.4 OS: Linux
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: cduncan at regatta dot com
New email:
PHP Version: OS:

 

 [2013-09-23 13:03 UTC] cduncan at regatta dot com
Description:
------------
When executing a query that returns a large number of rows (around 16 million) the query() function appears to use a lot more memory than it did in version 5.3.8

I looked through the changelogs and couldn't find any obvious reason why, or any mention of a change in how the function works

Test script:
---------------
<?php
$sql = new mysqli("localhost","username","password");
function checkMemory($key="") {
	$memory = round(memory_get_usage(true) / 1024 / 1024) . "mb";
	echo "Memory " . $key . ": " . $memory . "\n";
}

checkMemory("Before");

$query = "SELECT field1a, field2a, valb1, valb2, valb3, valb4 FROM test.table1
	JOIN test.table2 ON companyb=companya AND field1b=field1a
	WHERE companya='TEST'";
$result = $sql->query($query);

checkMemory("After");

$field = $result->fetch_field();
echo "End\n";

Expected result:
----------------
user@server:~$ /usr/local/php-5.3.8/bin/php memory.php 
Memory Before: 1mb
Memory After: 1mb
End

Actual result:
--------------
user@server:~$ /usr/local/php-5.5.4/bin/php memory.php 
Memory Before: 0mb
Memory After: 427mb
PHP Fatal error:  Out of memory (allocated 1603796992) (tried to allocate 20 bytes) in /home/user/memory.php on line 17

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2013-09-23 14:29 UTC] rasmus@php.net
-Status: Open +Status: Not a bug
 [2013-09-23 14:29 UTC] rasmus@php.net
The "appears to use" is key here. I bet you are using the mysqlnd driver now 
which means that the mysql library is using the PHP memory allocator which in 
turn means that memory_get_usage() is now able to see and track that memory. 
Before, using libmysqlclient, you were using just as much memory, but 
memory_get_usage() just couldn't see it. In fact, you were using more because it 
would get malloc'ed once by libmysqlclient and then when you fetched rows from 
PHP it would get copied to PHP-controlled memory. Now with the native driver that 
copy is no longer needed.
 [2013-09-23 15:06 UTC] cduncan at regatta dot com
Great, thanks for the explanation
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Mar 28 18:01:29 2024 UTC