Vendor JAVA Apps eating up Shared Memory.

We have a couple of Vendor JAVA applications that are eating up shared memory every time they execute there SQL calls. They have their owe pseudo session pooling, but each session increases in shared memory and doesn't reuse pre-allocated memory and gets out of hand. They pointed me to the information below, but wanted to know if anyone else has run into this issue and fix. I was also told that they should be using prepares SQL instead of dynamic SQL. Please explain. Resident Shared-Memory Segments The operating system, as it switches between the processes running on the system, normally swaps the contents of portions of memory to disk. When a portion of memory is designated as resident, however, it is not swapped to disk. Keeping frequently accessed data resident in memory improves performance because it reduces the number of disk I/O operations that would otherwise be required to access that data. The database server requests that the operating system keep the virtual portions in physical memory when the following two conditions exist: The operating system supports shared-memory residency. The RESIDENT parameter in the ONCONFIG file is set to -1 or a value that is greater than 0. Warning: You must consider the use of shared memory by all applications when you consider whether to set the RESIDENT parameter to -1. Locking all shared memory for the use of the Informix database server can adversely affect the performance of other applications, if any, on the same computer. For more information on the RESIDENT configuration parameter, refer to the Administrator's Reference. 7-16 IBM Informix Dynamic Server Administrator's Guide ************************************** Ernie Knox Sears Holding Co. IT Database Administrator Specialist IT Service Management, Strategy & Architecture 3333 Beverly Rd., B4-266A Hoffman Estates, IL. 60179 Office: (847) 286-5735 Fax: (847) 645-3874 Pager: (800) 759-8352 Pin#: 7271042 Email: eknox@sears.com " It's always a great day to watch Football ! " **************************************

Ernest- You didn't include your Informix and OS versions. Not reusing memory after a session is closed sounds like a memory leak type of bug. If you send your versions, someone may be able to peg which one and which release has a fix. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Knox, Ernest > Sent: Thursday, September 20, 2007 1:06 PM > To: ids@iiug.org > Subject: Vendor JAVA Apps eating up Shared Memory. [9981] > > We have a couple of Vendor JAVA applications that are eating up shared > memory every time they execute there SQL calls. They have their owe > pseudo session pooling, but each session increases in shared memory and > doesn't reuse pre-allocated memory and gets out of hand. > > They pointed me to the information below, but wanted to know if anyone > else has run into this issue and fix. > > I was also told that they should be using prepares SQL instead of > dynamic SQL. Please explain. > > Resident Shared-Memory Segments > > The operating system, as it switches between the processes running on > the system, normally swaps the contents of portions of memory to disk. > When a portion of memory is designated as resident, however, it is not > swapped to disk. Keeping frequently accessed data resident in memory > improves performance because it reduces the number of disk I/O > operations that would otherwise be required to access that data. > > The database server requests that the operating system keep the virtual > portions in physical memory when the following two conditions exist: > > The operating system supports shared-memory residency. > > The RESIDENT parameter in the ONCONFIG file is set to -1 or a value > that is greater than 0. > > Warning: You must consider the use of shared memory by all applications > when you consider whether to set the RESIDENT parameter to -1. Locking > all shared memory for the use of the Informix database server can > adversely affect the performance of other applications, if any, on the > same computer. > > For more information on the RESIDENT configuration parameter, refer to > the Administrator's Reference. > > 7-16 IBM Informix Dynamic Server Administrator's Guide > > ************************************** > > Ernie Knox > > Sears Holding Co. > > IT Database Administrator Specialist > > IT Service Management, Strategy & Architecture > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Fax: (847) 645-3874 > > Pager: (800) 759-8352 Pin#: 7271042 > > Email: eknox@sears.com > > " It's always a great day to watch Football ! " > > ************************************** > > > ************************************************************************ ** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum.

Environment: OS is SunOS 5.8 Informix versions are IDS 7.31.UD8, IDS 9.21.UC6, and 10.00.UC6. Thanks, ************************************** Ernie Knox Sears Holding Co. IT Database Administrator Specialist IT Service Management, Strategy & Architecture 3333 Beverly Rd., B4-266A Hoffman Estates, IL. 60179 Office: (847) 286-5735 Fax: (847) 645-3874 Pager: (800) 759-8352 Pin#: 7271042 Email: eknox@sears.com " It's always a great day to watch Football ! " ************************************** -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Everett Mills Sent: Thursday, September 20, 2007 1:44 PM To: ids@iiug.org Subject: RE: Vendor JAVA Apps eating up Shared Memory. [9982] Ernest- You didn't include your Informix and OS versions. Not reusing memory after a session is closed sounds like a memory leak type of bug. If you send your versions, someone may be able to peg which one and which release has a fix. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Knox, Ernest > Sent: Thursday, September 20, 2007 1:06 PM > To: ids@iiug.org > Subject: Vendor JAVA Apps eating up Shared Memory. [9981] > > We have a couple of Vendor JAVA applications that are eating up shared > memory every time they execute there SQL calls. They have their owe > pseudo session pooling, but each session increases in shared memory and > doesn't reuse pre-allocated memory and gets out of hand. > > They pointed me to the information below, but wanted to know if anyone > else has run into this issue and fix. > > I was also told that they should be using prepares SQL instead of > dynamic SQL. Please explain. > > Resident Shared-Memory Segments > > The operating system, as it switches between the processes running on > the system, normally swaps the contents of portions of memory to disk. > When a portion of memory is designated as resident, however, it is not > swapped to disk. Keeping frequently accessed data resident in memory > improves performance because it reduces the number of disk I/O > operations that would otherwise be required to access that data. > > The database server requests that the operating system keep the virtual > portions in physical memory when the following two conditions exist: > > The operating system supports shared-memory residency. > > The RESIDENT parameter in the ONCONFIG file is set to -1 or a value > that is greater than 0. > > Warning: You must consider the use of shared memory by all applications > when you consider whether to set the RESIDENT parameter to -1. Locking > all shared memory for the use of the Informix database server can > adversely affect the performance of other applications, if any, on the > same computer. > > For more information on the RESIDENT configuration parameter, refer to > the Administrator's Reference. > > 7-16 IBM Informix Dynamic Server Administrator's Guide > > ************************************** > > Ernie Knox > > Sears Holding Co. > > IT Database Administrator Specialist > > IT Service Management, Strategy & Architecture > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Fax: (847) 645-3874 > > Pager: (800) 759-8352 Pin#: 7271042 > > Email: eknox@sears.com > > " It's always a great day to watch Football ! " > > ************************************** > > > ************************************************************************ ** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, Not sure if I can help you, cause you gave too little information about the JAVA apps and their session pool. Just one thing I have been running in in the past: In Java, you have to close all statements, preparedstatments, resultsets etc. to free up shared memory. In case you have unnamed statements in the application, each call will prepare internally a cursor with a unique name. In case the statement is not closed explicitly (it is not auto-closed on commit or rollback !), the connected cursor on the database side will stay open and allocate memory until the connection is really closed (which is not the case, cause you are using a pool), or the statement is explicitly closed. Most likely, this is not a database but an application problem, so you have to search there. Be sure to use the latest version of JDBC driver (should be min. 3.00JC3 or later (I think in IDS 11.1 they have a newer one). So, either the pool should keep track of all open statements and close them before reusing the connection, or the programmers should explicitly close each statement (which is likely to be forgotten or the program never reaches the close because of an exception). In case you have no access to sourcecode, there is another way to get rid of this problem. Use the autofree feature of Informix: jdbc:informix-sqli://123.45.67.89:1533:INFORMIXSERVER=myserver; user=rdtest;password=test;ifx_autofree=true This works with 7.23+ or 9.x+ servers according to the JDBC documentation (the URL is taken from the jdbc4pg.pdf document in the JDBC package). I did not use it myself, cause I like the code to be correct, but this could help you. Another way could be with environment variables. I have found the following example (driver ignores user shell environment, you have to program this): pr = new Properties(); pr.put("AUTOFREE","1"); // Auto close cursors after last row is read pr.put("OPTOFC","1"); // Round trip (wire) to close cursors unnessacary c = DriverManager.getConnection( informixConnectionURL, pr ); If you do not want to modify the application (either decompile if it is not your source or use aspects to inject code ...), maybe you should consult the vendor and give him some hints what could be wrong. Hope this helps, Marcus -----Original Message----- From: Knox, Ernest [mailto:eknox@searshc.com] Sent: Thursday, September 20, 2007 8:06 PM To: ids@iiug.org Subject: Vendor JAVA Apps eating up Shared Memory. [9981] We have a couple of Vendor JAVA applications that are eating up shared memory every time they execute there SQL calls. They have their owe pseudo session pooling, but each session increases in shared memory and doesn't reuse pre-allocated memory and gets out of hand. They pointed me to the information below, but wanted to know if anyone else has run into this issue and fix. I was also told that they should be using prepares SQL instead of dynamic SQL. Please explain. Resident Shared-Memory Segments The operating system, as it switches between the processes running on the system, normally swaps the contents of portions of memory to disk. When a portion of memory is designated as resident, however, it is not swapped to disk. Keeping frequently accessed data resident in memory improves performance because it reduces the number of disk I/O operations that would otherwise be required to access that data. The database server requests that the operating system keep the virtual portions in physical memory when the following two conditions exist: The operating system supports shared-memory residency. The RESIDENT parameter in the ONCONFIG file is set to -1 or a value that is greater than 0. Warning: You must consider the use of shared memory by all applications when you consider whether to set the RESIDENT parameter to -1. Locking all shared memory for the use of the Informix database server can adversely affect the performance of other applications, if any, on the same computer. For more information on the RESIDENT configuration parameter, refer to the Administrator's Reference. 7-16 IBM Informix Dynamic Server Administrator's Guide ************************************** Ernie Knox Sears Holding Co. IT Database Administrator Specialist IT Service Management, Strategy & Architecture 3333 Beverly Rd., B4-266A Hoffman Estates, IL. 60179 Office: (847) 286-5735 Fax: (847) 645-3874 Pager: (800) 759-8352 Pin#: 7271042 Email: eknox@sears.com " It's always a great day to watch Football ! " ************************************** ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.