> > so it's a mystery why anything should be idle or waiting for more than a > few seconds (timeout above is 180 seconds) >
You should note that when a web requests is processed by DSpace (e.g. an item page view or bitstream download), DSpace will open a database transaction when it starts to process that request. (Most of the time) that database transaction is only closed at the very end when DSpace is ready with processing the web requests. I think this means that if there is a user downloading a large bitstream over a slow internet connection which takes 10 seconds in total to complete, you will see a corresponding database transaction that also lasted for 10 seconds which is mostly idle. But there are also other places that can stretch the transaction time like logging stats in SOLR or the Google Analytics <https://jira.duraspace.org/browse/DS-2904>. Is it then safe to kill those long transactions? It will depend on the type of web request. But for bitstream downloads I think it's safe since we do not access the database anymore after having written all bytes: https://github.com/DSpace/DSpace/blob/dspace-6.2/dspace-xmlui/src/main/java/org/dspace/app/xmlui/cocoon/BitstreamReader.java#L749 [image: logo] Tom Desair 250-B Suite 3A, Lucius Gordon Drive, West Henrietta, NY 14586 Gaston Geenslaan 14, Leuven 3001, Belgium www.atmire.com <http://atmire.com/website/?q=services&utm_source=emailfooter&utm_medium=email&utm_campaign=tomdesair> Op di 20 mrt. 2018 om 15:20 schreef Alan Orth <alan.o...@gmail.com>: > Thanks for the heads up, Mark. I'm curious how killing "idle in > transaction" connections in PostgreSQL itself compares to doing it in the > JDBC pool. I've been doing it in the JDBC pool since our last discussion in > the other thread in early 2018, though it's *way* too complicated for my > liking: > > <Resource name="jdbc/dspaceWeb" auth="Container" > type="javax.sql.DataSource" > driverClassName="org.postgresql.Driver" > ... > removeAbandoned='true' > removeAbandonedTimeout='180' > abandonWhenPercentageFull='50' > jdbcInterceptors='ResetAbandonedTimer' > logAbandoned='true' > testOnBorrow='true' > testOnReturn='true' /> > > I actually don't even feel comfortable killing these connections, but my > DSpace 5.5 repository is very unstable without this precaution and crashes > at the most inconvenient times. We have fast hardware with plenty of 24 GB > of RAM, eight CPUs, and PostgreSQL buffers using 1/4 of system RAM, so it's > a mystery why anything should be idle or waiting for more than a few > seconds (timeout above is 180 seconds). I suspect that there is either a > connection re-use issue or leak but I don't know how to figure that out. > Also, the indexes proposed by Tom Desair in the other thread seem to help > (GitHub PR #1791, DS-3636). > > Please Anis and Mark let me know how your tests go! > > Cheers, > > On Wed, Mar 7, 2018 at 9:15 AM Anis <anis.mouba...@gmail.com> wrote: > >> Thank you! I'll look into this and test for possible side effects. >> >> >> On Monday, March 5, 2018 at 11:30:43 PM UTC+2, Mark H. Wood wrote: >>> >>> I just came across a PostgreSQL configuration variable that may be of >>> interest: idle_in_transaction_session_timeout appeared in, I think, Pg >>> 9.6. "Terminate any session with an open transaction that has been idle >>> for longer than the specified duration in milliseconds." >>> >>> While the best answer will be to find out what is causing connections to >>> remain in this state, we may have a more direct way for dealing with them >>> than the various things we've all been trying. I haven't tried this >>> approach yet, but I may set it to, say, 60000 (one minute) just to see how >>> it works. >>> >> -- >> You received this message because you are subscribed to the Google Groups >> "DSpace Technical Support" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to dspace-tech+unsubscr...@googlegroups.com. >> To post to this group, send email to dspace-tech@googlegroups.com. >> Visit this group at https://groups.google.com/group/dspace-tech. >> For more options, visit https://groups.google.com/d/optout. >> > -- > > Alan Orth > alan.o...@gmail.com > https://picturingjordan.com > https://englishbulgaria.net > https://mjanja.ch > > -- > You received this message because you are subscribed to the Google Groups > "DSpace Technical Support" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to dspace-tech+unsubscr...@googlegroups.com. > To post to this group, send email to dspace-tech@googlegroups.com. > Visit this group at https://groups.google.com/group/dspace-tech. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group. To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech+unsubscr...@googlegroups.com. To post to this group, send email to dspace-tech@googlegroups.com. Visit this group at https://groups.google.com/group/dspace-tech. For more options, visit https://groups.google.com/d/optout.
