Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0012418
TypeCategorySeverityReproducibilityDate SubmittedLast Update
design defect[Openbravo ERP] A. Platformmajorsometimes2010-02-23 15:232022-02-01 08:08
ReporterfcatrinView Statuspublic 
Assigned ToTriage Platform Base 
PriorityhighResolutionopenFixed in Version
StatusacknowledgedFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseOracleJava version
OS VersionDatabase versionAnt version
Product VersionmainSCM revision 
Review Assigned To
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0012418: Cached prepared statements consumes tons of memory

DescriptionEach connection in the connection pool has a prepared statement cache. Current code uses an unlimited size for this cache through a StackKeyedObjectPoolFactory object.

For each prepared statement in the cache there is an open cursor in the database

Depending on how big the connection pool is, the application server dies with an out of memory error, or the database run out of cursors after a few days running.

With only 15 connections in the pool, I detected almost 3000 open cursors in the database, and almost 1GB used only by prepared statements.

I understand that we can assing high amounts of memory to the application server and thousands of open cursors in the database, but as I have checked, the objects retained in memory for the statement cache can go up to 1GB with 15 connections and less than 10 users.
Steps To ReproduceConfigure a connection pool with 15 connections (bbdd.maxConns = 15). If you don't set max cursors to thousands in the database it will reach the limit in a few hours/days, if you set less than 1GB of ram the application server will throw an out of memory error after some hours/days.
Proposed SolutionI wrote a patch in OpenbravoCore to add a new parameter: bbdd.maxStmts

This parameter controls the prepared statement cache for each connection. Possible values are:

 -1 or unspecified : no limit. The actual behavior
  0 : no cache at all
n>0 : cache size n

If the cache is not enough, it will grow temporally and then it will shrink again when the prepared statements get classified as unused for some time.
TagsPerformance
Attached Filespatch file icon ob-maxStmtPool.patch [^] (3,399 bytes) 2010-02-23 15:23 [Show Content]

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0026030 closedalostale OOM Error in Oracle due to prepared statements 

-  Notes
(0024752)
fcatrin (reporter)
2010-02-23 15:55

 I forgot to mention that using a small prepared statement cache (bbdd.maxStmts=20) with maxConn=15, openbravo has been running for almost a week without problems and requiring less than 1GB to run. After all sessions are gone it goes down to only 50MB of memory used.

Without the patch, the customer reported locks and no response from the application every two days.

Almost all memory assigned to the application server (more than 1GB) was used on stalled prepared statements, and the database (which is shared with other applications) got almost 3000 open cursors only to run openbravo.
(0025024)
shuehner (administrator)
2010-03-03 18:09

 Hi fcatrin,

i am just working on this issue... First thanks for your patch but i have a number of questions/things to check:

- For the 1G used on prepared statements you mentioned, is that memory used inside the oracle system or on the java side for the cached statements?
- If its on the java-side how did you map the memory used for objects to the pooledStatements?

Some comments on your patch:
Looks very nice, however i want to check one behavior of the pool/dbcp library used. How it does the selection of the PooledStatement when it does evict some on a completely full cache. According to its doc it evicts the 15% oldest statements. If that 'oldest' is corresponding to usual LRU selections (oldest ones not used) that behavior would fit nicely.

Some notes on the findings i have so far:
I checked the java memory heap usaeg on a number of short and longer running systems using jmap/jhat) and can confirm that the number of PooledStatements does increase over time (in my test-instance to about 7k after once month).
Total memory usage (inside the java-heap) is about 1.6G, with most of that String instances... but the tools didn't allow mapping of referencing classes (users) to the size used by those strings. Only a mapping of instance counts was possible. For instance counts the majority of String were referred to by cached XmlEngine-templates (openbravo templating engine).
(0025036)
fcatrin (reporter)
2010-03-03 20:04

 Hi shuehner!

Thanks for tracking this issue.

1G was used on the java side only. I don't have numbers for the Oracle side.

For diagnostics I have been using two tools : jconsole/jhat from the JDK and Heap Analyzer from IBM. The first one gave me some raw info about the object instances, and there is a view to filter all system classes like Strings (Show instance counts for all classes excluding platform).

The instance count for prepared statements was too high and from there I started digging the dependencies.

Heap Analyzer gives you a leak suspect. In this case the leak suspect was a generic object that holds the connection pools, and if you follow the references you will find that most of the memory used by these objects depends on the references hold by cached prepared statements.

I still have an old snapshot to compare with a new one. You can find both here:

http://www.tuxpan.com/fcatrin/ob [^]

The old snapshot (20100217) was taken when there was no activity in the server. The new snapshot has the patch and was taken with several users connected (20100302)

I also left a snapshot taken with Heap Analyzer on both dumps.

I can't confirm the behaviour of the cache, I just used that class because it can set a limit for the cache's size.

To track down those Strings to the Prepared Statements I recommend you to use IBM Heap Analyzer, it can build a tree view to dig down the memory used and the dependencies between instances. I first tried with jhat but the relationship between the objects that used most of the memory and their collectable root was too far away to track.

http://www.alphaworks.ibm.com/tech/heapanalyzer [^]
(0052503)
AugustoMauch (manager)
2012-09-24 23:37

 Effort: 10
Impact: mid
Plan: short

- Issue History
Date Modified Username Field Change
2010-02-23 15:23 fcatrin New Issue
2010-02-23 15:23 fcatrin Assigned To => alostale
2010-02-23 15:23 fcatrin File Added: ob-maxStmtPool.patch
2010-02-23 15:55 fcatrin Note Added: 0024752
2010-02-24 10:30 pjuvara Priority normal => urgent
2010-03-01 10:34 alostale Status new => scheduled
2010-03-01 10:34 alostale Assigned To alostale => shuehner
2010-03-01 10:34 alostale fix_in_branch => pi
2010-03-03 18:09 shuehner Note Added: 0025024
2010-03-03 20:04 fcatrin Note Added: 0025036
2010-04-16 16:05 jpabloae Issue Monitored: jpabloae
2010-05-13 19:20 iatienza Issue Monitored: iatienza
2011-10-28 20:37 iciordia Type defect => design defect
2011-10-28 20:37 iciordia fix_in_branch pi =>
2011-10-28 20:37 iciordia Assigned To shuehner => alostale
2012-04-13 10:51 alostale Tag Attached: Performance
2012-04-27 11:47 Xpand-IT Issue Monitored: Xpand-IT
2012-09-24 23:37 AugustoMauch Note Added: 0052503
2012-09-24 23:37 AugustoMauch Priority urgent => high
2013-05-22 06:36 eintelau Issue Monitored: eintelau
2014-03-18 15:51 alostale Relationship added related to 0026030
2014-03-18 15:52 alostale Triggers an Emergency Pack => No
2014-03-18 15:52 alostale Target Version => PR14Q3
2014-06-25 16:15 AugustoMauch Target Version PR14Q3 => PR14Q4
2014-10-02 11:10 alostale Target Version 3.0PR14Q4 =>
2017-03-31 14:36 alostale Status scheduled => acknowledged
2017-04-10 14:34 alostale Assigned To alostale => platform
2022-02-01 08:08 alostale Assigned To platform => Triage Platform Base

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker