Re: [Dspace-tech] Cannot get a connection, pool exhausted
On Wed, 2007-04-18 at 13:33 +0100, Richard Jones wrote: > 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. Hmmm... in theory, whilst transactions can 'fail', they should always rollback or commit. If we assume something really obscure isn't happening for now, it leaves us with two possibilities: 1) The Context has dropped out of scope without being cleaned up 2) A thread is still executing, and is still using it's Context / transaction. A few obvious things here spring to mind: 1) Check the logs for Exceptions - one being thrown without a finally to clean up the Context would be a likely (if not only) suspect for a Context being lost. 2) Launch Tomcat under JDK 1.5 with the JMX interface enabled, and attach JConsole to it (JMX adds little overhead to the server, and you don't need to attach JConsole until the problem arises). From JConsole, you can see how many Threads Tomcat is currently using, what status they are in and often a stack trace - so you can see where in the code that Thread is being blocked. (As an aside, we had a problem with a different application, where sooner or later - and often sooner - Tomcat would suddenly grind to a halt and stop serving requests. Using JConsole showed that the Threads were all blocked (and increasing in number with every request), and the point at which most of them was blocked was in trying to obtain a database connection from a custom built pool - and it actually came back to a background thread that was trying to close unwanted connections at the same time the business logic was trying to close the same connection. Both Threads got hung up on each other, and everything else blocked as the pool couldn't give new connections whilst the clean up was in progress.) Also note that JConsole has a button to force garbage collection - this may help in determining if there are dangling Contexts. If they (and therefore the transactions) disappear after forcing the garbage collection, then something was dangling - although that wouldn't tell you why). G - 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 DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
Re: [Dspace-tech] Cannot get a connection, pool exhausted
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]>; >> >> 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 >>>
Re: [Dspace-tech] Cannot get a connection, pool exhausted
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]>; > > 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 WeWeb 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. > > > > > > - >
Re: [Dspace-tech] Cannot get a connection, pool exhausted
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]>; 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 WeWeb 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 DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
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 WeWeb Applications Developer Gwasanaethau Gwybodaeth Information Services Prifysgol Cymru Aberystwyth University of Wales Aberystwyth E-bost / E-mail: [EMAIL PROTECTED] Ffon / Tel: (01970) 622860 _ - 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 DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
[Dspace-tech] Cannot get a connection, pool exhausted
Hi, 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 org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted What is solution of this problem. -- Filbert - 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 DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
