Bug #68318 [Nab]: mysqlnd Memory allocation problem - memory not freed

Edit report at https://bugs.php.net/bug.php?id=68318&edit=1 ID: 68318 User updated by: tomas at slax dot org Reported by: tomas at slax dot org Summary: mysqlnd Memory allocation problem - memory not freed Status: Not a bug Type: Bug Package: MySQLi related Operating System: Linux PHP Version: 5.5.18 Block user comment: N Private report: N New Comment: Thank you for pointing this out, I have to admit that my understanding of the caching is a bit different (corresponds with libmysqlclient way, not with mysqlnd way), thus I did not expect this to be the problem. Anyway it still seems like a bug to me. Let me explain. When the query is to be executed using mysqli_query($link,"SELECT * FROM tbl123"), then the query string is sent to the database server. Database server runs the query and returns a result, which I understand as a bunch of data (all rows in some binary form), let's say it is 30MB of data. This 30MB is cached in memory, and rows are fetched from that memory region. One can still move back and forth in that result set which is ENTIRELY cached in the client. The ENTIRELY part is very important. If using libmysqlclient, memory consumption corresponds to this. The entire result set is cached in memory once mysqli_query() finishes, and then the memory usage remains constant regardless if rows are read from the result set or not. This is all OK. The situation looks differently for mysqlnd. Calling mysqli_query() without MYSQLI_USE_RESULT means PHP uses MYSQLI_STORE_RESULT by default. I would expect that the entire result set will be cached similarly like in the previous case, just right after the mysqli_query() call, but as we can see from the charts produced by massif, memory increases over time, as new rows are fetched, so the result is NOT cached as a whole upon mysqli_query(), but it looks like it gets cached over time, using a method which is not optimal at all (since it consumes 5 times more RAM than it would with libmysqlclient), 149MB of RAM. If this is not bug, then it is at least very serious problem. I will solve my issue with using MYSQLI_USE_RESULT, so you can probably close this ticket. Unless you have the same strange feeling like I have, that there is something wrong with mysqlnd. :) I do not understand how is it possible that the result set is not physically cached as a whole with mysqlnd yet it is still accessible even if calling some other queries. I do not understand where the data is, before it gets cached by PHP. And I do not expect you to explain it to me :) But since I can run different queries before reading all rows from the first result, it looks like the data is cached somewhere outside of PHP and then cached again in PHP when reading the rows, it looks like PHP could drop the caches from time to time to avoid hitting memory_limit. Just an idea. Previous Comments: ------------------------------------------------------------------------ [2014-10-29 23:26:42] rasmus@php.net This is simply how it works. Just because you re-use $row doesn't mean that the row in the result set is free'ed up. You can still move back and forth in that result set which is cached in the client. If you put: mysqli_free_result($result); after your while loop you will see that the memory usage drops to almost 0 which tells you this is all in the cached result set. The other thing you can do is set the MYSQLI_USE_RESULT flag. eg. $result=mysqli_query($link,"SELECT * FROM tbl123", MYSQLI_USE_RESULT); And like I already mentioned, there is ongoing work to make this more memory-efficient. Please read http://blog.ulf-wendel.de/2007/php-59-tuning-screws-for-mysqlnd/ if you want to understand this stuff better. ------------------------------------------------------------------------ [2014-10-29 09:13:43] tomas at slax dot org I tried using massif. Here are the results: ------------------------------------------------------------------ Command: ./php /root/php-bug.php # mysqlnd Massif arguments: (none) ms_print arguments: massif.out.32672 ------------------------------------------------------------------ MB 140.2^ # | :@:# | ::::@:# | :::::::@:# | ::@: :::::@:# | ::::::@: :::::@:# | ::::: :::@: :::::@:# | ::::::::: :::@: :::::@:# | :::: : ::::: :::@: :::::@:# | ::: :: : ::::: :::@: :::::@:# | ::@@::: :: : ::::: :::@: :::::@:# | :::::@ ::: :: : ::::: :::@: :::::@:# | ::::: ::@ ::: :: : ::::: :::@: :::::@:# | ::::: :: ::@ ::: :: : ::::: :::@: :::::@:# | :::: ::: :: ::@ ::: :: : ::::: :::@: :::::@:# | ::::: :: ::: :: ::@ ::: :: : ::::: :::@: :::::@:# | :::: : :: ::: :: ::@ ::: :: : ::::: :::@: :::::@:# | :::: : :: ::: :: ::@ ::: :: : ::::: :::@: :::::@:# | :::: : :: ::: :: ::@ ::: :: : ::::: :::@: :::::@:# | @:::: : :: ::: :: ::@ ::: :: : ::::: :::@: :::::@:# 0 +--------------------------------------------------------->Gi ------------------------------------------------------------------ Command: php php-bug.php # libmysqlclient Massif arguments: (none) ms_print arguments: massif.out.32610 ------------------------------------------------------------------ MB 29.85^ #::::: ::::::@:::::::::@::::::::@:::::: :::: | #::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | #::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | #::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | #::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | :#::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | :#::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | :#::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | ::#::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | ::#::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | ::#::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | ::#::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | :::#::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | :::#::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | :::#::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | ::::#::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | ::::#::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: | ::::#::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: |:::::::::::#::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: |: :::::#::::::::::::@:::::: ::@:::: :::@::::::@::::@:::: 0 +--------------------------------------------------------->Gi You would expect that the charts will be the same. But you can see both charts are different. The first one shows that memory usage increases and is not freed when using mysqlnd. The other chart shows that memory usage is constant when using libmysqlclient. Furthermore, when the script is processed with PHP with libmysqlclient, it finishes properly for all rows (I used 60000 rows). But when PHP with mysqlnd is used, the script doesn't finish. It fails with this error: PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 1024 bytes) in /root/php-bug.php on line 18 Do you need another proof that there is a memory bug in PHP? ------------------------------------------------------------------------ [2014-10-29 08:36:22] tomas at slax dot org Hi, I noticed you changed this to "not a bug". What a shame. This is definitely a bug. The memory DOES NOT get free'ed eventually. It NEVER gets free'ed and the PHP script DIES on memory limit! I am sorry for using exclamation marks here, but you're probably completely missing the point. My primary problem was that a script similar to this one (with a simple while loop) died unexpectedly on memory limit for no reason, since the $row variable is reused million times, there is no reason for it to remain in memory. My concern is not that used memory reported by PHP's function memory_get_usage() is increasing. I don't care about that reported value, really. What I care about is that if my script outputs more rows (like a million rows) in a simple while loop, the memory consumed (and NEVER free'ed) increases to the point that the script dies on reaching the max available memory limit (configured in php.ini). There is something really wrong with that, I see no reason whatsoever why PHP should allocate memory and never free it. I am ready to provide more information in order to convince you that this is a real problem in PHP. Just let me know please what do you need from me. Thank you very much. ------------------------------------------------------------------------ [2014-10-29 05:56:41] rasmus@php.net The huge piece you are missing is that libmysqlclient doesn't use the PHP memory manager so any memory it uses is not counted when you call memory_get_usage(). So it makes perfect sense that your libmysqlclient example stays constant. It could be leaking Gigabytes of memory and you would still see constant numbers from memory_get_usage() so using that as any sort of proof of a problem is invalid. Looking at your script itself, it looks ok to me. When you fetch a row from the server into the client, the client allocates memory for that row. libmysqlclient does the same, just using malloc() not emalloc(). I bet actual memory usage is identical if you looked at it with something like Massif. The memory should eventually get free'ed. And there are some recent optimizations here too. Have a read through http://blog.ulf-wendel.de/2014/php-5-7-mysqlnd-memory-optimizations/ ------------------------------------------------------------------------ [2014-10-28 12:48:31] tomas at slax dot org Description: ------------ When PHP is compiled with mysqlnd (default), it has a problem with freeing memory, as I will demonstrate in test script. If PHP is compiled with libmysqlclient, the problem does not occur. Compiling PHP with mysqlnd causes the test script to work INCORRECTLY: ./configure \ --with-mysql-sock=/var/run/mysql/mysql.sock \ --with-mysql \ --with-mysqli # note that mysqlnd is enabled by default. It's like: --with-mysqli=mysqlnd Compile PHP with libmysqlclient to fix the memory allocation problem: ./configure \ --with-mysql-sock=/var/run/mysql/mysql.sock \ --with-mysql=/usr \ --with-mysqli=/usr/bin/mysql_config \ # note that this fixes the problem I expect you to fix bug in PHP's mysqlnd driver so it properly free()s the memory!! Thank you very much !!! This error is there since PHP 5.4.x and is there even now with PHP 5.6.x Test script: --------------- <?php echo "First, connect to database test...\n"; $link=mysqli_connect("localhost",'root','rrr','test'); echo "Create temporary table with lots of rows...\n"; $ok=mysqli_query($link,"CREATE TEMPORARY TABLE tbl123 LIKE mysql.user"); if (!$ok) die(); for ($i=0;$i<6000; $i++) { mysqli_query($link,"INSERT IGNORE INTO tbl123 SET host='x".$i."'"); } echo "Get all rows from database\n"; $result=mysqli_query($link,"SELECT * FROM tbl123"); while($row=mysqli_fetch_row($result)) { echo "Mem used: ".number_format(memory_get_usage()/1024,1,'.','')." KB\n"; } // expected result: memory usage still the same // libmysqlclient result: memory usage still the same // mysqlnd result: memory usage increases with each row!! :( // and if it reaches php memory limit, the script dies :( ?> Expected result: ---------------- I expect that PHP doesn't use more than, say, 512KB while processing 6000 rows from database as shown in the example. I expect the test script to print constant memory usage, as like: Mem used: 228.7 KB Mem used: 228.7 KB Mem used: 228.7 KB Mem used: 228.7 KB Mem used: 228.7 KB Mem used: 228.7 KB Mem used: 228.7 KB Mem used: 228.7 KB Mem used: 228.7 KB (still the same) Actual result: -------------- When using mysqlnd (compiled with mysqlnd by default), it increases memory usage with every row, this is very bad: Mem used: 14040.2 KB Mem used: 14042.1 KB Mem used: 14044.1 KB Mem used: 14046.1 KB Mem used: 14048.0 KB Mem used: 14050.0 KB Mem used: 14052.0 KB Mem used: 14054.0 KB Mem used: 14055.9 KB Mem used: 14057.9 KB Mem used: 14059.9 KB Mem used: 14061.8 KB Mem used: 14063.8 KB Mem used: 14065.8 KB (still bigger, this is WRONG) ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=68318&edit=1