php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #71468 increased memory usage with mysqli in php 7
Submitted: 2016-01-27 23:57 UTC Modified: 2016-05-14 21:56 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:0 of 0 (0.0%)
From: ryan dot brothers at gmail dot com Assigned:
Status: Not a bug Package: MySQLi related
PHP Version: 7.0.2 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: ryan dot brothers at gmail dot com
New email:
PHP Version: OS:

 

 [2016-01-27 23:57 UTC] ryan dot brothers at gmail dot com
Description:
------------
I am seeing an issue in PHP 7 where large database queries are taking up more memory compared to PHP 5.6.  

In the below example, PHP 7 uses 8.3MB while PHP 5.6 only uses 1.8MB.

I have placed SQL to create a test table of 100,000 rows at https://drive.google.com/uc?export=download&id=0B37DKbJklxg_Y3NadnRjc2tVbE0.

Is this expected behavior or an issue with PHP 7 memory usage?

Thanks for your help.


Test script:
---------------
<?php
$mysqli = new mysqli('localhost', 'test', 'test', 'test');

$mysqli->real_query("SELECT * FROM a");

$result = $mysqli->store_result();

echo memory_get_usage()."\n";
exit;


Expected result:
----------------
PHP 5.6.17:
1832472

PHP 7.0.2:
1832472 (or close to it)


Actual result:
--------------
PHP 5.6.17:
1832472

PHP 7.0.2:
8356704


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-01-28 03:02 UTC] laruence@php.net
do they all used mysqlnd?
 [2016-01-28 11:53 UTC] ryan dot brothers at gmail dot com
Yes, mysqlnd is used in both.  PHP was compiled with --with-mysqli.
 [2016-02-21 23:00 UTC] rendername at gmail dot com
We have the same issue, does anyone have any idea?
php5 - 45mb, php7 - 250mb
Debian 8 (jessie) 64bit
 [2016-02-22 05:43 UTC] rasmus@php.net
-Status: Open +Status: Feedback
 [2016-02-22 05:43 UTC] rasmus@php.net
Are you sure that your mysqli was compiled against mysqlnd in both PHP 5 and PHP 7? On your phpinfo() page, in the mysqli section, what does it say for "Client API library version" in both PHP 5 and 7?

The reason being, if PHP 5 was compiled against libmysqlclient then since libmysqlclient does not use the PHP memory allocator none of the memory allocated by that library is reported by memory_get_usage(). That doesn't mean it wasn't allocated, of course.
 [2016-02-22 08:35 UTC] rendername at gmail dot com
Of course, we use only mysqlnd:

PHP 7 phpinfo()
Client API library version  mysqlnd 5.0.12-dev - 20150407 - $Id: f59eb767fe17a6679589b5c076d9fa88d3d4eac0 $

php7.0 -m|grep mysql
mysqli
mysqlnd
pdo_mysql


PHP 5 phpinfo()
Client API version  mysqlnd 5.0.11-dev - 20120503 - $Id: 3c688b6bbc30d36af3ac34fdd4b7b5b787fe5555 $

php5 -m|grep mysql
mysql
mysqli
mysqlnd
pdo_mysql

New Example:
 (mysql table pays with 650k rows, getting ID numbers)
...
    echo round(memory_get_usage()/1024/1024)."m<br>";    
    $res=mysqli_query($db,"select id from pays");
    echo round(memory_get_usage()/1024/1024)."m<br>";
    die();

Output PHP7:
1m
52m

Output PHP5:
1m
11m

PHP7 need 5 times more memory for each Mysql Query
We have debian 8 64 bit + Dotdeb packets with PHP7
 [2016-02-22 14:36 UTC] ryan dot brothers at gmail dot com
-Status: Feedback +Status: Open
 [2016-02-22 14:36 UTC] ryan dot brothers at gmail dot com
Yes, I am using mysqlnd.  I confirmed I'm seeing the memory usage increase comparing the latest PHP 5.6.18 and PHP 7.0.3 using the reproduce script above.

5.6.18:
mysqlnd 5.0.11-dev - 20120503 - $Id: 76b08b24596e12d4553bd41fc93cccd5bac2fe7a $

7.0.3:
mysqlnd 5.0.12-dev - 20150407 - $Id: f59eb767fe17a6679589b5c076d9fa88d3d4eac0 $
 [2016-02-22 15:38 UTC] nikic@php.net
-Status: Open +Status: Not a bug
 [2016-02-22 15:38 UTC] nikic@php.net
store_result uses mysqlnd mempool chunks. In PHP 5.6 those used the system allocator (http://lxr.php.net/xref/PHP_5_6/ext/mysqlnd/mysqlnd_block_alloc.c#120). In PHP 7.0 they use the Zend MM (http://lxr.php.net/xref/PHP_MASTER/ext/mysqlnd/mysqlnd_block_alloc.c#119).

So the memory usage didn't actually change (or rather, it did change, but not in a bad way), it simply wasn't reported previously by memory_get_usage().
 [2016-02-22 15:59 UTC] nikic@php.net
Which isn't to say that there is nothing wrong with how mysqlnd manages memory. I'm not familiar with mysqlnd, but the way this works does look distinctly weird. Apart from wasting memory by being unnecessarily generic (having free_chunk and resize_chunk with always the same values, for every single chunk) it's very odd to see the chunks being allocated at all -- what's the point of using an arena allocator if you still use a full blown memory manager for the metadata?
 [2016-02-22 16:18 UTC] ryan dot brothers at gmail dot com
Thanks for the feedback.  I understand the changes, but my scripts in PHP 7 are hitting the memory limit when they didn't in PHP 5.6, yet one of the advantages of PHP 7 is reduced memory usage.  Is there someone more familiar with the change in mysqlnd that can look into it further to determine if PHP 7 will use more memory than PHP 5.6 and not less?
 [2016-02-22 18:23 UTC] rendername at gmail dot com
Ok, we maked new tests to see, how much memory was used for mysqli_query on process:
PHP5: before query 23m, after 237m => so we have 214m (memory_get_usage 45m) NOT OK
PHP7: before query 20m, after 270m => so we have 250m (memory_get_usage 250m) OK

So, php7 need a little more memory for this query, but we get memory usage correctly.
 [2016-02-22 18:46 UTC] nikic@php.net
I've implemented some tweaks in master to reduce memory usage for this case (https://github.com/php/php-src/commit/2d1559f827477963a3f8a40af64212a409ba9457). There's more that can be done here, but this was the lowest hanging fruit.
 [2016-05-14 20:49 UTC] ryan dot brothers at gmail dot com
Thanks for your help.  Have your changes been merged into a release?  I'm still seeing similar memory usage in PHP 7.0.6 where my scripts are hitting the memory limit, but they didn't in PHP 5.6.  Can more be done to optimize memory usage in mysqlnd?  Can this ticket be re-opened, or should I open a new ticket?  Thanks.
 [2016-05-14 21:56 UTC] nikic@php.net
The aforementioned change will be part of PHP 7.1.

I'm not sure I understand what your issue is. As said, the actual memory usage did not increase, only the way it is reported changed. You can safely raise the memory_limit to account for this.
 [2017-02-15 09:53 UTC] amigojack at gmx dot net
Using PHP 7.0.15-1~dotdeb+8.1 or 7.1.1 also gives me the same problem in contrast to PHP 5.6 version: executing a simple query thru MYSQLi ("SELECT text FROM table WHERE text LIKE '%url%'"), where the given column is datatype TEXT and the overall result has about 1M of rows.

The script exactly always dies upon calling mysqli_query(). PHP 5.6 had a memory limit of 384M which worked fine. I increased the memory limit of 7.0.15-1~dotdeb+8.1 to 2048M and it still wasn't enough! How on earth...?

No, mysqli_query() must have changed largely. Or are you telling me PHP 5.6 didn't die on exhausted memory just because the DBMS itself was using more than 2G of memory without reporting it to PHP?
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Mon Oct 18 21:03:39 2021 UTC