Help on jboss memory usage for persistant sessions

Hello. Informix 11.50.FC9W2 on a linux redhat. We have an application, running through JBOSS server, using persistant connections (connection pool). The issue is: sessions from this pool are constantly consuming 100% more memory, than regular sessions, without using PDQ. It seems that they are using memory, and not freeing it back to the engine (most of all, because they are not using the AUTOFREE parameter in the srings). I´ve already asked the developers to implement this (along with other good parameters), but they are asking me for evidencies that it would solve the trouble. So I´ve got some "onstat -g stm" outputs, and compared them to regular sessions. These sessions are constantly with more than 100 lines, in each session, instead of 1 or 2 of regular ones. Manual says it is only a report for prepared statements, so I am a little confused about it. Is "onstat -g stm" a good evidence about our memory issue? Or can I get another report, explaining how these sessions are using so many memory? Thanks a lot.

Hi, the problem is probably in the application, not in the database. When using a JDBC connection, each object should be closed after usage. ResultSet, PreparedStatement, Connection etc. When using a connection pool, the connection.close() method will return the connection to the pool for reuse. But it will not close ResultSets or Statments. When using a persistence layer like Hibernate or similar, the layer takes care about the statements when the transaction ends. When using native JDBC methods, the programmer has to close everything. AUTOFREE is only a fallback for unclean applications. Normally, JBoss supports setting up the database connections using XML files. There, you can specify JDBC parameters. Maybe you could set AUTOFREE there, but best help would be a good programming style ;) Marcus Haarmann ----- Ursprüngliche Mail ----- Von: "ALEXANDRE MARINI" <alexandre@briug.org> An: ids@iiug.org Gesendet: Montag, 3. Juni 2013 20:25:32 Betreff: Help on jboss memory usage for persistant sessions [30412] Hello. Informix 11.50.FC9W2 on a linux redhat. We have an application, running through JBOSS server, using persistant connections (connection pool). The issue is: sessions from this pool are constantly consuming 100% more memory, than regular sessions, without using PDQ. It seems that they are using memory, and not freeing it back to the engine (most of all, because they are not using the AUTOFREE parameter in the srings). I´ve already asked the developers to implement this (along with other good parameters), but they are asking me for evidencies that it would solve the trouble. So I´ve got some "onstat -g stm" outputs, and compared them to regular sessions. These sessions are constantly with more than 100 lines, in each session, instead of 1 or 2 of regular ones. Manual says it is only a report for prepared statements, so I am a little confused about it. Is "onstat -g stm" a good evidence about our memory issue? Or can I get another report, explaining how these sessions are using so many memory? Thanks a lot. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hello Alexandre, inline... On Mon, Jun 3, 2013 at 7:25 PM, ALEXANDRE MARINI <alexandre@briug.org>wrote: > Hello. > Informix 11.50.FC9W2 on a linux redhat. > > We have an application, running through JBOSS server, using persistant > connections (connection pool). > > The issue is: sessions from this pool are constantly consuming 100% more > memory, than regular sessions, without using PDQ. > What do you consider "regular" sessions? How much memory are we talking about? > > It seems that they are using memory, and not freeing it back to the engine > (most of all, because they are not using the AUTOFREE parameter in the > srings). > > Ie already asked the developers to implement this (along with other good > parameters), but they are asking me for evidencies that it would solve the > trouble. > > So Ie got some "onstat -g stm" outputs, and compared them to regular > sessions. These sessions are constantly with more than 100 lines, in each > session, instead of 1 or 2 of regular ones. > Are the lines "repeated". Ax an example, do you see things like: SELECT * FROM some_table WHERE some_column = ? SELECT * FROM some_table WHERE some_column = ?.... SELECT * FROM some_table WHERE some_column = ? or do you see things like: SELECT * FROM some_table WHERE some_column = 1 SELECT * FROM some_table WHERE some_column = 2 SELECT * FROM some_table WHERE some_column = 3... Manual says it is only a report for prepared statements, so I am a little > confused about it. > > Yes... And in JAVA is very easy to make a mistake and repeat "prepares". > Is "onstat -g stm" a good evidence about our memory issue? Or can I get > another report, explaining how these sessions are using so many memory? > It's just what it is... a list of prepared statements for the session. Depending on what it shows, it can be an evidence of good or bad things. Preparing statements is generally a good thing. But if you put the prepare inside a loop (by mistake) it becomes a nasty thing... The worst case I remember, a session had around 1GB of memory :) Regards -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf307abe87b4c4dd04de46c944

Hello, my friend. Answering your questions: By regular sessions, I mean comparing lower memory usage to the highest memory usage ones: 1) JBOSS regular memory session reports: Total session memory: 77824 (onstat -g ses) onstat -g afr: shows 106 lines of output onstat -g stm: shows no heapsz 2) JBOSS high memory session reports: Total session memory: 6455296 (onstat -g ses) onstat -g afr: shows 12271 lines of output (most lines - more than 90% - aredescribed as ralloc and fragman kinds) onstat -g stm: shows 110 lines of output (Unfortunately, manual just don´t tell anything about ralloc/fragman memory segments) onstat -g stm [high_mem_session] give us all kinds of different queries,instead of following a same pattern, even with different projection clauses, not just different values into where sections. For me, onstat -g afr output is now explaining better what kinds of memory are being used. I will take this new evidence, send them to the developers, making sure they give a high priority to implement the OPTOFC, AUTOFREE, and other good recommendations to their connections. In homolog box, they are still doing tests in several kinds of java codes, to implement those. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: domusonline@gmail.com > Subject: Re: Help on jboss memory usage for persistant .... [30418] > Date: Mon, 3 Jun 2013 17:38:52 -0400 > > Hello Alexandre, > inline... > > On Mon, Jun 3, 2013 at 7:25 PM, ALEXANDRE MARINI <alexandre@briug.org>wrote: > > > Hello. > > Informix 11.50.FC9W2 on a linux redhat. > > > > We have an application, running through JBOSS server, using persistant > > connections (connection pool). > > > > The issue is: sessions from this pool are constantly consuming 100% more > > memory, than regular sessions, without using PDQ. > > > > What do you consider "regular" sessions? > How much memory are we talking about? > > > > > It seems that they are using memory, and not freeing it back to the engine > > (most of all, because they are not using the AUTOFREE parameter in the > > srings). > > > > Ie already asked the developers to implement this (along with other good > > parameters), but they are asking me for evidencies that it would solve the > > trouble. > > > > So Ie got some "onstat -g stm" outputs, and compared them to regular > > sessions. These sessions are constantly with more than 100 lines, in each > > session, instead of 1 or 2 of regular ones. > > > > Are the lines "repeated". Ax an example, do you see things like: > > SELECT * FROM some_table WHERE some_column = ? > SELECT * FROM some_table WHERE some_column = ?> ..... > SELECT * FROM some_table WHERE some_column = ?> > or do you see things like: > > SELECT * FROM some_table WHERE some_column = 1 > SELECT * FROM some_table WHERE some_column = 2 > SELECT * FROM some_table WHERE some_column = 3> .... > > Manual says it is only a report for prepared statements, so I am a little > > confused about it. > > > > Yes... And in JAVA is very easy to make a mistake and repeat "prepares". > > > Is "onstat -g stm" a good evidence about our memory issue? Or can I get > > another report, explaining how these sessions are using so many memory? > > > > It's just what it is... a list of prepared statements for the session. > Depending on what it shows, it can be an evidence of good or bad things. > Preparing statements is generally a good thing. But if you put the prepare > inside a loop (by mistake) it becomes a nasty thing... > > The worst case I remember, a session had around 1GB of memory :) > > Regards > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --20cf307abe87b4c4dd04de46c944 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >