IDS Forum

RE: Vendor JAVA Apps eating up Shared Memory.

Posted By: Marcus Haarmann
Date: Thursday, 20 September 2007, at 3:17 p.m.

In Response To: Vendor JAVA Apps eating up Shared Memory. (Knox, Ernest)

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.

Messages In This Thread

• Vendor JAVA Apps eating up Shared Memory.
  Knox, Ernest -- Thursday, 20 September 2007, at 2:05 p.m.
  • RE: Vendor JAVA Apps eating up Shared Memory.
    Everett Mills -- Thursday, 20 September 2007, at 2:44 p.m.
    • RE: Vendor JAVA Apps eating up Shared Memory.
      Knox, Ernest -- Thursday, 20 September 2007, at 2:59 p.m.
  • RE: Vendor JAVA Apps eating up Shared Memory.
    Marcus Haarmann -- Thursday, 20 September 2007, at 3:17 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.