Hi All, I've been catching up on all the action on the list that ocurred while I was out on vacation and wanted to comment briefly on this thread regarding connection pools, connection leaks and Mifos behavior.
First off, thanks to everyone on the list who contributed to the discussion on this-- it is an important issue. And complements to the various contributors for digging in on this. I don't have a simple solution to the problem to offer up, but just wanted to confirm what others have already uncovered: Connection pools * Most (but not all) Mifos access to the database is via Hibernate * There is currently no connection pool set up at the Hiberante level * We previously had identified the lack of connection pooling as a problem and it is on the list of improvements to make for Mifos * c3p0 (as explored by Sam Lee) is a good candidate to use and is the connection pooling solution we have been wanting to try * since without a connection pool there is a bottleneck for long running actions, a workaround was implemented by for the 1.0 version of Mifos to provide a separate connection pool for reporting actions (which in general are the longest running actions). A general connection pooling solution was not added at that point because of the amount of work anticipated in integrating it into Mifos (as Sam has identified below). Connection leaks Transaction and connection management in Mifos is an area we have targeted for refactoring and improvement. The current implementation does not make it easy to guarantee that that Hibernate sessions are properly closed and that connections are properly freed. As noted, not all connections are managed through Hibernate, so this is another area when improper connection management could lead to leaks. There are still decisions to be taken as far as what the best course of action is to resolve this issue. In the short term that may involve just trying to track down existing session/connection management issues in the code and fixing them (as Sam mentions in points #3 and #4 below). Ultimately, we need to improve the way we manage sessions and connections so that it is easy for a developer to do the right thing and hard to do the wrong thing. We're working on it and considering making use of frameworks like Spring to provide a clean way to manage sessions/connections. Cheers, --Van > Over the weekend I took a stab to change the mifos' hibernate config > to use c3p0 connection pool (and turn on c3p0's debugging tools). > Here is the finding so far: > > 1. a casual simple test of the mifos webapp (simple clicking around > and creation some office, etc.) shows that the config works > functionally (not necessarily in all cases) > > 2. I ran the unit test suite using the c3p0 config. It resulted in > fair amount of errors, many of which seem to be due to some cascading > effect. The first one that fails shows some weird persistence error > that some SQL update fails, indicating that somehow some earlier SQL > operation was not committed. At this point, it is not clear to me > whether these errors indicate some existing issues on mifos code > (that got masked previously), or it might be due to bugs in the > hibernate / c3p0 we're using. > > I suppose we could pursue some of the followings: > 1. have an experimental c3p0 config run on a test server and put some > load. See if we could reproduce the problem, this time with > stacktrace on the problem. > > 2.someone look at the unit test failures using c3p0 conifg (attached > the first two here) to see if they could find any hint. > > 3. code review / inspection for raw SQL leaks. As Tom mentioned, some > of the use of raw SQL are problematic. I did a quick scan on the use > of raw SQL connection, and did see some problematic usage (such as > ResultSet, statements, not being closed defensively) . > > 4. code review / inspection for hibernate leaks: for the codes that > use hibernate, it is still possible to have leaks if some codes > forgot to close a session. > > Thoughts? > - sam > > > Note: the stack traces of the first two test failed due to the new > c3p0 config. > > <testcase > classname="org.mifos.application.customer.persistence.TestCustomerPersis tence" > name="testNumberOfMeetingsAttended" time="1.052"> > > <error message="Batch update returned unexpected row count from > update: 0 actual row count: 0 expected: 1" > type="org.hibernate.StaleStateException">org.hibernate.StaleStateExcepti on: > Batch update returned unexpected row count from update: 0 actual row > count: 0 expected: 1 > > at > org.hibernate.jdbc.BatchingBatcher.checkRowCount(BatchingBatcher.java:88 ) > > at > org.hibernate.jdbc.BatchingBatcher.checkRowCounts(BatchingBatcher.java:7 4) > > at > org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:5 7) > > at > org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:154 ) > > at > org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java :71) > > at > org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java :66) > > at > org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher .java:130) > > at > org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPe rsister.java:1924) > > at > org.hibernate.persister.entity.BasicEntityPersister.updateOrInsert(Basic EntityPersister.java:1880) > > at > org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPe rsister.java:2120) > > at > org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java: 75) > > at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239) > > at > org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:223) > > at > org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:137) > > at > org.hibernate.event.def.AbstractFlushingEventListener.performExecutions( AbstractFlushingEventListener.java:274) > > at > org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEv entListener.java:27) > > at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:669) > > at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:293) > > at > org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:86 ) > > at > org.mifos.framework.hibernate.helper.HibernateUtil.commitTransaction(Hib ernateUtil.java:230) > > at > org.mifos.application.customer.persistence.TestCustomerPersistence.testN umberOfMeetingsAttended(TestCustomerPersistence.java:397) > > </error> > > </testcase> > > <testcase > classname="org.mifos.application.customer.persistence.TestCustomerPersis tence" > name="testNumberOfMeetingsMissed" time="0.04"> > > <error message="Row was updated or deleted by another transaction > (or unsaved-value mapping was incorrect): > [org.mifos.application.customer.client.business.ClientBO#101]" > type="org.hibernate.StaleObjectStateException">org.hibernate.StaleObject StateException: > Row was updated or deleted by another transaction (or unsaved-value > mapping was incorrect): > [org.mifos.application.customer.client.business.ClientBO#101] > > at > org.hibernate.persister.entity.BasicEntityPersister.check(BasicEntityPer sister.java:1416) > > at > org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPe rsister.java:1956) > > at > org.hibernate.persister.entity.BasicEntityPersister.updateOrInsert(Basic EntityPersister.java:1880) > > at > org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPe rsister.java:2120) > > at > org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java: 75) > > at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239) > > at > org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:223) > > at > org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:137) > > at > org.hibernate.event.def.AbstractFlushingEventListener.performExecutions( AbstractFlushingEventListener.java:274) > > at > org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEv entListener.java:27) > > at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:669) > > at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:293) > > at > org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:86 ) > > at > org.mifos.framework.persistence.TestObjectPersistence.persist(TestObject Persistence.java:104) > > at > org.mifos.framework.util.helpers.TestObjectFactory.createMeeting(TestObj ectFactory.java:1143) > > at > org.mifos.application.customer.persistence.TestCustomerPersistence.creat eCenter(TestCustomerPersistence.java:1227) > > at > org.mifos.application.customer.persistence.TestCustomerPersistence.creat eCenter(TestCustomerPersistence.java:1223) > > at > org.mifos.application.customer.persistence.TestCustomerPersistence.testN umberOfMeetingsMissed(TestCustomerPersistence.java:413) > > </error> > > </testcase> > > > On Nov 16, 2007 5:08 PM, Tom Bostelmann <[EMAIL PROTECTED]> wrote: >> I haven't had much of a chance to look into this. But do a search in >> the code for 'java.sql.Connection'. This is probably the culprit. >> >> Based on what I've seen in the code, I would look there before I >> started worrying about Hibernate. >> >> From what I've seen, we're bypassing Hibernate in the reports >> functionality as well as other places. So, Connection management is >> most likely done explicitly there. >> >> If you're interested in gathering Hibernate statistics as well, I >> added a rough implementation of the StatisticsService in the >> MifosTestCase class. This keeps track of open connections and other >> very useful statistics (like the number of commits and such). >> >> Thanks for all the input everyone! :) -Tom >> >> >> >> On Nov 16, 2007 10:16 AM, Sam Lee <[EMAIL PROTECTED]> wrote: >>> >>> >>> >>> On Nov 15, 2007 9:50 PM, Amy Bensinger (Contractor) >>> >>> <[EMAIL PROTECTED]> wrote: >>>> What might also be interesting is if any DBA-types have workaround >>>> suggestions/scripts while the longer term solutions are considered? >>>> >>>> >>> >>> Workarounds: an ugly but fairly effective workaround is to >>> periodically force the application (mifos in this case) closes the >>> connections. >>> >>> Option 1: If a connection pool is used, one can also try to >>> reconfigure the connection pool to force connections to be closed >>> after certain amount of time. E.g., tricks one can use with c3p0: >>> >>> >> http://www.mchange.com/projects/c3p0/index.html#configuring_to_debug_and _workaround_broken_clients >>> >>> >>> Option 2: the most brute-force way would be to have a script to >>> periodically restarting the app server (in off peak hours, more >>> leeway if the deployment has multiple servers so that the restart >>> can be staggered). >>> >>> To those who knows mifos config better: is the db connection >>> properties all set in the confi/hibernate.properties and the >>> hibernate.cfg.xml (somewhere under the source tree) (at least for >>> the GK deployment)? If so, I could take a stab to figure out if we >>> could have it reconfigured to use c3p0 connection pool to timeout >>> the connections. >>> >>> Let me know. ------------------------------------------------------------------------- SF.Net email is sponsored by: The Future of Linux Business White Paper from Novell. From the desktop to the data center, Linux is going mainstream. Let it simplify your IT future. http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4
