Description: ------------ I am creating a script to insert 700.000 csv lines into my Postgres database. After extensive testing i found out that MDB2 keeps using more and more memory altough in my script i free/unset everything i get my hands on. MDB2 2.4.1 MDB2_pgsql // $Id: pgsql.php,v 1.173 2007/05/02 22:00:08 quipo Exp $ Postgres 8.2 Test script: --------------- <?php error_reporting(E_ALL); ini_set('display_errors','1'); require_once('MDB2.php'); $dsn = 'pgsql://postgres:postgres@127.0.0.1/testdb'; $mdb2 = MDB2::connect($dsn,array('debug' => 0,'result_buffering' => false)); for($i=1;$i<=1000;$i++){ echo 'Iteration: '. $i . ' Memory useage at start: '.memory_get_usage().'<br/>'; $result = $mdb2->queryOne('SELECT test_id FROM testtable WHERE id = '.$i); unset($result); } ?> Expected result: ---------------- I would expect the memory usage to be constant throughout the execution of the script. Actual result: -------------- Iteration: 1 Memory useage at start: 1565592 Iteration: 500 Memory useage at start: 6686072 Iteration: 1000 Memory useage at start: 11789760

Have you tried using the direct pgsql commands to achieve the same goal ? for ($i = 0; $i < 1000; $i++) { echo 'Iteration: '. $i . ' Memory useage at start: '; echo memory_get_usage().'<br/>'; $sql = ' SELECT test_id FROM testtable WHERE id ='.$i; $query = pgsql_query($sql); while ($res = pgsql_fetch_assoc($query)) { // Got here... $res[0]; } unset($query); } Or such and then tell us what you get.

I am experiencing the exact same problem. I am running on a mysql database using PHP 5 through CLI.

Allan, can you also try with the native functions to see if it does the same thing ? If your memory loads up or something ? Thanks,

Okay, I tried to run above test script with native functions vs a mdb2 version of same from PHP cli and got following result: Native functions: ----------------- Iteration: 0 Memory useage at start: 78704 Iteration: 1 Memory useage at start: 80904 Iteration: 2 Memory useage at start: 81048 Iteration: 3 Memory useage at start: 81048 Iteration: 4 Memory useage at start: 81048 ... Iteration: 997 Memory useage at start: 81048 Iteration: 998 Memory useage at start: 81048 Iteration: 999 Memory useage at start: 81048 MDB2 implementation: -------------------- Iteration: 0 Memory useage at start: 2378728 Iteration: 1 Memory useage at start: 2381400 Iteration: 2 Memory useage at start: 2381904 Iteration: 3 Memory useage at start: 2382160 Iteration: 4 Memory useage at start: 2382416 ... Iteration: 997 Memory useage at start: 2636624 Iteration: 998 Memory useage at start: 2636880 Iteration: 999 Memory useage at start: 2637136 I hope this helps.

Can anyone profile the script (with Zend Studio, Xdebug or something) and see where the memory leak occur?

I'll test with xdebug, if anyone else wants to try go ahead, it'd be good to have more than one test and mine is going to be over the weekend. Thanks,

As a 'workaround' i have tried to use the DB package - It has a lower memory load and it stays stable over the 700.000 INSERT/SELECT statements. Maybe this can be a tip to help the pinpointing.

The increasing memory useage has been verified by Allan Noer

Hey guys.. I've been having the same trouble, but I was testing it just now.. On an FC6 32 bit system: Memory leak as described - increases with every iteration. On latest centOS 5 release 64 BIT: No memory leak - usage is unchanged iteration 1 thru 1000. This is with all the same (current) versions of the PEAR libs, the same PHP (5.1.6), the same remote PostgreSQL server and database, identical versions of pdo_pgsql and pgsql libs on the client, all installed for standard respositories. And above is testing with php running from the command line, no apache involved. I'll see if I can find any other differences within phpinfo() on each machine. PS: I also tried going to 100000 iterations on the 64 bit OS, and the memory usage still remained constant.

I traced the leak on my system to the call to array_diff within the connect() function in the pgsql.php driver file. Attached a demo patch that resolves the leak for me. After applying, memory usage remains constant for example code.

The leak related to array_diff I was able to demo on PHP 5.1.6 with the following code: for($i = 0; $i < 5; $i++) { array_diff( array(1, 2, 3, 4, 5, 6, 7, 8, 9), array() ); echo memory_get_usage()."\n"; } Note: it only happens when array has between 9 and 16 elements. I could not create the problem with PHP 5.2.2

I've tested this bug with PHP 5.2.4, MDB2 CVS version and the pgsql and mysql drivers and I can't reproduce the leak. The patch written by Fergus doesn't bear any change at all on my system (BTW: that would be a leak in the PHP engine, not in the script). The numbers reported by Allan don't seem as dramatic as those reported by Mark. David, have you had a chance to test MDB2 with Xdebug? Can anyone see the leak with the latest PHP version?

The array_diff memleak indeed is a problem with PHP 5.1.x and has been fixed in PHP 5.2.x. However some distributions still havn't cleared for the 5.2.x being included in their packages (Debian Etch). So a fix would be nice.

OK, I've committed a patch against all drivers. Can you guys check the CVS version and report back? TIA

I tested the areEquals patch on my 5.1.6 system and there was still a leak. I checked the (over simplistic) patch I submitted previously and it does not leak. Will try and determine what exactly is leaking in areEquals.

It appears that nearly every array function that involves accessing the key values in some way causes a leak on 5.1.6. One possible alternative that appears to be stable is to compare serialized versions: return serialize($arr1) == serialize($arr2); That would not return equality unless the key/value pairs are in the same order in each array, but would it be adequate for this situation?

Hi Fergus, thanks a lot for your help, much appreciated. Does the leak occurr with all these functions? - array_key_exists() - array_keys() - ksort() If the last one is not affected, I guess we can use serialize() safely.

Could anyone check my last comment? Does ksort() leak memory?

Not sure how this could be closed. There are unresolved questions. I am finding a memory leak presently with latest version of php and latest pear beta mdb2. Just doing simple query creates memory leaks. the errchk is just handling exceptions. $db = & $GLOBALS['mdb2']->query($query); global_Debug::errchk($db,3); $vars=$db->fetchAll(MDB2_FETCHMODE_ASSOC); $db->free(); Doing this grows memory on every loop. Switching to $result = mysql_query($query, $this->conn); if(mysql_num_rows($result)){ while ($vars[] = mysql_fetch_assoc($result)); array_pop($vars); } Normal mysql_ creates no memory leak at all. PHP 5.2.6 (cli) (built: May 5 2008 10:32:59) Installed packages, channel pear.php.net: ========================================= Package Version State Archive_Tar 1.3.2 stable Console_Getopt 1.2.3 stable MDB2 2.5.0b1 beta MDB2_Driver_mysql 1.5.0b1 beta PEAR 1.7.2 stable Structures_Graph 1.0.2 stable XML_Parser 1.2.8 stable XML_RPC 1.5.0 stable XML_Serializer 0.18.0 beta XML_Util 1.1.4 stable

I had a hard time backtracking this bug but I found a working solution: the bug is in the debug_backtrace function ( http://bugs.php.net/bug.php?id=33487 ) which is called by PEAR::raiseError ( http://pear.php.net/bugs/bug.php?id=6788 ) which is called by MDB2 in the execute method. The solution is disabling the traceback in pear errors with this code: $PEAR_Error_skiptrace = &PEAR::getStaticProperty('PEAR_Error', 'skiptrace'); $PEAR_Error_skiptrace = true; Hope it helps.

I noticed, I wasn't correct in the previous description, the raiseError, was called (at least in my case) in the bindValue function, and only indirectly by execute.

Thanks Massimiliano. I'll fix it in the next few days. I knew there was something wrong with PEAR_Error, at least now I know who's the real culprit :-)