Bug->Doc #61060 [Nab->ReO]: MySQL memory leak

Edit report at https://bugs.php.net/bug.php?id=61060&edit=1 ID: 61060 Updated by: philip@php.net Reported by: trusty_jim at hotmail dot com Summary: MySQL memory leak -Status: Not a bug +Status: Re-Opened -Type: Bug +Type: Documentation Problem Package: MySQL related Operating System: Windows Vista PHP Version: 5.3.10 -Assigned To: +Assigned To: philip Block user comment: N Private report: N New Comment: Marking as a doc bug, as suggested by Rasmus. Assigning to self. Previous Comments: ------------------------------------------------------------------------ [2012-04-11 14:35:21] rasmus@php.net This will depend on your mysql client library, and it isn't actually a memory leak. Running your script on my Ubuntu box gives me: Start Memory : 227784 228240 228240 228240 228240 228240 228240 228240 228240 228240 228240 End Memory : 228120 On both PHP 5.3.11 and PHP 5.4.1 using the old outdated mysql extension built against libmysqlclient-dev-5.1.61-0ubuntu0.11.10.1 Now, if however you are using the mysqlnd library, keep in mind that this library uses PHP native memory management system, so anything allocated by the library shows up in memory_get_usage(). So, using mysqli linked against mysqlnd I get: Start Memory : 235440 9969808 9970720 9971632 9972544 9973456 9974368 9975280 9976192 9977104 9978016 End Memory : 295280 But this makes perfect sense. Each time you fetch a row from the MySQL server the client library needs to allocate memory to store that row. This happens in the non-mysqlnd case as well, but it uses a straight system malloc() call so the memory doesn't show up when you call memory_get_usage(). It is still eating memory, and it isn't actually a leak, that's how the library works. You get your memory back when you free the result. Now, what this bug report is really asking is how do we tell the client library not to buffer rows as we read them from the server. That's what unbuffered queries are all about. In mysqli this see the use_result() method. For the old deprecated mysql extension, see the mysql_unbuffered_query() function. So, this is not actually a bug. ------------------------------------------------------------------------ [2012-04-10 16:59:23] jim at bladehq dot com I have experienced this issue too. It appears to only leak memory when the query is SELECT *. This has caused our application to run out of memory when iterating over large result sets. ------------------------------------------------------------------------ [2012-02-12 03:50:28] trusty_jim at hotmail dot com Description: ------------ Fetching data through mysql_fetch_assoc() causes php memory usage to go up after each query. This occurs even after unsetting the retrieved data. Test script: --------------- <?php $db = mysql_connect(DB_DOMAIN, DB_USERNAME, DB_PASS, true); echo "Start Memory : ".memory_get_usage()."\n"; $result = mysql_query('SELECT * FROM addr', $db); for ($i = 0; $i < 10; $i++) { $row = mysql_fetch_assoc($result); if ($row === false) break; unset($row); gc_collect_cycles(); echo " ".memory_get_usage()."\n"; } mysql_free_result($result); echo "End Memory : ".memory_get_usage()."\n"; ?> Expected result: ---------------- Memory usage should be similar after each execution Start Memory : 31108008 65407576 65407576 65407576 65407576 65407576 65407576 65407576 65407576 65407576 65407576 End Memory : 31108160 Actual result: -------------- Memory usage increases significantly after each execution Start Memory : 31108008 65407576 65408120 65408664 65409208 65409752 65410296 65410840 65411384 65411944 65412488 End Memory : 31108160 ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=61060&edit=1