RE: Hunting down (possible) memory leak in DBD::Oracle
Thanks, Chris, I looked at the two kde bugs you pointed out. Neither of them was an "invalid write". Also the software is actually failing here - not "working but getting valgrind errors" as was the case with the kde bugs. In my case I have "failing and getting valgrind errors". I can run the same case on an older version of perl and an older version of "DBD::Oracle" module (with the exact same oracle database) and get no errors. Valgrind documentation describes an "invalid write" as follows: http://valgrind.org/docs/manual/mc-manual.html#mc-manual.badrw For example: Invalid read of size 4 at 0x40F6BBCC: (within /usr/lib/libpng.so.2.1.0.9) by 0x40F6B804: (within /usr/lib/libpng.so.2.1.0.9) by 0x40B07FF4: read_png_image(QImageIO *) (kernel/qpngio.cpp:326) by 0x40AC751B: QImageIO::read() (kernel/qimage.cpp:3621) Address 0xB0E0 is not stack'd, malloc'd or free'd This happens when your program reads or writes memory at a place which Memcheck reckons it shouldn't. In this example, the program did a 4-byte read at address 0xB0E0, somewhere within the system-supplied library libpng.so.2.1.0.9, which was called from somewhere else in the same library, called from line 326 of qpngio.cpp, and so on. Memcheck tries to establish what the illegal address might relate to, since that's often useful. So, if it points into a block of memory which has already been freed, you'll be informed of this, and also where the block was freed. Likewise, if it should turn out to be just off the end of a heap block, a common result of off-by-one-errors in array subscripting, you'll be informed of this fact, and also where the block was allocated. If you use the --read-var-info option Memcheck will run more slowly but may give a more detailed description of any illegal address. In this example, Memcheck can't identify the address. Actually the address is on the stack, but, for some reason, this is not a valid stack address -- it is below the stack pointer and that isn't allowed. In this particular case it's probably caused by GCC generating invalid code, a known bug in some ancient versions of GCC. Note that Memcheck only tells you that your program is about to access memory at an illegal address. It can't stop the access from happening. So, if your program makes an access which normally would result in a segmentation fault, you program will still suffer the same fate -- but you will get a message from Memcheck immediately prior to this. In this particular example, reading junk on the stack is non-fatal, and the program stays alive. -Original Message- From: Christopher Jones [mailto:christopher.jo...@oracle.com] Sent: Monday, January 15, 2018 6:56 PM To: dbi-users@perl.org Subject: Re: Hunting down (possible) memory leak in DBD::Oracle On 16/1/18 9:17 am, Fennell, Brian wrote: > $ egrep -B1 -A20 -i 'invalid write' > /copy/sandbox/feeds/data/search4_1/valgrind-log.txt | head -22 > ==19402== ==19402== Invalid write of size 4 > ==19402==at 0xBD747E6: __intel_ssse3_rep_memcpy (in > /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1) > ==19402==by 0xBD6CE95: _intel_fast_memcpy.P (in > /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1) Valgrind doesn't do the greatest on binaries built with the Intel compiler: https://bugs.kde.org/show_bug.cgi?id=286769 https://bugs.kde.org/show_bug.cgi?id=139776 Chris -- http://twitter.com/ghrd
Re: Hunting down (possible) memory leak in DBD::Oracle
On 16/1/18 9:17 am, Fennell, Brian wrote: $ egrep -B1 -A20 -i 'invalid write' /copy/sandbox/feeds/data/search4_1/valgrind-log.txt | head -22 ==19402== ==19402== Invalid write of size 4 ==19402==at 0xBD747E6: __intel_ssse3_rep_memcpy (in /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1) ==19402==by 0xBD6CE95: _intel_fast_memcpy.P (in /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1) Valgrind doesn't do the greatest on binaries built with the Intel compiler: https://bugs.kde.org/show_bug.cgi?id=286769 https://bugs.kde.org/show_bug.cgi?id=139776 Chris -- http://twitter.com/ghrd
RE: Hunting down (possible) memory leak in DBD::Oracle
I got this case running with valgrind - Valgrind reported - 8 invalid writes, 8 invalid writes, 2239 accesses to uninitialized values. All invalid writes have a stack trace leading back to XS_DBD__Oracle__st_fetchrow_array (Oracle.xsi:662) and ora_st_fetch (oci8.c:4032) Details follow: export DATE_MANIP=DM5; export ORACLE_HOME=/db/app/oracle/product/12.1.0/client_1 ; export LD_LIBRARY_PATH=$ORACLE_HOME/lib ; export PATH="$ORACLE_HOME/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin"; export NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P1"; export NLS_NCHAR="AL16UTF16" ; export NLS_NCHAR_CHARACTERSET="AL16UTF16" ; export LANGUAGE=en_US:en:C; export LANG=C; export LC_ALL=C; export LC_CTYPE=C; /bin/rm -rf/feeds/data/search4_1/ProductAttributes-dbi-trace.txt\ /feeds/data/search4_1/getTableData-debug-log.txt \ /feeds/data/search4_1/valgrind-log.txt ; /usr/local/bin/valgrind \ --tool=memcheck \ --leak-check=yes \ --track-origins=yes \ --leak-check=full \ --show-leak-kinds=all \ --num-callers=100 \ --error-limit=no \ --log-file=/feeds/data/search4_1/valgrind-log.txt \ /usr/local/bin/perl \ [ . . . ] $ wc -l /copy/sandbox/feeds/data/search4_1/valgrind-log.txt 696325 /copy/sandbox/feeds/data/search4_1/valgrind-log.txt $ du -h /copy/sandbox/feeds/data/search4_1/valgrind-log.txt 53M /copy/sandbox/feeds/data/search4_1/valgrind-log.txt $ egrep -c -i 'invalid write' /copy/sandbox/feeds/data/search4_1/valgrind-log.txt 8 $ egrep -c -i 'invalid read' /copy/sandbox/feeds/data/search4_1/valgrind-log.txt 8 $ egrep -c -i 'uninitialised value' /copy/sandbox/feeds/data/search4_1/valgrind-log.txt 2239 $ egrep -n -A20 -i 'invalid write' /copy/sandbox/feeds/data/search4_1/valgrind-log.txt | egrep '(oci8\.c|Oracle\.xsi)' 51575-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032) 51576-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array (Oracle.xsi:662) 51596-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032) 51597-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array (Oracle.xsi:662) 51614-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032) 51615-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array (Oracle.xsi:662) 51670-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032) 51671-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array (Oracle.xsi:662) 51710-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032) 51711-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array (Oracle.xsi:662) 51731-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032) 51732-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array (Oracle.xsi:662) 51771-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032) 51772-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array (Oracle.xsi:662) 51830-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032) 51831-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array (Oracle.xsi:662) $ egrep -B1 -A20 -i 'invalid write' /copy/sandbox/feeds/data/search4_1/valgrind-log.txt | head -22 ==19402== ==19402== Invalid write of size 4 ==19402==at 0xBD747E6: __intel_ssse3_rep_memcpy (in /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1) ==19402==by 0xBD6CE95: _intel_fast_memcpy.P (in /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1) ==19402==by 0xCF70D36: kpccclr (in /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1) ==19402==by 0xCF705D7: kpccs2c (in /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1) ==19402==by 0xCF6ED9E: ttccfpg (in /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1) ==19402==by 0xCF6C799: ttcfour (in /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1) ==19402==by 0xCF3B0AE: kpufcpf (in /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1) ==19402==by 0xCF392F5: kpufch0 (in /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1) ==19402==by 0xCF37C34: kpufch (in /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1) ==19402==by 0xB14F1CA: OCIStmtFetch2 (in /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1) ==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032) ==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array (Oracle.xsi:662) ==19402==by 0x6D33069: XS_DBI_dispatch (DBI.xs:3797) ==19402==by 0x4EF240E: Perl_pp_entersub (in /usr/lib64/perl5/CORE/libperl.so) ==19402==by 0x4EEAB85: Perl_runops_standard (in /usr/lib64/perl5/CORE/libperl.so) ==19402==by 0x4E87984: perl_run (in /usr/lib64/perl5/CORE/libperl.so) ==19402==by 0x400D98: ??? (in /usr/bin/perl) ==19402==by 0x6174C04: (below main) (in /usr/lib64/libc-2.17.so) ==19402== Address 0x14ec4275f is 4,095,739,679 bytes inside a block of size 4,095,741,856 in arena "client" ==19402==
RE: Hunting down (possible) memory leak in DBD::Oracle
John, Thanks for your incites. I tried what you said. I read up on NVARCHAR2 v VARCHAR2 - interesting. I also see that Oracle has a way (more than one way) to specify if a VARCHAR2 should contain bytes or characters - further while a VARCHAR2(11 byte) and a VARCHAR2(11 char) are different (the second can have as many as 4 times as many bytes in it as the first) VARCHAR2(4000 byte) and VARCHAR(4000 char) are not different - 4000 bytes is the max for either - no matter the size of a character. Unicode makes everything harder. "CHAR" may mean "BYTE" "2 BYTES" or "4 BYTES". And client and server have to agree. I tried this: I uses SUBSTRB(field,1,) to truncate the actual bytes coming from oracle back to the client. Results are a bit odd. For 3999 no change. For 2000 all errors disappear. For 3000 - some cases that used to error now succeed - but some cases that used to succeed now fail. I also played with "matching up" the inner select and the outer - which isn't really very valuable - the client never sees the data from the inner select - and I only added the outer select so I could select by row number (which I couldn't do on the inner select since "rownum" is a pseudo column). The outer select is basically the interface that OCI sees. Specifically cast(SUBSTRB(field,1,2000) as VARCHAR(4000)) errors out exactly the same way as no substrb(). For the 2000 byte case I used cast(SUBSTRB(field,1,2000) as VARCHAR(2000 byte)). For the 3000 byte case I used cast(SUBSTRB(field,1,3000) as VARCHAR(3000 byte)). I don't think truncating to 2000 bytes is a solution, but I suppose I could try breaking the field into 2 2000 byte strings (or 4 1000 bytes strings) and recombine them in some other part of the code. I would need to consider the best way to do that - perhaps a output column naming convention. I ran all of my variations on the old server and the new server and nothing failed on the old server. Too much output to send it all to the list - if anyone wants something specific I can send it. My money is still on a wild pointer or similar. I looked thru the DBI log and found the following various field rc error codes (sorted): field #3 with rc=12851(UNKNOWN RC=12851)) field #3 with rc=12854(UNKNOWN RC=12854)) field #3 with rc=20041(UNKNOWN RC=20041)) field #3 with rc=25934(UNKNOWN RC=25934)) field #3 with rc=26962(UNKNOWN RC=26962)) field #3 with rc=48(UNKNOWN RC=48)) field #3 with rc=83(UNKNOWN RC=83)) field #4 with rc=1280(UNKNOWN RC=1280)) field #4 with rc=12870(UNKNOWN RC=12870)) field #4 with rc=14128(UNKNOWN RC=14128)) field #4 with rc=17230(UNKNOWN RC=17230)) field #4 with rc=18688(UNKNOWN RC=18688)) field #4 with rc=24919(UNKNOWN RC=24919)) field #4 with rc=25196(UNKNOWN RC=25196)) field #4 with rc=25926(UNKNOWN RC=25926)) field #4 with rc=26691(UNKNOWN RC=26691)) I am not really sure which goes with what case - but I am really not thinking that the codes are real oracle error codes - but junk data which is a symptom of a problem somewhere else. I could modify the tests to clear out the log and capture the "UNKNOWN RC" codes for each, but I would rather to more digging on the original case I reported to this list. Brian Fennell
Re: Hunting down (possible) memory leak in DBD::Oracle
On 2017-12-19 20:55:30 +, Fennell, Brian wrote: > And, also with the log level set to 15 here are the LAST 200 lines [...] > -> fetchrow_array for DBD::Oracle::st > (AAA::DBI::Connection::st=HASH(0x3abd310)~0x39f75f0) thr#24d4010 > dbd_st_fetch 6 fields... > dbd_st_fetched 6 fields with status of 0(SUCCESS) > field #1 with rc=0(OK) > > 3abd340 (field=0): '1105427' > field #2 with rc=0(OK) > > 3abd340 (field=1): '1268254' > field #3 with rc=0(OK) > > 3abd340 (field=2): 'sampl...' > field #4 with rc=25196(UNKNOWN RC=25196)) > OCIErrorGet(3b535c8,1,"",7ffd7b72b7cc,"ORA-01403: no data found > ",1024,2)=SUCCESS > OCIErrorGet after ORA-25196 error on field 4 of 6, ora_type 2 > (er1:ok): -1, 1403: ORA-01403: no data found > > OCIErrorGet(3b535c8,2,"",7ffd7b72b7cc,"ORA-01403: no data found > ",1024,2)=NO_DATA > -- HandleSetErr err=1403, errstr='ORA-01403: no data found (DBD ERROR: > ORA-25196 error on field 4 of 6, ora_type 2)', state=undef, undef > > 3abd340 (field=3): undef Can you check the actual contents of any rows in tableA with field1=1268254 and field2='sampl...'? The error occurs in field3 which is numeric, but it's adjacent to field4 which is VARCHAR2(4000). I wonder if an overflow is possible if field4 is actually close to 4000 characters and it is expanded further by character encoding. Which raises the next question: What are the database and client encodings set to? hp -- _ | Peter J. Holzer | I think we need two definitions: |_|_) | WSR - Softwaredevelopment | 1) The problem the *users* want us to solve | | | und Projektunterstützung | 2) The problem our solution addresses. __/ | h...@wsr.ac.at |-- Phillip Hallam-Baker on spam signature.asc Description: PGP signature
RE: Hunting down (possible) memory leak in DBD::Oracle
And, also with the log level set to 15 here are the LAST 200 lines 3abd340 (field=0): '1127646' field #2 with rc=0(OK) 3abd340 (field=1): '1268251' field #3 with rc=0(OK) 3abd340 (field=2): 'a...' field #4 with rc=0(OK) 3abd340 (field=3): '1' field #5 with rc=1405(NULL) 3abd340 (field=4): undef field #6 with rc=0(OK) 3abd340 (field=5): 'en_US' <- fetchrow_array= ( '1127646' '1268251' 'aa' '1' undef 'en_US' ) [6 items] row858 at /dirname/scriptname.pl line 196 -> fetchrow_array for DBD::Oracle::st (AAA::DBI::Connection::st=HASH(0x3abd310)~0x39f75f0) thr#24d4010 dbd_st_fetch 6 fields... dbd_st_fetched 6 fields with status of 0(SUCCESS) field #1 with rc=0(OK) 3abd340 (field=0): '1093644' field #2 with rc=0(OK) 3abd340 (field=1): '1268251' field #3 with rc=0(OK) 3abd340 (field=2): 'sampl...' field #4 with rc=0(OK) 3abd340 (field=3): '1' field #5 with rc=1405(NULL) 3abd340 (field=4): undef field #6 with rc=0(OK) 3abd340 (field=5): 'en_US' <- fetchrow_array= ( '1093644' '1268251' 'sampledata' '1' undef 'en_US' ) [6 items] row859 at /dirname/scriptname.pl line 196 -> fetchrow_array for DBD::Oracle::st (AAA::DBI::Connection::st=HASH(0x3abd310)~0x39f75f0) thr#24d4010 dbd_st_fetch 6 fields... dbd_st_fetched 6 fields with status of 0(SUCCESS) field #1 with rc=0(OK) 3abd340 (field=0): '1093997' field #2 with rc=0(OK) 3abd340 (field=1): '1268252' field #3 with rc=0(OK) 3abd340 (field=2): 'sampl...' field #4 with rc=0(OK) 3abd340 (field=3): '1' field #5 with rc=1405(NULL) 3abd340 (field=4): undef field #6 with rc=0(OK) 3abd340 (field=5): 'en_US' <- fetchrow_array= ( '1093997' '1268252' 'sampledata' '1' undef 'en_US' ) [6 items] row860 at /dirname/scriptname.pl line 196 -> fetchrow_array for DBD::Oracle::st (AAA::DBI::Connection::st=HASH(0x3abd310)~0x39f75f0) thr#24d4010 dbd_st_fetch 6 fields... dbd_st_fetched 6 fields with status of 0(SUCCESS) field #1 with rc=0(OK) 3abd340 (field=0): '1093904' field #2 with rc=0(OK) 3abd340 (field=1): '1268252' field #3 with rc=0(OK) 3abd340 (field=2): 'sampl...' field #4 with rc=0(OK) 3abd340 (field=3): '1' field #5 with rc=1405(NULL) 3abd340 (field=4): undef field #6 with rc=0(OK) 3abd340 (field=5): 'en_US' <- fetchrow_array= ( '1093904' '1268252' 'samplecc' '1' undef 'en_US' ) [6 items] row861 at /dirname/scriptname.pl line 196 -> fetchrow_array for DBD::Oracle::st (AAA::DBI::Connection::st=HASH(0x3abd310)~0x39f75f0) thr#24d4010 dbd_st_fetch 6 fields... dbd_st_fetched 6 fields with status of 0(SUCCESS) field #1 with rc=0(OK) 3abd340 (field=0): '1127647' field #2 with rc=0(OK) 3abd340 (field=1): '1268253' field #3 with rc=0(OK) 3abd340 (field=2): 'd...' field #4 with rc=0(OK) 3abd340 (field=3): '0' field #5 with rc=1405(NULL) 3abd340 (field=4): undef field #6 with rc=0(OK) 3abd340 (field=5): 'en_US' <- fetchrow_array= ( '1127647' '1268253' 'd' '0' undef 'en_US' ) [6 items] row862 at /dirname/scriptname.pl line 196 -> fetchrow_array for DBD::Oracle::st (AAA::DBI::Connection::st=HASH(0x3abd310)~0x39f75f0) thr#24d4010 dbd_st_fetch 6 fields... dbd_st_fetched 6 fields with status of 0(SUCCESS) field #1 with rc=0(OK) 3abd340 (field=0): '1127648' field #2 with rc=0(OK) 3abd340 (field=1): '1268253' field #3 with rc=0(OK) 3abd340 (field=2): 'a...' field #4 with rc=0(OK) 3abd340 (field=3): '0' field #5 with rc=1405(NULL) 3abd340 (field=4): undef field #6 with rc=0(OK) 3abd340 (field=5): 'en_US' <- fetchrow_array= ( '1127648' '1268253' 'aa' '0' undef 'en_US' ) [6 items] row863 at /dirname/scriptname.pl line 196 -> fetchrow_array for DBD::Oracle::st (AAA::DBI::Connection::st=HASH(0x3abd310)~0x39f75f0) thr#24d4010 dbd_st_fetch 6 fields... dbd_st_fetched 6 fields with status of 0(SUCCESS) field #1 with rc=0(OK) 3abd340 (field=0): '1105426' field #2 with rc=0(OK) 3abd340 (field=1): '1268253' field #3 with rc=0(OK) 3abd340 (field=2): 'samp
RE: Hunting down (possible) memory leak in DBD::Oracle
With the log level set to 15 here are the first 200 lines of log AAA::DBI::Connection::db=HASH(0x3abce00) trace level set to 0x0/15 (DBI @ 0x0/0) in DBI 1.637-ithread (pid 12594) -> STORE for DBD::Oracle::db (AAA::DBI::Connection::db=HASH(0x3abce00)~INNER 'RowCacheSize' 2097152) thr#24d4010 <- STORE= ( 1 ) [1 items] at /dirname/scriptname.pl line 78 -> prepare for DBD::Oracle::db (AAA::DBI::Connection::db=HASH(0x3abcef0)~0x3abce00 'SELECT [ yada yada yada ]') thr#24d4010 New 'AAA::DBI::Connection::st' (for DBD::Oracle::st, parent=AAA::DBI::Connection::db=HASH(0x3abce00), id=undef) dbih_setup_handle(AAA::DBI::Connection::st=HASH(0x3abd310)=>AAA::DBI::Connection::st=HASH(0x39f75f0), DBD::Oracle::st, 268ae18, Null!) dbih_make_com(AAA::DBI::Connection::db=HASH(0x3abce00), 3abdfc0, DBD::Oracle::st, 464, 0) thr#24d4010 dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), Err, AAA::DBI::Connection::db=HASH(0x3abce00)) SCALAR(0x268acc8) (already defined) dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), State, AAA::DBI::Connection::db=HASH(0x3abce00)) SCALAR(0x268ad88) (already defined) dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), Errstr, AAA::DBI::Connection::db=HASH(0x3abce00)) SCALAR(0x268ad28) (already defined) dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), TraceLevel, AAA::DBI::Connection::db=HASH(0x3abce00)) 15 (already defined) dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), FetchHashKeyName, AAA::DBI::Connection::db=HASH(0x3abce00)) 'NAME' (already defined) dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), HandleSetErr, AAA::DBI::Connection::db=HASH(0x3abce00)) undef (not defined) dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), HandleError, AAA::DBI::Connection::db=HASH(0x3abce00)) undef (not defined) dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), ReadOnly, AAA::DBI::Connection::db=HASH(0x3abce00)) undef (not defined) dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), Profile, AAA::DBI::Connection::db=HASH(0x3abce00)) undef (not defined) OCIHandleAlloc(3b2c0f0,3be0cc8,OCI_HTYPE_STMT,0,0)=SUCCESS OCIStmtPrepare(3ba3190,3b535c8,'SELECT [ yada yada yada ]',513,1,0)=SUCCESS OCIAttrGet(3ba3190,OCI_HTYPE_STMT,3be0cdc,0,OCI_ATTR_STMT_TYPE,3b535c8)=SUCCESS dbd_st_prepare'd sql SELECT ( auto_lob1, check_sql1) dbd_describe SELECT (EXPLICIT, lb 80)... OCIStmtExecute(3b5b028,3ba3190,3b535c8,0,0,0,0,mode=DESCRIBE_ONLY,16)=SUCCESS OCIAttrGet(3ba3190,OCI_HTYPE_STMT,7ffd7b72baa4,0,OCI_ATTR_PARAM_COUNT,3b535c8)=SUCCESS OCIParamGet(3ba3190,4,3b535c8,3be10e0,1,OCI_HTYPE_STMT)=SUCCESS OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be1110,0,OCI_ATTR_DATA_TYPE,3b535c8)=SUCCESS OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be1112,0,OCI_ATTR_DATA_SIZE,3b535c8)=SUCCESS OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be1128,0,OCI_ATTR_CHAR_USED,3b535c8)=SUCCESS OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be112a,0,OCI_ATTR_CHAR_SIZE,3b535c8)=SUCCESS OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be112c,0,OCI_ATTR_CHARSET_ID,3b535c8)=SUCCESS OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be112e,0,OCI_ATTR_CHARSET_FORM,3b535c8)=SUCCESS OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be1114,0,OCI_ATTR_PRECISION,3b535c8)=SUCCESS OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be1116,0,OCI_ATTR_SCALE,3b535c8)=SUCCESS OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be1117,0,OCI_ATTR_IS_NULL,3b535c8)=SUCCESS OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be1118,7ffd7b72baac,OCI_ATTR_NAME,3b535c8)=SUCCESS Describe col #1 type=2(NVARCHAR2) Described col 1: dbtype 2(NVARCHAR2), scale -127, prec 0, nullok 1, name ROW_NUMBER : dbsize 22, char_used 0, char_size 0, csid 0, csform 0(0), disize 171 fbh 1: 'ROW_NUMBER' NULLable, otype 2-> 5, dbsize 22/172, p0.s-127 OCIParamGet(3ba3190,4,3b535c8,3be1188,2,OCI_HTYPE_STMT)=SUCCESS OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11b8,0,OCI_ATTR_DATA_TYPE,3b535c8)=SUCCESS OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11ba,0,OCI_ATTR_DATA_SIZE,3b535c8)=SUCCESS OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11d0,0,OCI_ATTR_CHAR_USED,3b535c8)=SUCCESS OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11d2,0,OCI_ATTR_CHAR_SIZE,3b535c8)=SUCCESS OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11d4,0,OCI_ATTR_CHARSET_ID,3b535c8)=SUCCESS OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11d6,0,OCI_ATTR_CHARSET_FORM,3b535c8)=SUCCESS OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11bc,0,OCI_ATTR_PRECISION,3b535c8)=SUCCESS OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11be,0,OCI_ATTR_SCALE,3b535c8)=SUCCESS OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11bf,0,OCI_ATTR_IS_NULL,3b535c8)=SUCCESS OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11c0,7ffd7b72baac,OCI_ATTR_NAME,3b535c8)=SUCCESS Describe col #2 type=2(NVARCHAR2) Described col 2: dbtype 2(NV
RE: Hunting down (possible) memory leak in DBD::Oracle
John,
Thanks for the ideas to change the cache params - I will try that!
Here is the SQL and the field types:
SELECT
d.ROW_NUMBER,
d.f1,
d.f2,
d.f3,
d.f4,
d.f5
FROM
(
SELECT /*+ FULL(A) PARALLEL(A 6) */
rownum ROW_NUMBER,
A.field1 f1 ,
A.field2 f2,
A.field3 f3,
A.field4 f4,
B.field5 f5
FROM
tableA A,
tableB B
WHERE
B.field6 IN ( 'TOK3', 'TOK4', 'TOK5' )
AND B.field7 LIKE'A%'
AND B.field8 IN ('TOK1', 'TOK2')
AND B.fkfield1= A.field1
ORDER BY
2, 3, 4, 5, 6
) d
WHERE
d.row_number < 202
AND d.row_number >= 100
Field Types:
A.field1 NUMBER(12)
A.field2 VARCHAR2(20)
A.field3 NUMBER(15,3)
A.field4 VARCHAR2(4000)
B.field5 VARCHAR2(5)
B.field6 VARCHAR2(20)
B.field7 VARCHAR2(8)
B.field8 VARCHAR2(8)
B.fkfield1 NUMBER(12)
Re: Hunting down (possible) memory leak in DBD::Oracle
Well it will be in either one of two .c files dbdimp.c or oci8.c The XS side of things Oracle.xs is not used very much. The level 15 debug will get deep inside the c to see where it is happening. The trace you gave is a little high level you are right ORA-01403 dose not make much sense here. If could be running out of buffer. Give some of the caching params a tweak https://metacpan.org/pod/DBD::Oracle#RowCacheSize if you can try give fetchrow_hashref a try as see if the error happens there as well. Cheers John DBD::Oracle - Oracle database driver for the DBI module ...<https://metacpan.org/pod/DBD::Oracle#RowCacheSize> metacpan.org Oracle database driver for the DBI module ... NAME; VERSION; SYNOPSIS; DESCRIPTION; CONSTANTS; DBI CLASS METHODS. connect. OS authentication From: Fennell, Brian Sent: December 18, 2017 11:25 AM To: John Scoles; dbi-users@perl.org Subject: RE: Hunting down (possible) memory leak in DBD::Oracle John, Thanks so much for your reply! I have put off this work for a few years and now the pressure is on - the original box and OS are so old that the DBA and System Engineer and the Operations manager have all ganged up on me. I suppose I could try and work around by downgrading both the perl and the DBD::Oracle to the same version we use in production, but it would be nice to actually fix the bug if I can. I tried just downgrading the DBD::Oracle, but changes in perl 5 to support MULTIPLICITY made that look like more than just a little work - spend two days on it and then backed off. I am a polyglot programmer so I can program in C and Perl (and about a dozen other languages). I have done enough time with C that it doesn't scare me. Valgrind is new to me, but make and gcc and ld are not. I have started to read the Valgrind docs and it seems to make sense - it basically emulates all the CPU instructions with injected instrumentation - I assume it works for Intel and Red Hat if it works at all (and it seems to have a long history and good open source support community). Perhaps I am fooling myself, but I figure it is worth a try. I have negotiated support from both DBA and System Engineering (the Red Hat OS guys) so if I am going to fix this now is the time. The only other option I can think of is to try to get the old code working with the DBD::JDBC driver (which would mean adding a JVM running in parallel and additional overhead - so I would rather not). 1) The error changes depending on the data - which is why I think it is a buffer overrun or a wild pointer - but it is always in "field N of N" - Current I can reproduce with ORA-01403 2) I will re-try at level 15 and post the results - current at 4 (or perhaps 5) here is a section from the log (which suggests to me it is happing in the C code and not in the Perl -> fetchrow_array for DBD::Oracle::st (GSI::DBI::Connection::st=HASH(0x29353c8)~0x286f6b0) thr#134c010 dbd_st_fetch 6 fields... dbd_st_fetched 6 fields with status of 0(SUCCESS) field #1 with rc=0(OK) field #2 with rc=0(OK) field #3 with rc=0(OK) field #4 with rc=1405(NULL) field #5 with rc=0(OK) field #6 with rc=0(OK) -> fetchrow_array for DBD::Oracle::st (GSI::DBI::Connection::st=HASH(0x29353c8)~0x286f6b0) thr#134c010 dbd_st_fetch 6 fields... dbd_st_fetched 6 fields with status of 0(SUCCESS) field #1 with rc=0(OK) field #2 with rc=0(OK) field #3 with rc=0(OK) field #4 with rc=14135(UNKNOWN RC=14135)) OCIErrorGet after ORA-14135 error on field 4 of 6, ora_type 2 (er1:ok): -1, 1403: ORA-01403: no data found -- HandleSetErr err=1403, errstr='ORA-01403: no data found (DBD ERROR: ORA-14135 error on field 4 of 6, ora_type 2)', state=undef, undef field #5 with rc=0(OK) field #6 with rc=0(OK) 1 -> FETCH for DBD::Oracle::st (GSI::DBI::Connection::st=HASH(0x286f6b0)~INNER 'ParamValues') thr#134c010 3) I think the most exotic thing in these tables is a VARCHAR2 but I will check and post the results. 4) I looks like it is in the XS to me (see answer to 2) - but I suppose it could be elsewhere - like a loopback-perl-ref that should be weak but is not. 5) I think I have what I need, DBA installed Oracle 12 OCI client and "dot.so" libraries but currently I am concerned that I am using "ins_rdbms.mk" when I should be using "demo.mk" or similar - I am getting a Warning (see details below) when I run Makefile.PL - I asked DBA to look into installing the "demo.mk" file and consider opening up a Oracle METALINK support ticket to see if another customer had already solved this with Oracle's help. Details: # /usr/local/bin/perl Makefile.PL -g Using DBI 1.637 (for perl 5.016003 on x86_64-linux-thread-multi) installed in /usr/local/lib64/perl5/auto/DB
RE: Hunting down (possible) memory leak in DBD::Oracle
Pluta, Looks like it is worth a try - when I looked at the project before it looked like it was for installing a "per user" perl. Does it work for root / all users on a box as well? Brian
RE: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **
Good question - I have asked DBA and am waiting for a reply.
-Original Message-
From: Howard, Chris [mailto:howa...@prpa.org]
Sent: Monday, December 18, 2017 11:36 AM
To: Fennell, Brian ; dbi-users@perl.org
Subject: RE: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **
Fennell,
Are you seeing anything on the database side, in the alert log, etc.?
Howard
-Original Message-
From: Fennell, Brian [mailto:fenne...@radial.com]
Sent: Monday, December 18, 2017 8:30 AM
To: Howard, Chris ; dbi-users@perl.org
Subject: RE: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **
Thanks for the reply, Howard,
I am using the exact same database - a test database that has copies of the
production data put into it once a day - besides that it doesn't change much
(if at all) during the day.
The SQL I am using doesn't leave out a "bad guy" - I thought of that and
actually had an off-by-one gap in some of my early tests. Closed that hole by
changing a ">" to a ">=".
Here is the SQL (with the original table and field names changed to allow for
sharing
SELECT
d.ROW_NUMBER,
d.f1,
d.f2,
d.f3,
d.f4,
d.f5
FROM
(
SELECT /*+ FULL(A) PARALLEL(A 6) */
rownum ROW_NUMBER,
A.field1 f1 ,
A.field2 f2,
A.field3 f3,
A.field4 f4,
B.field5 f5
FROM
tableA A,
tableB B
WHERE
B.field6 IN ( 'TOK3', 'TOK4', 'TOK5' )
AND B.field7 LIKE'A%'
AND B.field8 IN ('TOK1', 'TOK2')
AND B.fkfield1= A.field1
ORDER BY
1, 2, 3, 4, 5
) d
WHERE
d.row_number < 202
AND d.row_number >= 100
-Original Message-
From: Howard, Chris [mailto:howa...@prpa.org]
Sent: Monday, December 18, 2017 9:21 AM
To: Fennell, Brian ; dbi-users@perl.org
Subject: RE: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **
Same database... do you mean the actual very same data source?
What is the Oracle error?
To eliminate problems based on data (implicit conversions, that kind of thing)
can you do a run from row 500,000 to 1,500,000 ? (I think you have this
covered, but maybe that row right at the breaking spot is somehow a bad guy.)
-Original Message-
From: Fennell, Brian [mailto:fenne...@radial.com]
Sent: Saturday, December 16, 2017 3:19 PM
To: dbi-users@perl.org
Subject: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **
Dear DBI people -
I am trying to port some old perl code to a new box. (see Details below)
Needless to say the original box and code works fine, but the new box (and old
code) does not.
Specifically what I am seeing is that when I select slightly over a million
records from a specific join of two tables (to be dumped one row at a time into
a TSV file) we get strange ORA-N errors that don't really make any sense in
this context.
The Same database and same table works fine on the original box with the large
number of records. 2 million records always causes errors but two groups of
1million (divided up by ROWNUM - the EXACT same rows) causes no errors. I am
using a test database with little activity do I am reasonably certain that the
queries deal with the same rows.
So I am thinking the problem is data volume and not any specific piece of data
(originally I thought it might be an odd string/data related error, but I am
starting to think it is a memory leak of some kind).
The error always happens inside of fetchrow_array - and "$dbh->trace( 4 ,
$filename )" shows that the error originates inside the DBD::Oracle module
while reading field 3 of 6.
Researching the ORA-NN error gives a perfectly sane description that makes
no sense at all in the context of reading a specific field.
We are going thru an Audit and tightening up security so there are some things
(like REAL hostnames and REAL column/table names) that I cannot share - but I
will try to share as much as I can.
The Host I am calling "prod" below is the only one NOT exhibiting this issue.
Things I want to try -
1) recompile the DBD::Oracle module on Host "sandbox" with "perl Makefile.PL
-g" and then use Valgrind. I haven't used Valgrind before, but I guess it is
time to learn.
2) Anything else this list suggests.
Details:
Host: prod
OS: Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
Perl: 5.8.8 built for x86_64-linux
DBI: 1.53
DBD::Oracle: 1.19
Oracle: 10.2.0.1.0
Host: dev
OS: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Perl: 5.16.1 built for x86_64-linux
DBI: 1.631
DBD::Oracle: 1.74
Oracle: 11.2.0.3.0
Host: prodnew
OS: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Perl: 5.16.1 built
RE: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **
Ok . . . I am already benefiting from the support from this list : - )
I noticed that I was actually ordering my query BY rownum (which doesn't make
much sense . . . and perhaps oracle's optimizer recognized this and ignored the
pseudo column.)
Just to be sure, I change the query and then reran my tests - the results,
however, did not change
New SQL
SELECT
d.ROW_NUMBER,
d.f1,
d.f2,
d.f3,
d.f4,
d.f5
FROM
(
SELECT /*+ FULL(A) PARALLEL(A 6) */
rownum ROW_NUMBER,
A.field1 f1 ,
A.field2 f2,
A.field3 f3,
A.field4 f4,
B.field5 f5
FROM
tableA A,
tableB B
WHERE
B.field6 IN ( 'TOK3', 'TOK4', 'TOK5' )
AND B.field7 LIKE'A%'
AND B.field8 IN ('TOK1', 'TOK2')
AND B.fkfield1= A.field1
ORDER BY
2, 3, 4, 5, 6
) d
WHERE
d.row_number < 202
AND d.row_number >= 100
Here is a summary of the test results (I have a test harness which uses ssh to
run the exact same tests on more than one host).
INPUT: ( ( HOSTNAME = sandbox ) ( RANGE = 200 0 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 2 )
INPUT: ( ( HOSTNAME = prod) ( RANGE = 200 0 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = sandbox ) ( RANGE = 100 0 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = prod) ( RANGE = 100 0 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = sandbox ) ( RANGE = 200 100 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = prod) ( RANGE = 200 100 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = sandbox ) ( RANGE = 202 100 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = prod) ( RANGE = 202 100 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = sandbox ) ( RANGE = 203 100 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 2 )
INPUT: ( ( HOSTNAME = prod) ( RANGE = 203 100 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
RE: Hunting down (possible) memory leak in DBD::Oracle
John, Thanks so much for your reply! I have put off this work for a few years and now the pressure is on - the original box and OS are so old that the DBA and System Engineer and the Operations manager have all ganged up on me. I suppose I could try and work around by downgrading both the perl and the DBD::Oracle to the same version we use in production, but it would be nice to actually fix the bug if I can. I tried just downgrading the DBD::Oracle, but changes in perl 5 to support MULTIPLICITY made that look like more than just a little work - spend two days on it and then backed off. I am a polyglot programmer so I can program in C and Perl (and about a dozen other languages). I have done enough time with C that it doesn't scare me. Valgrind is new to me, but make and gcc and ld are not. I have started to read the Valgrind docs and it seems to make sense - it basically emulates all the CPU instructions with injected instrumentation - I assume it works for Intel and Red Hat if it works at all (and it seems to have a long history and good open source support community). Perhaps I am fooling myself, but I figure it is worth a try. I have negotiated support from both DBA and System Engineering (the Red Hat OS guys) so if I am going to fix this now is the time. The only other option I can think of is to try to get the old code working with the DBD::JDBC driver (which would mean adding a JVM running in parallel and additional overhead - so I would rather not). 1) The error changes depending on the data - which is why I think it is a buffer overrun or a wild pointer - but it is always in "field N of N" - Current I can reproduce with ORA-01403 2) I will re-try at level 15 and post the results - current at 4 (or perhaps 5) here is a section from the log (which suggests to me it is happing in the C code and not in the Perl -> fetchrow_array for DBD::Oracle::st (GSI::DBI::Connection::st=HASH(0x29353c8)~0x286f6b0) thr#134c010 dbd_st_fetch 6 fields... dbd_st_fetched 6 fields with status of 0(SUCCESS) field #1 with rc=0(OK) field #2 with rc=0(OK) field #3 with rc=0(OK) field #4 with rc=1405(NULL) field #5 with rc=0(OK) field #6 with rc=0(OK) -> fetchrow_array for DBD::Oracle::st (GSI::DBI::Connection::st=HASH(0x29353c8)~0x286f6b0) thr#134c010 dbd_st_fetch 6 fields... dbd_st_fetched 6 fields with status of 0(SUCCESS) field #1 with rc=0(OK) field #2 with rc=0(OK) field #3 with rc=0(OK) field #4 with rc=14135(UNKNOWN RC=14135)) OCIErrorGet after ORA-14135 error on field 4 of 6, ora_type 2 (er1:ok): -1, 1403: ORA-01403: no data found -- HandleSetErr err=1403, errstr='ORA-01403: no data found (DBD ERROR: ORA-14135 error on field 4 of 6, ora_type 2)', state=undef, undef field #5 with rc=0(OK) field #6 with rc=0(OK) 1 -> FETCH for DBD::Oracle::st (GSI::DBI::Connection::st=HASH(0x286f6b0)~INNER 'ParamValues') thr#134c010 3) I think the most exotic thing in these tables is a VARCHAR2 but I will check and post the results. 4) I looks like it is in the XS to me (see answer to 2) - but I suppose it could be elsewhere - like a loopback-perl-ref that should be weak but is not. 5) I think I have what I need, DBA installed Oracle 12 OCI client and "dot.so" libraries but currently I am concerned that I am using "ins_rdbms.mk" when I should be using "demo.mk" or similar - I am getting a Warning (see details below) when I run Makefile.PL - I asked DBA to look into installing the "demo.mk" file and consider opening up a Oracle METALINK support ticket to see if another customer had already solved this with Oracle's help. Details: # /usr/local/bin/perl Makefile.PL -g Using DBI 1.637 (for perl 5.016003 on x86_64-linux-thread-multi) installed in /usr/local/lib64/perl5/auto/DBI/ Configuring DBD::Oracle for perl 5.016003 on linux (x86_64-linux-thread-multi) Remember to actually *READ* the README file! Especially if you have any problems. Installing on a linux, Ver#3.10 Using Oracle in /db/app/oracle/product/12.1.0/client_1 DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR) Oracle version 12.1.0.2 (12.1) Found /db/app/oracle/product/12.1.0/client_1/rdbms/lib/ins_rdbms.mk Using /db/app/oracle/product/12.1.0/client_1/rdbms/lib/ins_rdbms.mk Your LD_LIBRARY_PATH env var is set to '/db/app/oracle/product/12.1.0/client_1/lib:/db/app/oracle/product/12.1.0/client_1' Reading /db/app/oracle/product/12.1.0/client_1/rdbms/lib/ins_rdbms.mk Reading /db/app/oracle/product/12.1.0/client_1/rdbms/lib/env_rdbms.mk WARNING: Oracle /db/app/oracle/product/12.1.0/client_1/rdbms/lib/ins_rdbms.mk doesn't define a 'build' rule. WARNING: I will now try to guess how to build and link DBD::Oracle for you. This kind of guess work is very error prone and Oracle-version sensitive. It is possible that it won't be supported in future versions of DBD::Oracle.
RE: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **
Thanks for the reply, Howard,
I am using the exact same database - a test database that has copies of the
production data put into it once a day - besides that it doesn't change much
(if at all) during the day.
The SQL I am using doesn't leave out a "bad guy" - I thought of that and
actually had an off-by-one gap in some of my early tests. Closed that hole by
changing a ">" to a ">=".
Here is the SQL (with the original table and field names changed to allow for
sharing
SELECT
d.ROW_NUMBER,
d.f1,
d.f2,
d.f3,
d.f4,
d.f5
FROM
(
SELECT /*+ FULL(A) PARALLEL(A 6) */
rownum ROW_NUMBER,
A.field1 f1 ,
A.field2 f2,
A.field3 f3,
A.field4 f4,
B.field5 f5
FROM
tableA A,
tableB B
WHERE
B.field6 IN ( 'TOK3', 'TOK4', 'TOK5' )
AND B.field7 LIKE'A%'
AND B.field8 IN ('TOK1', 'TOK2')
AND B.fkfield1= A.field1
ORDER BY
1, 2, 3, 4, 5
) d
WHERE
d.row_number < 202
AND d.row_number >= 100
-Original Message-
From: Howard, Chris [mailto:howa...@prpa.org]
Sent: Monday, December 18, 2017 9:21 AM
To: Fennell, Brian ; dbi-users@perl.org
Subject: RE: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **
Same database... do you mean the actual very same data source?
What is the Oracle error?
To eliminate problems based on data (implicit conversions, that kind of thing)
can you do a run from row 500,000 to 1,500,000 ? (I think you have this
covered, but maybe that row right at the breaking spot is somehow a bad guy.)
-Original Message-
From: Fennell, Brian [mailto:fenne...@radial.com]
Sent: Saturday, December 16, 2017 3:19 PM
To: dbi-users@perl.org
Subject: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **
Dear DBI people -
I am trying to port some old perl code to a new box. (see Details below)
Needless to say the original box and code works fine, but the new box (and old
code) does not.
Specifically what I am seeing is that when I select slightly over a million
records from a specific join of two tables (to be dumped one row at a time into
a TSV file) we get strange ORA-N errors that don't really make any sense in
this context.
The Same database and same table works fine on the original box with the large
number of records. 2 million records always causes errors but two groups of
1million (divided up by ROWNUM - the EXACT same rows) causes no errors. I am
using a test database with little activity do I am reasonably certain that the
queries deal with the same rows.
So I am thinking the problem is data volume and not any specific piece of data
(originally I thought it might be an odd string/data related error, but I am
starting to think it is a memory leak of some kind).
The error always happens inside of fetchrow_array - and "$dbh->trace( 4 ,
$filename )" shows that the error originates inside the DBD::Oracle module
while reading field 3 of 6.
Researching the ORA-NN error gives a perfectly sane description that makes
no sense at all in the context of reading a specific field.
We are going thru an Audit and tightening up security so there are some things
(like REAL hostnames and REAL column/table names) that I cannot share - but I
will try to share as much as I can.
The Host I am calling "prod" below is the only one NOT exhibiting this issue.
Things I want to try -
1) recompile the DBD::Oracle module on Host "sandbox" with "perl Makefile.PL
-g" and then use Valgrind. I haven't used Valgrind before, but I guess it is
time to learn.
2) Anything else this list suggests.
Details:
Host: prod
OS: Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
Perl: 5.8.8 built for x86_64-linux
DBI: 1.53
DBD::Oracle: 1.19
Oracle: 10.2.0.1.0
Host: dev
OS: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Perl: 5.16.1 built for x86_64-linux
DBI: 1.631
DBD::Oracle: 1.74
Oracle: 11.2.0.3.0
Host: prodnew
OS: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Perl: 5.16.1 built for x86_64-linux
DBI: 1.631
DBD::Oracle: 1.70
Oracle: 11.2.0.1.0
Host: sandbox
OS: CentOS Linux release 7.4.1708 (Core)
Perl: 5.16.3 built for x86_64-linux-thread-multi
DBI: 1.637
DBD::Oracle: 1.74
Oracle: 12.1.0.2.0
--
Brian Fennell, Software Engineer | Radial
O: 610 491 7308 | M: 484 354 1699
fenne...@radial.com
The information contained in this electronic mail transmission is intended only
for the use of the individual or entity named in this transmission. If you are
not the intended recipient of this transmission, you are hereby notified that
any disclosure, copying or distribution of the contents of this tran
Re: Hunting down (possible) memory leak in DBD::Oracle
Hmm this type of DBD::Oracle debugging will be tricky. Could be almost anything. You are jumping versions in a big way but that still should be ok A few questions 1) What is the ORA-NN in question 2) Set trace to 15 to see if that give you more details 3) What are the type of fields? Lob and blob and large varchars can be tricky 4) does the error happen in perl or XS (the 15 trace should) 5) To recompile you will need the latest version of the OCI client. Not sure what that is Cheers John From: Fennell, Brian Sent: December 16, 2017 5:19 PM To: dbi-users@perl.org Subject: Hunting down (possible) memory leak in DBD::Oracle Dear DBI people - I am trying to port some old perl code to a new box. (see Details below) Needless to say the original box and code works fine, but the new box (and old code) does not. Specifically what I am seeing is that when I select slightly over a million records from a specific join of two tables (to be dumped one row at a time into a TSV file) we get strange ORA-N errors that don't really make any sense in this context. The Same database and same table works fine on the original box with the large number of records. 2 million records always causes errors but two groups of 1million (divided up by ROWNUM - the EXACT same rows) causes no errors. I am using a test database with little activity do I am reasonably certain that the queries deal with the same rows. So I am thinking the problem is data volume and not any specific piece of data (originally I thought it might be an odd string/data related error, but I am starting to think it is a memory leak of some kind). The error always happens inside of fetchrow_array - and "$dbh->trace( 4 , $filename )" shows that the error originates inside the DBD::Oracle module while reading field 3 of 6. Researching the ORA-NN error gives a perfectly sane description that makes no sense at all in the context of reading a specific field. We are going thru an Audit and tightening up security so there are some things (like REAL hostnames and REAL column/table names) that I cannot share - but I will try to share as much as I can. The Host I am calling "prod" below is the only one NOT exhibiting this issue. Things I want to try - 1) recompile the DBD::Oracle module on Host "sandbox" with "perl Makefile.PL -g" and then use Valgrind. I haven't used Valgrind before, but I guess it is time to learn. 2) Anything else this list suggests. Details: Host: prod OS: Red Hat Enterprise Linux AS release 4 (Nahant Update 4) Perl: 5.8.8 built for x86_64-linux DBI: 1.53 DBD::Oracle: 1.19 Oracle: 10.2.0.1.0 Host: dev OS: Red Hat Enterprise Linux Server release 6.5 (Santiago) Perl: 5.16.1 built for x86_64-linux DBI: 1.631 DBD::Oracle: 1.74 Oracle: 11.2.0.3.0 Host: prodnew OS: Red Hat Enterprise Linux Server release 6.5 (Santiago) Perl: 5.16.1 built for x86_64-linux DBI: 1.631 DBD::Oracle: 1.70 Oracle: 11.2.0.1.0 Host: sandbox OS: CentOS Linux release 7.4.1708 (Core) Perl: 5.16.3 built for x86_64-linux-thread-multi DBI: 1.637 DBD::Oracle: 1.74 Oracle: 12.1.0.2.0 -- Brian Fennell, Software Engineer | Radial O: 610 491 7308 | M: 484 354 1699 fenne...@radial.com The information contained in this electronic mail transmission is intended only for the use of the individual or entity named in this transmission. If you are not the intended recipient of this transmission, you are hereby notified that any disclosure, copying or distribution of the contents of this transmission is strictly prohibited and that you should delete the contents of this transmission from your system immediately. Any comments or statements contained in this transmission do not necessarily reflect the views or position of Radial or its subsidiaries and/or affiliates. >++[>++>++>++>+++>+++>++>+++>+++><<<<<<<<<-]>-->++>+>>>+>-->--><<<<<<<<<>.>.>.>.>.>.>.>.
Hunting down (possible) memory leak in DBD::Oracle
Dear DBI people - I am trying to port some old perl code to a new box. (see Details below) Needless to say the original box and code works fine, but the new box (and old code) does not. Specifically what I am seeing is that when I select slightly over a million records from a specific join of two tables (to be dumped one row at a time into a TSV file) we get strange ORA-N errors that don't really make any sense in this context. The Same database and same table works fine on the original box with the large number of records. 2 million records always causes errors but two groups of 1million (divided up by ROWNUM - the EXACT same rows) causes no errors. I am using a test database with little activity do I am reasonably certain that the queries deal with the same rows. So I am thinking the problem is data volume and not any specific piece of data (originally I thought it might be an odd string/data related error, but I am starting to think it is a memory leak of some kind). The error always happens inside of fetchrow_array - and "$dbh->trace( 4 , $filename )" shows that the error originates inside the DBD::Oracle module while reading field 3 of 6. Researching the ORA-NN error gives a perfectly sane description that makes no sense at all in the context of reading a specific field. We are going thru an Audit and tightening up security so there are some things (like REAL hostnames and REAL column/table names) that I cannot share - but I will try to share as much as I can. The Host I am calling "prod" below is the only one NOT exhibiting this issue. Things I want to try - 1) recompile the DBD::Oracle module on Host "sandbox" with "perl Makefile.PL -g" and then use Valgrind. I haven't used Valgrind before, but I guess it is time to learn. 2) Anything else this list suggests. Details: Host: prod OS: Red Hat Enterprise Linux AS release 4 (Nahant Update 4) Perl: 5.8.8 built for x86_64-linux DBI: 1.53 DBD::Oracle: 1.19 Oracle: 10.2.0.1.0 Host: dev OS: Red Hat Enterprise Linux Server release 6.5 (Santiago) Perl: 5.16.1 built for x86_64-linux DBI: 1.631 DBD::Oracle: 1.74 Oracle: 11.2.0.3.0 Host: prodnew OS: Red Hat Enterprise Linux Server release 6.5 (Santiago) Perl: 5.16.1 built for x86_64-linux DBI: 1.631 DBD::Oracle: 1.70 Oracle: 11.2.0.1.0 Host: sandbox OS: CentOS Linux release 7.4.1708 (Core) Perl: 5.16.3 built for x86_64-linux-thread-multi DBI: 1.637 DBD::Oracle: 1.74 Oracle: 12.1.0.2.0 -- Brian Fennell, Software Engineer | Radial O: 610 491 7308 | M: 484 354 1699 fenne...@radial.com The information contained in this electronic mail transmission is intended only for the use of the individual or entity named in this transmission. If you are not the intended recipient of this transmission, you are hereby notified that any disclosure, copying or distribution of the contents of this transmission is strictly prohibited and that you should delete the contents of this transmission from your system immediately. Any comments or statements contained in this transmission do not necessarily reflect the views or position of Radial or its subsidiaries and/or affiliates. >++[>++>++>++>+++>+++>++>+++>+++><<<<<<<<<-]>-->++>+>>>+>-->--><<<<<<<<<>.>.>.>.>.>.>.>.
Re: Potential dbi memory leak.
Hi Tim,
Sorry to prolong this.
On 28/05/15 08:58, Tim Bunce wrote:
When a new handle is created the DBI simply pushes a weak reference to
the handle onto the end of the ChildHandles array. Because it's a weak
reference it doesn't interfere with the handle getting destroyed when
the last (non weak) reference is removed. When the handle is
destroyed, the corresponding element in the ChildHandles array is set
to undef by the weak reference mechanism deep in the perl internals.
The issue I have is that I do have real memory leaks in other parts of
my system and this caching makes it hard to detect where the real leaks are.
I have tried to suppress the caching using this code:
$loc_sth->finish();
#
print Dumper( $DBI::lasth );
$DBI::lasth ->{ChildHandles} = []; # <-- reset leaking data structure
delete $DBI::lasth ->{ChildHandles};
But I have not managed to stop it. In order to clarify my real leaks,
how can I suppress the dbi caching for the duration of my tests?
Thanks,
Steve.
Re: Potential dbi memory leak.
On Thu, May 28, 2015 at 03:59:51PM +1200, Duncan McEwan wrote: > On Tue, 26 May 2015 14:13:05 +0100 Tim Bunce wrote: > > > I've added this as a note: > > > > Note that the ChildHandles array holds weak references and that 'from > > time to time' the old slots get freed up. This isn't a leak, it just > > appears to be if you're not familiar with the caching that DBI does > > internally. You can rest assured that if the DBI did have a real leak > > a) a great many people would be affected and b) it would get fixed very > > quickly. > > > > I think 'from time to time' is every 120 or so newly created child handles. > But seeing this response from Tim about the fact that the DBI can cache up to > 120 or so handles made me wonder if this is true for database handles as well > as statement handles? Is it possible that our "problem" was simply the > correctly working DBI caching misbehaving due to our application running > persistently in multiple fcgid processes. Short answer: no. When a new handle is created the DBI simply pushes a weak reference to the handle onto the end of the ChildHandles array. Because it's a weak reference it doesn't interfere with the handle getting destroyed when the last (non weak) reference is removed. When the handle is destroyed, the corresponding element in the ChildHandles array is set to undef by the weak reference mechanism deep in the perl internals. The DBI isn't involved in that. The apparent 'leak' is because the element in the array still exists, albeit as an undef. The DBI clears out the undef elements occasionally to prevent the array growing indefinitely. Tim.
Re: Potential dbi memory leak.
I haven't tried it but you could probably modify the code I posted to test it. With the statement handle after 120(?) loops of apparent memory leak +1, there is a correction of -119. Good luck. Steve. On 28 May 2015 05:00, "Duncan McEwan" wrote: > Apologies for butting in on this thread, but I saw the following response > from Tim recently and it made me wonder ... > > On Tue, 26 May 2015 14:13:05 +0100 Tim Bunce wrote: > > > I've added this as a note: > > > > Note that the ChildHandles array holds weak references and that 'from > > time to time' the old slots get freed up. This isn't a leak, it just > > appears to be if you're not familiar with the caching that DBI does > > internally. You can rest assured that if the DBI did have a real leak > > a) a great many people would be affected and b) it would get fixed > very > > quickly. > > > > I think 'from time to time' is every 120 or so newly created child > handles. > > A while ago we had a mysterious problem using DBI in an application that > was > written as a plugin for the foswiki platform. Since our foswiki instance > was > running persistently under fcgid it was long-running and over time we'd > see a > gradual increase in the open connections it held to our mysql database > server. Eventually our server would reach it's maximum connection count > and > reject new connections. > > The most recent time I tried to debug this was over a year ago (March 2014) > and there was a brief exchange of emails on this list with the subject "DBI > Mysql Driver Handle Mysteriously Changes". > > Since then we've given up (!) and changed the way our application ran so it > is no longer a foswiki plugin. That seems to have "fixed" the connection > leakage and so we are unlikely to ever go back to find out exactly what was > going on here. > > But seeing this response from Tim about the fact that the DBI can cache up > to > 120 or so handles made me wonder if this is true for database handles as > well > as statement handles? Is it possible that our "problem" was simply the > correctly working DBI caching misbehaving due to our application running > persistently in multiple fcgid processes. > > I'm not looking to re-open investigating this issue - our environement has > now changed sufficiently that recreating the set up with the connection > leak > to do more debugging would be quite difficult. But I was just curious > about > whether the above could be the case. If the answer is "no" I'll be happy > to > just leave it at that! > > Thanks. > > Duncan >
Re: Potential dbi memory leak.
Apologies for butting in on this thread, but I saw the following response from Tim recently and it made me wonder ... On Tue, 26 May 2015 14:13:05 +0100 Tim Bunce wrote: > I've added this as a note: > > Note that the ChildHandles array holds weak references and that 'from > time to time' the old slots get freed up. This isn't a leak, it just > appears to be if you're not familiar with the caching that DBI does > internally. You can rest assured that if the DBI did have a real leak > a) a great many people would be affected and b) it would get fixed very > quickly. > > I think 'from time to time' is every 120 or so newly created child handles. A while ago we had a mysterious problem using DBI in an application that was written as a plugin for the foswiki platform. Since our foswiki instance was running persistently under fcgid it was long-running and over time we'd see a gradual increase in the open connections it held to our mysql database server. Eventually our server would reach it's maximum connection count and reject new connections. The most recent time I tried to debug this was over a year ago (March 2014) and there was a brief exchange of emails on this list with the subject "DBI Mysql Driver Handle Mysteriously Changes". Since then we've given up (!) and changed the way our application ran so it is no longer a foswiki plugin. That seems to have "fixed" the connection leakage and so we are unlikely to ever go back to find out exactly what was going on here. But seeing this response from Tim about the fact that the DBI can cache up to 120 or so handles made me wonder if this is true for database handles as well as statement handles? Is it possible that our "problem" was simply the correctly working DBI caching misbehaving due to our application running persistently in multiple fcgid processes. I'm not looking to re-open investigating this issue - our environement has now changed sufficiently that recreating the set up with the connection leak to do more debugging would be quite difficult. But I was just curious about whether the above could be the case. If the answer is "no" I'll be happy to just leave it at that! Thanks. Duncan
Re: Potential dbi memory leak.
Hi Again On 26/05/15 10:13, Tim Bunce wrote: Note that the ChildHandles array holds weak references and that 'from time to time' the old slots get freed up. This isn't a leak, it just appears to be if you're not familiar with the caching that DBI does internally. You can rest assured that if the DBI did have a real leak a) a great many people would be affected and b) it would get fixed very quickly. I think 'from time to time' is every 120 or so newly created child handles. Could I suggest that "/array holds weak references/" is changed to /"array holds a history of weak references" /Or something to explain the idea of a growing hash. I misinterpreted your reply at first. Thanks again, Regards Steve.
Re: Potential dbi memory leak.
Hi Tim,
Oh yes for (1..500) does exactly that. Thank you. No memory leak at all!
Regards
Steve.
On 26/05/15 10:13, Tim Bunce wrote:
I've added this as a note:
Note that the ChildHandles array holds weak references and that 'from
time to time' the old slots get freed up. This isn't a leak, it just
appears to be if you're not familiar with the caching that DBI does
internally. You can rest assured that if the DBI did have a real leak
a) a great many people would be affected and b) it would get fixed very
quickly.
I think 'from time to time' is every 120 or so newly created child handles.
Tim.
On Tue, May 26, 2015 at 07:57:53AM -0300, Steve Cookson - gmail wrote:
It seems to be further documented here, together with a solution:
http://stackoverflow.com/questions/13338308/perl-dbi-memory-leak,
But the solution does not seem to be reliable. Sometimes it works sometimes
not.
I'll update you when I know more.
Regards,
Steve.
On 26/05/15 07:07, Steve Cookson - gmail wrote:
Hi Guys,
You may have seen part of this post on PerlMonks. If so apologies for the
duplication. This started off as a general search for leaks in my code,
and resulted in a few hits, one of which was attached to every database
access.
A simple "select ATT_RECORD_NAME_TXT from TBL_TEST; " results in the leak
of one scalar value. It seems to be attached to the ->prepare statement.
At first I assumed it was down to my Firebird driver, which is relatively
new, so I switched the driver to ODBC::Firebird, with the same result.
Finally I changed to mysql and again got a memory leak. The only thing I
can assume is that either my code is generically wrong (and I hope this is
the case), or there is a leak in dbi, which I would be surprised by.
I would appreciate some advice.
Test code follows. Please install Devel::Leak to pick up leaked scalars
and update the dsn to the dsn of your choice.
Thanks for your help.
Regards,
Steve.
#! /usr/bin/perl
package main;
use strict;
use warnings;
use DBI;
#use DBD::Firebird;
use DBD::ODBC;
use Devel::Leak;
my $handle;
my $count_start;
my $count_stop;
my $gl_dbh;
# Just do this 5 times to make sure there is no contribution to
$handle count from Devel::Leak
for (1..10){
print "Handle init: ", Devel::Leak::NoteSV($Launch::handle),"\n";
}
#my $loc_dsn = <connect($loc_dsn,"root","password", {
PrintError => 1,# Report errors via warn
RaiseError => 1# Report errors via Die
}
) or die;
my @loc_sql_string =();
$loc_sql_string[0]="CREATE TABLE TBL_TEST_LEAK ( ATTR_RECORD_ID_TXT
VARCHAR(10) NOT NULL, ATT_RECORD_NAME_TXT VARCHAR(255), CONSTRAINT
PK_TBL_TEST_LEAK PRIMARY KEY (ATTR_RECORD_ID_TXT) ); ";
$loc_sql_string[1]="GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON TBL_TEST_LEAK TO SYSDBA WITH GRANT OPTION";
$loc_sql_string[2]="INSERT INTO TBL_TEST_LEAK (ATTR_RECORD_ID_TXT,
ATT_RECORD_NAME_TXT) VALUES ('206', 'Delay Test 1' )";
$loc_sql_string[3]="select ATT_RECORD_NAME_TXT from TBL_TEST_LEAK; ";
$loc_sql_string[4]= $loc_sql_string[3];
$loc_sql_string[5]= $loc_sql_string[3];
$loc_sql_string[6]= $loc_sql_string[3];
$loc_sql_string[7]= $loc_sql_string[3];
$loc_sql_string[8]= $loc_sql_string[3];
$loc_sql_string[9]="drop table TBL_TEST_LEAK; ";
for (my $i=1;$i<=9;$i++){
$count_start=Devel::Leak::NoteSV($Launch::handle);
print "DBD start: ", $count_start,"\n";
print $loc_sql_string[$i], "\n";
dbd_select($loc_sql_string[$i]);
# You can use
#$count_stop=Devel::Leak::CheckSV($Launch::handle);
$count_stop=Devel::Leak::NoteSV($Launch::handle);
print "Handle stop: ", $count_stop,"\n";
print "Count difference: ", $count_stop-$count_start,"\n";
}
$Launch::gl_dbh->disconnect;
sub dbd_select{
my $loc_sql_string=shift;
my $loc_sth=$Launch::gl_dbh->prepare($loc_sql_string) or die;
#$loc_sth->execute() or die;
#$loc_sth->finish();
return;
}
1;
Re: Potential dbi memory leak.
I've added this as a note:
Note that the ChildHandles array holds weak references and that 'from
time to time' the old slots get freed up. This isn't a leak, it just
appears to be if you're not familiar with the caching that DBI does
internally. You can rest assured that if the DBI did have a real leak
a) a great many people would be affected and b) it would get fixed very
quickly.
I think 'from time to time' is every 120 or so newly created child handles.
Tim.
On Tue, May 26, 2015 at 07:57:53AM -0300, Steve Cookson - gmail wrote:
> It seems to be further documented here, together with a solution:
>
> http://stackoverflow.com/questions/13338308/perl-dbi-memory-leak,
>
> But the solution does not seem to be reliable. Sometimes it works sometimes
> not.
>
> I'll update you when I know more.
>
> Regards,
>
> Steve.
>
> On 26/05/15 07:07, Steve Cookson - gmail wrote:
> >Hi Guys,
> >
> >You may have seen part of this post on PerlMonks. If so apologies for the
> >duplication. This started off as a general search for leaks in my code,
> >and resulted in a few hits, one of which was attached to every database
> >access.
> >
> >A simple "select ATT_RECORD_NAME_TXT from TBL_TEST; " results in the leak
> >of one scalar value. It seems to be attached to the ->prepare statement.
> >
> >At first I assumed it was down to my Firebird driver, which is relatively
> >new, so I switched the driver to ODBC::Firebird, with the same result.
> >Finally I changed to mysql and again got a memory leak. The only thing I
> >can assume is that either my code is generically wrong (and I hope this is
> >the case), or there is a leak in dbi, which I would be surprised by.
> >
> >I would appreciate some advice.
> >
> >Test code follows. Please install Devel::Leak to pick up leaked scalars
> >and update the dsn to the dsn of your choice.
> >
> >Thanks for your help.
> >
> >Regards,
> >
> >Steve.
> >
> >#! /usr/bin/perl
> >
> >package main;
> >use strict;
> >use warnings;
> >use DBI;
> >#use DBD::Firebird;
> >use DBD::ODBC;
> >use Devel::Leak;
> >my $handle;
> >my $count_start;
> >my $count_stop;
> >my $gl_dbh;
> >
> ># Just do this 5 times to make sure there is no contribution to
> >$handle count from Devel::Leak
> >for (1..10){
> >print "Handle init: ", Devel::Leak::NoteSV($Launch::handle),"\n";
> >}
> >#my $loc_dsn = < >#dbi:ODBC:Driver=Firebird;Dbname=/home/image/Documents/Endoscopia/DB/newEndo.fdb;
> >
> >#ib_dialect=3;
> >#DSN
> >my $loc_dsn = < >DBI:mysql:database=new_schema_test;
> >host=localhost;
> >port=3306";
> >DSN
> >$Launch::gl_dbh=DBI->connect($loc_dsn,"root","password", {
> >PrintError => 1,# Report errors via warn
> >RaiseError => 1# Report errors via Die
> >}
> >) or die;
> >
> >my @loc_sql_string =();
> >$loc_sql_string[0]="CREATE TABLE TBL_TEST_LEAK ( ATTR_RECORD_ID_TXT
> >VARCHAR(10) NOT NULL, ATT_RECORD_NAME_TXT VARCHAR(255), CONSTRAINT
> >PK_TBL_TEST_LEAK PRIMARY KEY (ATTR_RECORD_ID_TXT) ); ";
> >$loc_sql_string[1]="GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
> >ON TBL_TEST_LEAK TO SYSDBA WITH GRANT OPTION";
> >$loc_sql_string[2]="INSERT INTO TBL_TEST_LEAK (ATTR_RECORD_ID_TXT,
> >ATT_RECORD_NAME_TXT) VALUES ('206', 'Delay Test 1' )";
> >$loc_sql_string[3]="select ATT_RECORD_NAME_TXT from TBL_TEST_LEAK; ";
> >$loc_sql_string[4]= $loc_sql_string[3];
> >$loc_sql_string[5]= $loc_sql_string[3];
> >$loc_sql_string[6]= $loc_sql_string[3];
> >$loc_sql_string[7]= $loc_sql_string[3];
> >$loc_sql_string[8]= $loc_sql_string[3];
> >$loc_sql_string[9]="drop table TBL_TEST_LEAK; ";
> >
> >for (my $i=1;$i<=9;$i++){
> >$count_start=Devel::Leak::NoteSV($Launch::handle);
> >print "DBD start: ", $count_start,"\n";
> >print $loc_sql_string[$i], "\n";
> >dbd_select($loc_sql_string[$i]);
> ># You can use
> >#$count_stop=Devel::Leak::CheckSV($Launch::handle);
> >$count_stop=Devel::Leak::NoteSV($Launch::handle);
> >print "Handle stop: ", $count_stop,"\n";
> >print "Count difference: ", $count_stop-$count_start,"\n";
> >}
> >$Launch::gl_dbh->disconnect;
> >
> >sub dbd_select{
> >my $loc_sql_string=shift;
> >my $loc_sth=$Launch::gl_dbh->prepare($loc_sql_string) or die;
> >#$loc_sth->execute() or die;
> >#$loc_sth->finish();
> >return;
> >}
> >
> >1;
> >
> >
>
Re: Potential dbi memory leak.
It seems to be further documented here, together with a solution:
http://stackoverflow.com/questions/13338308/perl-dbi-memory-leak,
But the solution does not seem to be reliable. Sometimes it works
sometimes not.
I'll update you when I know more.
Regards,
Steve.
On 26/05/15 07:07, Steve Cookson - gmail wrote:
Hi Guys,
You may have seen part of this post on PerlMonks. If so apologies for
the duplication. This started off as a general search for leaks in my
code, and resulted in a few hits, one of which was attached to every
database access.
A simple "select ATT_RECORD_NAME_TXT from TBL_TEST; " results in the
leak of one scalar value. It seems to be attached to the ->prepare
statement.
At first I assumed it was down to my Firebird driver, which is
relatively new, so I switched the driver to ODBC::Firebird, with the
same result. Finally I changed to mysql and again got a memory leak.
The only thing I can assume is that either my code is generically
wrong (and I hope this is the case), or there is a leak in dbi, which
I would be surprised by.
I would appreciate some advice.
Test code follows. Please install Devel::Leak to pick up leaked
scalars and update the dsn to the dsn of your choice.
Thanks for your help.
Regards,
Steve.
#! /usr/bin/perl
package main;
use strict;
use warnings;
use DBI;
#use DBD::Firebird;
use DBD::ODBC;
use Devel::Leak;
my $handle;
my $count_start;
my $count_stop;
my $gl_dbh;
# Just do this 5 times to make sure there is no contribution to
$handle count from Devel::Leak
for (1..10){
print "Handle init: ", Devel::Leak::NoteSV($Launch::handle),"\n";
}
#my $loc_dsn = <#dbi:ODBC:Driver=Firebird;Dbname=/home/image/Documents/Endoscopia/DB/newEndo.fdb;
#ib_dialect=3;
#DSN
my $loc_dsn = <connect($loc_dsn,"root","password", {
PrintError => 1,# Report errors via warn
RaiseError => 1# Report errors via Die
}
) or die;
my @loc_sql_string =();
$loc_sql_string[0]="CREATE TABLE TBL_TEST_LEAK (
ATTR_RECORD_ID_TXT VARCHAR(10) NOT NULL, ATT_RECORD_NAME_TXT
VARCHAR(255), CONSTRAINT PK_TBL_TEST_LEAK PRIMARY KEY
(ATTR_RECORD_ID_TXT) ); ";
$loc_sql_string[1]="GRANT DELETE, INSERT, REFERENCES, SELECT,
UPDATE ON TBL_TEST_LEAK TO SYSDBA WITH GRANT OPTION";
$loc_sql_string[2]="INSERT INTO TBL_TEST_LEAK (ATTR_RECORD_ID_TXT,
ATT_RECORD_NAME_TXT) VALUES ('206', 'Delay Test 1' )";
$loc_sql_string[3]="select ATT_RECORD_NAME_TXT from TBL_TEST_LEAK; ";
$loc_sql_string[4]= $loc_sql_string[3];
$loc_sql_string[5]= $loc_sql_string[3];
$loc_sql_string[6]= $loc_sql_string[3];
$loc_sql_string[7]= $loc_sql_string[3];
$loc_sql_string[8]= $loc_sql_string[3];
$loc_sql_string[9]="drop table TBL_TEST_LEAK; ";
for (my $i=1;$i<=9;$i++){
$count_start=Devel::Leak::NoteSV($Launch::handle);
print "DBD start: ", $count_start,"\n";
print $loc_sql_string[$i], "\n";
dbd_select($loc_sql_string[$i]);
# You can use
#$count_stop=Devel::Leak::CheckSV($Launch::handle);
$count_stop=Devel::Leak::NoteSV($Launch::handle);
print "Handle stop: ", $count_stop,"\n";
print "Count difference: ", $count_stop-$count_start,"\n";
}
$Launch::gl_dbh->disconnect;
sub dbd_select{
my $loc_sql_string=shift;
my $loc_sth=$Launch::gl_dbh->prepare($loc_sql_string) or die;
#$loc_sth->execute() or die;
#$loc_sth->finish();
return;
}
1;
Potential dbi memory leak.
Hi Guys,
You may have seen part of this post on PerlMonks. If so apologies for
the duplication. This started off as a general search for leaks in my
code, and resulted in a few hits, one of which was attached to every
database access.
A simple "select ATT_RECORD_NAME_TXT from TBL_TEST; " results in the
leak of one scalar value. It seems to be attached to the ->prepare
statement.
At first I assumed it was down to my Firebird driver, which is
relatively new, so I switched the driver to ODBC::Firebird, with the
same result. Finally I changed to mysql and again got a memory leak.
The only thing I can assume is that either my code is generically wrong
(and I hope this is the case), or there is a leak in dbi, which I would
be surprised by.
I would appreciate some advice.
Test code follows. Please install Devel::Leak to pick up leaked scalars
and update the dsn to the dsn of your choice.
Thanks for your help.
Regards,
Steve.
#! /usr/bin/perl
package main;
use strict;
use warnings;
use DBI;
#use DBD::Firebird;
use DBD::ODBC;
use Devel::Leak;
my $handle;
my $count_start;
my $count_stop;
my $gl_dbh;
# Just do this 5 times to make sure there is no contribution to
$handle count from Devel::Leak
for (1..10){
print "Handle init: ", Devel::Leak::NoteSV($Launch::handle),"\n";
}
#my $loc_dsn = <connect($loc_dsn,"root","password", {
PrintError => 1,# Report errors via warn
RaiseError => 1# Report errors via Die
}
) or die;
my @loc_sql_string =();
$loc_sql_string[0]="CREATE TABLE TBL_TEST_LEAK ( ATTR_RECORD_ID_TXT
VARCHAR(10) NOT NULL, ATT_RECORD_NAME_TXT VARCHAR(255), CONSTRAINT
PK_TBL_TEST_LEAK PRIMARY KEY (ATTR_RECORD_ID_TXT) ); ";
$loc_sql_string[1]="GRANT DELETE, INSERT, REFERENCES, SELECT,
UPDATE ON TBL_TEST_LEAK TO SYSDBA WITH GRANT OPTION";
$loc_sql_string[2]="INSERT INTO TBL_TEST_LEAK (ATTR_RECORD_ID_TXT,
ATT_RECORD_NAME_TXT) VALUES ('206', 'Delay Test 1' )";
$loc_sql_string[3]="select ATT_RECORD_NAME_TXT from TBL_TEST_LEAK; ";
$loc_sql_string[4]= $loc_sql_string[3];
$loc_sql_string[5]= $loc_sql_string[3];
$loc_sql_string[6]= $loc_sql_string[3];
$loc_sql_string[7]= $loc_sql_string[3];
$loc_sql_string[8]= $loc_sql_string[3];
$loc_sql_string[9]="drop table TBL_TEST_LEAK; ";
for (my $i=1;$i<=9;$i++){
$count_start=Devel::Leak::NoteSV($Launch::handle);
print "DBD start: ", $count_start,"\n";
print $loc_sql_string[$i], "\n";
dbd_select($loc_sql_string[$i]);
# You can use
#$count_stop=Devel::Leak::CheckSV($Launch::handle);
$count_stop=Devel::Leak::NoteSV($Launch::handle);
print "Handle stop: ", $count_stop,"\n";
print "Count difference: ", $count_stop-$count_start,"\n";
}
$Launch::gl_dbh->disconnect;
sub dbd_select{
my $loc_sql_string=shift;
my $loc_sth=$Launch::gl_dbh->prepare($loc_sql_string) or die;
#$loc_sth->execute() or die;
#$loc_sth->finish();
return;
}
1;
Re: (Fwd) Strange memory leak (?) with DBI ? DBD::Pg
On Sun, Oct 21, 2007 at 09:48:44PM +0100, Tim Bunce wrote: > Hello Tim, I somehow missed the section of the docs that said to email the list, not Tim. :-) Tim added the "? DBD::Pg" to the subject. I didn't even think to check out DBD::Pg's open bugs. Turns out this has already been found and solved by someone else: http://rt.cpan.org/Public/Bug/Display.html?id=21392 The patch there fixed my problem. -- Troy Davis [EMAIL PROTECTED] http://www.tdavis.org/
Re: (Fwd) Strange memory leak (?) with DBI ? DBD::Pg
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tim Bunce wrote: > - Forwarded message from Troy Davis <[EMAIL PROTECTED]> - > # The database is Postgres and the "blob" column is of type bytea. As Troy mentioned BYTEA, I remembered I often noticed memory leaks when dealing with this kind of columns, so I hacked his test a "little bit" (check the attachment - it requires GTop, as I was too lazy to grep /proc/self/status) and run it against a PostgreSQL v8.2.5 server, using DBD::Pg v1.49 (DBI v1.59): $ ./leak_pgsql.pl vsz: 8581120 rss: 3710976 [before connect] vsz: 12308480 rss: 5136384 [after connecting] vsz: 12308480 rss: 5144576 [after creating table (TEXT)] vsz: 12308480 rss: 5152768 [after preparing insert] vsz: 16515072 rss: 8347648 [after inserting] vsz: 15462400 rss: 7303168 [after droping INSERT sth] vsz: 18616320 rss: 9420800 [after executing select (TEXT / #1)] vsz: 19668992 rss: 10477568 [after fetching data (TEXT / #1)] vsz: 17563648 rss: 8372224 [after droping SELECT sth (after 10 iterations)] vsz: 18616320 rss: 9424896 [after inserting] vsz: 17563648 rss: 8372224 [after droping INSERT sth] vsz: 18616320 rss: 9424896 [after executing select (BYTEA / #1)] vsz: 21774336 rss: 10485760 [after fetching data (BYTEA / #1)] vsz: 23879680 rss: 10493952 [after fetching data (BYTEA / #2)] vsz: 25985024 rss: 10502144 [after fetching data (BYTEA / #3)] vsz: 28090368 rss: 10510336 [after fetching data (BYTEA / #4)] vsz: 30195712 rss: 10518528 [after fetching data (BYTEA / #5)] vsz: 32301056 rss: 10526720 [after fetching data (BYTEA / #6)] vsz: 34406400 rss: 10534912 [after fetching data (BYTEA / #7)] vsz: 36511744 rss: 10543104 [after fetching data (BYTEA / #8)] vsz: 38617088 rss: 10551296 [after fetching data (BYTEA / #9)] vsz: 40722432 rss: 10559488 [after fetching data (BYTEA / #10)] vsz: 38617088 rss: 8454144 [after droping SELECT sth (after 10 iterations)] vsz: 34414592 rss: 6348800 [the end] Looks like fetching BYTEA columns leaks, but not TEXT. ++ BTW, I run a similar test against a MySQL v5.0.45 server using DBD::mysql v4.005 (DBI v1.59) - no leaks occurred. 'HTH - -- Marius Feraru -BEGIN PGP SIGNATURE- iD8DBQFHHBXMtZHp/AYZiNkRAplfAJ0WRP25rSmzrLyodLYcbH/LiENmwQCgjGqJ E/8XgF7wgZxEoBd4Dfk8Pvc= =N2Zd -END PGP SIGNATURE- leak_pgsql.pl Description: Perl program
(Fwd) Strange memory leak (?) with DBI ? DBD::Pg
- Forwarded message from Troy Davis <[EMAIL PROTECTED]> -
x-pobox-client-address: 216.37.68.82
x-pobox-client-name: cafeautism.net
Date: Sat, 20 Oct 2007 11:22:42 -0500
From: Troy Davis <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: Strange memory leak (?) with DBI
Hello Tim,
I've been trying to track down the source of mysteriously missing memory in
my mod_perl2 environment. I've boiled part of the problem down to the
following, and was wondering if you might be able to explain what's going
on. Consider the following:
---snip---
use DBI;
while (1) {
$dbh = DBI->connect('DBI:Pg:', 'db_name', 'db_pass',
{ RaiseError => 1 } );
# Just pick some random ID; I've verified 1-124 are all there.
my $id = int rand(124);
# The database is Postgres and the "blob" column is of type bytea.
# This field in the database is always 901120 bytes long.
my ($contents) = $dbh->selectrow_array('
SELECT blob FROM blobs WHERE id = ?
', undef, $id);
$dbh->disconnect;
}
---snip---
If I run the above script while doing the following in another console:
while [ 1 ]; do free; ps u -C perl; echo; sleep 3; done
Then the following happens. Watch the VSZ counter for the perl process:
---snip---
total used free sharedbuffers cached
Mem:516856 317948 198908 0 72056 176496
-/+ buffers/cache: 69396 447460
Swap: 979924 4840 975084
USER PID %CPU %MEMVSZ RSS TTY STAT START TIME COMMAND
root 1165 39.5 1.0 85276 5340 pts/1S+ 11:15 0:02 perl foo
total used free sharedbuffers cached
Mem:516856 321792 195064 0 72068 176504
-/+ buffers/cache: 73220 443636
Swap: 979924 4840 975084
USER PID %CPU %MEMVSZ RSS TTY STAT START TIME COMMAND
root 1165 40.7 1.1 134492 5772 pts/1S+ 11:15 0:03 perl foo
total used free sharedbuffers cached
Mem:516856 321304 195552 0 72072 176504
-/+ buffers/cache: 72728 444128
Swap: 979924 4840 975084
USER PID %CPU %MEMVSZ RSS TTY STAT START TIME COMMAND
root 1165 43.5 1.0 213052 5660 pts/1S+ 11:15 0:06 perl foo
total used free sharedbuffers cached
Mem:516856 318708 198148 0 72080 176500
-/+ buffers/cache: 70128 446728
Swap: 979924 4840 975084
USER PID %CPU %MEMVSZ RSS TTY STAT START TIME COMMAND
root 1165 41.7 1.1 268320 5972 pts/1S+ 11:15 0:07 perl foo
total used free sharedbuffers cached
Mem:516856 318840 198016 0 72084 176504
-/+ buffers/cache: 70252 446604
Swap: 979924 4840 975084
USER PID %CPU %MEMVSZ RSS TTY STAT START TIME COMMAND
root 1165 42.1 1.5 314396 7756 pts/1S+ 11:15 0:08 perl foo
---snip---
This will continue indefinitely until it runs out of memory (under
Apache/mod_perl2, Apache eventually dies with "Out of memory!", though again
the above is completely independent of Apache). However, I don't understand
why this is, as there's plenty of both physical memory and swap free the
whole time.
This is happening on a Debian etch box. Here are all the relevant package
versions I thought you would want to see:
ii libdbd-pg-perl 1.49-2
ii libdbi-perl1.53-1
ii perl-base 5.8.8-7
ii postgresql-8.1 8.1.9-0etch1
I checked the changelog for DBI and nothing that was fixed in versions >1.53
jumped out at me as being related to this. I'd have to go through a fair
bit of contorting to get 1.59 working on this box, but if you suspect that
this is already been fixed, then let me know.
Thank you for any help you might be able to offer.
--
Troy Davis
[EMAIL PROTECTED]
http://www.tdavis.org/
- End forwarded message -
- Forwarded message from Troy Davis <[EMAIL PROTECTED]> -
x-pobox-client-address: 216.37.68.82
x-pobox-client-name: cafeautism.net
Date: Sat, 20 Oct 2007 11:36:45 -0500
From: Troy Davis <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: Re: Strange memory leak (?) with DBI
On Sat, Oct 20, 2007 at 11:22:42AM -0500, Troy Davis wrote:
> I've been trying to track down the source of mysteriously missing memory in
> my mod_perl2 environment. I've boiled part of the problem down to the
> following, and was wondering if you might be able to explain what's going
> on. Con
Re: Memory leak, DBI 1.55 + DBD::ODBC 1.13 + MS SQL 2000
Ok that elliminates one source of the problem.
cheers
- Original Message -
From: "Fi Dot" <[EMAIL PROTECTED]>
To: "John Scoles" <[EMAIL PROTECTED]>
Cc: ; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, May 22, 2007 7:42 AM
Subject: Re: Memory leak, DBI 1.55 + DBD::ODBC 1.13 + MS SQL 2000
John,
No. I am running this script just from the shell.
Fi.
On 5/22/07, John Scoles <[EMAIL PROTECTED]> wrote:
Are you using IIS7?
- Original Message -
From: "Fi Dot" <[EMAIL PROTECTED]>
To:
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Monday, May 21, 2007 4:42 PM
Subject: Memory leak, DBI 1.55 + DBD::ODBC 1.13 + MS SQL 2000
> Hi All!
>
> Recently I have bumped into a memory leak happening in DBI.
>
> Running DBI version 1.55, DBD::ODBC version 1.13 on Windows XP SP2,
> Active State Active Perl 5.8 build 819. More comprehensive build info
> at the bottom of this email.
>
> You could find the code I am executing attached to this message.
>
> I was executing queries returning multiple record-sets with fields of
> text type in them and.. well, the memory usage of my script just grows
> drastically. Digging into this, I have found that it looks like
> DBD::ODBC is not freeing up memory buffers allocated for storing the
> current record when switching to the next record-set.
>
> Here are my findings:
>
> - Look on foo.sql defining my stored procedure. It is returning a
> record set of ints, and then another record set of text fields. This
> code will grow my script's memory footprint a lot every iteration
> through the for {} loop defined on line 22 of foo.pl.
> - If I will swap the select's in stored procedure, making text fields
> go first, memory footprint will still grow, but much slower.
> - This never happens when I have one single record set returned.
> - Undeffing the connection and calling DBI's disconnect() method does
> not
> help
> - Notice I set LongReadLen value to 16 megabytes (line 18 of foo.pl)?
> This value directly affects the amount of memory used by my script.
> The bigger it is, the larger my mem footprint will be:
>
> 16 Megabytes: 150 megabytes of RAM used
> 8 Megabytes: 80 megabytes of RAM used
> 4 Megabytes: 40 megabytes of RAM used.
>
> - Text fields were taken just as a good example for watching memory
> usage grow rapidly, in general this is happening with any query
> having more than one record-set, just with slower rates.
>
> This looks like a bug in DBD::ODBC... Had anybody on the list seen
> something similar? Are there any known fixes for this?
>
> Thanx!
>
> Fi.
>
> My Perl build config (apl is just an alias in BASH):
>
> $ apl -v
>
> This is perl, v5.8.8 built for MSWin32-x86-multi-thread
> (with 33 registered patches, see perl -V for more detail)
>
> Copyright 1987-2006, Larry Wall
>
> Binary build 819 [267479] provided by ActiveState
> http://www.ActiveState.com
> Built Aug 29 2006 12:42:41
>
> Perl may be copied only under the terms of either the Artistic License
> or
> the
> GNU General Public License, which may be found in the Perl 5 source
> kit.
>
> Complete documentation for Perl, including FAQ lists, should be found
> on
> this system using "man perl" or "perldoc perl". If you have access to
> the
> Internet, point your browser at http://www.perl.org/, the Perl Home
> Page.
>
> $ apl -V
> Summary of my perl5 (revision 5 version 8 subversion 8) configuration:
> Platform:
>osname=MSWin32, osvers=5.0, archname=MSWin32-x86-multi-thread
>uname=''
>config_args='undef'
>hint=recommended, useposix=true, d_sigaction=undef
>usethreads=define use5005threads=undef useithreads=define
> usemultiplicity=de
> fine
>useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
>use64bitint=undef use64bitall=undef uselongdouble=undef
>usemymalloc=n, bincompat5005=undef
> Compiler:
>cc='cl', ccflags
> ='-nologo -GF -W3 -MD -Zi -DNDEBUG -O1 -DWIN32 -D_CONSOLE -
> DNO_STRICT -DHAVE_DES_FCRYPT -DNO_HASH_SEED -DUSE_SITECUSTOMIZE
-DPERL_IMPLICIT_
> CONTEXT -DPERL_IMPLICIT_SYS -DUSE_PERLIO -DPERL_MSVCRT_READFIX',
>optimize='-MD -Zi -DNDEBUG -O1',
>cppflags='-DWIN32'
>ccversion='12.00.8804', gccversion='', gccosandvers=''
>intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234
>d_longlong=undef, longlongsize=8, d_longdbl=define, longdblsize=10
>ivtype='long', ivsize=4, n
Re: Memory leak, DBI 1.55 + DBD::ODBC 1.13 + MS SQL 2000
John,
No. I am running this script just from the shell.
Fi.
On 5/22/07, John Scoles <[EMAIL PROTECTED]> wrote:
Are you using IIS7?
- Original Message -
From: "Fi Dot" <[EMAIL PROTECTED]>
To:
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Monday, May 21, 2007 4:42 PM
Subject: Memory leak, DBI 1.55 + DBD::ODBC 1.13 + MS SQL 2000
> Hi All!
>
> Recently I have bumped into a memory leak happening in DBI.
>
> Running DBI version 1.55, DBD::ODBC version 1.13 on Windows XP SP2,
> Active State Active Perl 5.8 build 819. More comprehensive build info
> at the bottom of this email.
>
> You could find the code I am executing attached to this message.
>
> I was executing queries returning multiple record-sets with fields of
> text type in them and.. well, the memory usage of my script just grows
> drastically. Digging into this, I have found that it looks like
> DBD::ODBC is not freeing up memory buffers allocated for storing the
> current record when switching to the next record-set.
>
> Here are my findings:
>
> - Look on foo.sql defining my stored procedure. It is returning a
> record set of ints, and then another record set of text fields. This
> code will grow my script's memory footprint a lot every iteration
> through the for {} loop defined on line 22 of foo.pl.
> - If I will swap the select's in stored procedure, making text fields
> go first, memory footprint will still grow, but much slower.
> - This never happens when I have one single record set returned.
> - Undeffing the connection and calling DBI's disconnect() method does not
> help
> - Notice I set LongReadLen value to 16 megabytes (line 18 of foo.pl)?
> This value directly affects the amount of memory used by my script.
> The bigger it is, the larger my mem footprint will be:
>
> 16 Megabytes: 150 megabytes of RAM used
> 8 Megabytes: 80 megabytes of RAM used
> 4 Megabytes: 40 megabytes of RAM used.
>
> - Text fields were taken just as a good example for watching memory
> usage grow rapidly, in general this is happening with any query
> having more than one record-set, just with slower rates.
>
> This looks like a bug in DBD::ODBC... Had anybody on the list seen
> something similar? Are there any known fixes for this?
>
> Thanx!
>
> Fi.
>
> My Perl build config (apl is just an alias in BASH):
>
> $ apl -v
>
> This is perl, v5.8.8 built for MSWin32-x86-multi-thread
> (with 33 registered patches, see perl -V for more detail)
>
> Copyright 1987-2006, Larry Wall
>
> Binary build 819 [267479] provided by ActiveState
> http://www.ActiveState.com
> Built Aug 29 2006 12:42:41
>
> Perl may be copied only under the terms of either the Artistic License or
> the
> GNU General Public License, which may be found in the Perl 5 source kit.
>
> Complete documentation for Perl, including FAQ lists, should be found on
> this system using "man perl" or "perldoc perl". If you have access to the
> Internet, point your browser at http://www.perl.org/, the Perl Home Page.
>
> $ apl -V
> Summary of my perl5 (revision 5 version 8 subversion 8) configuration:
> Platform:
>osname=MSWin32, osvers=5.0, archname=MSWin32-x86-multi-thread
>uname=''
>config_args='undef'
>hint=recommended, useposix=true, d_sigaction=undef
>usethreads=define use5005threads=undef useithreads=define
> usemultiplicity=de
> fine
>useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
>use64bitint=undef use64bitall=undef uselongdouble=undef
>usemymalloc=n, bincompat5005=undef
> Compiler:
>cc='cl', ccflags
> ='-nologo -GF -W3 -MD -Zi -DNDEBUG -O1 -DWIN32 -D_CONSOLE -
> DNO_STRICT -DHAVE_DES_FCRYPT -DNO_HASH_SEED -DUSE_SITECUSTOMIZE
-DPERL_IMPLICIT_
> CONTEXT -DPERL_IMPLICIT_SYS -DUSE_PERLIO -DPERL_MSVCRT_READFIX',
>optimize='-MD -Zi -DNDEBUG -O1',
>cppflags='-DWIN32'
>ccversion='12.00.8804', gccversion='', gccosandvers=''
>intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234
>d_longlong=undef, longlongsize=8, d_longdbl=define, longdblsize=10
>ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='__int64',
> lseeksi
> ze=8
>alignbytes=8, prototype=define
> Linker and Libraries:
>ld='link', ldflags
> '-nologo -nodefaultlib -debug -opt:ref,icf -libpath:"C:
> \Perl\lib\CORE" -machine:x86'
>libpth=\lib
>libs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib
> comdlg32
> .lib advapi32.lib shell32.li
Re: Memory leak, DBI 1.55 + DBD::ODBC 1.13 + MS SQL 2000
Are you using IIS7?
- Original Message -
From: "Fi Dot" <[EMAIL PROTECTED]>
To:
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Monday, May 21, 2007 4:42 PM
Subject: Memory leak, DBI 1.55 + DBD::ODBC 1.13 + MS SQL 2000
Hi All!
Recently I have bumped into a memory leak happening in DBI.
Running DBI version 1.55, DBD::ODBC version 1.13 on Windows XP SP2,
Active State Active Perl 5.8 build 819. More comprehensive build info
at the bottom of this email.
You could find the code I am executing attached to this message.
I was executing queries returning multiple record-sets with fields of
text type in them and.. well, the memory usage of my script just grows
drastically. Digging into this, I have found that it looks like
DBD::ODBC is not freeing up memory buffers allocated for storing the
current record when switching to the next record-set.
Here are my findings:
- Look on foo.sql defining my stored procedure. It is returning a
record set of ints, and then another record set of text fields. This
code will grow my script's memory footprint a lot every iteration
through the for {} loop defined on line 22 of foo.pl.
- If I will swap the select's in stored procedure, making text fields
go first, memory footprint will still grow, but much slower.
- This never happens when I have one single record set returned.
- Undeffing the connection and calling DBI's disconnect() method does not
help
- Notice I set LongReadLen value to 16 megabytes (line 18 of foo.pl)?
This value directly affects the amount of memory used by my script.
The bigger it is, the larger my mem footprint will be:
16 Megabytes: 150 megabytes of RAM used
8 Megabytes: 80 megabytes of RAM used
4 Megabytes: 40 megabytes of RAM used.
- Text fields were taken just as a good example for watching memory
usage grow rapidly, in general this is happening with any query
having more than one record-set, just with slower rates.
This looks like a bug in DBD::ODBC... Had anybody on the list seen
something similar? Are there any known fixes for this?
Thanx!
Fi.
My Perl build config (apl is just an alias in BASH):
$ apl -v
This is perl, v5.8.8 built for MSWin32-x86-multi-thread
(with 33 registered patches, see perl -V for more detail)
Copyright 1987-2006, Larry Wall
Binary build 819 [267479] provided by ActiveState
http://www.ActiveState.com
Built Aug 29 2006 12:42:41
Perl may be copied only under the terms of either the Artistic License or
the
GNU General Public License, which may be found in the Perl 5 source kit.
Complete documentation for Perl, including FAQ lists, should be found on
this system using "man perl" or "perldoc perl". If you have access to the
Internet, point your browser at http://www.perl.org/, the Perl Home Page.
$ apl -V
Summary of my perl5 (revision 5 version 8 subversion 8) configuration:
Platform:
osname=MSWin32, osvers=5.0, archname=MSWin32-x86-multi-thread
uname=''
config_args='undef'
hint=recommended, useposix=true, d_sigaction=undef
usethreads=define use5005threads=undef useithreads=define
usemultiplicity=de
fine
useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=undef use64bitall=undef uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc='cl', ccflags
='-nologo -GF -W3 -MD -Zi -DNDEBUG -O1 -DWIN32 -D_CONSOLE -
DNO_STRICT -DHAVE_DES_FCRYPT -DNO_HASH_SEED -DUSE_SITECUSTOMIZE -DPERL_IMPLICIT_
CONTEXT -DPERL_IMPLICIT_SYS -DUSE_PERLIO -DPERL_MSVCRT_READFIX',
optimize='-MD -Zi -DNDEBUG -O1',
cppflags='-DWIN32'
ccversion='12.00.8804', gccversion='', gccosandvers=''
intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234
d_longlong=undef, longlongsize=8, d_longdbl=define, longdblsize=10
ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='__int64',
lseeksi
ze=8
alignbytes=8, prototype=define
Linker and Libraries:
ld='link', ldflags
'-nologo -nodefaultlib -debug -opt:ref,icf -libpath:"C:
\Perl\lib\CORE" -machine:x86'
libpth=\lib
libs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib
comdlg32
.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib netapi32.lib
uuid.lib ws2_
32.lib mpr.lib winmm.lib version.lib odbc32.lib odbccp32.lib msvcrt.lib
perllibs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib
comd
lg32.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib netapi32.lib
uuid.lib
ws2_32.lib mpr.lib winmm.lib version.lib odbc32.lib odbccp32.lib
msvcrt.lib
libc=msvcrt.lib, so=dll, useshrplib=yes, libperl=perl58.lib
gnulibc_version=''
Dynamic Linking:
dlsrc=dl_win32.xs, dlext=dll, d_dlsymun=undef, ccdlflags=' '
cccdlflags=' '
Memory leak, DBI 1.55 + DBD::ODBC 1.13 + MS SQL 2000
Hi All!
Recently I have bumped into a memory leak happening in DBI.
Running DBI version 1.55, DBD::ODBC version 1.13 on Windows XP SP2,
Active State Active Perl 5.8 build 819. More comprehensive build info
at the bottom of this email.
You could find the code I am executing attached to this message.
I was executing queries returning multiple record-sets with fields of
text type in them and.. well, the memory usage of my script just grows
drastically. Digging into this, I have found that it looks like
DBD::ODBC is not freeing up memory buffers allocated for storing the
current record when switching to the next record-set.
Here are my findings:
- Look on foo.sql defining my stored procedure. It is returning a
record set of ints, and then another record set of text fields. This
code will grow my script's memory footprint a lot every iteration
through the for {} loop defined on line 22 of foo.pl.
- If I will swap the select's in stored procedure, making text fields
go first, memory footprint will still grow, but much slower.
- This never happens when I have one single record set returned.
- Undeffing the connection and calling DBI's disconnect() method does not help
- Notice I set LongReadLen value to 16 megabytes (line 18 of foo.pl)?
This value directly affects the amount of memory used by my script.
The bigger it is, the larger my mem footprint will be:
16 Megabytes: 150 megabytes of RAM used
8 Megabytes: 80 megabytes of RAM used
4 Megabytes: 40 megabytes of RAM used.
- Text fields were taken just as a good example for watching memory
usage grow rapidly, in general this is happening with any query
having more than one record-set, just with slower rates.
This looks like a bug in DBD::ODBC... Had anybody on the list seen
something similar? Are there any known fixes for this?
Thanx!
Fi.
My Perl build config (apl is just an alias in BASH):
$ apl -v
This is perl, v5.8.8 built for MSWin32-x86-multi-thread
(with 33 registered patches, see perl -V for more detail)
Copyright 1987-2006, Larry Wall
Binary build 819 [267479] provided by ActiveState http://www.ActiveState.com
Built Aug 29 2006 12:42:41
Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.
Complete documentation for Perl, including FAQ lists, should be found on
this system using "man perl" or "perldoc perl". If you have access to the
Internet, point your browser at http://www.perl.org/, the Perl Home Page.
$ apl -V
Summary of my perl5 (revision 5 version 8 subversion 8) configuration:
Platform:
osname=MSWin32, osvers=5.0, archname=MSWin32-x86-multi-thread
uname=''
config_args='undef'
hint=recommended, useposix=true, d_sigaction=undef
usethreads=define use5005threads=undef useithreads=define usemultiplicity=de
fine
useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=undef use64bitall=undef uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc='cl', ccflags ='-nologo -GF -W3 -MD -Zi -DNDEBUG -O1 -DWIN32 -D_CONSOLE -
DNO_STRICT -DHAVE_DES_FCRYPT -DNO_HASH_SEED -DUSE_SITECUSTOMIZE -DPERL_IMPLICIT_
CONTEXT -DPERL_IMPLICIT_SYS -DUSE_PERLIO -DPERL_MSVCRT_READFIX',
optimize='-MD -Zi -DNDEBUG -O1',
cppflags='-DWIN32'
ccversion='12.00.8804', gccversion='', gccosandvers=''
intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234
d_longlong=undef, longlongsize=8, d_longdbl=define, longdblsize=10
ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='__int64', lseeksi
ze=8
alignbytes=8, prototype=define
Linker and Libraries:
ld='link', ldflags ='-nologo -nodefaultlib -debug -opt:ref,icf -libpath:"C:
\Perl\lib\CORE" -machine:x86'
libpth=\lib
libs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib comdlg32
.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib netapi32.lib uuid.lib ws2_
32.lib mpr.lib winmm.lib version.lib odbc32.lib odbccp32.lib msvcrt.lib
perllibs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib comd
lg32.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib netapi32.lib uuid.lib
ws2_32.lib mpr.lib winmm.lib version.lib odbc32.lib odbccp32.lib msvcrt.lib
libc=msvcrt.lib, so=dll, useshrplib=yes, libperl=perl58.lib
gnulibc_version=''
Dynamic Linking:
dlsrc=dl_win32.xs, dlext=dll, d_dlsymun=undef, ccdlflags=' '
cccdlflags=' ', lddlflags='-dll -nologo -nodefaultlib -debug -opt:ref,icf -
libpath:"C:\Perl\lib\CORE" -machine:x86'
Characteristics of this binary (from libperl):
Compile-time options: MULTIPLICITY PERL_IMPLICIT_CONTEXT
PERL_IMPLICIT_SYS PERL_MALLOC_WRAP
PL_OP_SLAB_ALLOC USE_ITHREADS USE_
Memory leak with DBI 1.52 & Oracle - Please Help
Hi, I'm no Perl expert, but I also would not consider myself a newbie. I
seem to have a memory leak issue with the DBI module (1.52) on Windows
connecting to Oracle (10gR2).
For reasons I won't go into at this time, I need to have a continuous loop
that will connect to many Oracle instances. I'm doing this using
parallelized subroutine calls and I do have to connect, perform the required
queries, and then disconnect inside the parallelized subroutine (leaving a
persistent connection unfortunately is not an option). The subroutine calls
must be parallelized so they are non-blocking.
What I find is that with repeated subsequent database connections, the
working set memory continuously grows until the Perl script exhausts all of
the memory on the machine. It does go up and down as procedures begin and
end, but it also grows over time. I've developed an overly simplified
version of my program (shown below) to illustrate/replicate the memory leak.
I'm using ActivePerl 5.8.8 (builds 816 and 818). With build 816 (and
whatever version of DBI comes with it), the script will run and continue to
loop until it crashes from lack of memory due to the leak. Upgrading to
build 818, (or build 816 with DBI 1.52 and DBD-Oracle 1.17) still shows the
memory leak but also throws an occasional Windows error: "An unhandled win32
exception occurred in perl.exe [5488] ". The strange part is that the
unhandled exception error will sometimes come on pretty much any iteration
of the loop (sometimes as soon as the 3rd, sometimes as late as the 30th).
Here is a simple test script that illustrates the leak, and the unhandled
win32 exception (exception is only with the later DBI versions):
use strict;
use DBI;
use subs::parallel;
sub DB_Connect : Parallel {
print "DB_Connect: parallel subroutine $_[0] is starting\n";
my $dbh =
DBI->connect('dbi:Oracle:host=127.0.0.1;sid=ORA1020;port=1521','SYSTEM','MAN
AGER',{RaiseError=>0,PrintError=>1,AutoCommit=>0});
print "Connected to Database\n";
$dbh->disconnect || warn $dbh->errstr;
print "DB_Connect: parallel subroutine $_[0] is ending\n";
sleep 4;
}
main {
while (1) {
print "Starting Loop\n";
&DB_Connect("A");
&DB_Connect("B");
&DB_Connect("C");
print "Finished Loop\n";
sleep 5
}
}
Can someone please help me by confirming if this is a true leak/bug with the
DBI 1.52 module or am I doing something incorrectly? I don't think that the
problem is with subs::parallel since if I take out the DBI calls, the loop
will run continuously without error or memory leaks.
Help is greatly appreciated - I've been scouring the net for days and see
reports of similar problems but reports that they were fixed in 1.52.
Help and advise is really appreciated since I can't work out anything that
I'm doing wrong.
Thanks in advance for any comments or suggestions!!
Simon.
([EMAIL PROTECTED])
Re: memory leak in DBI ...
On 7/17/06, Ephraim Dan <[EMAIL PROTECTED]> wrote:
Can someone else try to reproduce a memory leak in a simple
connect/prepare/execute/disconnect loop using DBI 1.51 and any DBD driver?
I created a test program using the test harness distributed with
DBD::Informix and found a small but gentle leak. Over a period of multiple
thousand connections, the Perl executable grew from 866 KB to 973 KB, and
showed no signs of stopping. Is that what you were looking for? (In an
Informix database, the Systables system catalog table is always present.)
This was tested with DBI 1.50 on Solaris 8 (and Perl 5.8.7 - I won't bore
you with why it wasn't 5.8.8). I upgraded to DBI 1.51 (same DBD::Informix
2005.02) and got the same basic result, with Perl growing from 869 to 903
over 2000 iterations.
#!/bin/perl -w
#
# Memory leak test per Ephraim Dan
use strict;
use DBD::Informix::TestHarness;
my $conn_count = 0;
sub test_connection
{
$conn_count++;
my $dbh = &connect_to_test_database({ AutoCommit => 0, RaiseError => 1,
PrintError => 1 });
my $sth = $dbh->prepare("SELECT * FROM Systables");
$sth->execute;
$dbh->disconnect;
}
sub test_subroutine
{
while (1)
{
test_connection;
print "Connections: $conn_count\n" if ($conn_count % 1000 == 0);
}
}
memory_leak_test(\&test_subroutine);
exit;
--
Jonathan Leffler <[EMAIL PROTECTED]> #include
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
RE: FW: memory leak in DBI ...
It certainly does appear to leak memory for you, too. My script is similar, as
are my results.
Thanks for checking.
Appears to be a confirmed memory leak to me. Does anyone have an idea of how
the weakref code can be fixed to solve this? It appears that Tim doesn't have
time...
Thanks,
Ephraim
> -Original Message-
> From: Tielman de Villiers [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 18, 2006 15:38
> To: Ephraim Dan
> Subject: RE: FW: memory leak in DBI ...
>
> perl v5.8.7
>
> DBI ver 1.50:
> before | total pid size: 6024 kB
> 1 | total pid size: 9152 kB (+3128 kB)
>101 | total pid size: 9152 kB (+3128 kB)
>201 | total pid size: 9152 kB (+3128 kB)
>301 | total pid size: 9208 kB (+3184 kB)
>401 | total pid size: 9208 kB (+3184 kB)
>501 | total pid size: 9208 kB (+3184 kB)
>601 | total pid size: 9208 kB (+3184 kB)
>701 | total pid size: 9208 kB (+3184 kB)
>801 | total pid size: 9208 kB (+3184 kB)
>901 | total pid size: 9208 kB (+3184 kB)
> after | total pid size: 9332 kB
>
> DBI 1.51:
> before | total pid size: 5988 kB
> 1 | total pid size: 9116 kB (+3128 kB)
>101 | total pid size: 9116 kB (+3128 kB)
>201 | total pid size: 9116 kB (+3128 kB)
>301 | total pid size: 9172 kB (+3184 kB)
>401 | total pid size: 9172 kB (+3184 kB)
>501 | total pid size: 9172 kB (+3184 kB)
>601 | total pid size: 9172 kB (+3184 kB)
>701 | total pid size: 9172 kB (+3184 kB)
>801 | total pid size: 9172 kB (+3184 kB)
>901 | total pid size: 9172 kB (+3184 kB)
> after | total pid size: 9296 kB
>
>
>
>
>
>
>
>
>
> On Tue, 2006-07-18 at 05:31 -0700, Ephraim Dan wrote:
> > What results did this give you?
> >
> > Thanks for testing.
> > I'll send you my test script soon.
> >
> > > -Original Message-
> > > From: Tielman de Villiers [mailto:[EMAIL PROTECTED]
> > > Sent: Tuesday, July 18, 2006 15:29
> > > To: Ephraim Dan
> > > Subject: Re: FW: memory leak in DBI ...
> > >
> > > Hi Ephraim,
> > >
> > > I can have a look at this for you, could you pls send me your test
> > > script for the mem leaks? (I'll leave it up to you to post the results
> > > to the list).
> > >
> > > --tielman
> > >
> > > I've tested something like the below, but not sure if that gives the
> > > results you want (linux)?
> > >
> > >
> > > __START__
> > > my @statusArray;
> > >
> > > @statusArray = split (/\s+/, `grep Vm /proc/$$/status`);
> > > printf "before | total pid size: %5d kB\n", $statusArray[1];
> > >
> > > my $startmem = $statusArray[1];
> > >
> > > use DBI;
> > > ##my $dbh = DBI->connect( "dbi:Pg:dbname=test;host=xx", "user",
> > > "secret" );
> > > for my $i ( 1 .. 1000 ) {
> > > my $dbh = DBI->connect( "dbi:Pg:dbname=test;host=xx", "user",
> > > "secret" );
> > > my $sth = $dbh->prepare("SELECT * FROM test");
> > > $sth->execute();
> > > $sth->finish;
> > >$dbh->disconnect;
> > > @statusArray = split (/\s+/, `grep Vm /proc/$$/status`);
> > > if ( $i % 100 == 1 ) {
> > > printf "%6d | total pid size: %5d kB (+%3d kB)\n", $i,
> > > $statusArray[1], $statusArray[1] - $startmem;
> > > }
> > > }
> > > ##$dbh->disconnect;
> > >
> > > @statusArray = split (/\s+/, `grep Vm /proc/$$/status`);
> > > printf "after | total pid size: %5d kB\n", $statusArray[1];
> > >
> > > __END__
> > >
> > >
> > >
> > >
> > > On Mon, 2006-07-17 at 20:41 +0100, Tielman de Villiers wrote:
> > > >
> > > > > -Original Message-
> > > > > From: Ephraim Dan [mailto:[EMAIL PROTECTED]
> > > > > Sent: 17 July 2006 18:39
> > > > > To: Ephraim Dan; Tim Bunce
> > > > > Cc: dbi-users@perl.org
> > > > > Subject: RE: memory leak in DBI ...
> > > > >
> > > > >
> > > > > Tim,
> > > > >
> > > > > Any chance you've had a chance to look at this?
> > > > >
> > > > > Can someone else try to reproduce a memory leak in a simple
> > > > > connect/prepare/execute/disconnect loop using DBI 1.51 and
> > > > > a
RE: memory leak in DBI ...
Tim, Any chance you've had a chance to look at this? Can someone else try to reproduce a memory leak in a simple connect/prepare/execute/disconnect loop using DBI 1.51 and any DBD driver? Thanks... --edan From: Ephraim Dan [mailto:[EMAIL PROTECTED] Sent: Thu 7/13/2006 11:02 To: Tim Bunce Cc: dbi-users@perl.org Subject: RE: memory leak in DBI XS bootstrap code > I've just noticed that you're using 5.8.0. Try the latest 5.8.x. [edan] We can't upgrade perl very easily in our production environment, but I tested on another box running perl 5.8.8, DBI 1.50, and I get the same leak of 4 per loop. Any chance you could find the time to attempt to reproduce this yourself? Thanks, edan
RE: memory leak in DBI XS bootstrap code
> I've just noticed that you're using 5.8.0. Try the latest 5.8.x. [edan] We can't upgrade perl very easily in our production environment, but I tested on another box running perl 5.8.8, DBI 1.50, and I get the same leak of 4 per loop. Any chance you could find the time to attempt to reproduce this yourself? Thanks, edan
Re: memory leak in DBI XS bootstrap code
On Wed, Jul 12, 2006 at 10:44:38PM -0700, Ephraim Dan wrote: > > > Did you try the other suggestions? > > How much improvement did each give? (when testing by destroying the > > interpreter) > > Yeah, sorry I didn't give more detail on that. Here goes: > > The first change (Doru Petrescu): > No change, still leaks 4 per connect/prepare/execute/fetch/disconnect loop. That change is definitely a leak fix - but the leak would only happen every 120 new handles where some old handles have also been destroyed. > The (av_len(av) % 120 == 0) change: > > That does reduce it to 3 per loop, but I am not sure what you mean > that it is not a leak - when it leaks 4, it's 4 every time - it's not > stabilizing after some time, or jumping back down once in a while, or > only leaking 3 sometimes, just going up and up by 4 every time. > So how is that not a leak? I said "If that seems to have fixed one then it's not really a leak, just some caching that the DBI does." I would be more clear after s/then it's not/then that one is not/. > As I said, the "if (0)" on the whole chunk of code produces squeaky clean > iterations. I've just noticed that you're using 5.8.0. Try the latest 5.8.x. Tim.
RE: memory leak in DBI XS bootstrap code
> Did you try the other suggestions?
> How much improvement did each give? (when testing by destroying the
> interpreter)
Yeah, sorry I didn't give more detail on that. Here goes:
The first change (Doru Petrescu):
No change, still leaks 4 per connect/prepare/execute/fetch/disconnect loop.
The (av_len(av) % 120 == 0) change:
That does reduce it to 3 per loop, but I am not sure what you mean that it is
not a leak - when it leaks 4, it's 4 every time - it's not stabilizing after
some time, or jumping back down once in a while, or only leaking 3 sometimes,
just going up and up by 4 every time. So how is that not a leak?
As I said, the "if (0)" on the whole chunk of code produces squeaky clean
iterations.
-edan
> > So... do I need to keep this patched myself, at the risk of breaking
> > when the code changes in some future release, or can you somehow make
> > this optional, or fix it,
>
> I'd probably accept a patch that adds an option to Makefile.PL that then
> passes a -DDBI_NO_WEAKREFS to the compiler which, with a suitable
> #ifndef in DBI.xs, would disable that code. Or a new per-handle
> flag could be added.
>
> But I'm reluctant to do either till I know more about the cause.
>
> > or explain to me what I can do to make the
> > leak go away using the current code?
>
> I need more info - see above.
>
> Tim.
>
> > Thanks so much,
> > edan
> >
> > > > > -Original Message-
> > > > > From: Tim Bunce [mailto:[EMAIL PROTECTED]
> > > > > Sent: Tuesday, July 11, 2006 16:59
> > > > > To: Ephraim Dan
> > > > > Cc: Tim Bunce; dbi-users@perl.org
> > > > > Subject: Re: memory leak in DBI XS bootstrap code
> > > > >
> > > > > I'm sorry but I simply don't have the time at the moment.
> > > > >
> > > > > Some random suggestions:
> > > > > - try using different sets of methods to isolate the minimum
> > > > > requirements.
> > > > >for example:
> > > > > call fetchrow_array just once.
> > > > > don't call fetchrow_array at all.
> > > > > call fetchrow_array in loop but don't call finish
> > > > > don't execute, fetch, finish.
> > > > > etc etc etc
> > > > > - try with other drivers to see if it's the driver or the DBI
> that's
> > > > > leaking
> > > > >
> > > > > Tim.
> > > > >
> > > > > On Tue, Jul 11, 2006 at 03:59:05AM -0700, Ephraim Dan wrote:
> > > > > > Tim,
> > > > > >
> > > > > > I'm experiencing further memory leaks which are causing me more
> > > grief.
> > > > > I put together the following test script which illustrates the
> > > leakage:
> > > > > >
> > > > > > #/usr/bin/perl
> > > > > > # dbi.pl
> > > > > >
> > > > > > use DBI;
> > > > > > use Devel::Leak;
> > > > > > my $leak_handle;
> > > > > >
> > > > > > for ( 1 .. 1000 ) {
> > > > > >my $dbh = DBI->connect("DBI:mysql:MYDB","user","secret",
> > > > > {RaiseError=>1});
> > > > > >my $sth = $dbh->prepare("SELECT COUNT(*) FROM TABLE");
> > > > > >$sth->execute();
> > > > > >while ( my ($c) = $sth->fetchrow_array() ) {
> > > > > > # print "$c\n";
> > > > > >}
> > > > > >$sth->finish;
> > > > > >$dbh->disconnect;
> > > > > >
> > > > > >system("ps -aux | fgrep dbi.pl | fgrep -v fgrep");
> > > > > >test_leak();
> > > > > > }
> > > > > >
> > > > > > sub test_leak {
> > > > > > if ( $handle ) {
> > > > > > Devel::Leak::CheckSV($handle);
> > > > > > }
> > > > > > print STDERR "count: " . Devel::Leak::NoteSV($handle) . "\n";
> > > > > > }
> > > > > >
> > > > > > Devel::Leak shows that it is leaking 4 "things" every iteration:
> > > > > >
> > > > > > count: 12128
> > > > > > root 17720 17.8 0.3 6840 4220 pts/0S09:
Re: memory leak in DBI XS bootstrap code
On Wed, Jul 12, 2006 at 10:12:26AM -0700, Ephraim Dan wrote:
> > You could also try another change a few lines further up where it says
> >
> > #ifdef sv_rvweaken
> > if (1) {
> >
> > Change the 1 to a 0 to disable the use of weakrefs which was added in
> > 1.49.
>
> [edan] This seems to have done it! It fixes all the leaks! Hurrah!
Did you try the other suggestions?
How much improvement did each give? (when testing by destroying the interpreter)
> So... do I need to keep this patched myself, at the risk of breaking
> when the code changes in some future release, or can you somehow make
> this optional, or fix it,
I'd probably accept a patch that adds an option to Makefile.PL that then
passes a -DDBI_NO_WEAKREFS to the compiler which, with a suitable
#ifndef in DBI.xs, would disable that code. Or a new per-handle
flag could be added.
But I'm reluctant to do either till I know more about the cause.
> or explain to me what I can do to make the
> leak go away using the current code?
I need more info - see above.
Tim.
> Thanks so much,
> edan
>
> > > > -Original Message-
> > > > From: Tim Bunce [mailto:[EMAIL PROTECTED]
> > > > Sent: Tuesday, July 11, 2006 16:59
> > > > To: Ephraim Dan
> > > > Cc: Tim Bunce; dbi-users@perl.org
> > > > Subject: Re: memory leak in DBI XS bootstrap code
> > > >
> > > > I'm sorry but I simply don't have the time at the moment.
> > > >
> > > > Some random suggestions:
> > > > - try using different sets of methods to isolate the minimum
> > > > requirements.
> > > >for example:
> > > > call fetchrow_array just once.
> > > > don't call fetchrow_array at all.
> > > > call fetchrow_array in loop but don't call finish
> > > > don't execute, fetch, finish.
> > > > etc etc etc
> > > > - try with other drivers to see if it's the driver or the DBI that's
> > > > leaking
> > > >
> > > > Tim.
> > > >
> > > > On Tue, Jul 11, 2006 at 03:59:05AM -0700, Ephraim Dan wrote:
> > > > > Tim,
> > > > >
> > > > > I'm experiencing further memory leaks which are causing me more
> > grief.
> > > > I put together the following test script which illustrates the
> > leakage:
> > > > >
> > > > > #/usr/bin/perl
> > > > > # dbi.pl
> > > > >
> > > > > use DBI;
> > > > > use Devel::Leak;
> > > > > my $leak_handle;
> > > > >
> > > > > for ( 1 .. 1000 ) {
> > > > >my $dbh = DBI->connect("DBI:mysql:MYDB","user","secret",
> > > > {RaiseError=>1});
> > > > >my $sth = $dbh->prepare("SELECT COUNT(*) FROM TABLE");
> > > > >$sth->execute();
> > > > >while ( my ($c) = $sth->fetchrow_array() ) {
> > > > > # print "$c\n";
> > > > >}
> > > > >$sth->finish;
> > > > >$dbh->disconnect;
> > > > >
> > > > >system("ps -aux | fgrep dbi.pl | fgrep -v fgrep");
> > > > >test_leak();
> > > > > }
> > > > >
> > > > > sub test_leak {
> > > > > if ( $handle ) {
> > > > > Devel::Leak::CheckSV($handle);
> > > > > }
> > > > > print STDERR "count: " . Devel::Leak::NoteSV($handle) . "\n";
> > > > > }
> > > > >
> > > > > Devel::Leak shows that it is leaking 4 "things" every iteration:
> > > > >
> > > > > count: 12128
> > > > > root 17720 17.8 0.3 6840 4220 pts/0S09:55 0:00 perl
> > > > dbi.pl
> > > > > new 0x8aad3e8 : SV = PVHV(0x8a68b30) at 0x8aad3e8
> > > > > REFCNT = 1
> > > > > FLAGS = (PADBUSY,PADMY,SHAREKEYS)
> > > > > IV = 0
> > > > > NV = 0
> > > > > ARRAY = 0x0
> > > > > KEYS = 0
> > > > > FILL = 0
> > > > > MAX = 7
> > > > > RITER = -1
> > > > > EITER = 0x0
> > > > > new 0x8aad3f4 : SV = NULL(0x0) at 0x8aad3f4
> > > > > REFCNT = 1
> > > > > FLAGS = (PADBUSY,PADMY)
&g
RE: memory leak in DBI XS bootstrap code
> You could also try another change a few lines further up where it says
>
> #ifdef sv_rvweaken
> if (1) {
>
> Change the 1 to a 0 to disable the use of weakrefs which was added in
> 1.49.
[edan] This seems to have done it! It fixes all the leaks! Hurrah! So... do
I need to keep this patched myself, at the risk of breaking when the code
changes in some future release, or can you somehow make this optional, or fix
it, or explain to me what I can do to make the leak go away using the current
code?
Thanks so much,
edan
> > > -Original Message-
> > > From: Tim Bunce [mailto:[EMAIL PROTECTED]
> > > Sent: Tuesday, July 11, 2006 16:59
> > > To: Ephraim Dan
> > > Cc: Tim Bunce; dbi-users@perl.org
> > > Subject: Re: memory leak in DBI XS bootstrap code
> > >
> > > I'm sorry but I simply don't have the time at the moment.
> > >
> > > Some random suggestions:
> > > - try using different sets of methods to isolate the minimum
> > > requirements.
> > >for example:
> > > call fetchrow_array just once.
> > > don't call fetchrow_array at all.
> > > call fetchrow_array in loop but don't call finish
> > > don't execute, fetch, finish.
> > > etc etc etc
> > > - try with other drivers to see if it's the driver or the DBI that's
> > > leaking
> > >
> > > Tim.
> > >
> > > On Tue, Jul 11, 2006 at 03:59:05AM -0700, Ephraim Dan wrote:
> > > > Tim,
> > > >
> > > > I'm experiencing further memory leaks which are causing me more
> grief.
> > > I put together the following test script which illustrates the
> leakage:
> > > >
> > > > #/usr/bin/perl
> > > > # dbi.pl
> > > >
> > > > use DBI;
> > > > use Devel::Leak;
> > > > my $leak_handle;
> > > >
> > > > for ( 1 .. 1000 ) {
> > > >my $dbh = DBI->connect("DBI:mysql:MYDB","user","secret",
> > > {RaiseError=>1});
> > > >my $sth = $dbh->prepare("SELECT COUNT(*) FROM TABLE");
> > > >$sth->execute();
> > > >while ( my ($c) = $sth->fetchrow_array() ) {
> > > > # print "$c\n";
> > > >}
> > > >$sth->finish;
> > > >$dbh->disconnect;
> > > >
> > > >system("ps -aux | fgrep dbi.pl | fgrep -v fgrep");
> > > >test_leak();
> > > > }
> > > >
> > > > sub test_leak {
> > > > if ( $handle ) {
> > > > Devel::Leak::CheckSV($handle);
> > > > }
> > > > print STDERR "count: " . Devel::Leak::NoteSV($handle) . "\n";
> > > > }
> > > >
> > > > Devel::Leak shows that it is leaking 4 "things" every iteration:
> > > >
> > > > count: 12128
> > > > root 17720 17.8 0.3 6840 4220 pts/0S09:55 0:00 perl
> > > dbi.pl
> > > > new 0x8aad3e8 : SV = PVHV(0x8a68b30) at 0x8aad3e8
> > > > REFCNT = 1
> > > > FLAGS = (PADBUSY,PADMY,SHAREKEYS)
> > > > IV = 0
> > > > NV = 0
> > > > ARRAY = 0x0
> > > > KEYS = 0
> > > > FILL = 0
> > > > MAX = 7
> > > > RITER = -1
> > > > EITER = 0x0
> > > > new 0x8aad3f4 : SV = NULL(0x0) at 0x8aad3f4
> > > > REFCNT = 1
> > > > FLAGS = (PADBUSY,PADMY)
> > > > new 0x8aad400 : SV = NULL(0x0) at 0x8aad400
> > > > REFCNT = 1
> > > > FLAGS = (PADBUSY,PADMY)
> > > > new 0x8aad40c : SV = NULL(0x0) at 0x8aad40c
> > > > REFCNT = 1
> > > > FLAGS = (PADBUSY,PADMY)
> > > > count: 12132
> > > >
> > > > I think there might be several leaks here. If I move the connect()
> out
> > > of the loop, it's leaking, but less - just one of those NULL SV guys.
> > > >
> > > > Can you please help me track this down? I'm still a little scared
> of
> > > the DBI.xs internals, despite my recent exposure...
> > > >
> > > > Thanks a lot,
> > > > edan
> > > >
> > > >
> > > > > -Original Message-
> > > > > From: Tim Bunce [mailto:[EMAIL PROTECTED]
> > > > > Sent: Monday, July 03, 2006 12:16
> > >
Re: memory leak in DBI XS bootstrap code
On Wed, Jul 12, 2006 at 05:30:47AM -0700, Ephraim Dan wrote:
> I am 99% sure that it's DBI that's leaking. We were using DBI 1.32
> previously, and the same script does not leak when 1.32 is installed.
> Changing only DBI to 1.51 produces the leak. No DBDs were touched.
>
> I think it's isolated to connect and prepare:
>
> 1. When the loop consists only of connect and disconnect, 1 SV is leaked.
> 2. When the loop is connect, prepare and disconnect, 4 SVs are leaked.
> 3. When the connect is done only once, and the loop is only prepare, 1 SV is
> leaked.
> 4. When connect and prepare are outside the loop, and the loop consists of
> execute, fetchrow_array, finish, 0 SVs are leaked.
>
> I also tried it with DBD::Sponge, and the same numbers resulted.
>
> I have tried various attempts at finding this leak, but to no avail.
> I am pretty sure it's in the XS code, but I can't be sure. I tried
> using Devel::Cycle to see if it's a circular reference, but it didn't
> show anything. I don't know of other ways to make a memory leak in
> pure perl, which is why I suspect XS.
>
> I tried to use Devel::LeakTrace to find where the leaks are, but I'm
> not so sure of its results. For what it's worth, it seems to report
> leaks within DBI.pm in the following subroutines:
>
> connect() (return $dbh) install_driver() (eval qq{ DBI::_firesafe
> require $driver_class }, $DBI::installed_drh{$driver} = $drh)
> setup_driver() (push @{"${class}_mem::ISA"}, $mem_class) _new_handle()
> (DBI::_setup_handle($h, $imp_class, $parent, $imp_data))
>
> Do you have any other pointers on how to track this down? I am more
> than willing to try more things, but I've run out of ideas. I have
> tried valgrind but I don't think it helps with perl refcount issues,
> since they are not true memory leaks in the sense of lost pointers to
> malloc'ed buffers... In any event this has been a dead end so far...
>
> Thanks for any help you can give, Tim (or anyone else, please?)
You've reminded me that there's a memleak patch from Doru
Theodor Petrescu in the current development version. 1 sv per sth:
--- dbi/trunk/DBI.xs(original)
+++ dbi/trunk/DBI.xsMon Jun 12 13:24:19 2006
@@ -1069,6 +1069,8 @@
SV *sv = av_shift(av);
if (SvOK(sv))
av_push(av, sv);
+ else
+ sv_free(sv); /* keep it leak-free by Doru
Petrescu [EMAIL PROTECTED] */
}
}
}
Also, just above there is the line:
if (av_len(av) % 120 == 0) {
try changing that to
if (1) {
and recheck the leaks. If that seems to have fixed one then it's not
really a leak, just some caching that the DBI does.
You could also try another change a few lines further up where it says
#ifdef sv_rvweaken
if (1) {
Change the 1 to a 0 to disable the use of weakrefs which was added in 1.49.
Tim.
> -edan
>
> > -Original Message-
> > From: Tim Bunce [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, July 11, 2006 16:59
> > To: Ephraim Dan
> > Cc: Tim Bunce; dbi-users@perl.org
> > Subject: Re: memory leak in DBI XS bootstrap code
> >
> > I'm sorry but I simply don't have the time at the moment.
> >
> > Some random suggestions:
> > - try using different sets of methods to isolate the minimum
> > requirements.
> >for example:
> > call fetchrow_array just once.
> > don't call fetchrow_array at all.
> > call fetchrow_array in loop but don't call finish
> > don't execute, fetch, finish.
> > etc etc etc
> > - try with other drivers to see if it's the driver or the DBI that's
> > leaking
> >
> > Tim.
> >
> > On Tue, Jul 11, 2006 at 03:59:05AM -0700, Ephraim Dan wrote:
> > > Tim,
> > >
> > > I'm experiencing further memory leaks which are causing me more grief.
> > I put together the following test script which illustrates the leakage:
> > >
> > > #/usr/bin/perl
> > > # dbi.pl
> > >
> > > use DBI;
> > > use Devel::Leak;
> > > my $leak_handle;
> > >
> > > for ( 1 .. 1000 ) {
> > >my $dbh = DBI->connect("DBI:mysql:MYDB","user","secret",
> > {RaiseError=>1});
> > >my $sth = $dbh->prepare("SELECT COUNT(*) FROM TABLE");
> > >$sth->execute();
> > >while ( my ($c) = $sth->fetchrow_array() ) {
> > > # print "$c\n";
> >
RE: memory leak in DBI XS bootstrap code
I am 99% sure that it's DBI that's leaking. We were using DBI 1.32 previously,
and the same script does not leak when 1.32 is installed. Changing only DBI to
1.51 produces the leak. No DBDs were touched.
I think it's isolated to connect and prepare:
1. When the loop consists only of connect and disconnect, 1 SV is leaked.
2. When the loop is connect, prepare and disconnect, 4 SVs are leaked.
3. When the connect is done only once, and the loop is only prepare, 1 SV is
leaked.
4. When connect and prepare are outside the loop, and the loop consists of
execute, fetchrow_array, finish, 0 SVs are leaked.
I also tried it with DBD::Sponge, and the same numbers resulted.
I have tried various attempts at finding this leak, but to no avail. I am
pretty sure it's in the XS code, but I can't be sure. I tried using
Devel::Cycle to see if it's a circular reference, but it didn't show anything.
I don't know of other ways to make a memory leak in pure perl, which is why I
suspect XS.
I tried to use Devel::LeakTrace to find where the leaks are, but I'm not so
sure of its results. For what it's worth, it seems to report leaks within
DBI.pm in the following subroutines:
connect() (return $dbh)
install_driver() (eval qq{ DBI::_firesafe require $driver_class },
$DBI::installed_drh{$driver} = $drh)
setup_driver() (push @{"${class}_mem::ISA"}, $mem_class)
_new_handle() (DBI::_setup_handle($h, $imp_class, $parent, $imp_data))
Do you have any other pointers on how to track this down? I am more than
willing to try more things, but I've run out of ideas. I have tried valgrind
but I don't think it helps with perl refcount issues, since they are not true
memory leaks in the sense of lost pointers to malloc'ed buffers... In any
event this has been a dead end so far...
Thanks for any help you can give, Tim (or anyone else, please?)
-edan
> -Original Message-
> From: Tim Bunce [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 11, 2006 16:59
> To: Ephraim Dan
> Cc: Tim Bunce; dbi-users@perl.org
> Subject: Re: memory leak in DBI XS bootstrap code
>
> I'm sorry but I simply don't have the time at the moment.
>
> Some random suggestions:
> - try using different sets of methods to isolate the minimum
> requirements.
>for example:
> call fetchrow_array just once.
> don't call fetchrow_array at all.
> call fetchrow_array in loop but don't call finish
> don't execute, fetch, finish.
> etc etc etc
> - try with other drivers to see if it's the driver or the DBI that's
> leaking
>
> Tim.
>
> On Tue, Jul 11, 2006 at 03:59:05AM -0700, Ephraim Dan wrote:
> > Tim,
> >
> > I'm experiencing further memory leaks which are causing me more grief.
> I put together the following test script which illustrates the leakage:
> >
> > #/usr/bin/perl
> > # dbi.pl
> >
> > use DBI;
> > use Devel::Leak;
> > my $leak_handle;
> >
> > for ( 1 .. 1000 ) {
> >my $dbh = DBI->connect("DBI:mysql:MYDB","user","secret",
> {RaiseError=>1});
> >my $sth = $dbh->prepare("SELECT COUNT(*) FROM TABLE");
> >$sth->execute();
> >while ( my ($c) = $sth->fetchrow_array() ) {
> > # print "$c\n";
> >}
> >$sth->finish;
> >$dbh->disconnect;
> >
> >system("ps -aux | fgrep dbi.pl | fgrep -v fgrep");
> >test_leak();
> > }
> >
> > sub test_leak {
> > if ( $handle ) {
> > Devel::Leak::CheckSV($handle);
> > }
> > print STDERR "count: " . Devel::Leak::NoteSV($handle) . "\n";
> > }
> >
> > Devel::Leak shows that it is leaking 4 "things" every iteration:
> >
> > count: 12128
> > root 17720 17.8 0.3 6840 4220 pts/0S09:55 0:00 perl
> dbi.pl
> > new 0x8aad3e8 : SV = PVHV(0x8a68b30) at 0x8aad3e8
> > REFCNT = 1
> > FLAGS = (PADBUSY,PADMY,SHAREKEYS)
> > IV = 0
> > NV = 0
> > ARRAY = 0x0
> > KEYS = 0
> > FILL = 0
> > MAX = 7
> > RITER = -1
> > EITER = 0x0
> > new 0x8aad3f4 : SV = NULL(0x0) at 0x8aad3f4
> > REFCNT = 1
> > FLAGS = (PADBUSY,PADMY)
> > new 0x8aad400 : SV = NULL(0x0) at 0x8aad400
> > REFCNT = 1
> > FLAGS = (PADBUSY,PADMY)
> > new 0x8aad40c : SV = NULL(0x0) at 0x8aad40c
> > REFCNT = 1
> > FLAGS = (PADBUSY,PADMY)
> > count: 12132
> >
> > I think there might be several leaks here. If I move the connect() out
> of the loop, it's leaking, but less - just one of th
Re: memory leak in DBI XS bootstrap code
I'm sorry but I simply don't have the time at the moment.
Some random suggestions:
- try using different sets of methods to isolate the minimum requirements.
for example:
call fetchrow_array just once.
don't call fetchrow_array at all.
call fetchrow_array in loop but don't call finish
don't execute, fetch, finish.
etc etc etc
- try with other drivers to see if it's the driver or the DBI that's leaking
Tim.
On Tue, Jul 11, 2006 at 03:59:05AM -0700, Ephraim Dan wrote:
> Tim,
>
> I'm experiencing further memory leaks which are causing me more grief. I put
> together the following test script which illustrates the leakage:
>
> #/usr/bin/perl
> # dbi.pl
>
> use DBI;
> use Devel::Leak;
> my $leak_handle;
>
> for ( 1 .. 1000 ) {
>my $dbh = DBI->connect("DBI:mysql:MYDB","user","secret", {RaiseError=>1});
>my $sth = $dbh->prepare("SELECT COUNT(*) FROM TABLE");
>$sth->execute();
>while ( my ($c) = $sth->fetchrow_array() ) {
> # print "$c\n";
>}
>$sth->finish;
>$dbh->disconnect;
>
>system("ps -aux | fgrep dbi.pl | fgrep -v fgrep");
>test_leak();
> }
>
> sub test_leak {
> if ( $handle ) {
> Devel::Leak::CheckSV($handle);
> }
> print STDERR "count: " . Devel::Leak::NoteSV($handle) . "\n";
> }
>
> Devel::Leak shows that it is leaking 4 "things" every iteration:
>
> count: 12128
> root 17720 17.8 0.3 6840 4220 pts/0S09:55 0:00 perl dbi.pl
> new 0x8aad3e8 : SV = PVHV(0x8a68b30) at 0x8aad3e8
> REFCNT = 1
> FLAGS = (PADBUSY,PADMY,SHAREKEYS)
> IV = 0
> NV = 0
> ARRAY = 0x0
> KEYS = 0
> FILL = 0
> MAX = 7
> RITER = -1
> EITER = 0x0
> new 0x8aad3f4 : SV = NULL(0x0) at 0x8aad3f4
> REFCNT = 1
> FLAGS = (PADBUSY,PADMY)
> new 0x8aad400 : SV = NULL(0x0) at 0x8aad400
> REFCNT = 1
> FLAGS = (PADBUSY,PADMY)
> new 0x8aad40c : SV = NULL(0x0) at 0x8aad40c
> REFCNT = 1
> FLAGS = (PADBUSY,PADMY)
> count: 12132
>
> I think there might be several leaks here. If I move the connect() out of
> the loop, it's leaking, but less - just one of those NULL SV guys.
>
> Can you please help me track this down? I'm still a little scared of the
> DBI.xs internals, despite my recent exposure...
>
> Thanks a lot,
> edan
>
>
> > -Original Message-
> > From: Tim Bunce [mailto:[EMAIL PROTECTED]
> > Sent: Monday, July 03, 2006 12:16
> > To: Ephraim Dan
> > Cc: Tim Bunce; dbi-users@perl.org
> > Subject: Re: memory leak in DBI XS bootstrap code
> >
> > On Sat, Jul 01, 2006 at 10:42:58PM -0700, Ephraim Dan wrote:
> > > Thanks for the help Tim! You've really done me a great service.
> > >
> > > I have created the following patch, which appears to fix all of the
> > leaks. Can you please review it, and give it your stamp of approval? I
> > am willing to use this patch if you approve it, until a new DBI is
> > available with the fixes.
> >
> > Looks okay at first sight, except that Newz returns memory that's been
> > reset to zero bytes. You need to add the memzero I suggested previously.
> > Might as well go into malloc_using_sv since it's not performance critical.
> >
> > Um, looks like CLONE plus _clone_dbis plus dbi_bootinit already do the
> > right thing (your patch also fixes a leak on thread creation) so that's
> > okay. INIT_PERINTERP is harder to be sure about, but I think it's okay.
> >
> > I'll apply the patch and add the memzero. Thanks edan.
> >
> > Tim.
> >
> > > Thanks again,
> > > edan
> > >
> > > The patch:
> > >
> > > --- DBI.xs.orig 2006-06-30 10:20:10.0 -0400
> > > +++ DBI.xs 2006-07-02 04:59:32.0 -0400
> > > @@ -171,7 +171,7 @@
> > > dPERINTERP_SV; dPERINTERP_PTR(PERINTERP_t *, PERINTERP)
> > > # define INIT_PERINTERP \
> > > dPERINTERP; \
> > > - Newz(0,PERINTERP,1,PERINTERP_t); \
> > > +PERINTERP = malloc_using_sv(sizeof(PERINTERP_t));\
> > > sv_setiv(perinterp_sv, PTR2IV(PERINTERP))
> > >
> > > # undef DBIS
> > > @@ -209,13 +209,29 @@
> > > stc_s, sizeof(dbih_stc_t), fdc_s, sizeof(dbih_fdc_t),
> > msg);
> > > }
> > >
> > > +static void *
> > > +malloc_us
RE: memory leak in DBI XS bootstrap code
Tim,
I'm experiencing further memory leaks which are causing me more grief. I put
together the following test script which illustrates the leakage:
#/usr/bin/perl
# dbi.pl
use DBI;
use Devel::Leak;
my $leak_handle;
for ( 1 .. 1000 ) {
my $dbh = DBI->connect("DBI:mysql:MYDB","user","secret", {RaiseError=>1});
my $sth = $dbh->prepare("SELECT COUNT(*) FROM TABLE");
$sth->execute();
while ( my ($c) = $sth->fetchrow_array() ) {
# print "$c\n";
}
$sth->finish;
$dbh->disconnect;
system("ps -aux | fgrep dbi.pl | fgrep -v fgrep");
test_leak();
}
sub test_leak {
if ( $handle ) {
Devel::Leak::CheckSV($handle);
}
print STDERR "count: " . Devel::Leak::NoteSV($handle) . "\n";
}
Devel::Leak shows that it is leaking 4 "things" every iteration:
count: 12128
root 17720 17.8 0.3 6840 4220 pts/0S09:55 0:00 perl dbi.pl
new 0x8aad3e8 : SV = PVHV(0x8a68b30) at 0x8aad3e8
REFCNT = 1
FLAGS = (PADBUSY,PADMY,SHAREKEYS)
IV = 0
NV = 0
ARRAY = 0x0
KEYS = 0
FILL = 0
MAX = 7
RITER = -1
EITER = 0x0
new 0x8aad3f4 : SV = NULL(0x0) at 0x8aad3f4
REFCNT = 1
FLAGS = (PADBUSY,PADMY)
new 0x8aad400 : SV = NULL(0x0) at 0x8aad400
REFCNT = 1
FLAGS = (PADBUSY,PADMY)
new 0x8aad40c : SV = NULL(0x0) at 0x8aad40c
REFCNT = 1
FLAGS = (PADBUSY,PADMY)
count: 12132
I think there might be several leaks here. If I move the connect() out of the
loop, it's leaking, but less - just one of those NULL SV guys.
Can you please help me track this down? I'm still a little scared of the
DBI.xs internals, despite my recent exposure...
Thanks a lot,
edan
> -Original Message-
> From: Tim Bunce [mailto:[EMAIL PROTECTED]
> Sent: Monday, July 03, 2006 12:16
> To: Ephraim Dan
> Cc: Tim Bunce; dbi-users@perl.org
> Subject: Re: memory leak in DBI XS bootstrap code
>
> On Sat, Jul 01, 2006 at 10:42:58PM -0700, Ephraim Dan wrote:
> > Thanks for the help Tim! You've really done me a great service.
> >
> > I have created the following patch, which appears to fix all of the
> leaks. Can you please review it, and give it your stamp of approval? I
> am willing to use this patch if you approve it, until a new DBI is
> available with the fixes.
>
> Looks okay at first sight, except that Newz returns memory that's been
> reset to zero bytes. You need to add the memzero I suggested previously.
> Might as well go into malloc_using_sv since it's not performance critical.
>
> Um, looks like CLONE plus _clone_dbis plus dbi_bootinit already do the
> right thing (your patch also fixes a leak on thread creation) so that's
> okay. INIT_PERINTERP is harder to be sure about, but I think it's okay.
>
> I'll apply the patch and add the memzero. Thanks edan.
>
> Tim.
>
> > Thanks again,
> > edan
> >
> > The patch:
> >
> > --- DBI.xs.orig 2006-06-30 10:20:10.0 -0400
> > +++ DBI.xs 2006-07-02 04:59:32.0 -0400
> > @@ -171,7 +171,7 @@
> > dPERINTERP_SV; dPERINTERP_PTR(PERINTERP_t *, PERINTERP)
> > # define INIT_PERINTERP \
> > dPERINTERP; \
> > - Newz(0,PERINTERP,1,PERINTERP_t); \
> > +PERINTERP = malloc_using_sv(sizeof(PERINTERP_t));\
> > sv_setiv(perinterp_sv, PTR2IV(PERINTERP))
> >
> > # undef DBIS
> > @@ -209,13 +209,29 @@
> > stc_s, sizeof(dbih_stc_t), fdc_s, sizeof(dbih_fdc_t),
> msg);
> > }
> >
> > +static void *
> > +malloc_using_sv(STRLEN len)
> > +{
> > +dTHX;
> > +SV *sv = newSV(len);
> > +return SvPVX(sv);
> > +}
> > +
> > +static char *
> > +savepv_using_sv(char *str)
> > +{
> > +char *buf = malloc_using_sv(strlen(str));
> > +strcpy(buf, str);
> > +return buf;
> > +}
> > +
> > static void
> > dbi_bootinit(dbistate_t * parent_dbis)
> > {
> > dTHX;
> > INIT_PERINTERP;
> >
> > -Newz(dummy, DBIS, 1, dbistate_t);
> > +DBIS = (struct dbistate_st*)malloc_using_sv(sizeof(struct
> dbistate_st));
> >
> > /* store version and size so we can spot DBI/DBD version mismatch
> */
> > DBIS->check_version = check_version;
> > @@ -3793,7 +3809,7 @@
> > ima->minargs = (U8)SvIV(*av_fetch(av, 0, 1));
> > ima->maxargs = (U8)SvIV(*av_fetch(av, 1, 1));
> > svp = av_fetch(av, 2, 0);
> > - ima->usage_msg = savepv( (svp) ? SvPV(*svp,lna) : "");
> > + ima->u
Re: memory leak in DBI XS bootstrap code
On Sat, Jul 01, 2006 at 10:42:58PM -0700, Ephraim Dan wrote:
> Thanks for the help Tim! You've really done me a great service.
>
> I have created the following patch, which appears to fix all of the leaks.
> Can you please review it, and give it your stamp of approval? I am willing
> to use this patch if you approve it, until a new DBI is available with the
> fixes.
Looks okay at first sight, except that Newz returns memory that's been
reset to zero bytes. You need to add the memzero I suggested previously.
Might as well go into malloc_using_sv since it's not performance critical.
Um, looks like CLONE plus _clone_dbis plus dbi_bootinit already do the
right thing (your patch also fixes a leak on thread creation) so that's
okay. INIT_PERINTERP is harder to be sure about, but I think it's okay.
I'll apply the patch and add the memzero. Thanks edan.
Tim.
> Thanks again,
> edan
>
> The patch:
>
> --- DBI.xs.orig 2006-06-30 10:20:10.0 -0400
> +++ DBI.xs 2006-07-02 04:59:32.0 -0400
> @@ -171,7 +171,7 @@
> dPERINTERP_SV; dPERINTERP_PTR(PERINTERP_t *, PERINTERP)
> # define INIT_PERINTERP \
> dPERINTERP; \
> - Newz(0,PERINTERP,1,PERINTERP_t); \
> +PERINTERP = malloc_using_sv(sizeof(PERINTERP_t));\
> sv_setiv(perinterp_sv, PTR2IV(PERINTERP))
>
> # undef DBIS
> @@ -209,13 +209,29 @@
> stc_s, sizeof(dbih_stc_t), fdc_s, sizeof(dbih_fdc_t), msg);
> }
>
> +static void *
> +malloc_using_sv(STRLEN len)
> +{
> +dTHX;
> +SV *sv = newSV(len);
> +return SvPVX(sv);
> +}
> +
> +static char *
> +savepv_using_sv(char *str)
> +{
> +char *buf = malloc_using_sv(strlen(str));
> +strcpy(buf, str);
> +return buf;
> +}
> +
> static void
> dbi_bootinit(dbistate_t * parent_dbis)
> {
> dTHX;
> INIT_PERINTERP;
>
> -Newz(dummy, DBIS, 1, dbistate_t);
> +DBIS = (struct dbistate_st*)malloc_using_sv(sizeof(struct dbistate_st));
>
> /* store version and size so we can spot DBI/DBD version mismatch */
> DBIS->check_version = check_version;
> @@ -3793,7 +3809,7 @@
> ima->minargs = (U8)SvIV(*av_fetch(av, 0, 1));
> ima->maxargs = (U8)SvIV(*av_fetch(av, 1, 1));
> svp = av_fetch(av, 2, 0);
> - ima->usage_msg = savepv( (svp) ? SvPV(*svp,lna) : "");
> + ima->usage_msg = (svp) ? savepv_using_sv(SvPV(*svp, lna)) : "";
> ima->flags |= IMA_HAS_USAGE;
> if (trace_msg && DBIS_TRACE_LEVEL >= 11)
> sv_catpvf(trace_msg, ",\nusage: min %d, max %d, '%s'",
>
> > -Original Message-
> > From: Tim Bunce [mailto:[EMAIL PROTECTED]
> > Sent: Friday, June 30, 2006 16:33
> > To: Ephraim Dan
> > Cc: Tim Bunce; dbi-users@perl.org
> > Subject: Re: memory leak in DBI XS bootstrap code
> >
> > On Fri, Jun 30, 2006 at 04:08:46AM -0700, Ephraim Dan wrote:
> > >This is why I love the perl community - you're willing to help even
> > though you don't have time, since
> > >you care about your code, and that other people can benefit from it.
> > >
> > >But ... you sort of left a dangling "But" in your last post. Do you
> > have a suggestion to fix the
> > >_install_method problem?
> > >
> > >I just ran valgrind with your fix - there seems to still be a leak.
> > It is reported on DBI.xs line 216:
> > >INIT_PERINTERP;
> > >That looks to also have a Newz in it - is that what leaks?
> >
> > Yes. It's basically a plain malloc and there's no mechanism to free it
> > later.
> >
> > > Can you suggest how to fix that one?
> >
> > The trick is to use newSV to alloc memory in the form of an SV
> > so that when the interpreter is destroyed the SV memory gets freed
> > automatically.
> >
> > >The _install_method leak is reported on line 3800 (in my modified
> > DBI.xs) which validates your theory:
> > >ima->usage_msg = savepv( (svp) ? SvPV(*svp,lna) : "");
> > >If you can fix these, I'll be forever in your debt...
> >
> > As above, and as outlined previously, use an SV.
> > Something like this (completely untested):
> >
> > static void *
> > malloc_using_sv(STRLEN len) {
> >SV *sv = newSV(len);
> >return SvPVX(sv);
> > }
> >
> > static char *
> > savepv_using_
RE: memory leak in DBI XS bootstrap code
Thanks for the help Tim! You've really done me a great service.
I have created the following patch, which appears to fix all of the leaks. Can
you please review it, and give it your stamp of approval? I am willing to use
this patch if you approve it, until a new DBI is available with the fixes.
Thanks again,
edan
The patch:
--- DBI.xs.orig 2006-06-30 10:20:10.0 -0400
+++ DBI.xs 2006-07-02 04:59:32.0 -0400
@@ -171,7 +171,7 @@
dPERINTERP_SV; dPERINTERP_PTR(PERINTERP_t *, PERINTERP)
# define INIT_PERINTERP \
dPERINTERP; \
- Newz(0,PERINTERP,1,PERINTERP_t); \
+PERINTERP = malloc_using_sv(sizeof(PERINTERP_t));\
sv_setiv(perinterp_sv, PTR2IV(PERINTERP))
# undef DBIS
@@ -209,13 +209,29 @@
stc_s, sizeof(dbih_stc_t), fdc_s, sizeof(dbih_fdc_t), msg);
}
+static void *
+malloc_using_sv(STRLEN len)
+{
+dTHX;
+SV *sv = newSV(len);
+return SvPVX(sv);
+}
+
+static char *
+savepv_using_sv(char *str)
+{
+char *buf = malloc_using_sv(strlen(str));
+strcpy(buf, str);
+return buf;
+}
+
static void
dbi_bootinit(dbistate_t * parent_dbis)
{
dTHX;
INIT_PERINTERP;
-Newz(dummy, DBIS, 1, dbistate_t);
+DBIS = (struct dbistate_st*)malloc_using_sv(sizeof(struct dbistate_st));
/* store version and size so we can spot DBI/DBD version mismatch */
DBIS->check_version = check_version;
@@ -3793,7 +3809,7 @@
ima->minargs = (U8)SvIV(*av_fetch(av, 0, 1));
ima->maxargs = (U8)SvIV(*av_fetch(av, 1, 1));
svp = av_fetch(av, 2, 0);
- ima->usage_msg = savepv( (svp) ? SvPV(*svp,lna) : "");
+ ima->usage_msg = (svp) ? savepv_using_sv(SvPV(*svp, lna)) : "";
ima->flags |= IMA_HAS_USAGE;
if (trace_msg && DBIS_TRACE_LEVEL >= 11)
sv_catpvf(trace_msg, ",\nusage: min %d, max %d, '%s'",
> -Original Message-
> From: Tim Bunce [mailto:[EMAIL PROTECTED]
> Sent: Friday, June 30, 2006 16:33
> To: Ephraim Dan
> Cc: Tim Bunce; dbi-users@perl.org
> Subject: Re: memory leak in DBI XS bootstrap code
>
> On Fri, Jun 30, 2006 at 04:08:46AM -0700, Ephraim Dan wrote:
> >This is why I love the perl community - you're willing to help even
> though you don't have time, since
> >you care about your code, and that other people can benefit from it.
> >
> >But ... you sort of left a dangling "But" in your last post. Do you
> have a suggestion to fix the
> >_install_method problem?
> >
> >I just ran valgrind with your fix - there seems to still be a leak.
> It is reported on DBI.xs line 216:
> >INIT_PERINTERP;
> >That looks to also have a Newz in it - is that what leaks?
>
> Yes. It's basically a plain malloc and there's no mechanism to free it
> later.
>
> > Can you suggest how to fix that one?
>
> The trick is to use newSV to alloc memory in the form of an SV
> so that when the interpreter is destroyed the SV memory gets freed
> automatically.
>
> >The _install_method leak is reported on line 3800 (in my modified
> DBI.xs) which validates your theory:
> >ima->usage_msg = savepv( (svp) ? SvPV(*svp,lna) : "");
> >If you can fix these, I'll be forever in your debt...
>
> As above, and as outlined previously, use an SV.
> Something like this (completely untested):
>
> static void *
> malloc_using_sv(STRLEN len) {
>SV *sv = newSV(len);
>return SvPVX(sv);
> }
>
> static char *
> savepv_using_sv(char *str) {
> char *buf = malloc_using_sv(strlen(str));
> strcpy(buf, str);
> return buf;
> }
>
> Tim.
>
> >--edan
> >
> >-
> ---
> >
> >From: Tim Bunce [mailto:[EMAIL PROTECTED]
> >Sent: Fri 6/30/2006 11:35
> >To: Ephraim Dan
> >Cc: Tim Bunce; dbi-users@perl.org
> >Subject: Re: memory leak in DBI XS bootstrap code
> >
> >On Fri, Jun 30, 2006 at 12:24:33AM -0700, Ephraim Dan wrote:
> >> > > I don't see what you mean in the "INSTALL" that comes with perl
> 5.8.0 (that's what we're using).
> >> > The file called INSTALL in the perl source code directory.
> >>
> >> That I knew. What are the special instructions that I'm supposed
> to
> >> find there? Am I supposed to build it with debugging? Can you
> >> specify what special configuration exactly you meant th
Re: memory leak in DBI XS bootstrap code
On Fri, Jun 30, 2006 at 04:08:46AM -0700, Ephraim Dan wrote:
>This is why I love the perl community - you're willing to help even though
> you don't have time, since
>you care about your code, and that other people can benefit from it.
>
>But ... you sort of left a dangling "But" in your last post. Do you have
> a suggestion to fix the
>_install_method problem?
>
>I just ran valgrind with your fix - there seems to still be a leak. It is
> reported on DBI.xs line 216:
>INIT_PERINTERP;
>That looks to also have a Newz in it - is that what leaks?
Yes. It's basically a plain malloc and there's no mechanism to free it later.
> Can you suggest how to fix that one?
The trick is to use newSV to alloc memory in the form of an SV
so that when the interpreter is destroyed the SV memory gets freed
automatically.
>The _install_method leak is reported on line 3800 (in my modified DBI.xs)
> which validates your theory:
>ima->usage_msg = savepv( (svp) ? SvPV(*svp,lna) : "");
>If you can fix these, I'll be forever in your debt...
As above, and as outlined previously, use an SV.
Something like this (completely untested):
static void *
malloc_using_sv(STRLEN len) {
SV *sv = newSV(len);
return SvPVX(sv);
}
static char *
savepv_using_sv(char *str) {
char *buf = malloc_using_sv(strlen(str));
strcpy(buf, str);
return buf;
}
Tim.
>--edan
>
>
>
>
>From: Tim Bunce [mailto:[EMAIL PROTECTED]
>Sent: Fri 6/30/2006 11:35
>To: Ephraim Dan
>Cc: Tim Bunce; dbi-users@perl.org
>Subject: Re: memory leak in DBI XS bootstrap code
>
>On Fri, Jun 30, 2006 at 12:24:33AM -0700, Ephraim Dan wrote:
>> > > I don't see what you mean in the "INSTALL" that comes with perl
> 5.8.0 (that's what we're using).
>> > The file called INSTALL in the perl source code directory.
>>
>> That I knew. What are the special instructions that I'm supposed to
>> find there? Am I supposed to build it with debugging? Can you
>> specify what special configuration exactly you meant that I should use?
>
>I'm sorry. I should have checked myself first. You can build perl with
>-DPURIFY to enable more precise leak detection.
>
>> It looks like the leak in boot_DBI is on purpose:
>> /* Remember the last handle used. BEWARE! Sneaky stuff here! */
>> /* We want a handle reference but we don't want to increment */
>> /* the handle's reference count and we don't want perl to try */
>> /* to destroy it during global destruction. Take care!*/
>> DBI_UNSET_LAST_HANDLE;/* ensure setup the correct way */
>>
>> Why is this being done, and does anyone have a way to fix it? Why don't
> we want perl to destroy it?
>Me, that's exactly what I want.
>
>I don't believe that's a leak. It's just using an SV as a reference
>without telling perl its a reference. If perl knew it was a reference
>we'd get double-free warnings as the referenced value would be freed twice.
>
>The leak is probably the DBIS structure itself:
>
>Newz(dummy, DBIS, 1, dbistate_t);
>
>That should probably be using along the lines of:
>
>sv = newSV(sizeof(struct dbistate_st));
>DBIS = (struct dbistate_st*)SvPVX(sv);
>memzero(DBIS, sizeof(struct dbistate_st));
>
>So the memory is allocated within an SV so gets freed on global
> destruction.
>
>> Still looking into the other leak in _install_method... any pointers
> still appreciated... Thanks for
>your help so far, Tim.
>
>I've taken a look at the code. I'd guess that it's due to savepnv:
>
>ima->usage_msg = savepv( (svp) ? SvPV(*svp,lna) : "");
>
>But
>
>Tim.
>
>> -edan
>>
>> > > -Original Message-
>> > > From: Tim Bunce [[1]mailto:[EMAIL PROTECTED]
>> > > Sent: Thursday, June 29, 2006 14:47
>> > > To: Ephraim Dan
>> > > Cc: dbi-users@perl.org
>> > > Subject: Re: memory leak in DBI XS bootstrap code
>> > >
>> > > Try building perl with options to make valgrind leak tracing more
>> > > effective (see perl's INSTALL file). That may help you pinpoint
>> > > the problem.
>
RE: memory leak in DBI XS bootstrap code
This is why I love the perl community - you're willing to help even though you don't have time, since you care about your code, and that other people can benefit from it. But ... you sort of left a dangling "But" in your last post. Do you have a suggestion to fix the _install_method problem? I just ran valgrind with your fix - there seems to still be a leak. It is reported on DBI.xs line 216: INIT_PERINTERP; That looks to also have a Newz in it - is that what leaks? Can you suggest how to fix that one? The _install_method leak is reported on line 3800 (in my modified DBI.xs) which validates your theory: ima->usage_msg = savepv( (svp) ? SvPV(*svp,lna) : ""); If you can fix these, I'll be forever in your debt... --edan From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Fri 6/30/2006 11:35 To: Ephraim Dan Cc: Tim Bunce; dbi-users@perl.org Subject: Re: memory leak in DBI XS bootstrap code On Fri, Jun 30, 2006 at 12:24:33AM -0700, Ephraim Dan wrote: > > > I don't see what you mean in the "INSTALL" that comes with perl 5.8.0 > > > (that's what we're using). > > The file called INSTALL in the perl source code directory. > > That I knew. What are the special instructions that I'm supposed to > find there? Am I supposed to build it with debugging? Can you > specify what special configuration exactly you meant that I should use? I'm sorry. I should have checked myself first. You can build perl with -DPURIFY to enable more precise leak detection. > It looks like the leak in boot_DBI is on purpose: > /* Remember the last handle used. BEWARE! Sneaky stuff here! */ > /* We want a handle reference but we don't want to increment */ > /* the handle's reference count and we don't want perl to try */ > /* to destroy it during global destruction. Take care!*/ > DBI_UNSET_LAST_HANDLE;/* ensure setup the correct way */ > > Why is this being done, and does anyone have a way to fix it? Why don't we > want perl to destroy it? Me, that's exactly what I want. I don't believe that's a leak. It's just using an SV as a reference without telling perl its a reference. If perl knew it was a reference we'd get double-free warnings as the referenced value would be freed twice. The leak is probably the DBIS structure itself: Newz(dummy, DBIS, 1, dbistate_t); That should probably be using along the lines of: sv = newSV(sizeof(struct dbistate_st)); DBIS = (struct dbistate_st*)SvPVX(sv); memzero(DBIS, sizeof(struct dbistate_st)); So the memory is allocated within an SV so gets freed on global destruction. > Still looking into the other leak in _install_method... any pointers still > appreciated... Thanks for your help so far, Tim. I've taken a look at the code. I'd guess that it's due to savepnv: ima->usage_msg = savepv( (svp) ? SvPV(*svp,lna) : ""); But Tim. > -edan > > > > -Original Message- > > > From: Tim Bunce [mailto:[EMAIL PROTECTED] > > > Sent: Thursday, June 29, 2006 14:47 > > > To: Ephraim Dan > > > Cc: dbi-users@perl.org > > > Subject: Re: memory leak in DBI XS bootstrap code > > > > > > Try building perl with options to make valgrind leak tracing more > > > effective (see perl's INSTALL file). That may help you pinpoint > > > the problem. > > > > > > Tim. > > > > > > On Thu, Jun 29, 2006 at 04:33:40AM -0700, Ephraim Dan wrote: > > > > I am experiencing what I believe to be a memory leak in the DBI > > > bootstrap code. This is a problem for me because I am embedding perl in a > > > long-running program, and DBI is being loaded over and over, so my program > > > grows and grows. > > > > > > > > The problem appears to be in the following routines: > > > > > > > > boot_DBI (in /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread- > > > multi/auto/DBI/DBI.so) > > > > XS_DBI__install_method (in /usr/lib/perl5/vendor_perl/5.8.0/i386-linux- > > > thread-multi/auto/DBI/DBI.so) > > > > > > > > I am using DBI 1.51 > > > > > > > > The tool "valgrind" (http://valgrind.org <http://valgrind.org/> > > > > <http://valgrind.org/> ) can be used to reproduce the > > > leak using the following code: > > > > > > > > --- > > > > File: embed_test.c > > > > --- > > > > > > > > #include/* from the Perl distribution */ > > > > #incl
Re: memory leak in DBI XS bootstrap code
On Fri, Jun 30, 2006 at 12:24:33AM -0700, Ephraim Dan wrote:
> > > I don't see what you mean in the "INSTALL" that comes with perl 5.8.0
> > > (that's what we're using).
> > The file called INSTALL in the perl source code directory.
>
> That I knew. What are the special instructions that I'm supposed to
> find there? Am I supposed to build it with debugging? Can you
> specify what special configuration exactly you meant that I should use?
I'm sorry. I should have checked myself first. You can build perl with
-DPURIFY to enable more precise leak detection.
> It looks like the leak in boot_DBI is on purpose:
> /* Remember the last handle used. BEWARE! Sneaky stuff here! */
> /* We want a handle reference but we don't want to increment */
> /* the handle's reference count and we don't want perl to try */
> /* to destroy it during global destruction. Take care!*/
> DBI_UNSET_LAST_HANDLE;/* ensure setup the correct way */
>
> Why is this being done, and does anyone have a way to fix it? Why don't we
> want perl to destroy it? Me, that's exactly what I want.
I don't believe that's a leak. It's just using an SV as a reference
without telling perl its a reference. If perl knew it was a reference
we'd get double-free warnings as the referenced value would be freed twice.
The leak is probably the DBIS structure itself:
Newz(dummy, DBIS, 1, dbistate_t);
That should probably be using along the lines of:
sv = newSV(sizeof(struct dbistate_st));
DBIS = (struct dbistate_st*)SvPVX(sv);
memzero(DBIS, sizeof(struct dbistate_st));
So the memory is allocated within an SV so gets freed on global destruction.
> Still looking into the other leak in _install_method... any pointers still
> appreciated... Thanks for your help so far, Tim.
I've taken a look at the code. I'd guess that it's due to savepnv:
ima->usage_msg = savepv( (svp) ? SvPV(*svp,lna) : "");
But
Tim.
> -edan
>
> > > -----Original Message-
> > > From: Tim Bunce [mailto:[EMAIL PROTECTED]
> > > Sent: Thursday, June 29, 2006 14:47
> > > To: Ephraim Dan
> > > Cc: dbi-users@perl.org
> > > Subject: Re: memory leak in DBI XS bootstrap code
> > >
> > > Try building perl with options to make valgrind leak tracing more
> > > effective (see perl's INSTALL file). That may help you pinpoint
> > > the problem.
> > >
> > > Tim.
> > >
> > > On Thu, Jun 29, 2006 at 04:33:40AM -0700, Ephraim Dan wrote:
> > > > I am experiencing what I believe to be a memory leak in the DBI
> > > bootstrap code. This is a problem for me because I am embedding perl in a
> > > long-running program, and DBI is being loaded over and over, so my program
> > > grows and grows.
> > > >
> > > > The problem appears to be in the following routines:
> > > >
> > > > boot_DBI (in /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-
> > > multi/auto/DBI/DBI.so)
> > > > XS_DBI__install_method (in /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-
> > > thread-multi/auto/DBI/DBI.so)
> > > >
> > > > I am using DBI 1.51
> > > >
> > > > The tool "valgrind" (http://valgrind.org <http://valgrind.org/> ) can
> > > > be used to reproduce the
> > > leak using the following code:
> > > >
> > > > ---
> > > > File: embed_test.c
> > > > ---
> > > >
> > > > #include/* from the Perl distribution */
> > > > #include /* from the Perl distribution */
> > > >
> > > > static PerlInterpreter *my_perl; /***The Perl interpreter***/
> > > > EXTERN_C void xs_init (pTHX); /***init dyn. loading ***/
> > > >
> > > > int main(int argc, char **argv, char **env)
> > > > {
> > > > char *embedding[] = { "", "-e", "0" };
> > > > my_perl = perl_alloc();
> > > > perl_construct(my_perl);
> > > > perl_parse(my_perl, xs_init, 3, embedding, (char **)NULL);
> > > > PL_exit_flags |= PERL_EXIT_DESTRUCT_END;
> > > > perl_run(my_perl);
> > > > eval_pv("use DBI", TRUE);
> > > > perl_destruct(my_perl);
> > > > perl_free(my_perl);
> > > > }
> > > >
> > > > ---
> > > > File: Makefile
> > > > ---
> > > >
> > > > CC_OPTS = $(shell perl -MExtUtils::Embed -e ccopts)
> > > > LD_OPTS = $(shell perl -MExtUtils::Embed -e ldopts)
> > > >
> > > > EXE = embed_test
> > > >
> > > > $(EXE): xsinit.o embed_test.o
> > > > gcc -o $(EXE) embed_test.o xsinit.o $(LD_OPTS)
> > > >
> > > > embed_test.o: embed_test.c
> > > > gcc -c embed_test.c $(CC_OPTS)
> > > >
> > > > xsinit.o: xsinit.c
> > > > gcc -c xsinit.c $(CC_OPTS)
> > > >
> > > > xsinit.c:
> > > > perl -MExtUtils::Embed -e xsinit -- -o xsinit.c
> > > >
> > > > clean:
> > > > rm -f *.o xsinit.c $(EXE)
> > > >
> > > > ---
> > > > EOF
> > > > ---
> > > >
> > > > Can anyone suggest a fix for this? I'd be more than willing to take a
> > > patch to DBI 1.51 as soon as someone has one.
> > > >
> > > > Thanks,
> > > > Ephraim Dan
> > > >
>
RE: memory leak in DBI XS bootstrap code
> > I don't see what you mean in the "INSTALL" that comes with perl 5.8.0
> > (that's what we're using).
> The file called INSTALL in the perl source code directory.
That I knew. What are the special instructions that I'm supposed to find
there? Am I supposed to build it with debugging? Can you specify what special
configuration exactly you meant that I should use?
> I've no time, sorry. You'll have to scratch this itch yourself (or hope that
> someone else has the same itch.)
It looks like the leak in boot_DBI is on purpose:
/* Remember the last handle used. BEWARE! Sneaky stuff here!*/
/* We want a handle reference but we don't want to increment*/
/* the handle's reference count and we don't want perl to try */
/* to destroy it during global destruction. Take care! */
DBI_UNSET_LAST_HANDLE; /* ensure setup the correct way */
Why is this being done, and does anyone have a way to fix it? Why don't we
want perl to destroy it? Me, that's exactly what I want.
Still looking into the other leak in _install_method... any pointers still
appreciated... Thanks for your help so far, Tim.
-edan
> > -Original Message-
> > From: Tim Bunce [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, June 29, 2006 14:47
> > To: Ephraim Dan
> > Cc: dbi-users@perl.org
> > Subject: Re: memory leak in DBI XS bootstrap code
> >
> > Try building perl with options to make valgrind leak tracing more
> > effective (see perl's INSTALL file). That may help you pinpoint
> > the problem.
> >
> > Tim.
> >
> > On Thu, Jun 29, 2006 at 04:33:40AM -0700, Ephraim Dan wrote:
> > > I am experiencing what I believe to be a memory leak in the DBI
> > bootstrap code. This is a problem for me because I am embedding perl in a
> > long-running program, and DBI is being loaded over and over, so my program
> > grows and grows.
> > >
> > > The problem appears to be in the following routines:
> > >
> > > boot_DBI (in /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-
> > multi/auto/DBI/DBI.so)
> > > XS_DBI__install_method (in /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-
> > thread-multi/auto/DBI/DBI.so)
> > >
> > > I am using DBI 1.51
> > >
> > > The tool "valgrind" (http://valgrind.org <http://valgrind.org/> ) can be
> > > used to reproduce the
> > leak using the following code:
> > >
> > > ---
> > > File: embed_test.c
> > > ---
> > >
> > > #include/* from the Perl distribution */
> > > #include /* from the Perl distribution */
> > >
> > > static PerlInterpreter *my_perl; /***The Perl interpreter***/
> > > EXTERN_C void xs_init (pTHX); /***init dyn. loading ***/
> > >
> > > int main(int argc, char **argv, char **env)
> > > {
> > > char *embedding[] = { "", "-e", "0" };
> > > my_perl = perl_alloc();
> > > perl_construct(my_perl);
> > > perl_parse(my_perl, xs_init, 3, embedding, (char **)NULL);
> > > PL_exit_flags |= PERL_EXIT_DESTRUCT_END;
> > > perl_run(my_perl);
> > > eval_pv("use DBI", TRUE);
> > > perl_destruct(my_perl);
> > > perl_free(my_perl);
> > > }
> > >
> > > ---
> > > File: Makefile
> > > ---
> > >
> > > CC_OPTS = $(shell perl -MExtUtils::Embed -e ccopts)
> > > LD_OPTS = $(shell perl -MExtUtils::Embed -e ldopts)
> > >
> > > EXE = embed_test
> > >
> > > $(EXE): xsinit.o embed_test.o
> > > gcc -o $(EXE) embed_test.o xsinit.o $(LD_OPTS)
> > >
> > > embed_test.o: embed_test.c
> > > gcc -c embed_test.c $(CC_OPTS)
> > >
> > > xsinit.o: xsinit.c
> > > gcc -c xsinit.c $(CC_OPTS)
> > >
> > > xsinit.c:
> > > perl -MExtUtils::Embed -e xsinit -- -o xsinit.c
> > >
> > > clean:
> > > rm -f *.o xsinit.c $(EXE)
> > >
> > > ---
> > > EOF
> > > ---
> > >
> > > Can anyone suggest a fix for this? I'd be more than willing to take a
> > patch to DBI 1.51 as soon as someone has one.
> > >
> > > Thanks,
> > > Ephraim Dan
> > >
Re: memory leak in DBI XS bootstrap code
On Thu, Jun 29, 2006 at 06:52:56AM -0700, Ephraim Dan wrote:
> I don't see what you mean in the "INSTALL" that comes with perl 5.8.0 (that's
> what we're using).
The file called INSTALL in the perl source code directory.
Note that perl 5.8.0 is very old now and many significant bugs have been
fixed in later 5.8.x versions.
> Can you be more specific? Would I need to build perl in a special way, or
> DBI, or both?
Both. Configure the new perl with a new install directory, install it,
add that dir to your PATH then build a new DBI and 'make install' it
into that new perl directory.
> I was sort of hoping someone more familiar with the code might be able to
> pinpoint it (of course, this is always preferable to doing it myself).
I've no time, sorry. You'll have to scratch this itch yourself
(or hope that someone else has the same itch.)
Tim.
> Any further pointers are appreciated...
>
> -edan
>
> > -Original Message-
> > From: Tim Bunce [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, June 29, 2006 14:47
> > To: Ephraim Dan
> > Cc: dbi-users@perl.org
> > Subject: Re: memory leak in DBI XS bootstrap code
> >
> > Try building perl with options to make valgrind leak tracing more
> > effective (see perl's INSTALL file). That may help you pinpoint
> > the problem.
> >
> > Tim.
> >
> > On Thu, Jun 29, 2006 at 04:33:40AM -0700, Ephraim Dan wrote:
> > > I am experiencing what I believe to be a memory leak in the DBI
> > bootstrap code. This is a problem for me because I am embedding perl in a
> > long-running program, and DBI is being loaded over and over, so my program
> > grows and grows.
> > >
> > > The problem appears to be in the following routines:
> > >
> > > boot_DBI (in /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-
> > multi/auto/DBI/DBI.so)
> > > XS_DBI__install_method (in /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-
> > thread-multi/auto/DBI/DBI.so)
> > >
> > > I am using DBI 1.51
> > >
> > > The tool "valgrind" (http://valgrind.org) can be used to reproduce the
> > leak using the following code:
> > >
> > > ---
> > > File: embed_test.c
> > > ---
> > >
> > > #include/* from the Perl distribution */
> > > #include /* from the Perl distribution */
> > >
> > > static PerlInterpreter *my_perl; /***The Perl interpreter***/
> > > EXTERN_C void xs_init (pTHX); /***init dyn. loading ***/
> > >
> > > int main(int argc, char **argv, char **env)
> > > {
> > > char *embedding[] = { "", "-e", "0" };
> > > my_perl = perl_alloc();
> > > perl_construct(my_perl);
> > > perl_parse(my_perl, xs_init, 3, embedding, (char **)NULL);
> > > PL_exit_flags |= PERL_EXIT_DESTRUCT_END;
> > > perl_run(my_perl);
> > > eval_pv("use DBI", TRUE);
> > > perl_destruct(my_perl);
> > > perl_free(my_perl);
> > > }
> > >
> > > ---
> > > File: Makefile
> > > ---
> > >
> > > CC_OPTS = $(shell perl -MExtUtils::Embed -e ccopts)
> > > LD_OPTS = $(shell perl -MExtUtils::Embed -e ldopts)
> > >
> > > EXE = embed_test
> > >
> > > $(EXE): xsinit.o embed_test.o
> > > gcc -o $(EXE) embed_test.o xsinit.o $(LD_OPTS)
> > >
> > > embed_test.o: embed_test.c
> > > gcc -c embed_test.c $(CC_OPTS)
> > >
> > > xsinit.o: xsinit.c
> > > gcc -c xsinit.c $(CC_OPTS)
> > >
> > > xsinit.c:
> > > perl -MExtUtils::Embed -e xsinit -- -o xsinit.c
> > >
> > > clean:
> > > rm -f *.o xsinit.c $(EXE)
> > >
> > > ---
> > > EOF
> > > ---
> > >
> > > Can anyone suggest a fix for this? I'd be more than willing to take a
> > patch to DBI 1.51 as soon as someone has one.
> > >
> > > Thanks,
> > > Ephraim Dan
> > >
RE: memory leak in DBI XS bootstrap code
I don't see what you mean in the "INSTALL" that comes with perl 5.8.0 (that's
what we're using). Can you be more specific? Would I need to build perl in a
special way, or DBI, or both?
I was sort of hoping someone more familiar with the code might be able to
pinpoint it (of course, this is always preferable to doing it myself).
Any further pointers are appreciated...
-edan
> -Original Message-
> From: Tim Bunce [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 29, 2006 14:47
> To: Ephraim Dan
> Cc: dbi-users@perl.org
> Subject: Re: memory leak in DBI XS bootstrap code
>
> Try building perl with options to make valgrind leak tracing more
> effective (see perl's INSTALL file). That may help you pinpoint
> the problem.
>
> Tim.
>
> On Thu, Jun 29, 2006 at 04:33:40AM -0700, Ephraim Dan wrote:
> > I am experiencing what I believe to be a memory leak in the DBI
> bootstrap code. This is a problem for me because I am embedding perl in a
> long-running program, and DBI is being loaded over and over, so my program
> grows and grows.
> >
> > The problem appears to be in the following routines:
> >
> > boot_DBI (in /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-
> multi/auto/DBI/DBI.so)
> > XS_DBI__install_method (in /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-
> thread-multi/auto/DBI/DBI.so)
> >
> > I am using DBI 1.51
> >
> > The tool "valgrind" (http://valgrind.org) can be used to reproduce the
> leak using the following code:
> >
> > ---
> > File: embed_test.c
> > ---
> >
> > #include/* from the Perl distribution */
> > #include /* from the Perl distribution */
> >
> > static PerlInterpreter *my_perl; /***The Perl interpreter***/
> > EXTERN_C void xs_init (pTHX); /***init dyn. loading ***/
> >
> > int main(int argc, char **argv, char **env)
> > {
> > char *embedding[] = { "", "-e", "0" };
> > my_perl = perl_alloc();
> > perl_construct(my_perl);
> > perl_parse(my_perl, xs_init, 3, embedding, (char **)NULL);
> > PL_exit_flags |= PERL_EXIT_DESTRUCT_END;
> > perl_run(my_perl);
> > eval_pv("use DBI", TRUE);
> > perl_destruct(my_perl);
> > perl_free(my_perl);
> > }
> >
> > ---
> > File: Makefile
> > ---
> >
> > CC_OPTS = $(shell perl -MExtUtils::Embed -e ccopts)
> > LD_OPTS = $(shell perl -MExtUtils::Embed -e ldopts)
> >
> > EXE = embed_test
> >
> > $(EXE): xsinit.o embed_test.o
> > gcc -o $(EXE) embed_test.o xsinit.o $(LD_OPTS)
> >
> > embed_test.o: embed_test.c
> > gcc -c embed_test.c $(CC_OPTS)
> >
> > xsinit.o: xsinit.c
> > gcc -c xsinit.c $(CC_OPTS)
> >
> > xsinit.c:
> > perl -MExtUtils::Embed -e xsinit -- -o xsinit.c
> >
> > clean:
> > rm -f *.o xsinit.c $(EXE)
> >
> > ---
> > EOF
> > ---
> >
> > Can anyone suggest a fix for this? I'd be more than willing to take a
> patch to DBI 1.51 as soon as someone has one.
> >
> > Thanks,
> > Ephraim Dan
> >
Re: memory leak in DBI XS bootstrap code
Try building perl with options to make valgrind leak tracing more
effective (see perl's INSTALL file). That may help you pinpoint
the problem.
Tim.
On Thu, Jun 29, 2006 at 04:33:40AM -0700, Ephraim Dan wrote:
> I am experiencing what I believe to be a memory leak in the DBI bootstrap
> code. This is a problem for me because I am embedding perl in a long-running
> program, and DBI is being loaded over and over, so my program grows and grows.
>
> The problem appears to be in the following routines:
>
> boot_DBI (in
> /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi/auto/DBI/DBI.so)
> XS_DBI__install_method (in
> /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi/auto/DBI/DBI.so)
>
> I am using DBI 1.51
>
> The tool "valgrind" (http://valgrind.org) can be used to reproduce the leak
> using the following code:
>
> ---
> File: embed_test.c
> ---
>
> #include/* from the Perl distribution */
> #include /* from the Perl distribution */
>
> static PerlInterpreter *my_perl; /***The Perl interpreter***/
> EXTERN_C void xs_init (pTHX); /***init dyn. loading ***/
>
> int main(int argc, char **argv, char **env)
> {
> char *embedding[] = { "", "-e", "0" };
> my_perl = perl_alloc();
> perl_construct(my_perl);
> perl_parse(my_perl, xs_init, 3, embedding, (char **)NULL);
> PL_exit_flags |= PERL_EXIT_DESTRUCT_END;
> perl_run(my_perl);
> eval_pv("use DBI", TRUE);
> perl_destruct(my_perl);
> perl_free(my_perl);
> }
>
> ---
> File: Makefile
> ---
>
> CC_OPTS = $(shell perl -MExtUtils::Embed -e ccopts)
> LD_OPTS = $(shell perl -MExtUtils::Embed -e ldopts)
>
> EXE = embed_test
>
> $(EXE): xsinit.o embed_test.o
> gcc -o $(EXE) embed_test.o xsinit.o $(LD_OPTS)
>
> embed_test.o: embed_test.c
> gcc -c embed_test.c $(CC_OPTS)
>
> xsinit.o: xsinit.c
> gcc -c xsinit.c $(CC_OPTS)
>
> xsinit.c:
> perl -MExtUtils::Embed -e xsinit -- -o xsinit.c
>
> clean:
> rm -f *.o xsinit.c $(EXE)
>
> ---
> EOF
> ---
>
> Can anyone suggest a fix for this? I'd be more than willing to take a patch
> to DBI 1.51 as soon as someone has one.
>
> Thanks,
> Ephraim Dan
>
memory leak in DBI XS bootstrap code
I am experiencing what I believe to be a memory leak in the DBI bootstrap code.
This is a problem for me because I am embedding perl in a long-running
program, and DBI is being loaded over and over, so my program grows and grows.
The problem appears to be in the following routines:
boot_DBI (in
/usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi/auto/DBI/DBI.so)
XS_DBI__install_method (in
/usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi/auto/DBI/DBI.so)
I am using DBI 1.51
The tool "valgrind" (http://valgrind.org) can be used to reproduce the leak
using the following code:
---
File: embed_test.c
---
#include/* from the Perl distribution */
#include /* from the Perl distribution */
static PerlInterpreter *my_perl; /***The Perl interpreter***/
EXTERN_C void xs_init (pTHX); /***init dyn. loading ***/
int main(int argc, char **argv, char **env)
{
char *embedding[] = { "", "-e", "0" };
my_perl = perl_alloc();
perl_construct(my_perl);
perl_parse(my_perl, xs_init, 3, embedding, (char **)NULL);
PL_exit_flags |= PERL_EXIT_DESTRUCT_END;
perl_run(my_perl);
eval_pv("use DBI", TRUE);
perl_destruct(my_perl);
perl_free(my_perl);
}
---
File: Makefile
---
CC_OPTS = $(shell perl -MExtUtils::Embed -e ccopts)
LD_OPTS = $(shell perl -MExtUtils::Embed -e ldopts)
EXE = embed_test
$(EXE): xsinit.o embed_test.o
gcc -o $(EXE) embed_test.o xsinit.o $(LD_OPTS)
embed_test.o: embed_test.c
gcc -c embed_test.c $(CC_OPTS)
xsinit.o: xsinit.c
gcc -c xsinit.c $(CC_OPTS)
xsinit.c:
perl -MExtUtils::Embed -e xsinit -- -o xsinit.c
clean:
rm -f *.o xsinit.c $(EXE)
---
EOF
---
Can anyone suggest a fix for this? I'd be more than willing to take a patch to
DBI 1.51 as soon as someone has one.
Thanks,
Ephraim Dan
Re: Possible memory leak using $sth->{NAME} ?
At this point I'm pretty sure it's a bug in DBD::ODBC, so I'll let
Jeff Urlwin pick up from here.
Tim.
On Wed, Jun 07, 2006 at 11:52:15AM -0700, David Brewer wrote:
> I'm still not able to replicate on mysql. However, I have created a
> test script (included below) which, given access to an empty mssql
> database, should do everything necessary to demonstrate the behavior.
>
> Please let me know if there is any further information I could provide
> or tests I could create that might be helpful.
>
> Thanks,
>
> David Brewer
>
> ###
> # mssql_leak.t
> #
> # Script for demonstrating substantial memory leak with DBI, DBD::ODBC, and
> # a MSSQL 2000 database.
> #
> # To use this script you must have DBI 1.50, DBD::ODBC 1.13, and owner
> # access to an MSSQL database. A table called 'leak_test' will be created
> # and destroyed in the database. See the CONFIGURATION section below to set
> # up the desired connection.
> #
> # In order to reproduce the leak, you must meet four criteria:
> #
> # 1) In your query, you must create a table variable.
> # 2) You must insert at least one row into the table variable. After that,
> #you can completely ignore the table variable!
> # 3) You must select from a table that contains 'text' columns. The more
> #text columns you select, the larger the leak. Note that you don't
> #actually have to select any rows to cause the leak!
> # 4) After executing the query, you must do something which triggers the
> #lookup of column information. I've found that either $sth->{NAME} or
> #$sth->fetchrow_hashref() will cause the leak, for instance.
> #
> # In addition, the 'LongReadLen' property of the database handle is somehow
> # related because the higher this number, the greater the leak.
> #
> # Once you've verified that the leak is occuring using the code below, you
> # can try commenting out various lines to see how it affects things.
> # The two clearest examples are commenting out the insertion into the
> # table variable, and commenting out the $sth->{NAME} line.
> #
> # I wasn't able to figure out how to get at the memory usage of perl
> # programmatically on Windows, so rather than writing tests to test the
> # memory usage, I just made a loop that demonstrates the leak. It pauses
> # after the first iteration and then again at the end so you can observe the
> # memory used by perl using the task manager or 'Process Explorer' from
> # sysinternals.com.
> #
> # Author: David Brewer, Second Story Interactive
> # Date: June 7, 2006
> ###
>
> use strict;
> use warnings;
>
> ###
> # CONFIGURATION
> ###
>
> # Database connection
> my $dsn = "DBI:ODBC:driver={SQL Server};server=localhost;database=test;";
> my $user = "test";
> my $pass = "test";
>
> # number of times to perform the leaky query in the loop
> my $leak_iterations = 1200;
>
> # seconds to pause at beginning and end of loop to permit memory observation
> my $pause = 7;
>
> # LongReadLen value to use on the database handle; this is somehow related
> # to the leak because the greater this value, the greater the leak.
> my $LongReadLen = 2;
>
>
> ###
> # SETUP
> ###
>
> use Test::More tests => 8;
>
> # Verify we have the correct versions of the modules
> BEGIN {
>use_ok( 'DBI 1.50' );
>use_ok( 'DBD::ODBC 1.13' );
> }
>
> # Prepare the database by creating the test table. Drop it first if it
> # already exists.
>
> my $create_sql = q{
>IF OBJECT_ID('leak_test','U')IS NOT NULL DROP TABLE leak_test;
>CREATE TABLE [dbo].[leak_test] (
>IDint NOT NULL,
>LongText1 text,
>LongText2 text,
>LongText3 text,
>LongText4 text,
>LongText5 text,
>PRIMARY KEY (ID)
>)
> };
>
> my $dbh = DBI->connect($dsn, $user, $pass);
> isa_ok($dbh, 'DBI::db');
> ok($dbh->do($create_sql), 'Created leak_test table');
> ok($dbh->disconnect, 'Disconnected from database');
>
>
> #
Re: Possible memory leak using $sth->{NAME} ?
I'm still not able to replicate on mysql. However, I have created a
test script (included below) which, given access to an empty mssql
database, should do everything necessary to demonstrate the behavior.
Please let me know if there is any further information I could provide
or tests I could create that might be helpful.
Thanks,
David Brewer
###
# mssql_leak.t
#
# Script for demonstrating substantial memory leak with DBI, DBD::ODBC, and
# a MSSQL 2000 database.
#
# To use this script you must have DBI 1.50, DBD::ODBC 1.13, and owner
# access to an MSSQL database. A table called 'leak_test' will be created
# and destroyed in the database. See the CONFIGURATION section below to set
# up the desired connection.
#
# In order to reproduce the leak, you must meet four criteria:
#
# 1) In your query, you must create a table variable.
# 2) You must insert at least one row into the table variable. After that,
#you can completely ignore the table variable!
# 3) You must select from a table that contains 'text' columns. The more
#text columns you select, the larger the leak. Note that you don't
#actually have to select any rows to cause the leak!
# 4) After executing the query, you must do something which triggers the
#lookup of column information. I've found that either $sth->{NAME} or
#$sth->fetchrow_hashref() will cause the leak, for instance.
#
# In addition, the 'LongReadLen' property of the database handle is somehow
# related because the higher this number, the greater the leak.
#
# Once you've verified that the leak is occuring using the code below, you
# can try commenting out various lines to see how it affects things.
# The two clearest examples are commenting out the insertion into the
# table variable, and commenting out the $sth->{NAME} line.
#
# I wasn't able to figure out how to get at the memory usage of perl
# programmatically on Windows, so rather than writing tests to test the
# memory usage, I just made a loop that demonstrates the leak. It pauses
# after the first iteration and then again at the end so you can observe the
# memory used by perl using the task manager or 'Process Explorer' from
# sysinternals.com.
#
# Author: David Brewer, Second Story Interactive
# Date: June 7, 2006
###
use strict;
use warnings;
###
# CONFIGURATION
###
# Database connection
my $dsn = "DBI:ODBC:driver={SQL Server};server=localhost;database=test;";
my $user = "test";
my $pass = "test";
# number of times to perform the leaky query in the loop
my $leak_iterations = 1200;
# seconds to pause at beginning and end of loop to permit memory observation
my $pause = 7;
# LongReadLen value to use on the database handle; this is somehow related
# to the leak because the greater this value, the greater the leak.
my $LongReadLen = 2;
###
# SETUP
###
use Test::More tests => 8;
# Verify we have the correct versions of the modules
BEGIN {
use_ok( 'DBI 1.50' );
use_ok( 'DBD::ODBC 1.13' );
}
# Prepare the database by creating the test table. Drop it first if it
# already exists.
my $create_sql = q{
IF OBJECT_ID('leak_test','U')IS NOT NULL DROP TABLE leak_test;
CREATE TABLE [dbo].[leak_test] (
IDint NOT NULL,
LongText1 text,
LongText2 text,
LongText3 text,
LongText4 text,
LongText5 text,
PRIMARY KEY (ID)
)
};
my $dbh = DBI->connect($dsn, $user, $pass);
isa_ok($dbh, 'DBI::db');
ok($dbh->do($create_sql), 'Created leak_test table');
ok($dbh->disconnect, 'Disconnected from database');
###
# THE TEST
###
my $leaky_sql = q{
DECLARE @table_variable TABLE (
TestInteger int DEFAULT(0)
);
-- Commenting the line below prevents the leak!
INSERT INTO @table_variable (TestInteger) VALUES (1);
SELECT TOP 0
*
FROM
leak_test;
};
my $iterations = 1200;
print(qq{
We will run the leaky query $leak_iterations times, pausing $pause seconds
after the first run so you can observe the memory usage.
});
$dbh = DBI->connect($dsn, $user, $pass);
$dbh->{LongReadLen} = $LongReadLen;
for my $i (1..$leak_iterations) {
# print a dot for each run through the query
print
Re: Possible memory leak using $sth->{NAME} ?
I have not yet been able to replicate in mysql but I spent some time
paring down the query to the minimum version that causes the memory
leak. The results are interesting and I think may point toward
DBD::ODBC being the issue. I don't understand the internals well
enough to be sure of that, though.
The basic idea behind the original query was to do a complicated
search, store the resulting distinct ids into a table variable called
@search_hits, and then use that table joined to some other tables to
select the final results. I cut out everything that I could remove
while still seeing the leak. Here is the simplest query I could come
up with that exhibits the behavior:
DECLARE @search_hits TABLE (
ObjectID int DEFAULT(0)
);
INSERT INTO @search_hits (ObjectID) VALUES (1);
SELECT TOP 0
*
FROM
Objects;
Basically, it has to have the following properties to exhibit the large leak:
1) You must create a table variable.
2) You must insert at least one row into the table variable.
3) You must select from a table that contains "text" columns. The
more columns you select, especially those that contain long text, the
greater the leak. Note that you don't have to select ANY rows in
order to cause the leak!
4) After executing the query, you must do something which triggers the
lookup of column information. I've found that either $sth->{NAME} or
$sth->fetchrow_hashref() will cause the behavior.
If any of these conditions is not met in the query, the leak does not
happen. I'm guessing this means it will not be reproducable in mysql,
as if I recall correctly mysql doesn't have an equivalent to a table
variable.
Do DBI drivers typically store information about a query aside from
information about the actual result of the query? My original
assumption was that if you did a complicated query, it was only the
results of the query that would make a difference to the memory usage
of perl. It surprised me to find that creating a table variable,
doing an insertion, and then ignoring that table could have an effect
on memory usage in the perl process.
I've included the entire script for reproducing the problem below,
this time including the SQL. Thanks again!
use strict;
use warnings;
use DBI;
my $dsn = qq{DBI:ODBC:driver={SQL
Server};Server=SERVERNAME;database=DBNAME;uid=DBUSER;pwd=PASSWORD;};
# 'Objects' must contain several columns of type 'text' to get
# a substantial leak
my $sql = q{
DECLARE @search_hits TABLE (
ObjectID int DEFAULT(0)
);
INSERT INTO @search_hits (ObjectID) VALUES (1);
SELECT TOP 0
*
FROM
Objects;
};
for my $i (1..50) {
print "Executing iteration $i... \n";
my $dbh = DBI->connect($dsn);
$dbh->{LongReadLen} = 2;
my $sth = $dbh->prepare($sql);
$sth->execute();
$sth->{NAME};
$sth->finish;
$dbh->disconnect();
undef($dbh);
}
On 6/6/06, David Brewer <[EMAIL PROTECTED]> wrote:
OK, I will attempt to replicate in mysql and get back to you. In the
meantime, I have run my test script in some older versions of DBI to
see if that helps anything. I tried 1.49, 1.48, 1.47, 1.45, 1.41, and
1.38. I saw pretty the same behavior in each version.
Thanks,
David
On 6/6/06, Tim Bunce <[EMAIL PROTECTED]> wrote:
> Can you get DBD::mysql to leak in the same way ($sth->{NAME})?
> I need to be able to duplicate the problem and I can't do that
> easily with ODBC or ADO.
>
> Tim.
>
> On Tue, Jun 06, 2006 at 12:14:36PM -0700, David Brewer wrote:
> > I've tried this with a couple of different drivers now -- DBD::ADO and
> > DBD::mysql. In both cases the same simple connect and disconnect
> > routine seems to leak 1 SV per execution.
> >
> > The ADO example is exactly the same as the previous example, except I
> > substituted "DBI:ADO" for "DBI:ODBC". The MySQL example is a little
> > different and is included below as 'Example 1'.
> >
> > I'm actually not too concerned about that particular leak -- it seems
> > to be a fairly insignificant amount of memory. It's just the simplest
> > example of a leak I could construct and I was hoping that it might be
> > a symptom of some mistake that I might be making. The problem that
> > led me to it is a much larger leak that occurs when try to get the
> > column names from a very complicated query (either directly using
> > $sth->{NAME} or indirectly by calling $sth->fetchrow_hashref).
> >
> > In the case of one query I am using it seems to be leaking about 144k
> > of memory per trip
Re: Possible memory leak using $sth->{NAME} ?
one does a similar
> >leak test but repeats it 50 times. leak_test reports 50 SV leaked, so
> >it seems like it's consistent.
> >
> >I will start trying some different drivers and report back my results
> >shortly. Thanks for your response!
> >
> >David
> >
> >###
> >
> >use strict;
> >use warnings;
> >use DBI;
> >
> >my $dsn = qq{DBI:ODBC:driver={SQL
> >Server};Server=SERVERNAME;database=DBNAME;uid=DBUSER;pwd=PASSWORD;};
> >my $options = { RaiseError => 1 } ;
> >
> >use Apache::Leak;
> >leak_test {
> >for (1..50) {
> >my $dbd = DBI->connect($dsn, $options);
> >$dbd->disconnect();
> >undef($dbd);
> >}
> >};
> >
> >###
> >
> >On 6/6/06, Tim Bunce <[EMAIL PROTECTED]> wrote:
> >> On Mon, Jun 05, 2006 at 07:51:22PM -0700, David Brewer wrote:
> >> > OK, I have pared my problem down to a small test script and I've cut
> >> > away a lot of the modules I'm using that don't seem to be part of the
> >> > issue. The test script is included at the end of this message.
> >>
> >> Thanks.
> >>
> >> > This small script doesn't leak much memory, but it's surprising to me
> >> > that it leaks at all. Essentially, I just connect to a database and
> >> > then disconnect from it, and Apache::Leak reports that this process
> >> > leaks 1 SV. If I add a simple query then Apache::Leak reports I leak
> >4 SVs.
> >>
> >> 'leaks' from one-off calls are rarely real leaks, they're often just
> >> internal caching of one kind or another.
> >>
> >> Real leaks leak in proportion to the number of calls made. I'd expect
> >> you to be able to say something like "each call to foo leaks N scalars"
> >> (because 100 calls leak X and 101 calls leak X+N).
> >>
> >> Can you check for that? And can you also try a different driver or two?
> >>
> >> Tim.
> >>
> >> > I am using DBI 1.50 with DBD::ODBC 1.13. This is on a Windows XP
> >> > machine, using ActivePerl 5.8.8 (I was using 5.8.7 previously with the
> >> > same issue). I am talking to a MSSQL Server 2000 database.
> >> >
> >> > ##
> >> > use strict;
> >> > use warnings;
> >> > use DBI;
> >> >
> >> > my $dsn = qq{DBI:ODBC:driver={SQL
> >> > Server};Server=SERVERNAME;database=DBNAME;uid=DBUSER;pwd=PASSWORD;};
> >> > my $options = { RaiseError => 1 } ;
> >> >
> >> > use Apache::Leak;
> >> > leak_test {
> >> >my $dbd = DBI->connect($dsn, $options);
> >> >$dbd->disconnect();
> >> > };
> >> > ##
> >> >
> >> > On 6/2/06, David Brewer <[EMAIL PROTECTED]> wrote:
> >> > >I am having what appears to a memory leak problem on a mod_perl
> >> > >project I am working on. On the worst case web page (a search results
> >> > >page) I am leaking an average of about 160k per page load! I think
> >> > >I've finally isolated the problem and it appears to be related to DBI.
> >> > > It's very possible that I am doing something wrong to cause the
> >> > >problem. :-)
> >> > >
> >> > >First of all, some version and module information. I am using DBI
> >> > >1.50 with DBD::ODBC 1.13. I am using persistent database connections
> >> > >via the Apache::DBI module. The project is using mod_perl and
> >> > >Apache::ASP.
> >> > >
> >> > >I isolated the problem by commenting out great swaths of code until
> >> > >the problem went away, and then slowly adding them back in until it
> >> > >reappeared. My first thought was that it had something to do with
> >> > >fetchrow_hashref. I have a loop like this:
> >> > >
> >> > >while ($row = $sth->fetchrow_hashref) {
> >> > ># do stuff here
> >> > >}
> >> > >
> >> > >All of the functionality inside the loop has been commented, but my
> >> > >memory leak still happens. However, if I comment the loop entirely,
> >> > >the leak goes away (well, about 158k of it at least!).
> >> > >
> >> > >If I replace the loop with something like:
> >> > >
> >> > >while ($row = $sth->fetchrow_arrayref) {
> >> > ># do stuff here
> >> > >}
> >> > >
> >> > >... no leak. I need to get at some of the column names, though, so I
> >> > >added this line before the loop:
> >> > >
> >> > >my $column_names = $sth->{NAME};
> >> > >
> >> > >... and the leak was back! It stays even if I don't save the column
> >> > >names into a variable, but just touch them:
> >> > >
> >> > >$sth->{NAME};
> >> > >
> >> > >In fact, it even stays if I remove the loop entirely and just include
> >> > >the line above! Any ideas why this might be happening and what I
> >> > >could do to fix it or work around it? Is there possibly something I'm
> >> > >doing wrong here?
> >> > >
> >> > >My next step is going to be to try to make some kind of simple test
> >> > >outside of the framework of my web probject that reproduces the same
> >> > >behavior. I'll post that here when I have it.
> >> > >
> >> > >Thanks in advance for any insight,
> >> > >
> >> > >David Brewer
> >> > >
> >>
> >
>
>
Re: Possible memory leak using $sth->{NAME} ?
#
> >
> >use strict;
> >use warnings;
> >use DBI;
> >
> >my $dsn = qq{DBI:ODBC:driver={SQL
> >Server};Server=SERVERNAME;database=DBNAME;uid=DBUSER;pwd=PASSWORD;};
> >my $options = { RaiseError => 1 } ;
> >
> >use Apache::Leak;
> >leak_test {
> >for (1..50) {
> >my $dbd = DBI->connect($dsn, $options);
> >$dbd->disconnect();
> >undef($dbd);
> >}
> >};
> >
> >###
> >
> >On 6/6/06, Tim Bunce <[EMAIL PROTECTED]> wrote:
> >> On Mon, Jun 05, 2006 at 07:51:22PM -0700, David Brewer wrote:
> >> > OK, I have pared my problem down to a small test script and I've cut
> >> > away a lot of the modules I'm using that don't seem to be part of the
> >> > issue. The test script is included at the end of this message.
> >>
> >> Thanks.
> >>
> >> > This small script doesn't leak much memory, but it's surprising to me
> >> > that it leaks at all. Essentially, I just connect to a database and
> >> > then disconnect from it, and Apache::Leak reports that this process
> >> > leaks 1 SV. If I add a simple query then Apache::Leak reports I leak
> >4 SVs.
> >>
> >> 'leaks' from one-off calls are rarely real leaks, they're often just
> >> internal caching of one kind or another.
> >>
> >> Real leaks leak in proportion to the number of calls made. I'd expect
> >> you to be able to say something like "each call to foo leaks N scalars"
> >> (because 100 calls leak X and 101 calls leak X+N).
> >>
> >> Can you check for that? And can you also try a different driver or two?
> >>
> >> Tim.
> >>
> >> > I am using DBI 1.50 with DBD::ODBC 1.13. This is on a Windows XP
> >> > machine, using ActivePerl 5.8.8 (I was using 5.8.7 previously with the
> >> > same issue). I am talking to a MSSQL Server 2000 database.
> >> >
> >> > ##
> >> > use strict;
> >> > use warnings;
> >> > use DBI;
> >> >
> >> > my $dsn = qq{DBI:ODBC:driver={SQL
> >> > Server};Server=SERVERNAME;database=DBNAME;uid=DBUSER;pwd=PASSWORD;};
> >> > my $options = { RaiseError => 1 } ;
> >> >
> >> > use Apache::Leak;
> >> > leak_test {
> >> >my $dbd = DBI->connect($dsn, $options);
> >> >$dbd->disconnect();
> >> > };
> >> > ##
> >> >
> >> > On 6/2/06, David Brewer <[EMAIL PROTECTED]> wrote:
> >> > >I am having what appears to a memory leak problem on a mod_perl
> >> > >project I am working on. On the worst case web page (a search results
> >> > >page) I am leaking an average of about 160k per page load! I think
> >> > >I've finally isolated the problem and it appears to be related to DBI.
> >> > > It's very possible that I am doing something wrong to cause the
> >> > >problem. :-)
> >> > >
> >> > >First of all, some version and module information. I am using DBI
> >> > >1.50 with DBD::ODBC 1.13. I am using persistent database connections
> >> > >via the Apache::DBI module. The project is using mod_perl and
> >> > >Apache::ASP.
> >> > >
> >> > >I isolated the problem by commenting out great swaths of code until
> >> > >the problem went away, and then slowly adding them back in until it
> >> > >reappeared. My first thought was that it had something to do with
> >> > >fetchrow_hashref. I have a loop like this:
> >> > >
> >> > >while ($row = $sth->fetchrow_hashref) {
> >> > ># do stuff here
> >> > >}
> >> > >
> >> > >All of the functionality inside the loop has been commented, but my
> >> > >memory leak still happens. However, if I comment the loop entirely,
> >> > >the leak goes away (well, about 158k of it at least!).
> >> > >
> >> > >If I replace the loop with something like:
> >> > >
> >> > >while ($row = $sth->fetchrow_arrayref) {
> >> > ># do stuff here
> >> > >}
> >> > >
> >> > >... no leak. I need to get at some of the column names, though, so I
> >> > >added this line before the loop:
> >> > >
> >> > >my $column_names = $sth->{NAME};
> >> > >
> >> > >... and the leak was back! It stays even if I don't save the column
> >> > >names into a variable, but just touch them:
> >> > >
> >> > >$sth->{NAME};
> >> > >
> >> > >In fact, it even stays if I remove the loop entirely and just include
> >> > >the line above! Any ideas why this might be happening and what I
> >> > >could do to fix it or work around it? Is there possibly something I'm
> >> > >doing wrong here?
> >> > >
> >> > >My next step is going to be to try to make some kind of simple test
> >> > >outside of the framework of my web probject that reproduces the same
> >> > >behavior. I'll post that here when I have it.
> >> > >
> >> > >Thanks in advance for any insight,
> >> > >
> >> > >David Brewer
> >> > >
> >>
> >
>
>
Re: Possible memory leak using $sth->{NAME} ?
;s surprising to me
> > that it leaks at all. Essentially, I just connect to a database and
> > then disconnect from it, and Apache::Leak reports that this process
> > leaks 1 SV. If I add a simple query then Apache::Leak reports I leak 4 SVs.
>
> 'leaks' from one-off calls are rarely real leaks, they're often just
> internal caching of one kind or another.
>
> Real leaks leak in proportion to the number of calls made. I'd expect
> you to be able to say something like "each call to foo leaks N scalars"
> (because 100 calls leak X and 101 calls leak X+N).
>
> Can you check for that? And can you also try a different driver or two?
>
> Tim.
>
> > I am using DBI 1.50 with DBD::ODBC 1.13. This is on a Windows XP
> > machine, using ActivePerl 5.8.8 (I was using 5.8.7 previously with the
> > same issue). I am talking to a MSSQL Server 2000 database.
> >
> > ##
> > use strict;
> > use warnings;
> > use DBI;
> >
> > my $dsn = qq{DBI:ODBC:driver={SQL
> > Server};Server=SERVERNAME;database=DBNAME;uid=DBUSER;pwd=PASSWORD;};
> > my $options = { RaiseError => 1 } ;
> >
> > use Apache::Leak;
> > leak_test {
> >my $dbd = DBI->connect($dsn, $options);
> >$dbd->disconnect();
> > };
> > ##
> >
> > On 6/2/06, David Brewer <[EMAIL PROTECTED]> wrote:
> > >I am having what appears to a memory leak problem on a mod_perl
> > >project I am working on. On the worst case web page (a search results
> > >page) I am leaking an average of about 160k per page load! I think
> > >I've finally isolated the problem and it appears to be related to DBI.
> > > It's very possible that I am doing something wrong to cause the
> > >problem. :-)
> > >
> > >First of all, some version and module information. I am using DBI
> > >1.50 with DBD::ODBC 1.13. I am using persistent database connections
> > >via the Apache::DBI module. The project is using mod_perl and
> > >Apache::ASP.
> > >
> > >I isolated the problem by commenting out great swaths of code until
> > >the problem went away, and then slowly adding them back in until it
> > >reappeared. My first thought was that it had something to do with
> > >fetchrow_hashref. I have a loop like this:
> > >
> > >while ($row = $sth->fetchrow_hashref) {
> > ># do stuff here
> > >}
> > >
> > >All of the functionality inside the loop has been commented, but my
> > >memory leak still happens. However, if I comment the loop entirely,
> > >the leak goes away (well, about 158k of it at least!).
> > >
> > >If I replace the loop with something like:
> > >
> > >while ($row = $sth->fetchrow_arrayref) {
> > ># do stuff here
> > >}
> > >
> > >... no leak. I need to get at some of the column names, though, so I
> > >added this line before the loop:
> > >
> > >my $column_names = $sth->{NAME};
> > >
> > >... and the leak was back! It stays even if I don't save the column
> > >names into a variable, but just touch them:
> > >
> > >$sth->{NAME};
> > >
> > >In fact, it even stays if I remove the loop entirely and just include
> > >the line above! Any ideas why this might be happening and what I
> > >could do to fix it or work around it? Is there possibly something I'm
> > >doing wrong here?
> > >
> > >My next step is going to be to try to make some kind of simple test
> > >outside of the framework of my web probject that reproduces the same
> > >behavior. I'll post that here when I have it.
> > >
> > >Thanks in advance for any insight,
> > >
> > >David Brewer
> > >
>
Re: Possible memory leak using $sth->{NAME} ?
Sure, I'd be glad to check into that.
I think Apache::Leak already runs the code twice -- first it runs the
code to make sure that anything that would get cached is already in
memory. Then it measures the memory usage, runs the code again, and
measures the memory usage a final time to determine how much was
leaked.
I made a new script which you can see below. This one does a similar
leak test but repeats it 50 times. leak_test reports 50 SV leaked, so
it seems like it's consistent.
I will start trying some different drivers and report back my results
shortly. Thanks for your response!
David
###
use strict;
use warnings;
use DBI;
my $dsn = qq{DBI:ODBC:driver={SQL
Server};Server=SERVERNAME;database=DBNAME;uid=DBUSER;pwd=PASSWORD;};
my $options = { RaiseError => 1 } ;
use Apache::Leak;
leak_test {
for (1..50) {
my $dbd = DBI->connect($dsn, $options);
$dbd->disconnect();
undef($dbd);
}
};
###
On 6/6/06, Tim Bunce <[EMAIL PROTECTED]> wrote:
On Mon, Jun 05, 2006 at 07:51:22PM -0700, David Brewer wrote:
> OK, I have pared my problem down to a small test script and I've cut
> away a lot of the modules I'm using that don't seem to be part of the
> issue. The test script is included at the end of this message.
Thanks.
> This small script doesn't leak much memory, but it's surprising to me
> that it leaks at all. Essentially, I just connect to a database and
> then disconnect from it, and Apache::Leak reports that this process
> leaks 1 SV. If I add a simple query then Apache::Leak reports I leak 4 SVs.
'leaks' from one-off calls are rarely real leaks, they're often just
internal caching of one kind or another.
Real leaks leak in proportion to the number of calls made. I'd expect
you to be able to say something like "each call to foo leaks N scalars"
(because 100 calls leak X and 101 calls leak X+N).
Can you check for that? And can you also try a different driver or two?
Tim.
> I am using DBI 1.50 with DBD::ODBC 1.13. This is on a Windows XP
> machine, using ActivePerl 5.8.8 (I was using 5.8.7 previously with the
> same issue). I am talking to a MSSQL Server 2000 database.
>
> ##
> use strict;
> use warnings;
> use DBI;
>
> my $dsn = qq{DBI:ODBC:driver={SQL
> Server};Server=SERVERNAME;database=DBNAME;uid=DBUSER;pwd=PASSWORD;};
> my $options = { RaiseError => 1 } ;
>
> use Apache::Leak;
> leak_test {
>my $dbd = DBI->connect($dsn, $options);
> $dbd->disconnect();
> };
> ##
>
> On 6/2/06, David Brewer <[EMAIL PROTECTED]> wrote:
> >I am having what appears to a memory leak problem on a mod_perl
> >project I am working on. On the worst case web page (a search results
> >page) I am leaking an average of about 160k per page load! I think
> >I've finally isolated the problem and it appears to be related to DBI.
> > It's very possible that I am doing something wrong to cause the
> >problem. :-)
> >
> >First of all, some version and module information. I am using DBI
> >1.50 with DBD::ODBC 1.13. I am using persistent database connections
> >via the Apache::DBI module. The project is using mod_perl and
> >Apache::ASP.
> >
> >I isolated the problem by commenting out great swaths of code until
> >the problem went away, and then slowly adding them back in until it
> >reappeared. My first thought was that it had something to do with
> >fetchrow_hashref. I have a loop like this:
> >
> >while ($row = $sth->fetchrow_hashref) {
> ># do stuff here
> >}
> >
> >All of the functionality inside the loop has been commented, but my
> >memory leak still happens. However, if I comment the loop entirely,
> >the leak goes away (well, about 158k of it at least!).
> >
> >If I replace the loop with something like:
> >
> >while ($row = $sth->fetchrow_arrayref) {
> ># do stuff here
> >}
> >
> >... no leak. I need to get at some of the column names, though, so I
> >added this line before the loop:
> >
> >my $column_names = $sth->{NAME};
> >
> >... and the leak was back! It stays even if I don't save the column
> >names into a variable, but just touch them:
> >
> >$sth->{NAME};
> >
> >In fact, it even stays if I remove the loop entirely and just include
> >the line above! Any ideas why this might be happening and what I
> >could do to fix it or work around it? Is there possibly something I'm
> >doing wrong here?
> >
> >My next step is going to be to try to make some kind of simple test
> >outside of the framework of my web probject that reproduces the same
> >behavior. I'll post that here when I have it.
> >
> >Thanks in advance for any insight,
> >
> >David Brewer
> >
Re: Possible memory leak using $sth->{NAME} ?
On Mon, Jun 05, 2006 at 07:51:22PM -0700, David Brewer wrote:
> OK, I have pared my problem down to a small test script and I've cut
> away a lot of the modules I'm using that don't seem to be part of the
> issue. The test script is included at the end of this message.
Thanks.
> This small script doesn't leak much memory, but it's surprising to me
> that it leaks at all. Essentially, I just connect to a database and
> then disconnect from it, and Apache::Leak reports that this process
> leaks 1 SV. If I add a simple query then Apache::Leak reports I leak 4 SVs.
'leaks' from one-off calls are rarely real leaks, they're often just
internal caching of one kind or another.
Real leaks leak in proportion to the number of calls made. I'd expect
you to be able to say something like "each call to foo leaks N scalars"
(because 100 calls leak X and 101 calls leak X+N).
Can you check for that? And can you also try a different driver or two?
Tim.
> I am using DBI 1.50 with DBD::ODBC 1.13. This is on a Windows XP
> machine, using ActivePerl 5.8.8 (I was using 5.8.7 previously with the
> same issue). I am talking to a MSSQL Server 2000 database.
>
> ##
> use strict;
> use warnings;
> use DBI;
>
> my $dsn = qq{DBI:ODBC:driver={SQL
> Server};Server=SERVERNAME;database=DBNAME;uid=DBUSER;pwd=PASSWORD;};
> my $options = { RaiseError => 1 } ;
>
> use Apache::Leak;
> leak_test {
>my $dbd = DBI->connect($dsn, $options);
>$dbd->disconnect();
> };
> ##
>
> On 6/2/06, David Brewer <[EMAIL PROTECTED]> wrote:
> >I am having what appears to a memory leak problem on a mod_perl
> >project I am working on. On the worst case web page (a search results
> >page) I am leaking an average of about 160k per page load! I think
> >I've finally isolated the problem and it appears to be related to DBI.
> > It's very possible that I am doing something wrong to cause the
> >problem. :-)
> >
> >First of all, some version and module information. I am using DBI
> >1.50 with DBD::ODBC 1.13. I am using persistent database connections
> >via the Apache::DBI module. The project is using mod_perl and
> >Apache::ASP.
> >
> >I isolated the problem by commenting out great swaths of code until
> >the problem went away, and then slowly adding them back in until it
> >reappeared. My first thought was that it had something to do with
> >fetchrow_hashref. I have a loop like this:
> >
> >while ($row = $sth->fetchrow_hashref) {
> ># do stuff here
> >}
> >
> >All of the functionality inside the loop has been commented, but my
> >memory leak still happens. However, if I comment the loop entirely,
> >the leak goes away (well, about 158k of it at least!).
> >
> >If I replace the loop with something like:
> >
> >while ($row = $sth->fetchrow_arrayref) {
> ># do stuff here
> >}
> >
> >... no leak. I need to get at some of the column names, though, so I
> >added this line before the loop:
> >
> >my $column_names = $sth->{NAME};
> >
> >... and the leak was back! It stays even if I don't save the column
> >names into a variable, but just touch them:
> >
> >$sth->{NAME};
> >
> >In fact, it even stays if I remove the loop entirely and just include
> >the line above! Any ideas why this might be happening and what I
> >could do to fix it or work around it? Is there possibly something I'm
> >doing wrong here?
> >
> >My next step is going to be to try to make some kind of simple test
> >outside of the framework of my web probject that reproduces the same
> >behavior. I'll post that here when I have it.
> >
> >Thanks in advance for any insight,
> >
> >David Brewer
> >
Re: Possible memory leak using $sth->{NAME} ?
OK, I have pared my problem down to a small test script and I've cut
away a lot of the modules I'm using that don't seem to be part of the
issue. The test script is included at the end of this message.
This small script doesn't leak much memory, but it's surprising to me
that it leaks at all. Essentially, I just connect to a database and
then disconnect from it, and Apache::Leak reports that this process
leaks 1 SV. If I add a simple query then Apache::Leak reports I leak
4 SVs.
I am using DBI 1.50 with DBD::ODBC 1.13. This is on a Windows XP
machine, using ActivePerl 5.8.8 (I was using 5.8.7 previously with the
same issue). I am talking to a MSSQL Server 2000 database.
##
use strict;
use warnings;
use DBI;
my $dsn = qq{DBI:ODBC:driver={SQL
Server};Server=SERVERNAME;database=DBNAME;uid=DBUSER;pwd=PASSWORD;};
my $options = { RaiseError => 1 } ;
use Apache::Leak;
leak_test {
my $dbd = DBI->connect($dsn, $options);
$dbd->disconnect();
};
##
On 6/2/06, David Brewer <[EMAIL PROTECTED]> wrote:
I am having what appears to a memory leak problem on a mod_perl
project I am working on. On the worst case web page (a search results
page) I am leaking an average of about 160k per page load! I think
I've finally isolated the problem and it appears to be related to DBI.
It's very possible that I am doing something wrong to cause the
problem. :-)
First of all, some version and module information. I am using DBI
1.50 with DBD::ODBC 1.13. I am using persistent database connections
via the Apache::DBI module. The project is using mod_perl and
Apache::ASP.
I isolated the problem by commenting out great swaths of code until
the problem went away, and then slowly adding them back in until it
reappeared. My first thought was that it had something to do with
fetchrow_hashref. I have a loop like this:
while ($row = $sth->fetchrow_hashref) {
# do stuff here
}
All of the functionality inside the loop has been commented, but my
memory leak still happens. However, if I comment the loop entirely,
the leak goes away (well, about 158k of it at least!).
If I replace the loop with something like:
while ($row = $sth->fetchrow_arrayref) {
# do stuff here
}
... no leak. I need to get at some of the column names, though, so I
added this line before the loop:
my $column_names = $sth->{NAME};
... and the leak was back! It stays even if I don't save the column
names into a variable, but just touch them:
$sth->{NAME};
In fact, it even stays if I remove the loop entirely and just include
the line above! Any ideas why this might be happening and what I
could do to fix it or work around it? Is there possibly something I'm
doing wrong here?
My next step is going to be to try to make some kind of simple test
outside of the framework of my web probject that reproduces the same
behavior. I'll post that here when I have it.
Thanks in advance for any insight,
David Brewer
Possible memory leak using $sth->{NAME} ?
I am having what appears to a memory leak problem on a mod_perl
project I am working on. On the worst case web page (a search results
page) I am leaking an average of about 160k per page load! I think
I've finally isolated the problem and it appears to be related to DBI.
It's very possible that I am doing something wrong to cause the
problem. :-)
First of all, some version and module information. I am using DBI
1.50 with DBD::ODBC 1.13. I am using persistent database connections
via the Apache::DBI module. The project is using mod_perl and
Apache::ASP.
I isolated the problem by commenting out great swaths of code until
the problem went away, and then slowly adding them back in until it
reappeared. My first thought was that it had something to do with
fetchrow_hashref. I have a loop like this:
while ($row = $sth->fetchrow_hashref) {
# do stuff here
}
All of the functionality inside the loop has been commented, but my
memory leak still happens. However, if I comment the loop entirely,
the leak goes away (well, about 158k of it at least!).
If I replace the loop with something like:
while ($row = $sth->fetchrow_arrayref) {
# do stuff here
}
... no leak. I need to get at some of the column names, though, so I
added this line before the loop:
my $column_names = $sth->{NAME};
... and the leak was back! It stays even if I don't save the column
names into a variable, but just touch them:
$sth->{NAME};
In fact, it even stays if I remove the loop entirely and just include
the line above! Any ideas why this might be happening and what I
could do to fix it or work around it? Is there possibly something I'm
doing wrong here?
My next step is going to be to try to make some kind of simple test
outside of the framework of my web probject that reproduces the same
behavior. I'll post that here when I have it.
Thanks in advance for any insight,
David Brewer
DBD::Oracle w/HP-UX memory leak
OS: HP-UX 11i Oracle: 9.2.0.1.0 I've followed some directions to build the DBD::Oracle driver against HP's depot install of Perl 5.8.2. The make, make test, and make install went fine. However, when I execute a prepared statement multiple times using DBD::Oracle, my perl process size grows quickly, eventually dying with a "Out of Memory!". The problem goes away if I implement the build from source, specifying some different threading flags, as described on the HP-UX doc on the DBD::Oracle page. BUT! management wants to leverage the support contract we have with HP, where HP will support the Perl install (and they won't support a built-from-source install). (I'm aware that no vendor will support DBD::Oracle, or any downloaded modules for that matter, but they will support core Perl) Any one successfully create a non-memory-leaky DBD::Oracle against an HP provided install of Perl? Thanks! John Watson
Re: Parallel::ForkManager && DBI memory leak ?
Ron Wingfield [RW], on Wednesday, December 29, 2004 at 10:29 (-0600) wrote the following: RW> I know my comments do not provide an immediate solution, but RW> this could be a very interesting thread. Infact, I'm going to be RW> writing some IPC intensive code in the near future, and I want to RW> interface via Perl and web browsers. ok, so what's the point ? just do it :) RW> First, are you running on an MS/Windows or Unix server? Fork Hm, my pitty is that, now everything I am coding on windows, but in real life it will runs on Free BSD (like you have, really nice OS!). With windows I have really bad feeling with perl. I did some scripts: network programming and IPC are horrible. First of all, it doesn't give me good timeout via alarm, and as I wrote in previous message - it leaks memory, but under unix systems everything runs just ok! ...and I spend meny hours to cheat out on windoze. Hate them in this point. RW> is a Unix OS feature and I'm not sure that fork( ) is implemented RW> effectively for Windows (. . .granted that I don't know very much RW> about Windoze). Without going into too much detail before we know windows emulate fork(). -- ...m8s, cu l8r, Brano. [I am Uhura of Borg: Assimilating frequencies open, sir.] -=x=- Skontrolované antivírovým programom NOD32
Re: Parallel::ForkManager && DBI memory leak ?
Follett, Robert wrote: You will get much better performance by connecting once, outside your foreach loop as well as disconnecting and finishing after the loop. Having tried this, I can assure you that connecting *inside* the loop is the only alternative here. In fact, for every loop iteration, Parallel::ForkManager starts up a new process (`$pm->start()') that *must* take care of (re)connecting to database, because AFAIK DBI handles will not stay shared among different processes. -- Cosimo
Re: Parallel::ForkManager && DBI memory leak ?
I know my comments do not provide an immediate solution, but this could be a
very interesting thread. Infact, I'm going to be writing some IPC intensive
code in the near future, and I want to interface via Perl and web browsers.
First, are you running on an MS/Windows or Unix server? Fork is a Unix OS
feature and I'm not sure that fork( ) is implemented effectively for Windows (.
. .granted that I don't know very much about Windoze). Without going into too
much detail before we know what server OS, etc. you're running, I'll suggest
that there are three types of forks in unix-world:
parent process child process
===
blocking running
killedrunning
running running
1st.case -- The parent spawns the child and waits until the child is through
before continuing.
2nd. case -- The parent dies and the child carries on,
3rd. case -- Two active process.
This third case is my favorite -- I've often commented that unix is the only
programming environment where parents can fork and spawn daemon children (. .
.I don't want to hear any criticism -- this is perfectly acceptable terminology
in the culture). But seriously folks, my point is that the exec, fork, ,
wait, signal, etc., system calls in Unix are not an easy read. How these
concepts are accomodated in MS/Windows, . . .I haven't a clue.
Steven Holzner comments, "In Unix, you can use the Perl function fork to start
a new process and communicate with it after seting up a pipe with the pipe
function. In fact, you can use the open function to do the same. You can also
send signals between process and handle the signals that are sent to you. . .
. In MS-DOS, the situation is different; fork and other IPC (interprocess
communication) functions are not implemented; however, when running under
Windows, the MS-DOS port of Perl can use OLE automation -- also called using
code components and code clients -- to send data between processes." (p.
602"Perl black Book", CoriolisOpen Press, 1999.)
A good read on interprocess communication for unix can be found in Marc
Rochkind's book, "Advanced Unix Programming", Prentice-Hall Software Series,
1985. The books may be about twenty years old, but keep in mind that Rochkind
is/was a protege of Dennis Ritchie, Ken Thompson, Brian Kernighan, and others,
i.e., the inventors of Unix. (Rochkind also wrote the original make system.)
I found Rochkind's book invaluable when (some years ago) I designed and coded a
real-time polling system for monitoring traffic from central station burglar
and fire alarm receivers. Some engineers will argue that Unix is not a
real-time system -- certainly not like industrial PLC motion control systems;
regardless, I once worked with a former NASA guy who commented, "Is it really
real-time when you send a signal to the Voyager Space Craft and it responds two
hours later?".
OTTF,
Ron Wingfield
FreeBSD 4.8 -- Apache http 2.0.28 -- MySQL client/server 4.1.7
Perl 5.8.5 -- p5-DBD-mysql-2.9004 driver -- p5-DBI-1.46
- Original Message -
From: Ing. Branislav Gerzo
To: dbi-users@perl.org
Sent: Thursday, December 30, 2004 2:36 AM
Subject: Parallel::ForkManager && DBI memory leak ?
Hi pals,
this is my first post to this mailing list. I have one problem, it is
maybe for you small, but for me a big problem. If I want improve my
scripts I _have_ to use parallel DB access. When I looked around I
come to Parallel::ForkManager module, easy to use.
When I wrote script, I saw it is leaking memory, so I think it is DBI
related problem, you will help me for sure, I hope, here is isolated
problem:
# this is just example script
use strict;
use warnings;
use Parallel::ForkManager;
use DBI;
my @links = (1 .. 1000);
my $pm = new Parallel::ForkManager(5);
foreach my $x (0..$#links) {
$pm->start and next;
my $dbh =
DBI->connect("DBI:mysql:database=test;host=localhost;port=3306", "root", "")
or die "Can't connect: ", $DBI::errstr;
$dbh->{RaiseError} = 1;
print "$links[$x]\n";
# do something (read, update, insert from db)
$dbh->do("INSERT INTO TEST (field) VALUES ($x)");
$dbh->disconnect;
$pm->finish;
}
on 100th iteration it uses around 20 MB of memory, on the 200th
iteration it is 30 MB!
I tried to destruct $dbh in many ways (undef for example), but nothing
helps. Could someone give me answer for this ?
And if this is not possible via ForkManager exist some simple script
with forking DBI?
--
--. ,-- ,- ICQ: 7552083 \|||/`//EB: www.2ge.us
,--' | - |--IRC: [2ge](. .),\\SN: 2ge!2ge_us
`+==+=+===~ ~=-o00-(_)-00o-~
Growing old makes one miss a lot of things, but poor memory helps!OJW
RE: Parallel::ForkManager && DBI memory leak ?
You will get much better performance by connecting once, outside your
foreach loop as well as disconnecting and finishing after the loop.
Also read up on prepare_cached using bind params. You will want to
prepare outside your foreach as well. The only thing you need to do
inside your loop is assign the param and execute. This should help
performance tremendously.
> When I wrote script, I saw it is leaking memory, so I think it is DBI
> related problem, you will help me for sure, I hope, here is isolated
> problem:
And it is not leaking without the ForkManager?
> foreach my $x (0..$#links) {
> $pm->start and next;
> my $dbh =
> DBI->connect("DBI:mysql:database=test;host=localhost;port=3306",
> "root", "")
> or die "Can't connect: ", $DBI::errstr;
> $dbh->{RaiseError} = 1;
> print "$links[$x]\n";
> # do something (read, update, insert from db)
> $dbh->do("INSERT INTO TEST (field) VALUES ($x)");
> $dbh->disconnect;
> $pm->finish;
> }
In your real application, are you also
- opening a new DB connection for every loop iteration
- and not using prepared statements
as in your example?
That could have a big performance impact.
Thilo
Re: Parallel::ForkManager && DBI memory leak ?
Moosmann, James [MJ], on Wednesday, December 29, 2004 at 07:07 (-0500) thoughtfully wrote the following: MJ> I am really disappointed with Perl's lack of support for any reliable. MJ> multithreaded behavior. I keep hoping it gets fixed. But I have waited for MJ> 2 years. ahm, I wrote some posts with Thilo Planz and forget to send it to mailing list. Is this server correctly setup ? I thought default reply adress should be mailing-adress of server...ok, anyway: - windows 2003 - This is perl, v5.8.6 built for MSWin32-x86-multi-thread (with 3 registered patches, see perl -V for more detail) - DBI v1.46 - Parallel::ForkManager v0.7.5 under unix systems it worked without any memory leak ? -- ...m8s, cu l8r, Brano. ["I'm very sorry but I ran like a fire hydrant." Michael Curtiz]
Re: Parallel::ForkManager && DBI memory leak ?
Hello,
this is my first post to this mailing list. I have one problem, it is
maybe for you small, but for me a big problem. If I want improve my
scripts I _have_ to use parallel DB access. When I looked around I
come to Parallel::ForkManager module, easy to use.
I do not know Parallel::ForkManager at all, but why can you not just
run the complete script
in several parallel processes ?
Not sure if anyone here knows ForkManager, maybe you should ask the
Perl Monks in addition to this list.
They know everything.
http://perlmonks.org/
When I wrote script, I saw it is leaking memory, so I think it is DBI
related problem, you will help me for sure, I hope, here is isolated
problem:
And it is not leaking without the ForkManager?
foreach my $x (0..$#links) {
$pm->start and next;
my $dbh =
DBI->connect("DBI:mysql:database=test;host=localhost;port=3306",
"root", "")
or die "Can't connect: ", $DBI::errstr;
$dbh->{RaiseError} = 1;
print "$links[$x]\n";
# do something (read, update, insert from db)
$dbh->do("INSERT INTO TEST (field) VALUES ($x)");
$dbh->disconnect;
$pm->finish;
}
In your real application, are you also
- opening a new DB connection for every loop iteration
- and not using prepared statements
as in your example?
That could have a big performance impact.
Thilo
Parallel::ForkManager && DBI memory leak ?
Hi pals,
this is my first post to this mailing list. I have one problem, it is
maybe for you small, but for me a big problem. If I want improve my
scripts I _have_ to use parallel DB access. When I looked around I
come to Parallel::ForkManager module, easy to use.
When I wrote script, I saw it is leaking memory, so I think it is DBI
related problem, you will help me for sure, I hope, here is isolated
problem:
# this is just example script
use strict;
use warnings;
use Parallel::ForkManager;
use DBI;
my @links = (1 .. 1000);
my $pm = new Parallel::ForkManager(5);
foreach my $x (0..$#links) {
$pm->start and next;
my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost;port=3306",
"root", "")
or die "Can't connect: ", $DBI::errstr;
$dbh->{RaiseError} = 1;
print "$links[$x]\n";
# do something (read, update, insert from db)
$dbh->do("INSERT INTO TEST (field) VALUES ($x)");
$dbh->disconnect;
$pm->finish;
}
on 100th iteration it uses around 20 MB of memory, on the 200th
iteration it is 30 MB!
I tried to destruct $dbh in many ways (undef for example), but nothing
helps. Could someone give me answer for this ?
And if this is not possible via ForkManager exist some simple script
with forking DBI?
--
--. ,-- ,- ICQ: 7552083 \|||/`//EB: www.2ge.us
,--' | - |--IRC: [2ge](. .),\\SN: 2ge!2ge_us
`+==+=+===~ ~=-o00-(_)-00o-~
Growing old makes one miss a lot of things, but poor memory helps!OJW
Re: DBD::Oracle memory leak
Alright. So after a lot of trial and error and poking around here is
what I found. I observed the leak as described below on a G5 running
Panther/perl 5.8.1, but not on a G4 laptop running Jaguar/perl 5.6.0,
when I kept DBI and DBD::Oracle versions identical on both boxes.
Since everything is fine on the G5 when I go against Mysql, I suspect
the problem is with DBD::Oracle or even the Oracle OCI8 libraries under
a perl with multi-threading enabled (ithreads), which is how perl comes
on Panther. (note I wasn't actually using threads)
So I finally went ahead and rebuilt my own perl (5.8.4) on Panther and
made sure I compiled it without multi-threading support. Sure enough,
when I install DBI and DBD::Oracle against that custom-built perl, the
memory leak disappears.
This is probably worth mentioning in the README.macosx. Basically, you
won't be able to use DBD::Oracle under Panther for anything serious
unless you build your own perl with multi-threading disabled. Or maybe
Tim has a chance to spot where the problem lies ...
-hilmar
(Also, as an aside, the -R option to nm given in README.macosx is not
supported by the system-nm, at least neither in Jaguar nor Panther. I
found though that the symbol table modification of libclntsh is no
longer necessary under Panther, so I hacked the Makefile.PL to allow me
to continue ... Under Jaguar I hacked one C source file of the Perl IO
lib to get rid of the symbol clash; I can post the patch if anyone's
interested.)
On Jul 7, 2004, at 3:07 PM, Hilmar Lapp wrote:
If I run the test case code enclosed below against an Oracle database
(first arg dbi:Oracle:blah) the memory consumption goes continuously
up from a few MB to eventually more than 100MB.
If I run the same code against a MySQL database (first arg
dbi:mysql:database=blah, using DBD::mysql v2.9003), the memory
consumption remains at its initial level.
The issue seems to have been reported before, even though in different
variants. A patch has been posted on the CPAN page that seems to
address only LOB handling, which clearly is not involved here. Tim
responded to an earlier bug report there that he can't reproduce it.
I'm on Mac OSX Panther, using perl 5.8.1RC3 (the one that comes with
the system), DBI 1.38, DBD::Oracle 1.15, and the Oracle client
libraries from the Oracle preview for MacOSX (there's just one AFAIK:
9.2.0.1.0 Developer's release).
Is there a solution/fix/patch known already that escaped my attention?
Tim, if you can't reproduce this, I'd be perfectly willing to help
track down the cause if you provide guidance as to how I would best go
about this.
Thanks in advance for any help, suggestions, patch(es).
-hilmar
--
-
Hilmar Lappemail: lapp at gnf.org
GNF, San Diego, Ca. 92121 phone: +1-858-812-1757
-
use DBI;
my $dbh = DBI->connect($ARGV[0], $ARGV[1], $ARGV[2],
{ RaiseError=> 1, AutoCommit => 0, PrintError
=> 1 });
print "dropping possibly left over test table A1 ...\n";
eval {
$dbh->do("DROP TABLE A1");
};
print "\tfyi, table A1 doesn't exist yet\n" if $@;
print "creating test table ...\n";
$dbh->do('CREATE TABLE A1 (C1 INTEGER)');
my $sql = 'INSERT INTO A1 (c1) VALUES (?)';
print "about to insert 100k numbers with one statement:\n";
my $sth = $dbh->prepare($sql);
for (my $i = 1; $i < (1024*128); $i++) {
$sth->execute($i);
print "\t$i rows ...\n" if ($i % 5000) == 0;
}
print "done, committing this step\n";
$dbh->commit;
$dbh->disconnect;
--
-
Hilmar Lappemail: lapp at gnf.org
GNF, San Diego, Ca. 92121 phone: +1-858-812-1757
-
Fwd: DBD::Oracle memory leak
DISPATCH (DBI::st=HASH(0x1858e94) rc1/1 @1 g0 ima4
pid#8122)
<> DESTROY ignored for outer handle DBI::st=HASH(0x1858e94) (inner
DBI::st=HASH(0x18012f4))
>> DESTROY DISPATCH (DBI::st=HASH(0x18012f4) rc1/1 @1 g0 ima4
pid#8122)
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x18012f4)~INNER)
thr#1800200
dbd_st_destroy
OCIHandleFree(7dabc,OCI_HTYPE_STMT)=SUCCESS
<- DESTROY= undef
DESTROY (dbih_clearcom) (sth 0x1858e94 0x0, com 0x50a670, imp
DBD::Oracle::st):
FLAGS 0x2191: COMSET Warn RaiseError PrintError
ShowErrorStatement
PARENT DBI::db=HASH(0x1858cc0)
KIDS 0 (0 Active)
IMP_DATA undef
NUM_OF_FIELDS 0
NUM_OF_PARAMS 1
dbih_clearcom 0x1858e94 (com 0x50a670, type 3) done.
>> DESTROY DISPATCH (DBI::db=HASH(0x1860440) rc1/1 @1 g0 ima4
pid#8122)
<> DESTROY ignored for outer handle DBI::db=HASH(0x1860440) (inner
DBI::db=HASH(0x1858cc0))
>> DESTROY DISPATCH (DBI::db=HASH(0x1858cc0) rc1/1 @1 g0 ima4
pid#8122)
-> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x1858cc0)~INNER)
thr#1800200
OCIHandleFree(7ef14,OCI_HTYPE_SESSION)=SUCCESS
OCIHandleFree(3c02c,OCI_HTYPE_SERVER)=SUCCESS
OCIHandleFree(18774b0,OCI_HTYPE_SVCCTX)=SUCCESS
OCIHandleFree(1877524,OCI_HTYPE_ERROR)=SUCCESS
<- DESTROY= undef
DESTROY (dbih_clearcom) (dbh 0x1860440 0x0, com 0x500910, imp
DBD::Oracle::db):
FLAGS 0x2191: COMSET Warn RaiseError PrintError
ShowErrorStatement
PARENT DBI::dr=HASH(0x1860470)
KIDS 0 (0 Active)
IMP_DATA undef
dbih_clearcom 0x1860440 (com 0x500910, type 2) done.
-- DBI::END
>> disconnect_all DISPATCH (DBI::dr=HASH(0x180785c) rc1/3 @1 g0
ima801 pid#8122) at
/sw/lib/perl5/5.8.1/darwin-thread-multi-2level/DBI.pm line 653 via
dbd-ora.pl line 0
-> disconnect_all for DBD::Oracle::dr
(DBI::dr=HASH(0x180785c)~0x1860470) thr#1800200
<- disconnect_all= (not implemented) at
/sw/lib/perl5/5.8.1/darwin-thread-multi-2level/DBI.pm line 653 via
dbd-ora.pl line 0
! >> DESTROY DISPATCH (DBI::dr=HASH(0x1860470) rc1/1 @1 g0 ima4
pid#8122) during global destruction
! -> DESTROY for DBD::Oracle::dr (DBI::dr=HASH(0x1860470)~INNER)
thr#1800200
! <- DESTROY= (not implemented) during global destruction
DESTROY (dbih_clearcom) (drh 0x180785c 0x0, com 0x333e40, imp
DBD::Oracle::dr):
FLAGS 0x2215: COMSET Active Warn ShowErrorStatement AutoCommit
PARENT undef
KIDS 0 (0 Active)
IMP_DATA undef
dbih_clearcom 0x180785c (com 0x333e40, type 1) done.
! >> DESTROY DISPATCH (DBI::dr=HASH(0x180785c) rc1/1 @1 g0 ima4
pid#8122) during global destruction
! <> DESTROY for DBI::dr=HASH(0x180785c) ignored (inner handle gone)
Begin forwarded message:
From: Hilmar Lapp <[EMAIL PROTECTED]>
Date: July 7, 2004 3:07:43 PM PDT
To: [EMAIL PROTECTED]
Subject: DBD::Oracle memory leak
If I run the test case code enclosed below against an Oracle database
(first arg dbi:Oracle:blah) the memory consumption goes continuously
up from a few MB to eventually more than 100MB.
If I run the same code against a MySQL database (first arg
dbi:mysql:database=blah, using DBD::mysql v2.9003), the memory
consumption remains at its initial level.
The issue seems to have been reported before, even though in different
variants. A patch has been posted on the CPAN page that seems to
address only LOB handling, which clearly is not involved here. Tim
responded to an earlier bug report there that he can't reproduce it.
I'm on Mac OSX Panther, using perl 5.8.1RC3 (the one that comes with
the system), DBI 1.38, DBD::Oracle 1.15, and the Oracle client
libraries from the Oracle preview for MacOSX (there's just one AFAIK:
9.2.0.1.0 Developer's release).
Is there a solution/fix/patch known already that escaped my attention?
Tim, if you can't reproduce this, I'd be perfectly willing to help
track down the cause if you provide guidance as to how I would best go
about this.
Thanks in advance for any help, suggestions, patch(es).
-hilmar
--
-
Hilmar Lappemail: lapp at gnf.org
GNF, San Diego, Ca. 92121 phone: +1-858-812-1757
-
use DBI;
my $dbh = DBI->connect($ARGV[0], $ARGV[1], $ARGV[2],
{ RaiseError=> 1, AutoCommit => 0, PrintError
=> 1 });
print "dropping possibly left over test table A1 ...\n";
eval {
$dbh->do("DROP TABLE A1");
};
print "\tfyi, table A1 doesn't exist yet\n" if $@;
print "creating test table ...\n";
$dbh->do('CREATE TABLE A1 (C1 INTEGER)');
my $sql = 'INSERT INTO A1 (c1) VALUES (?)';
print "about to insert 100k numbers with one statement:\n";
my $sth = $dbh->prepare($sql);
for (my $i = 1; $i < (1024*128); $i++) {
$sth->execute($i);
print "\t$i rows ...\n" if ($i % 5000) == 0;
}
print "done, committing this step\n";
$dbh->commit;
$dbh->disconnect;
--
-
Hilmar Lappemail: lapp at gnf.org
GNF, San Diego, Ca. 92121 phone: +1-858-812-1757
-
DBD::Oracle memory leak
If I run the test case code enclosed below against an Oracle database
(first arg dbi:Oracle:blah) the memory consumption goes continuously up
from a few MB to eventually more than 100MB.
If I run the same code against a MySQL database (first arg
dbi:mysql:database=blah, using DBD::mysql v2.9003), the memory
consumption remains at its initial level.
The issue seems to have been reported before, even though in different
variants. A patch has been posted on the CPAN page that seems to
address only LOB handling, which clearly is not involved here. Tim
responded to an earlier bug report there that he can't reproduce it.
I'm on Mac OSX Panther, using perl 5.8.1RC3 (the one that comes with
the system), DBI 1.38, DBD::Oracle 1.15, and the Oracle client
libraries from the Oracle preview for MacOSX (there's just one AFAIK:
9.2.0.1.0 Developer's release).
Is there a solution/fix/patch known already that escaped my attention?
Tim, if you can't reproduce this, I'd be perfectly willing to help
track down the cause if you provide guidance as to how I would best go
about this.
Thanks in advance for any help, suggestions, patch(es).
-hilmar
--
-
Hilmar Lappemail: lapp at gnf.org
GNF, San Diego, Ca. 92121 phone: +1-858-812-1757
-
use DBI;
my $dbh = DBI->connect($ARGV[0], $ARGV[1], $ARGV[2],
{ RaiseError=> 1, AutoCommit => 0, PrintError =>
1 });
print "dropping possibly left over test table A1 ...\n";
eval {
$dbh->do("DROP TABLE A1");
};
print "\tfyi, table A1 doesn't exist yet\n" if $@;
print "creating test table ...\n";
$dbh->do('CREATE TABLE A1 (C1 INTEGER)');
my $sql = 'INSERT INTO A1 (c1) VALUES (?)';
print "about to insert 100k numbers with one statement:\n";
my $sth = $dbh->prepare($sql);
for (my $i = 1; $i < (1024*128); $i++) {
$sth->execute($i);
print "\t$i rows ...\n" if ($i % 5000) == 0;
}
print "done, committing this step\n";
$dbh->commit;
$dbh->disconnect;
possible memory leak
Hi All,
I am using
Fedora Linux Core 1 and redHat 8.0
oraclie client 9.2.0.1.0
DBI 1.37
DBD::Oracle 1.15
I seem to have a memory leak problem when using bind variables and the
same prepared statement for hundreds of thousands of rows. Below is my
function call. For my test I have only one statement prepared and held
with $INS. I am inserting about 450k rows and I commit every 5000
inserts. The only thing I can think of is that my $bind_list is being
stored somewhere within the DBI or DBD::Oracle. And these values my be
getting appended to some data structure instead of clearing once I am
done with them. Anyone have any ideas?
Thanks,
Ian
sub insert {
$l->info("function ".(caller(0))[3]);
my $rec = shift; #-- the record we want to insert.
my $table = shift; #-- the name of the table we are going to insert
into
my $columns = getMetaData( $table );
my $bind_list = [];
my $col_str = join(',', @{$columns});
my $place_holders;
foreach my $column ( @{$columns} ){
push(@{$bind_list}, $rec->{$column});
$place_holders .= '?,';
}
$place_holders =~ s/,$//;
my $sql = "INSERT INTO $table ($col_str) VALUES($place_holders)";
#-- check and see if we have already prepared this statement.
unless( exists $INS->{$sql} ){
my $sth = $db->prepare($sql) || $l->logdie($db->errstr());
$INS->{$sql} = $sth;
}
$l->debug("sql: $sql");
$l->debug("bind_list: ",Dumper($bind_list));
my $rv = $INS->{$sql}->execute(@{$bind_list}) ||
$l->logdie($db->errstr());
$l->debug("rv: $rv");
}
Re: possible memory leak in either DBI or DBD:Oracle (or maybe oci??)
On Wed, 12 Nov 2003, Tim Bunce wrote: > On Wed, Nov 12, 2003 at 12:34:08PM +0100, Roman Hochleitner wrote: > > > > repeated connects to 2 or more 8.1.7 DBs OR schematas DOES leak > > repeated connects to only ONE (or more) 9.2.0 DB DOES leak > > > so the conclusion could be that it looks like the problem is more/only > > related to the DBD::Oracle module. > > Since connecting to a single 9.2.0 does leak, but connecting to a > single 8.1.7 does not, *that* leak must be in Oracle's own code. > > > to (almost completely) rule out the assumption that the leak could be in > > the oracle driver itself I wrote a tcl script using Tcl 8.4.4 and Oratcl 3.2. > > any variation of connects to either 9.2.0 or 8.1.7 did NOT leak with Tcl > > and Oratcl and as I'm an old Tcl'er I kind of find that amusing (not > > that I want to start a flame war or something ;) > > The OCI API usage would be different. The key point is that exactly > the same DBD::Oracle code leaked with 9.2.0 but not with 8.1.7. > Since the DBD::Oracle code is the same it seems very unlikely it > would be a leak in the DBD::Oracle code. > > The issue of leaks with *multiple* connects may be unrelated. Hi, since I still have this problem with leaking perl nocol monitors using DBI & DBD:Oracle and I'm no perl expert, could anybody tell me how to debug this to find out exactly where it leaks and maybe prove that it is the oracle client itself so that I can make an official request to oracle to fix it? thanks in advice mfg Roman Hochleitner Unix System Programmer ecetra Central European e-Finance AG Neutorgasse 2 | A-1010 Vienna Tel:+43-1-536 89-17725 Fax:+43-1-536 89-17719 http://www.ecetra.com
Re: Memory leak?: execute() with bind values (again?)
The patch works OK!
I have to apologise - it was my mistake. There are two versions of Perl on my
mashine - the patch was installed on 5.8.3 and I was testing on 5.6.1. Now
everything works fine on both versions.
Thanks a lot!
> Interesting. I just tried it on my computer and it seems to get rid the
> (a?) leak.
>
> I modified your code a tad, and this is what I am testing with:
>
> my
> $dbh1=DBI->connect("DBI:Pg:dbname=rlippan",'' ,'');
>
> $sqltext="SELECT 1 WHERE 'this' = ?";
> $sth1=$dbh1->prepare($sqltext);
> $md="x"x1022;
>
> for ($i=1;$i<100;$i++) {
> $sth1->bind_param(1,$md);
> $sth1->execute;
> #$dbh1->do($sqltext ,{pg_type=>DBD::Pg::PG_INT8},$md);
> }
> $sth1->finish;
> $dbh1->disconnect;
>
> and the above code leaks 1K/iteration w/o the patch and does not leak with
> the path. Did you do a 'make install' before testing again?
>
> > And this patch is for revision 1.40 and the one I have with the DBD::Pg
> > 1.31 is 1.32.
>
> I know, but that section of code has not changed so that part of the
> patch should apply wo a problem.
>
>
> Here. same patch just against DBD::Pg 1.31:
>
> --- dbdimp.c2004-01-28 13:20:45.611337072 -0500
> +++ dbdimp.c.leakfix2004-01-28 13:20:38.842366112 -0500
> @@ -856,6 +856,9 @@
> (void)SvUPGRADE(newvalue, SVt_PV);
>
>
> + if (phs->quoted)
> + Safefree(phs->quoted);
> +
> if (!SvOK(newvalue)) {
> phs->quoted = strdup("NULL");
> if (NULL == phs->quoted)
>
>
>
> Rudy
--
Zhivko Duchev
===
Institute for Animal Breeding
Mariensee 31535 Neustadt Germany
===
DBD-Oracle-1.1{3,4} memory leak ?
Hello, I found that some of my perl programs using DBD::Oracle are leaking. It seems to be similar to: http://www.mail-archive.com/[EMAIL PROTECTED]/msg19296.html So far I found that: * it's leaking on PC and also solaris * DBD::Oracle-1.13 and 1.14 seems to be affected, 1.12 is ok (on both PC and solaris) * it seems to be leaking when DBD::Oracle is linked against OCI 9x, with OCI 8x it seems to be fine. I'm using DBI-1.39, perl 5.8.0 and 5.6.x I looked briefly to what changed in sources between 1.12 and 1.13 but found nothing interesting. So to stop leaking I downgraded to 1.12 everywhere. I won't be much more usefull than reporting this probably, but I will be happy to test patches (if there will be any). -- Vladimír Marek
Re: Memory leak?: execute() with bind values (again?)
On Tue, 27 Jan 2004, Zhivko Duchev wrote:
> Thanks for the information!
> Unfortunately the patch did not solve the problem.
Interesting. I just tried it on my computer and it seems to get rid the
(a?) leak.
I modified your code a tad, and this is what I am testing with:
my
$dbh1=DBI->connect("DBI:Pg:dbname=rlippan",'' ,'');
$sqltext="SELECT 1 WHERE 'this' = ?";
$sth1=$dbh1->prepare($sqltext);
$md="x"x1022;
for ($i=1;$i<100;$i++) {
$sth1->bind_param(1,$md);
$sth1->execute;
#$dbh1->do($sqltext ,{pg_type=>DBD::Pg::PG_INT8},$md);
}
$sth1->finish;
$dbh1->disconnect;
and the above code leaks 1K/iteration w/o the patch and does not leak with
the path. Did you do a 'make install' before testing again?
> And this patch is for revision 1.40 and the one I have with the DBD::Pg 1.31
> is 1.32.
>
I know, but that section of code has not changed so that part of the
patch should apply wo a problem.
Here. same patch just against DBD::Pg 1.31:
--- dbdimp.c2004-01-28 13:20:45.611337072 -0500
+++ dbdimp.c.leakfix2004-01-28 13:20:38.842366112 -0500
@@ -856,6 +856,9 @@
(void)SvUPGRADE(newvalue, SVt_PV);
+ if (phs->quoted)
+ Safefree(phs->quoted);
+
if (!SvOK(newvalue)) {
phs->quoted = strdup("NULL");
if (NULL == phs->quoted)
Rudy
Re: Memory leak?: execute() with bind values (again?)
Thanks for the information!
Unfortunately the patch did not solve the problem.
And this patch is for revision 1.40 and the one I have with the DBD::Pg 1.31
is 1.32.
Am Montag 26 Januar 2004 9:06 nachmittags/abends schrieb Rudy Lippan:
> On Tue, 20 Jan 2004, Zhivko Duchev wrote:
> > Hello All,
> >
> > I think this is is an old known problem
> > (http://www.mail-archive.com/[EMAIL PROTECTED]/msg18506.html), but just
> > to be sure:
>
> I think this one is a new problem... And it looks to be my fault :(
>
> > There is a memory leak when using binding and multiply executes also in
> > DBD::Pg 1.31.
> >
> >
> > Any suggestions?
>
> Try this:
>
> Index: dbdimp.c
> ===
> RCS file: /usr/local/cvsroot/dbdpg/dbdpg/dbdimp.c,v
> retrieving revision 1.40
> diff -u -r1.40 dbdimp.c
> --- dbdimp.c21 Jan 2004 23:47:17 - 1.40
> +++ dbdimp.c26 Jan 2004 19:58:51 -
> @@ -828,6 +828,9 @@
> (void)SvUPGRADE(newvalue, SVt_PV);
>
>
> + if (phs->quoted)
> + Safefree(phs->quoted);
> +
> if (!SvOK(newvalue)) {
> phs->quoted = safemalloc(sizeof("NULL"));
> if (NULL == phs->quoted)
>
>
> And let me know if it works for you.
>
> Rudy
--
Zhivko Duchev
===
Institute for Animal Breeding
Mariensee 31535 Neustadt Germany
===
Re: Memory leak?: execute() with bind values (again?)
On Tue, 20 Jan 2004, Zhivko Duchev wrote:
> Hello All,
>
> I think this is is an old known problem
> (http://www.mail-archive.com/[EMAIL PROTECTED]/msg18506.html), but just to
> be sure:
I think this one is a new problem... And it looks to be my fault :(
> There is a memory leak when using binding and multiply executes also in
> DBD::Pg 1.31.
>
> Any suggestions?
Try this:
Index: dbdimp.c
===
RCS file: /usr/local/cvsroot/dbdpg/dbdpg/dbdimp.c,v
retrieving revision 1.40
diff -u -r1.40 dbdimp.c
--- dbdimp.c21 Jan 2004 23:47:17 - 1.40
+++ dbdimp.c26 Jan 2004 19:58:51 -
@@ -828,6 +828,9 @@
(void)SvUPGRADE(newvalue, SVt_PV);
+ if (phs->quoted)
+ Safefree(phs->quoted);
+
if (!SvOK(newvalue)) {
phs->quoted = safemalloc(sizeof("NULL"));
if (NULL == phs->quoted)
And let me know if it works for you.
Rudy
Memory leak?: execute() with bind values (again?)
Hello All,
I think this is is an old known problem
(http://www.mail-archive.com/[EMAIL PROTECTED]/msg18506.html), but just to
be sure:
There is a memory leak when using binding and multiply executes also in
DBD::Pg 1.31.
The following script reproduces the problem:
use DBI ;
use DBD::Pg qw/:pg_types/;
my
$dbh1=DBI->connect("DBI:Pg:dbname='blob_test';host='torro';port='5432'",'duchev','')
or die 'err';
$sqltext="INSERT INTO delme3 (v) VALUES(?)";
$sth1=$dbh1->prepare($sqltext);
$md=999;
for ($i=1;$i<100;$i++) {
$sth1->bind_param(1,$md, {pg_type=>DBD::Pg::PG_INT8});
$sth1->execute;
#$dbh1->do($sqltext ,{pg_type=>DBD::Pg::PG_INT8},$md);
}
$sth1->finish;
$dbh1->disconnect;
The problem was not observed when using 'do'.
This was tested with Perl 5.6.1 and DBI 1.38 and 1.40
Any suggestions?
Thanks!
Zhivko Duchev
===
Institute for Animal Breeding
Mariensee 31535 Neustadt Germany
===
Memory leak in DBD::Oracle 1.14 ... ?
I've attached a little script which replicates the problem on my machine as well as
the build session log for DBD::Oracle 1.14.
I've tested it using DBI 1.38 and DBI 1.28 with both DBD::Oracle 1.12 and 1.14.
Regardless of the DBI version, DBD::Oracle 1.14 leaks on my system and DBD::Oracle
1.12 does not. I've noticed the leak when calling connect_cached(), do() (or
prepare()/execute()/commit/finish).
-mike
script ---
#!/usr/bin/perl
use strict;
use DBI;
my($dbp) = "dbi:Oracle:host=weirdo.com;port=1521;sid=SID";
my($dbu) = "username";
my($dbpass) = "password";
while (1) {
my($sth);
my(@row);
my($dbh) = DBI->connect_cached($dbp,$dbu,$dbpass) || die "Couldn't connect to
oracle db: $DBI::errstr\n";
##
## uncomment these and it just leaks faster.
##
# $sth = $dbh->prepare("SELECT * from FROM_STATS");
# $sth->execute;
# while(@row = $sth->fetchrow_array) {
##print "row: @row\n";
# }
# $sth->finish;
}
exit;
end script
log ---
Script started on Fri 28 Nov 2003 09:11:15 AM PST
[EMAIL PROTECTED] DBD-Oracle-1.14]$ setenv ORACLE_HOME /home/orahome
[EMAIL PROTECTED] DBD-Oracle-1.14]$ make realclean
rm -f blib/script/ora_explain
rm -rf Oracle.c Oracle.xsi dll.base dll.exp sqlnet.log libOracle.def ora_explain mk.pm
./blib Makefile.aperl blib/arch/auto/DBD/Oracle/extralibs.all perlmain.c tmon.out
mon.out so_locations pm_to_blib *.o *.a perl.exe perl perl Oracle.bs Oracle.bso
Oracle.def libOracle.def Oracle.exp Oracle.x core core.*perl.*.? *perl.core
mv Makefile Makefile.old > /dev/null 2>&1
rm -rf blib/lib/auto/DBD/Oracle blib/arch/auto/DBD/Oracle
rm -rf DBD-Oracle-1.14
rm -f blib/arch/auto/DBD/Oracle/Oracle.so blib/arch/auto/DBD/Oracle/Oracle.bs
rm -f blib/arch/auto/DBD/Oracle/Oracle.a
rm -f blib/lib/DBD/Oracle.pm blib/arch/auto/DBD/Oracle/dbdimp.h blib/lib/oraperl.ph
rm -f blib/arch/auto/DBD/Oracle/ocitrace.h blib/lib/Oraperl.pm
rm -f blib/arch/auto/DBD/Oracle/Oracle.h blib/arch/auto/DBD/Oracle/mk.pm
rm -f blib/lib/DBD/Oracle/GetInfo.pm
rm -rf Makefile Makefile.old
[EMAIL PROTECTED] DBD-Oracle-1.14]$ perl Makefile.PL -v
Using DBI 1.38 installed in
/usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/auto/DBI
Configuring DBD::Oracle ...
>>> Remember to actually *READ* the README file!
Especially if you have any problems.
Using Oracle in /home/orahome
WARNING: could not decode oracle version from
/home/orahome/orainst/inspdver, or /home/orahome/install/unix.rgs
or from ORACLE_HOME path /home/orahome.
Oracle version based logic in Makefile.PL may produce erroneous results.
Found header files in rdbms/public rdbms/demo.
Found /home/orahome/rdbms/demo/demo_rdbms.mk
Found /home/orahome/otrace/demo/atmoci.mk
Found /home/orahome/precomp/demo/proc/demo_proc.mk
Using /home/orahome/rdbms/demo/demo_rdbms.mk
Reading /home/orahome/rdbms/demo/demo_rdbms.mk
Reading /home/orahome/rdbms/lib/env_rdbms.mk
Read a total of 2202 lines from /home/orahome/rdbms/lib/env_rdbms.mk (including
inclusions)
Read a total of 2493 lines from /home/orahome/rdbms/demo/demo_rdbms.mk (including
inclusions)
Deleted SHELL definition: SHELL=/bin/sh
Deleted LIB_EXT definition: LIB_EXT=a
Deleted OBJ_EXT definition: OBJ_EXT=o
Deleted AR definition: AR=ar
Deleted AS definition: AS=as
Deleted CC definition: CC=cc
Deleted CHMOD definition: CHMOD=chmod
Deleted CPP definition: CPP=cpp
Deleted ECHO definition: ECHO=echo
Deleted LD definition: LD=ld
Deleted PERL definition: PERL=perl
Deleted CFLAGS definition: CFLAGS=$(GFLAG) $(OPTIMIZE) $(CDEBUG) $(CCFLAGS) $(PFLAGS)\
$(SHARED_CFLAG) $(USRFLAGS)
Deleted LDFLAGS definition: LDFLAGS=-o $@ $(LDPATHFLAG)$(PRODLIBHOME)
$(LDPATHFLAG)$(LIBHOME)
Deleted LDFLAGS definition: LDFLAGS=-o $@ $(LDPATHFLAG)$(PRODLIBHOME)
$(LDPATHFLAG)$(LIBHOME) $(LDPATHFLAG)$(LIBHOME)stubs/
Deleted OPTIMIZE definition: OPTIMIZE=$(OPTIMIZE3)
Deleted AR definition: AR=/usr/bin/ar
Deleted AS definition: AS=/usr/bin/as
Deleted LD definition: LD=/usr/bin/ld
Deleted CPP definition: CPP=/lib/cpp
Deleted CHMOD definition: CHMOD=/bin/chmod
Deleted ASFLAGS definition: ASFLAGS=
Deleting ORA_NLS = $(ORACLE_HOME)/ocommon/nls/admin/data/
because it is not already set in the environment
and it can cause ORA-01019 errors.
Deleted ORA_NLS definition: ORA_NLS = $(ORACLE_HOME)/ocommon/nls/admin/data/
Deleting ORA_NLS33 = $(ORACLE_HOME)/ocommon/nls/admin/data/
because it is not already set in the environment
and it can cause ORA-01019 errors.
Deleted ORA_NLS33 definition: ORA_NLS33 = $(ORACLE_HOME)/ocommon/nls/admin/data/
Appending '/home/orahome/rdbms/lib/libskgxpd.a /home/orahome/r
Re: possible memory leak in either DBI or DBD:Oracle (or maybe oci??)
On Thu, 6 Nov 2003, Tim Bunce wrote: > On Wed, Nov 05, 2003 at 03:15:40PM +0100, Roman Hochleitner wrote: > > > > Hi! > > > > We are using Oracle 8.1.7 server/client under Redhat 7.1 perl 5.6.0 with > > DBD::Oracle V1.14 and DBI V1.37. > > > > With the attached script I can reproduce a memory leak growing at a rate > > of about 12MB/min. We use scripts simmiliar to that for monitoring > > (nocol/snips client). > > At first I searched the leak in nocol/snips but after a while i stripped > > the monitors down until I got this remainder. > > > > The funny thing is: if you connect/disconnect to only one and the same > > database, there is no memory leak, but if you alternate between at least > > two databases, it starts growing. > > Interesting. Probably means an Oracle issue though. > > > Can anybody reproduce the same memory leak with this little script? > > I'll try later - but it might be a week or three or ... Hi, I have new evidence to add to this thread: tests were made with Perl 5.8.2 DBI V1.38 DBD::Oracle V1.14 and Oracle client 9.2.0. (also DBD::Pg V 1.22 was used for comparision) the script does the following: connect to the database prepare a statement execute the statement finish the statement disconnect from the database good: repeated connects to one ore more PostgreSQL 7.2 DB does NOT leak repeated connects to one ore more PostgreSQL 7.2 DB and only ONE 8.1.7 does NOT leak repeated connects to only one 8.1.7 DB does NOT leak bad: repeated connects to 2 or more 8.1.7 DBs OR schematas DOES leak repeated connects to only ONE (or more) 9.2.0 DB DOES leak in all test the connects were made sequential and not in parallel all tests were made from a RedHat 7.3 workstation ( i hate RedHat and love Debian ) so the conclusion could be that it looks like the problem is more/only related to the DBD::Oracle module. to (almost completely) rule out the assumption that the leak could be in the oracle driver itself I wrote a tcl script using Tcl 8.4.4 and Oratcl 3.2. any variation of connects to either 9.2.0 or 8.1.7 did NOT leak with Tcl and Oratcl and as I'm an old Tcl'er I kind of find that amusing (not that I want to start a flame war or something ;) i hope my tests have made it easier to locate the problem and to fix it, and thank for all your time you've and will put into any open source project. have fun mfg Roman Hochleitner Unix System Programmer ecetra Central European e-Finance AG Neutorgasse 2 | A-1010 Vienna Tel:+43-1-536 89-17725 Fax:+43-1-536 89-17719 http://www.ecetra.com
Re: possible memory leak in either DBI or DBD:Oracle (or maybe oci??)
On Wed, Nov 12, 2003 at 12:34:08PM +0100, Roman Hochleitner wrote: > > repeated connects to 2 or more 8.1.7 DBs OR schematas DOES leak > repeated connects to only ONE (or more) 9.2.0 DB DOES leak > so the conclusion could be that it looks like the problem is more/only > related to the DBD::Oracle module. Since connecting to a single 9.2.0 does leak, but connecting to a single 8.1.7 does not, *that* leak must be in Oracle's own code. > to (almost completely) rule out the assumption that the leak could be in > the oracle driver itself I wrote a tcl script using Tcl 8.4.4 and Oratcl 3.2. > any variation of connects to either 9.2.0 or 8.1.7 did NOT leak with Tcl > and Oratcl and as I'm an old Tcl'er I kind of find that amusing (not > that I want to start a flame war or something ;) The OCI API usage would be different. The key point is that exactly the same DBD::Oracle code leaked with 9.2.0 but not with 8.1.7. Since the DBD::Oracle code is the same it seems very unlikely it would be a leak in the DBD::Oracle code. The issue of leaks with *multiple* connects may be unrelated. > i hope my tests have made it easier to locate the problem and to fix it, It all helps. > and thank for all your time you've and will put into any open source > project. Thanks. Tim.
Re: possible memory leak in either DBI or DBD:Oracle (or maybe oci??)
On Wed, Nov 05, 2003 at 03:15:40PM +0100, Roman Hochleitner wrote: > > Hi! > > We are using Oracle 8.1.7 server/client under Redhat 7.1 perl 5.6.0 with > DBD::Oracle V1.14 and DBI V1.37. > > With the attached script I can reproduce a memory leak growing at a rate > of about 12MB/min. We use scripts simmiliar to that for monitoring > (nocol/snips client). > At first I searched the leak in nocol/snips but after a while i stripped > the monitors down until I got this remainder. > > The funny thing is: if you connect/disconnect to only one and the same > database, there is no memory leak, but if you alternate between at least > two databases, it starts growing. Interesting. Probably means an Oracle issue though. > Can anybody reproduce the same memory leak with this little script? I'll try later - but it might be a week or three or ... Tim. p.s. prepare() doesn't pay attention to handle attributes like RaiseError passed in it's \%attr parameter.
RE: possible memory leak in either DBI or DBD:Oracle (or maybe oci??)
I don't see the script? On 05-Nov-2003 Roman Hochleitner wrote: > On Wed, 5 Nov 2003 [EMAIL PROTECTED] wrote: > >> >> I was just debugging this problem for co-workers, on RedHat 7.3 perl 5.6.1, >> DBI 1.37 & DBD::Oracle 1.14. I am going to upgrade the DBI to 1.38 and the >> perl 5.8.1(not that this is affecting the memory leak). If you search the >> archives, DBI 1.37 seems to had memory leak problems with other drivers as >> well. So I would say you might want to upgrade to the latest DBI(1.38). > > Thank you, > > If upgrading fixes this problem, then I will do that. It's just that I > would like to have confirmation that exactly the script that I attached > (db connections/user/pwd edited of course and 2 different databases) > runs without leaking, with the setup you suggested, because I got the > answer to upgrade very often (for different problems) and it often didn't > help at all. > > So, does the script I attached run smothely with your setup without > leaking? > > > Thanks > > mfg > > Roman Hochleitner > Unix System Programmer > > > ecetra Central European e-Finance AG > > Neutorgasse 2 | A-1010 Vienna > Tel: +43-1-536 89-17725 > Fax: +43-1-536 89-17719 > http://www.ecetra.com > -- -- E-Mail: [EMAIL PROTECTED] Date: 05-Nov-2003 Time: 16:23:02 --
RE: possible memory leak in either DBI or DBD:Oracle (or maybe oci??)
On Wed, 5 Nov 2003 [EMAIL PROTECTED] wrote: > > I was just debugging this problem for co-workers, on RedHat 7.3 perl 5.6.1, > DBI 1.37 & DBD::Oracle 1.14. I am going to upgrade the DBI to 1.38 and the > perl 5.8.1(not that this is affecting the memory leak). If you search the > archives, DBI 1.37 seems to had memory leak problems with other drivers as > well. So I would say you might want to upgrade to the latest DBI(1.38). Thank you, If upgrading fixes this problem, then I will do that. It's just that I would like to have confirmation that exactly the script that I attached (db connections/user/pwd edited of course and 2 different databases) runs without leaking, with the setup you suggested, because I got the answer to upgrade very often (for different problems) and it often didn't help at all. So, does the script I attached run smothely with your setup without leaking? Thanks mfg Roman Hochleitner Unix System Programmer ecetra Central European e-Finance AG Neutorgasse 2 | A-1010 Vienna Tel:+43-1-536 89-17725 Fax:+43-1-536 89-17719 http://www.ecetra.com
RE: possible memory leak in either DBI or DBD:Oracle (or maybe oci??)
I was just debugging this problem for co-workers, on RedHat 7.3 perl 5.6.1, DBI 1.37 & DBD::Oracle 1.14. I am going to upgrade the DBI to 1.38 and the perl 5.8.1(not that this is affecting the memory leak). If you search the archives, DBI 1.37 seems to had memory leak problems with other drivers as well. So I would say you might want to upgrade to the latest DBI(1.38). On 05-Nov-2003 Roman Hochleitner wrote: > > Hi! > > We are using Oracle 8.1.7 server/client under Redhat 7.1 perl 5.6.0 with > DBD::Oracle V1.14 and DBI V1.37. > > With the attached script I can reproduce a memory leak growing at a rate > of about 12MB/min. We use scripts simmiliar to that for monitoring > (nocol/snips client). > At first I searched the leak in nocol/snips but after a while i stripped > the monitors down until I got this remainder. > > The funny thing is: if you connect/disconnect to only one and the same > database, there is no memory leak, but if you alternate between at least > two databases, it starts growing. > > Can anybody reproduce the same memory leak with this little script? > > Thanks for your time > > PS: please reply to my email address too (I am not subscribed) > > mfg > > Roman Hochleitner > Unix System Programmer > > > ecetra Central European e-Finance AG > > Neutorgasse 2 | A-1010 Vienna > Tel: +43-1-536 89-17725 > http://www.ecetra.com -- -- E-Mail: [EMAIL PROTECTED] Date: 05-Nov-2003 Time: 10:19:57 --
possible memory leak in either DBI or DBD:Oracle (or maybe oci??)
Hi!
We are using Oracle 8.1.7 server/client under Redhat 7.1 perl 5.6.0 with
DBD::Oracle V1.14 and DBI V1.37.
With the attached script I can reproduce a memory leak growing at a rate
of about 12MB/min. We use scripts simmiliar to that for monitoring
(nocol/snips client).
At first I searched the leak in nocol/snips but after a while i stripped
the monitors down until I got this remainder.
The funny thing is: if you connect/disconnect to only one and the same
database, there is no memory leak, but if you alternate between at least
two databases, it starts growing.
Can anybody reproduce the same memory leak with this little script?
Thanks for your time
PS: please reply to my email address too (I am not subscribed)
mfg
Roman Hochleitner
Unix System Programmer
ecetra Central European e-Finance AG
Neutorgasse 2 | A-1010 Vienna
Tel:+43-1-536 89-17725
http://www.ecetra.com
#! /usr/bin/perl -w
use DBI;
use strict;
sub connect_and_disconnect()
{
my $dbh;
my $db_str;
my %StdAttr = (
PrintError => 1,
AutoCommit => 0,
RaiseError => 1
);
my %attr = (
PrintError => 0,
RaiseError => 0
);
$dbh = DBI->connect('dbi:Oracle:DBONE.ACPT','emerald',
"d0gf00d", \%StdAttr);
if (!defined $dbh) { print "Error"; exit 1; }
$db_str = $dbh->prepare("select * from dual",\%attr);
$db_str->execute();
$db_str->finish();
$dbh->disconnect;
undef $dbh;
}
sub connect_and_disconnect2()
{
my $dbh;
my $db_str;
my %StdAttr = (
PrintError => 1,
AutoCommit => 0,
RaiseError => 1
);
my %attr = (
PrintError => 0,
RaiseError => 0
);
$dbh = DBI->connect('dbi:Oracle:DBTWO.DEV','gizmo',
"catf00d", \%StdAttr);
if (!defined $dbh) { print "Error"; exit 1; }
$db_str = $dbh->prepare("select * from dual",\%attr);
$db_str->execute();
$db_str->finish();
$dbh->disconnect;
undef $dbh;
}
my $loops=1;
while($loops < 1)
{
$loops++;
if ( ($loops % 100) == 0 ) {
print ".\n" ;
}
connect_and_disconnect();
connect_and_disconnect2();
}
Re: memory leak with DBD::DB2
(111.72%)RSS 14856 (126.20%)
7000 SZ 29640 (113.72%)RSS 15376 (130.62%)
8000 SZ 30164 (115.73%)RSS 15900 (135.07%)
9000 SZ 30684 (117.73%)RSS 16420 (139.48%)
1 SZ 31204 (119.72%)RSS 16940 (143.90%)
DBD::DB2 0.76 prepare + execute + fetchrow_arrayref
0SZ 26064 (100.00%)RSS 11764 (100.00%)
1000 SZ 26368 (101.17%)RSS 12096 (102.82%)
2000 SZ 26680 (102.36%)RSS 12408 (105.47%)
3000 SZ 26992 (103.56%)RSS 12720 (108.13%)
4000 SZ 27304 (104.76%)RSS 13032 (110.78%)
5000 SZ 27620 (105.97%)RSS 13348 (113.46%)
6000 SZ 27932 (107.17%)RSS 13660 (116.12%)
7000 SZ 28244 (108.36%)RSS 13972 (118.77%)
8000 SZ 28556 (109.56%)RSS 14284 (121.42%)
9000 SZ 28868 (110.76%)RSS 14596 (124.07%)
1 SZ 29180 (111.96%)RSS 14908 (126.73%)
>
> On Wed, Sep 24, 2003 at 03:51:50PM +0200, [EMAIL PROTECTED] wrote:
> > Hi all,
> >
> > I'm working in a database migration project (Sybase ASE -> DB2 V8) with perl, DBI
> > and DBD::DB2 and we have a memory leak problem when we run a lot of select
> > statements during data import/export.
> >
> > We tested the System with different combinations of DBI (1.20 - 1.38), DBD::DB2
> > (0.75-0.76) and DB2 V8.1.3 Fixpacks (FP2 + FP3).
> >
> > All test results in a more or less large memory leak (see Script output).
> > Someone can help me or should we go back to Sybase ASE.
>
> It's more likely to get fixed if you can narrow down the cause.
> For example
> does just doing prepare() in a loop leak, or
> does just doing prepare() and execute() with no fetches leak, or
> does fetchrow_arrayref leak (eg avoid hashes), or
> etc etc
>
> Tim.
>
> > Rgds,
> >
> > Ruediger
> >
> >
> > Script:
> > --
> > #!/usr/bin/perl
> >
> > use strict;
> > use locale;
> > use DBI;
> >
> > my $dbh = DBI->connect('dbi:DB2:xxx','xxx','xxx',{AutoCommit => 1,});
> >
> > sub getps {
> > my @a = split(/ +/,`ps auxww | grep _00_xx_test5.pl | grep -v grep`);
> > return ($a[4],$a[5],$a[1]);
> > }
> >
> > my @procorg;
> >
> > sub ps {
> > my @proc = getps();
> > my $d1 = sprintf("%3.2f",$proc[0]/$procorg[0]*100.0);
> > my $d2 = sprintf("%3.2f",$proc[1]/$procorg[1]*100.0);
> > return "SZ ".($proc[0])." ($d1%)RSS ".($proc[1])." ($d2%)";
> > }
> >
> > my $c = 0;
> > while (1) {
> > my @RESULT = ();
> > $c++;
> > # select 13 records (ID, NAME, ID_TYPE)
> > my $st = $dbh->prepare('select * from ut_attribute');
> >
> > $st->execute();
> >
> > # test 1
> > # --
> > # while (my $ref = $st->fetchrow_hashref()) {
> > #push (@RESULT, $ref);
> > # }
> >
> > # test 2
> > # --
> > # my $ref = $st->fetchall_hashref('ID');
> >
> > # test 3
> > # --
> > while (my $hashref = $sth->fetchrow_hashref) {
> > }
> >
> > $st->finish();
> > if ($c==1) {
> > @procorg = getps();
> > print "0".ps()."\n";
> > }
> > elsif ($c % 1000 == 0) {
> > print "$c ".ps()."\n";
> > }
> > }
> > $dbh->disconnect;
> >
> >
> > Script output:
> > -
> > with DBD::DB2 0.76
> > --
> > 0 SZ 25412 (100.00%) RSS 11300 (100.00%)
> > 1000 SZ 26196 (103.09%) RSS 12088 (106.97%)
> > 2000 SZ 27028 (106.36%) RSS 12920 (114.34%)
> > 3000 SZ 27860 (109.63%) RSS 13752 (121.70%)
> > 4000 SZ 28692 (112.91%) RSS 14584 (129.06%)
> > 5000 SZ 29524 (116.18%) RSS 15416 (136.42%)
> > 6000 SZ 30356 (119.46%) RSS 16248 (143.79%)
> > 7000 SZ 31188 (122.73%) RSS 17080 (151.15%)
> > 8000 SZ 32020 (126.00%) RSS 17912 (158.51%)
> > 9000 SZ 32852 (129.28%) RSS 18744 (165.88%)
> > 1 SZ 33684 (132.55%) RSS 19576 (173.24%)
> > 11000 SZ 34512 (135.81%) RSS 20404 (180.57%)
> >
> >
> > with DBD::DB2 0.75
> > --
> > 0 SZ 25416 (100.00%) RSS 11296 (100.00%)
> > 1000 SZ 25572 (100.61%) RSS 11456 (101.42%)
> > 2000 SZ 25776 (101.42%) RSS 11660 (103.22%)
> > 3000 SZ 25980 (102.22%) RSS 11864 (105.03%)
> > 4000 SZ 26184 (103.02%) RSS 12068 (106.83%)
> > 5000 SZ 26388 (103.82%) RSS 12272 (108.64%)
> > 6000 SZ 26592 (104.63%) RSS 12476 (110.45%)
> > 7000 SZ 26796 (105.43%) RSS 12680 (112.25%)
> > 8000 SZ 27000 (106.23%) RSS 12884 (114.06%)
> > 9000 SZ 27204 (107.03%) RSS 13088 (115.86%)
> > 1 SZ 27408 (107.84%) RSS 13292 (117.67%)
> > 11000 SZ 27616 (108.66%) RSS 13500 (119.51%)
> > 12000 SZ 27816 (109.44%) RSS 13700 (121.28%)
> >
> >
> >
> > Environment:
> >
> > SuSE SLES 8
> > Linux Kernel 2.4.19
> > DB2/LINUX 8.1.3 (also testet Fixpack 2 (8.1.2))
> > Perl 5.8.0
> > DBI 1.38 (also tested 1.20 - 1.37)
> > DBD::DB2 0.76 (also tested 0.75)
> >
> >
Re: memory leak with DBD::DB2
On Wed, Sep 24, 2003 at 03:51:50PM +0200, [EMAIL PROTECTED] wrote:
> Hi all,
>
> I'm working in a database migration project (Sybase ASE -> DB2 V8) with perl, DBI
> and DBD::DB2 and we have a memory leak problem when we run a lot of select
> statements during data import/export.
>
> We tested the System with different combinations of DBI (1.20 - 1.38), DBD::DB2
> (0.75-0.76) and DB2 V8.1.3 Fixpacks (FP2 + FP3).
>
> All test results in a more or less large memory leak (see Script output).
> Someone can help me or should we go back to Sybase ASE.
It's more likely to get fixed if you can narrow down the cause.
For example
does just doing prepare() in a loop leak, or
does just doing prepare() and execute() with no fetches leak, or
does fetchrow_arrayref leak (eg avoid hashes), or
etc etc
Tim.
> Rgds,
>
> Ruediger
>
>
> Script:
> --
> #!/usr/bin/perl
>
> use strict;
> use locale;
> use DBI;
>
> my $dbh = DBI->connect('dbi:DB2:xxx','xxx','xxx',{AutoCommit => 1,});
>
> sub getps {
> my @a = split(/ +/,`ps auxww | grep _00_xx_test5.pl | grep -v grep`);
> return ($a[4],$a[5],$a[1]);
> }
>
> my @procorg;
>
> sub ps {
> my @proc = getps();
> my $d1 = sprintf("%3.2f",$proc[0]/$procorg[0]*100.0);
> my $d2 = sprintf("%3.2f",$proc[1]/$procorg[1]*100.0);
> return "SZ ".($proc[0])." ($d1%)RSS ".($proc[1])." ($d2%)";
> }
>
> my $c = 0;
> while (1) {
> my @RESULT = ();
> $c++;
> # select 13 records (ID, NAME, ID_TYPE)
> my $st = $dbh->prepare('select * from ut_attribute');
>
> $st->execute();
>
> # test 1
> # --
> # while (my $ref = $st->fetchrow_hashref()) {
> #push (@RESULT, $ref);
> # }
>
> # test 2
> # --
> # my $ref = $st->fetchall_hashref('ID');
>
> # test 3
> # --
> while (my $hashref = $sth->fetchrow_hashref) {
> }
>
> $st->finish();
> if ($c==1) {
> @procorg = getps();
> print "0".ps()."\n";
> }
> elsif ($c % 1000 == 0) {
> print "$c ".ps()."\n";
> }
> }
> $dbh->disconnect;
>
>
> Script output:
> -
> with DBD::DB2 0.76
> --
> 0 SZ 25412 (100.00%) RSS 11300 (100.00%)
> 1000 SZ 26196 (103.09%) RSS 12088 (106.97%)
> 2000 SZ 27028 (106.36%) RSS 12920 (114.34%)
> 3000 SZ 27860 (109.63%) RSS 13752 (121.70%)
> 4000 SZ 28692 (112.91%) RSS 14584 (129.06%)
> 5000 SZ 29524 (116.18%) RSS 15416 (136.42%)
> 6000 SZ 30356 (119.46%) RSS 16248 (143.79%)
> 7000 SZ 31188 (122.73%) RSS 17080 (151.15%)
> 8000 SZ 32020 (126.00%) RSS 17912 (158.51%)
> 9000 SZ 32852 (129.28%) RSS 18744 (165.88%)
> 1 SZ 33684 (132.55%) RSS 19576 (173.24%)
> 11000 SZ 34512 (135.81%) RSS 20404 (180.57%)
>
>
> with DBD::DB2 0.75
> --
> 0 SZ 25416 (100.00%) RSS 11296 (100.00%)
> 1000 SZ 25572 (100.61%) RSS 11456 (101.42%)
> 2000 SZ 25776 (101.42%) RSS 11660 (103.22%)
> 3000 SZ 25980 (102.22%) RSS 11864 (105.03%)
> 4000 SZ 26184 (103.02%) RSS 12068 (106.83%)
> 5000 SZ 26388 (103.82%) RSS 12272 (108.64%)
> 6000 SZ 26592 (104.63%) RSS 12476 (110.45%)
> 7000 SZ 26796 (105.43%) RSS 12680 (112.25%)
> 8000 SZ 27000 (106.23%) RSS 12884 (114.06%)
> 9000 SZ 27204 (107.03%) RSS 13088 (115.86%)
> 1 SZ 27408 (107.84%) RSS 13292 (117.67%)
> 11000 SZ 27616 (108.66%) RSS 13500 (119.51%)
> 12000 SZ 27816 (109.44%) RSS 13700 (121.28%)
>
>
>
> Environment:
>
> SuSE SLES 8
> Linux Kernel 2.4.19
> DB2/LINUX 8.1.3 (also testet Fixpack 2 (8.1.2))
> Perl 5.8.0
> DBI 1.38 (also tested 1.20 - 1.37)
> DBD::DB2 0.76 (also tested 0.75)
>
>
memory leak with DBD::DB2
Hi all,
I'm working in a database migration project (Sybase ASE -> DB2 V8) with perl, DBI and
DBD::DB2 and we have a memory leak problem when we run a lot of select statements
during data import/export.
We tested the System with different combinations of DBI (1.20 - 1.38), DBD::DB2
(0.75-0.76) and DB2 V8.1.3 Fixpacks (FP2 + FP3).
All test results in a more or less large memory leak (see Script output).
Someone can help me or should we go back to Sybase ASE.
Rgds,
Ruediger
Script:
--
#!/usr/bin/perl
use strict;
use locale;
use DBI;
my $dbh = DBI->connect('dbi:DB2:xxx','xxx','xxx',{AutoCommit => 1,});
sub getps {
my @a = split(/ +/,`ps auxww | grep _00_xx_test5.pl | grep -v grep`);
return ($a[4],$a[5],$a[1]);
}
my @procorg;
sub ps {
my @proc = getps();
my $d1 = sprintf("%3.2f",$proc[0]/$procorg[0]*100.0);
my $d2 = sprintf("%3.2f",$proc[1]/$procorg[1]*100.0);
return "SZ ".($proc[0])." ($d1%)RSS ".($proc[1])." ($d2%)";
}
my $c = 0;
while (1) {
my @RESULT = ();
$c++;
# select 13 records (ID, NAME, ID_TYPE)
my $st = $dbh->prepare('select * from ut_attribute');
$st->execute();
# test 1
# --
# while (my $ref = $st->fetchrow_hashref()) {
#push (@RESULT, $ref);
# }
# test 2
# --
# my $ref = $st->fetchall_hashref('ID');
# test 3
# --
while (my $hashref = $sth->fetchrow_hashref) {
}
$st->finish();
if ($c==1) {
@procorg = getps();
print "0".ps()."\n";
}
elsif ($c % 1000 == 0) {
print "$c ".ps()."\n";
}
}
$dbh->disconnect;
Script output:
-
with DBD::DB2 0.76
--
0 SZ 25412 (100.00%) RSS 11300 (100.00%)
1000 SZ 26196 (103.09%) RSS 12088 (106.97%)
2000 SZ 27028 (106.36%) RSS 12920 (114.34%)
3000 SZ 27860 (109.63%) RSS 13752 (121.70%)
4000 SZ 28692 (112.91%) RSS 14584 (129.06%)
5000 SZ 29524 (116.18%) RSS 15416 (136.42%)
6000 SZ 30356 (119.46%) RSS 16248 (143.79%)
7000 SZ 31188 (122.73%) RSS 17080 (151.15%)
8000 SZ 32020 (126.00%) RSS 17912 (158.51%)
9000 SZ 32852 (129.28%) RSS 18744 (165.88%)
1 SZ 33684 (132.55%) RSS 19576 (173.24%)
11000 SZ 34512 (135.81%) RSS 20404 (180.57%)
with DBD::DB2 0.75
--
0 SZ 25416 (100.00%) RSS 11296 (100.00%)
1000 SZ 25572 (100.61%) RSS 11456 (101.42%)
2000 SZ 25776 (101.42%) RSS 11660 (103.22%)
3000 SZ 25980 (102.22%) RSS 11864 (105.03%)
4000 SZ 26184 (103.02%) RSS 12068 (106.83%)
5000 SZ 26388 (103.82%) RSS 12272 (108.64%)
6000 SZ 26592 (104.63%) RSS 12476 (110.45%)
7000 SZ 26796 (105.43%) RSS 12680 (112.25%)
8000 SZ 27000 (106.23%) RSS 12884 (114.06%)
9000 SZ 27204 (107.03%) RSS 13088 (115.86%)
1 SZ 27408 (107.84%) RSS 13292 (117.67%)
11000 SZ 27616 (108.66%) RSS 13500 (119.51%)
12000 SZ 27816 (109.44%) RSS 13700 (121.28%)
Environment:
SuSE SLES 8
Linux Kernel 2.4.19
DB2/LINUX 8.1.3 (also testet Fixpack 2 (8.1.2))
Perl 5.8.0
DBI 1.38 (also tested 1.20 - 1.37)
DBD::DB2 0.76 (also tested 0.75)
Re: Memory leak?: execute() with bind values
On Thu, 18 Sep 2003, Thomas L. Shinnick wrote: > Been around the block on this one so I might be ... confusing. > > I believe that the single prepare() with placeholders and multiple > execute()s with bind values has a memory leak. Program memory size > grows in proportion to number of INSERT's and average data size. > Memory is not released until program termination. This has been > observed on at least AS Perl 5.6.1 and 5.8.0 and Perl 5.6.1 on RH. > > Switching to the use of do() to accomplish INSERT's, even with bind > values, cures the problem. Backtracking to a previous version of > DBD::mysql (2.1026 vs. 2.9002) also makes my symptoms disappear. > Try DBD::mysql 2.9003_1. There is a memory leak in bind_param for DBD::mysql 2.9002. FYI the entry from the changelog: * Fix for memory leak in bind_param() introduced in 2.9002 (2.9002 changed bind_param so that changing the value of a scalar after binding would not affect what was passed to execute eg: $sth->bind_param(1,$foo); $foo = 'bar' $sth->execute() -- $foo would contain 'bar') [reported by <[EMAIL PROTECTED]>] > People have observed similar problems using DBI 1.35 and 1.37 > against versions of three different drivers, MySQL, Pg, and SQLite. > With similar problems in each of these drivers I'm fearing either a > basic disagreement has developed between DBI/DBD modules or some > common mistake in DBD's. I saw a comment about it for DBD::Pg in 1.22, but the next release 1.31 will have a pretty much full rewrite of prepare, bind_pram, & execute, so the search for leaks can begin anew :) Rudy
Re: Memory leak?: execute() with bind values
Thomas L. Shinnick said: > I believe that the single prepare() with placeholders and multiple > execute()s with bind values has a memory leak. Program memory size > grows in proportion to number of INSERT's and average data size. > Memory is not released until program termination. This has been > observed on at least AS Perl 5.6.1 and 5.8.0 and Perl 5.6.1 on RH. [snip] > People have observed similar problems using DBI 1.35 and 1.37 > against versions of three different drivers, MySQL, Pg, and SQLite. > With similar problems in each of these drivers I'm fearing either a > basic disagreement has developed between DBI/DBD modules or some > common mistake in DBD's. I don't know if this will help, but I am unable to reproduce your results with DBI 1.34, Perl 5.8.0, PostgreSQL 7.3.4, and DBD::Pg 1.24. I used the code you posted, except the DROP TABLE, CREATE TABLE, and, of course, the connect(). The memory usage stays constant through the entire 5 INSERT loop (which actually takes much longer than I thought it would). Colin -- Colin W. Wetherbee http://hydrogen.denterprises.org/
Memory leak?: execute() with bind values
Been around the block on this one so I might be ... confusing.
I believe that the single prepare() with placeholders and multiple
execute()s with bind values has a memory leak. Program memory size
grows in proportion to number of INSERT's and average data size.
Memory is not released until program termination. This has been
observed on at least AS Perl 5.6.1 and 5.8.0 and Perl 5.6.1 on RH.
Switching to the use of do() to accomplish INSERT's, even with bind
values, cures the problem. Backtracking to a previous version of
DBD::mysql (2.1026 vs. 2.9002) also makes my symptoms disappear.
People have observed similar problems using DBI 1.35 and 1.37
against versions of three different drivers, MySQL, Pg, and SQLite.
With similar problems in each of these drivers I'm fearing either a
basic disagreement has developed between DBI/DBD modules or some
common mistake in DBD's.
Original posting to PerlMonks thinking Class::DBI was to blame:
http://www.perlmonks.com/index.pl?node_id=292065 and replies
Postings to the Class::DBI maillist, during which I reproduced
the problem using plain DBI prepare/execute:
http://groups.kasei.com/mail/arc/cdbi-talk/2003-09/msg00164.html
http://groups.kasei.com/mail/arc/cdbi-talk/2003-09/msg00165.html
http://groups.kasei.com/mail/arc/cdbi-talk/2003-09/msg00166.html
http://groups.kasei.com/mail/arc/cdbi-talk/2003-09/msg00167.html
A program as simple as the following will show the problem:
(Please excuse the style - it's been twacked repeatedly)
-8<-8<---
my $dsn = 'dbi:mysql:test';
my %dbi_opts = ( AutoCommit => 1, RaiseError => 1 );
$dbh = DBI->connect( $dsn, undef, undef, \%dbi_opts );
unless( $dbh ) {
die "*Error: unable to connect to database: '%s'\n", $DBI::errstr;
}
$dbh->do( 'DROP TABLE IF EXISTS test1' );
$dbh->do( <prepare( 'INSERT INTO test1 (id,logtext) VALUES (?,?)' );
my $newentry;
for( my $i=0 ; $i<5 ; ++$i ) {
$sth->execute( $i, 'faked'x50 );
}
my( $count ) = $dbh->selectrow_array(
'SELECT count(*) FROM test1' );
printf " Record count '%d'\n", $count;
-8<-8<---
Please, somebody tell me I just didn't read some FM detail.
Memory Leak - DBD::mysql
I know this was discussed before, but I don't if anything came out of it. I upgraded one of our servers to DBD::mysql 2.9002 and suddenly one of our programs grows in memory until it runs out. I ran the same program on my server which had 2.1020, no problems, then I upgraded to version 2.9002 and the proces started "eating" up memory. Basically all the code is doing is reading a record in, pulling a key, using a prepared sth, an execute and fetch. I'm going to switch the production server to a earlier version of DBD::mysql. Let me know if or how I can help trace this. Thanks, STH -- E-Mail: [EMAIL PROTECTED] Date: 07-Aug-2003 Time: 06:38:15 --
RE: Memory leak in selectall_hashref suspected....
Hi TIM,
I've tried your fix and now this is OK.
thanks for your help.
rgds,
David
-Original Message-
From: Tim Bunce [mailto:[EMAIL PROTECTED]
Sent: vendredi 4 juillet 2003 14:22
To: Guillaume, David [CTF:8D60:EXCH]
Cc: 'Tim Bunce'; '[EMAIL PROTECTED]'
Subject: Re: Memory leak in selectall_hashref suspected
On Fri, Jul 04, 2003 at 09:22:07AM +0200, David Guillaume wrote:
> Thanks for information, I will try that. I suppose it's in a C file code,
> but which one ?
Ooops, sorry, it's DBI.xs
> I have some memory leak in our application, I've reduced our code ( we
have
> a big loop with insert) so now I hve a little test code just with a
> selectall_hashref but I don't have quantify this leak. If I have time to
do
> test today I will have this information this evening or last week.
Thanks.
Tim.
> rgds,
>
> David
>
> -Original Message-
> From: Tim Bunce [mailto:[EMAIL PROTECTED]
> Sent: jeudi 3 juillet 2003 22:11
> To: Guillaume, David [CTF:8D60:EXCH]
> Cc: '[EMAIL PROTECTED]'
> Subject: Re: Memory leak in selectall_hashref suspected
>
>
> On Thu, Jul 03, 2003 at 02:05:22PM +0200, David Guillaume wrote:
> >
> > The memory leak is in DBI.pm at the fetchall_hashref line 3:
> > my $names_hash = $sth->FETCH("${hash_key_name}_hash");
>
> How did you isolate that?
>
> Hoe big is the leak? (What amount per what action?)
>
> > Someone, know where I can found the code for this FETCH method ? Is it
> > depending of the used database ?
> > Someone can help me ??
>
> Try changing the code near line 1479 from
>
> else
> hv_store(hv, name, SvCUR(sv), newSViv(i), 0);
> to
> else {
> sv_2mortal(sv);
> hv_store(hv, name, SvCUR(sv), newSViv(i), 0);
> }
>
> Please let me know if that helps.
>
> Tim.
>
> p.s. The line number may be a bit different.
>
> > rgds,
> >
> > David
> > -Original Message-
> > From: Guillaume, David [CTF:8D60:EXCH]
> > Sent: jeudi 3 juillet 2003 12:28
> > To: '[EMAIL PROTECTED]'
> > Subject: Memory leak in selectall_hashref suspected
> >
> >
> > Hi all,
> >
> > I'm working in a project with perl and postgres database on a SUN
server.
> We
> > work with the DBI v1.37 and we have a memory leak problem when we use
the
> > selecall_hashref method.
> >
> > When I replace this method a while loop which return the same result (an
> > hash ref with result query), I have not this memory leak. So I quite
sure
> > the problem is in this method, but I can't found really where.
> >
> > Have you got any information about this ? I'm trying to investigate more
> in
> > the lib.
> >
> > Rgds,
> >
> > David
