Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0012418Openbravo ERPA. Platformpublic2010-02-23 15:232022-02-01 08:08
Reporterfcatrin 
Assigned ToTriage Platform Base 
PriorityhighSeveritymajorReproducibilitysometimes
StatusacknowledgedResolutionopen 
PlatformOS5OS Version
Product Versionmain 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0012418: 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.
Additional Information
TagsPerformance
Relationships
related to defect 0026030 closed alostale OOM Error in Oracle due to prepared statements 
Attached Filespatch ob-maxStmtPool.patch (3,399) 2010-02-23 15:23
https://issues.openbravo.com/file_download.php?file_id=2271&type=bug
Issue History
Date ModifiedUsernameFieldChange
2010-02-23 15:23fcatrinNew Issue
2010-02-23 15:23fcatrinAssigned To => alostale
2010-02-23 15:23fcatrinFile Added: ob-maxStmtPool.patch
2010-02-23 15:55fcatrinNote Added: 0024752
2010-02-24 10:30pjuvaraPrioritynormal => urgent
2010-03-01 10:34alostaleStatusnew => scheduled
2010-03-01 10:34alostaleAssigned Toalostale => shuehner
2010-03-01 10:34alostalefix_in_branch => pi
2010-03-03 18:09shuehnerNote Added: 0025024
2010-03-03 20:04fcatrinNote Added: 0025036
2010-04-16 16:05jpabloaeIssue Monitored: jpabloae
2010-05-13 19:20iatienzaIssue Monitored: iatienza
2011-10-28 20:37iciordiaTypedefect => design defect
2011-10-28 20:37iciordiafix_in_branchpi =>
2011-10-28 20:37iciordiaAssigned Toshuehner => alostale
2012-04-13 10:51alostaleTag Attached: Performance
2012-04-27 11:47Xpand-ITIssue Monitored: Xpand-IT
2012-09-24 23:37AugustoMauchNote Added: 0052503
2012-09-24 23:37AugustoMauchPriorityurgent => high
2013-05-22 06:36eintelauIssue Monitored: eintelau
2014-03-18 15:51alostaleRelationship addedrelated to 0026030
2014-03-18 15:52alostaleTriggers an Emergency Pack => No
2014-03-18 15:52alostaleTarget Version => PR14Q3
2014-06-25 16:15AugustoMauchTarget VersionPR14Q3 => PR14Q4
2014-10-02 11:10alostaleTarget Version3.0PR14Q4 =>
2017-03-31 14:36alostaleStatusscheduled => acknowledged
2017-04-10 14:34alostaleAssigned Toalostale => platform
2022-02-01 08:08alostaleAssigned Toplatform => Triage Platform Base

Notes
(0024752)
fcatrin   
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   
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   
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   
2012-09-24 23:37   
Effort: 10
Impact: mid
Plan: short