Hi, I just put a page on the wiki with some thoughts and possible ways of debugging this, as it happens:
http://wiki.dspace.org/index.php/Idle_In_Transaction_Problem If you can have a look at the results of the query that this page suggests and post it to the list or the wiki, perhaps we can start to tighten up the database connections. Cheers, Richard > In our experience, this problem appears to be due to a bug somewhere in > freeing connections back to the pool--we tend to see steady linear > growth in the number of 'idle in transaction' connections until we get > this error. These are visible with ps. > > Increasing the number of connections in the pool, for us, only delayed > the occurrence of the problem. Ultimately the number of 'idle in > transaction' connections would climb to the max. > > We put a workaround in place. This is a root crontab entry: > > # kill old 'idle in transaction' postgres processes, leaving up to 10 > * * * * * while /usr/bin/test `/usr/bin/pgrep -f 'idle in transaction' > | /usr/bin/wc -l` -gt 10; do /usr/bin/pkill -o -f 'idle in transaction'; > done > > At one point I was entertaining a theory that the Apache connection pool > manager delivered with DSpace was a stale version. To date, the > workaround has worked so well that I'm not sure that theory has been > fully explored. > > Also, FWIW, there have been lengthy discussions on this list about this > topic already. You would probably find the previous thread useful as I'm > quite sure I'm not retelling everything here. > > Cory Snavely > University of Michigan Library IT Core Services > > On Wed, 2007-04-18 at 12:13 +0530, Filbert Minj wrote: >> Hi Stuart, >> >> Thanks very much for the prompt reply. >> Recently we have upgraded it to Dspace 1.4.1 on RHEL 4 using postgres >> database. >> I made the change in db.maxconnections and I think this should solve the >> problem. >> >> I had forgotten, earlier we had the same problem and did exactly what you >> suggested. >> >> Cheers, >> >> -- >> Filbert >> >> ----- Original Message ----- >> From: "Stuart Lewis [sdl]" <[EMAIL PROTECTED]> >> To: "Filbert Minj" <[EMAIL PROTECTED]>; >> <[email protected]> >> Sent: Wednesday, April 18, 2007 11:32 AM >> Subject: Re: [Dspace-tech] Cannot get a connection, pool exhausted >> >> >>> Hi Filbert, >>> >>>> Has any one faced similar problem. >>>> >>>> WARN org.dspace.app.webui.servlet.DSpaceServlet @ >>>> anonymous:no_context:database_error:org.apache.commons.dbcp.SQLNestedException >>>> : >>>> Cannot get a connection, pool exhausted >>>> >>>> What is solution of this problem. >>> DSpace holds a 'pool' of connections to the database which it reuses. This >>> means it doesn't have the overhead of creating a connection to the >>> database >>> each time it needs to talk to the database. >>> >>> The error message suggests that all of these connections are in use, and >>> it >>> has reached the number of connections that you have said it can have. The >>> default set in [connections]/config/dspace.cfg is: >>> >>> db.maxconnections = 30 >>> >>> There are two reasons that you might be reaching this limit - >>> >>> 1) Your DSpace is very busy (lots of visitors) and there are not enough >>> connections to cope. If your hardware is large enough to cope with number >>> of >>> connections, you could think about increasing the number of connections in >>> the pool. (change the number, restart Tomcat). >>> >>> 2) For some reason, DSpace might not be letting go of some old >>> connections, >>> or they might be stuck in some way. If you are using UNIX and postgres, >>> you >>> should be able to see the connections, and what they are doing, by running >>> a >>> 'ps' on them (make sure you're screen is wide to see what comes at the >>> end >>> of the line). This might show that the connections are stuck - typical >>> state >>> might be 'idle in transaction'. This can also happen if connections to the >>> database are not closed properly by DSpace. >>> >>> Which version / operating system / database do you use? >>> >>> I hope this helps, >>> >>> >>> Stuart >>> _________________________________________________________________ >>> >>> Datblygydd Cymwysiadau'r We Web Applications Developer >>> Gwasanaethau Gwybodaeth Information Services >>> Prifysgol Cymru Aberystwyth University of Wales Aberystwyth >>> >>> E-bost / E-mail: [EMAIL PROTECTED] >>> Ffon / Tel: (01970) 622860 >>> _________________________________________________________________ >>> >>> >>> -- >>> This message has been scanned for viruses and >>> dangerous content by MailScanner, and is >>> believed to be clean. >>> >> >> >> ------------------------------------------------------------------------- >> This SF.net email is sponsored by DB2 Express >> Download DB2 Express C - the FREE version of DB2 express and take >> control of your XML. No limits. Just data. Click to get it now. >> http://sourceforge.net/powerbar/db2/ >> _______________________________________________ >> DSpace-tech mailing list >> [email protected] >> https://lists.sourceforge.net/lists/listinfo/dspace-tech > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by DB2 Express > Download DB2 Express C - the FREE version of DB2 express and take > control of your XML. No limits. Just data. Click to get it now. > http://sourceforge.net/powerbar/db2/ > _______________________________________________ > DSpace-tech mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/dspace-tech -- Richard ------------------------------------------------------------------------ Richard Jones | t: +44 (0)20 759 [48614 / 41815] Web & Database | e: [EMAIL PROTECTED] Technology Specialist | b: http://chronicles-of-richard.blogspot.com/ Imperial College London | ------------------------------------------------------------------------ ------------------------------------------------------------------------- This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ _______________________________________________ DSpace-tech mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/dspace-tech
