php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #75843 Memory leak with prepare/execute statements
Submitted: 2018-01-18 16:08 UTC Modified: 2018-08-27 09:31 UTC
Votes:2
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: davetay1021 at gmail dot com Assigned: vnkbabu (profile)
Status: Closed Package: ibm_db2 (PECL)
PHP Version: 7.1.13 OS: IBM i OS V7.3
Private report: No CVE-ID: None
 [2018-01-18 16:08 UTC] davetay1021 at gmail dot com
Description:
------------
Creating a prepared statement using db2_prepare() and then executing that statement using db2_execute() in a loop increases memory usage over each iteration.

This happens using ibm_db2 v2.0.2 on PHP 7.1.12 and IBMi OS v7.2, v7.3.

This code works as expected using ibm_db2 v1.9.7 on PHP 5.6.18 and IBMi OS v7.2.

Test script:
---------------
$db = db2_connect('*LOCAL', '', '');
$stmt = db2_prepare($db, 'SELECT * FROM SCHEMA.TABLE WHERE ID = ?');

$cnt = 0;
foreach (range(1,20000) as $id) {
        db2_execute($stmt, [$id]);
        if (($cnt % 1000) == 0) { #Display memory usage each 1000 iterations
                echo "MEMORY: " . memory_get_usage() . "\n";
        }
        $cnt++;
        db2_free_result($stmt);
}

Expected result:
----------------
I expect memory usage to remain fairly constant during the foreach loop.
MEMORY: 1687540
MEMORY: 1687576
MEMORY: 1687576
MEMORY: 1687576
MEMORY: 1687576
MEMORY: 1687576

Actual result:
--------------
The memory usage increases each iteration of the loop until the allowed memory size is exhausted and the script fails.
MEMORY: 1668208
MEMORY: 2300208
MEMORY: 2932208
MEMORY: 3564208
MEMORY: 4196208
MEMORY: 4828208
MEMORY: 5460208
MEMORY: 6092208
MEMORY: 6724208
MEMORY: 7356208
MEMORY: 7988208

Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 4096 bytes) in ...

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2018-02-05 21:10 UTC] adc at us dot ibm dot com
This test is unrealistic. No script will execute 20000 times in a single script request before returning to the browser with results. Sure, fetch can be 1000s, but not prepare/execute (or execute). If this is production code, it needs to be rewritten.

Technically, db2_free_result($stmt) was made a no-op by the LIW team. They did this because too many customers (and php) double free stmt handles. On IBM i this is especially drmatic double free becuase DB2 re-uses the handle. In fact, DB2 only allows 32K handles active in a single php/QSQSRVR pair. Therefore this test is almost at the limit of DB2 itself.

I don't believe this is work is worthy the effort. Especially as the side effects of user control over db2_free_result($stmt) seem to be so very bad. Typical production application script maybe open 1-15 handles, then return to browser. These handles are close sutomatic by php ibm_db2 dtor logic (php pecl clean-up register).

Again, I don't believe this is work is worthy the effort. Howvere feel free to make your case.
 [2018-02-05 21:36 UTC] adc at us dot ibm dot com
Again, not realistic. However, I mixed topics to avoid 2 unrealistic tests (expecting next also).

1) db2_execute/db2_free_result 20000 times is unrealistic. Single application have 20000 result sets to worry about in single request ... no??? The db2_free_result will clean up most of any allocation (632 bytes/per maybe temp leak). Clean-up both result and stmt occurs on PHP dtor.

2) db2_prepare/db2_execute/db2_free_stmt 20000 times is also unrealistic. Single application have 20000 statements to worry about in single request ... no??? The db2_free_stmt is a no-op (double free). The db2_free_stmt is no-op. Clean-up both result and stmt occurs on PHP dtor. Also IBM i DB2<>QSQSRVR has only 32K handles (run out).
 [2018-02-05 23:00 UTC] adc at us dot ibm dot com
However, beyond initial, if legitimate production problem. Please describe 20000 operations per browser request are doing. Who knows, maybe we can do something in ibm_db2, or, maybe db2sock to help.
 [2018-02-06 14:48 UTC] adc at us dot ibm dot com
Works for me (no leak). I can't even replicate the problem on My IBM i (V7R1). Maybe your issue is result set data specific???

===
my run works fine (no leak)
===
bash-4.3$ php --version | grep built              
PHP 7.1.2 (cli) (built: Feb 22 2017 16:06:40) ( NTS )
bash-4.3$ php -i | grep 2.0.2                     
Module release => 2.0.2-db2sock3
Phar EXT version => 2.0.2
bash-4.3$ cat loopleak.php                        
<?php
$db = db2_connect('*LOCAL', '', '');
$stmt = db2_prepare($db, 'select * from QIWS.QCUSTCDT where LSTNAM=?');
var_dump($stmt);
$cnt = 0;
foreach (range(1,20000) as $id) {
 db2_execute($stmt, [$id]);
 if (($cnt % 1000) == 0) { #Display memory usage each 1000 iterations
   echo "MEMORY: " . memory_get_usage() . "\n";
 }
 $cnt++;
 db2_free_result($stmt);
}
?>
bash-4.3$ php loopleak.php                        
resource(5) of type (DB2 Statement)
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
bash-4.3$ cat /usr/local/zendphp7/var/log/php.log 
star
 [2018-02-06 14:53 UTC] adc at us dot ibm dot com
My test works (no leak).

Wild guessing ... your test ... maybe look at the php log and see if the test is running at all??

clear the log ...
$ echo start > /usr/local/zendphp7/var/log/php.log 
$ php mytest.php
... anything in the log ???
$ cat /usr/local/zendphp7/var/log/php.log
start
 [2018-02-06 15:20 UTC] davetay1021 at gmail dot com
Thank you for time on this, I appreciate it.

My example is extremely simplistic to simple show the memory usage.  The production issue that turned us on to this is much more complicated.
1)Browser app allows for excel spreadsheet upload.
2)Spreadsheet data is validated and saved in a table (possibly up to 700K rows).
3)Command line job then has to process that working table and update 1 or 2 master tables.  This is where our memory issues show up.
4)Command line script uses a PHP generator function to select the 700K rows and loop over them.  Outside of this loop, we have used db2_prepare to setup some SQL Update statements.  Inside the loop, we look at each row and depending on certain flags use db2_execute to update rows in other tables.
5)Without the db2_execute, we can loop over the 700K rows in seconds with no memory issues.  Using db2_free_result or not makes no difference.  But the db2_execute seems to increase our memory usage until we run out of resources.

I have run the exact sample code you supplied to show that I get the memory increase on my IBMi 7.3 system.

[10:02:43][dst871@ZENDTST:~]$ /usr/local/zendphp7/bin/php --version | grep built
PHP 7.1.12 (cli) (built: Nov 26 2017 15:29:15) ( NTS )
[10:03:09][dst871@ZENDTST:~]$ /usr/local/zendphp7/bin/php -i | grep 2.0.2
Module release => 2.0.2-zs1
Phar EXT version => 2.0.2
[10:03:29][dst871@ZENDTST:~]$ cat loopleak.php
<?php
$db = db2_connect('*LOCAL', '', '');
$stmt = db2_prepare($db, 'select * from QIWS.QCUSTCDT where LSTNAM=?');
var_dump($stmt);
$cnt = 0;
foreach (range(1,20000) as $id) {
 db2_execute($stmt, [$id]);
 if (($cnt % 1000) == 0) { #Display memory usage each 1000 iterations
   echo "MEMORY: " . memory_get_usage() . "\n";
 }
 $cnt++;
 db2_free_result($stmt);
}
?>
[10:03:41][dst871@ZENDTST:~]$ /usr/local/zendphp7/bin/php loopleak.php
resource(42) of type (DB2 Statement)
MEMORY: 1668040
MEMORY: 2324040
MEMORY: 2980040
MEMORY: 3636040
MEMORY: 4292040
MEMORY: 4948040
MEMORY: 5604040
MEMORY: 6260040
[10:05:03][dst871@ZENDTST:~]$ cat /usr/local/zendphp7/var/log/php.log
start
 [2018-02-06 15:52 UTC] adc at us dot ibm dot com
I just don't know what is wrong. Your test maybe? Data specific column? Need a lot more info for any hope to figure out. Ball in your court (davetay1021).


bash-4.3$ cat loopleak.php 
<?php
$db = db2_connect('*LOCAL', '', '');
$ret = db2_exec($db,'drop table bob');
$ret = db2_exec($db,'create table bob(id int not null, msg char(4096) not null)');
$stmt = db2_prepare($db, 'insert into bob values(?,?)');
var_dump($stmt);
$cnt = 0;
foreach (range(1,20000) as $id) {
  $msg = "bob is happy ".$id;
  db2_execute($stmt, [$id, $msg]);
 if (($cnt % 4000) == 0) {
   echo "insert ".$msg. "\n";
 }
 $cnt++;
}
$stmt = db2_prepare($db, 'select * from bob');
db2_execute($stmt);
var_dump($stmt);
$cnt = 0;
while($row=db2_fetch_array($stmt)) {
 if (($cnt % 4000) == 0) {
   echo "select ".trim($row[1]). "\n";
 }
 $cnt++;
}
$stmt = db2_prepare($db, 'select * from bob where id=?');
var_dump($stmt);
$cnt = 0;
foreach (range(1,20000) as $id) {
 db2_execute($stmt, [$id]);
 if (($cnt % 1000) == 0) { #Display memory usage each 1000 iterations
   echo "select by id=".$id;
   echo " MEMORY: " . memory_get_usage() . "\n";
 }
 $cnt++;
 db2_free_result($stmt);
}
?>
bash-4.3$ php loopleak.php 
resource(7) of type (DB2 Statement)
insert bob is happy 1
insert bob is happy 4001
insert bob is happy 8001
insert bob is happy 12001
insert bob is happy 16001
resource(8) of type (DB2 Statement)
select bob is happy 1
select bob is happy 4001
select bob is happy 8001
select bob is happy 12001
select bob is happy 16001
resource(9) of type (DB2 Statement)
select by id=1 MEMORY: 1137120
select by id=1001 MEMORY: 1137120
select by id=2001 MEMORY: 1137120
select by id=3001 MEMORY: 1137120
select by id=4001 MEMORY: 1137120
select by id=5001 MEMORY: 1137120
select by id=6001 MEMORY: 1137120
select by id=7001 MEMORY: 1137120
select by id=8001 MEMORY: 1137120
select by id=9001 MEMORY: 1137120
select by id=10001 MEMORY: 1137120
select by id=11001 MEMORY: 1137120
select by id=12001 MEMORY: 1137120
select by id=13001 MEMORY: 1137120
select by id=14001 MEMORY: 1137120
select by id=15001 MEMORY: 1137120
select by id=16001 MEMORY: 1137120
select by id=17001 MEMORY: 1137120
select by id=18001 MEMORY: 1137120
select by id=19001 MEMORY: 1137120
bash-4.3
 [2018-02-06 20:22 UTC] davetay1021 at gmail dot com
I have cut down your example to just the loop of insert statements. I still see the memory issue but also found a large difference in speed of execution using only db2_exec() vs db2_prepare()/db2_execute().

db2_exec() only:
[14:41:09][dst871@ZENDTST:/home/dst871]$ cat exec-only.php
<?php
$db = db2_connect('*LOCAL', '', '');
$ret = db2_exec($db,'drop table phptst.bob');
$ret = db2_exec($db,'create table phptst.bob(id int not null, msg char(4096) not null)');

$now = time();
$duration = 0;

$cnt = 0;
foreach (range(1,20000) as $id) {
  $msg = "bob is happy ".$id;
  db2_exec($db, "insert into phptst.bob values($id, '$msg')");
  if (($cnt % 4000) == 0) {
        $duration = (time() - $now);
        $now = time();
        echo $id . ": Duration " . $duration . " seconds\n";
        echo $id . ": Memory " . memory_get_usage() . "\n";
  }
  $cnt++;
}
[14:36:32][dst871@ZENDTST:/home/dst871]$ /usr/local/zendphp7/bin/php exec-only.php
1: Duration 0 seconds
1: Memory 1669512
4001: Duration 2 seconds
4001: Memory 3269520
8001: Duration 1 seconds
8001: Memory 4869520
12001: Duration 2 seconds
12001: Memory 6469520
16001: Duration 1 seconds
16001: Memory 8069520

db2_prepare()/db2_execute():
[14:47:13][dst871@ZENDTST:/home/dst871]$ cat test.php
<?php
$db = db2_connect('*LOCAL', '', '');
$ret = db2_exec($db,'drop table phptst.bob');
$ret = db2_exec($db,'create table phptst.bob(id int not null, msg char(4096) not null)');

$stmt = db2_prepare($db, 'insert into phptst.bob values(?,?)');

$now = time();
$duration = 0;

$cnt = 0;
foreach (range(1,20000) as $id) {
  $msg = "bob is happy ".$id;
  db2_execute($stmt, [$id, $msg]);
  if (($cnt % 4000) == 0) {
        $duration = (time() - $now);
        $now = time();
        echo $id . ": Duration " . $duration . " seconds\n";
        echo $id . ": Memory " . memory_get_usage() . "\n";
  }
  $cnt++;
}

[14:47:14][dst871@ZENDTST:/home/dst871]$ /usr/local/zendphp7/bin/php test.php
1: Duration 0 seconds
1: Memory 1670024
4001: Duration 47 seconds
4001: Memory 4485240
8001: Duration 148 seconds
8001: Memory 7301240
12001: Duration 258 seconds
12001: Memory 10117240
16001: Duration 393 seconds
16001: Memory 12933240

The time it takes db2_execute() to complete this script is more than excessive. Is it possible it is a system issue causing that big a difference between those 2 db2_ functions?

I am currently working on getting other versions of PHP on this system to test with and see if there's a difference.  

The results above are from PHP 7.1.12 and ibm_db2 version 2.0.2-zs1.
On PHP 5.6.18 and ibm_db2 version 1.9.7, this script runs in seconds with no memory issue.
 [2018-02-09 14:35 UTC] davetay1021 at gmail dot com
After further testing, I was able to run the loop of insert statements successfully on one of our 4 LPARs.  All 4 LPARs are identical in software and hardware.  The issue persists on 3 of these LPARs.  But since we were able to successfully run this on the 4th, I agree this is not an ibm_db2 extension issue. This can be closed.
 [2018-08-27 09:31 UTC] vnkbabu@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: vnkbabu
 [2018-08-27 09:31 UTC] vnkbabu@php.net
As per last comment closing the issue.
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Sat Jun 06 09:01:23 2020 UTC