Note that this error is not referring to the Postgres connections themselves, but the connection pool within DSpace from which the database connections are allocated. Postgres is blissfully ignorant of the problem, and I believe we'd see this problem even if we tripled the number of connections.
At one point we did see the number of Postgres connections being exhausted because I hadn't "done the math" for how many DSpace instances we're running and configured Postgres accordingly, but as soon as I tweaked that up to account for that, that problem went away. What we are observing now is much more like a database connection pool leak of some kind. Little by little, apparently after aggressive hits, Postgres connections go into a permanent "idle in transaction" state, and eventually all of the pool is used up. A restart of Tomcat or Postgres will free the connections. Apparently "idle in transaction" is Postgres waiting on the client mid-transaction. We don't seem to see hangs on database activity manifested in the web interface, which makes me suspect there is not a problem with queries completing successfully but rather something more insidious in how the pool is managed--maybe the "idle in transaction" state is caused due to some sort of race condition as an active connection in the pool is assigned to another running thread. For the moment, I have installed a dirty little crontab entry that runs this on the minute: /usr/bin/test `/usr/bin/pgrep -f 'idle in transaction' | \ /usr/bin/wc -l ` -gt 20 && /usr/bin/pkill -o -f 'idle in transaction' In English: every minute, if there are more than 20 "idle in transaction" Postgres processes, it kills the oldest one. Cory Snavely University of Michigan Library IT Core Services On Fri, 2007-01-19 at 11:58 -0500, Mark Diggory wrote: > What about postgres? How many connections is it making available? > You'll want to roughly multiply it by the number of webapplications > your running, so for instance > > db.maxconnections = 50 > db.maxwait = 5000 > db.maxidle = 5 > > running dspace.war, dspace-oai.war and dspace-srw.war postgres needs > about 150 connections in it postgres.conf. I usually increment that > by one for cron jobs as well: > > > for instance in my current config we run two virtual hosts with 3 > webapps each and 1 set for crons: > > 2 vhosts *( 3 webservices +1 cron) * 50 in pool = 400 > > > #--------------------------------------------------------------------- > > ------ > > # CONNECTIONS AND AUTHENTICATION > > #--------------------------------------------------------------------- > > ------ > > > > max_connections = 400 > > # note: increasing max_connections costs ~400 bytes of shared > > memory per > > # connection slot, plus lock space (see > > max_locks_per_transaction). You > > # might also need to raise shared_buffers to support more connections. > > Its not a hard-fast rule, we never really exhaust that many > connections in one instance, but somewhere between that and the > default "100" there is a sweet spot. > > -Mark > > On Jan 19, 2007, at 11:43 AM, Jose Blanco wrote: > > > Actually I mean, more frequently today. Sorry about that. > > > > -----Original Message----- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On Behalf Of > > Jose Blanco > > Sent: Friday, January 19, 2007 11:42 AM > > To: 'Dorothea Salo' > > Cc: dspace-tech@lists.sourceforge.net > > Subject: Re: [Dspace-tech] connections to db seem to be getting stuck > > > > It was dying on us a couple of times a week, but for some reason, > > it's dying > > more frequently this week. Could you share your config db parameters. > > Right now I have the default settings. > > > > -----Original Message----- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On Behalf Of > > Dorothea > > Salo > > Sent: Friday, January 19, 2007 11:28 AM > > Cc: dspace-tech@lists.sourceforge.net > > Subject: Re: [Dspace-tech] connections to db seem to be getting stuck > > > > Jose Blanco wrote: > >> So what do you do? Restart tomcat all day long? For some reason, > >> it is > >> happening very frequently today. It's making the system kind of > >> unusable > >> when every 30 minutes to an hour tomcat has to be restarted. > > > > That often? Wow. It dies on us a couple of times a week, and not > > always for > > this reason as best I can tell. > > > >> It's a bit comforting to know it's not just my problem. Will you > >> be at > > the > >> Open repository conference in San Antonio next week? I'll be > >> there, and > >> hope we can get some help on this. > > > > Agreed! And yes, I will be there. > > > > Dorothea > > > > -- > > Dorothea Salo, Digital Repository Services Librarian > > (703)993-3742 [EMAIL PROTECTED] AIM: gmumars > > MSN 2FL, Fenwick Library > > George Mason University > > 4400 University Drive, Fairfax VA 22031 > > > > ---------------------------------------------------------------------- > > --- > > Take Surveys. Earn Cash. Influence the Future of IT > > Join SourceForge.net's Techsay panel and you'll get the chance to > > share your > > opinions on IT & business topics through brief surveys - and earn cash > > http://www.techsay.com/default.php? > > page=join.php&p=sourceforge&CID=DEVDEV > > _______________________________________________ > > DSpace-tech mailing list > > DSpace-tech@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/dspace-tech > > > > > > > > > > ---------------------------------------------------------------------- > > --- > > Take Surveys. Earn Cash. Influence the Future of IT > > Join SourceForge.net's Techsay panel and you'll get the chance to > > share your > > opinions on IT & business topics through brief surveys - and earn cash > > http://www.techsay.com/default.php? > > page=join.php&p=sourceforge&CID=DEVDEV > > _______________________________________________ > > DSpace-tech mailing list > > DSpace-tech@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/dspace-tech > > > > > > > > > > ---------------------------------------------------------------------- > > --- > > Take Surveys. Earn Cash. Influence the Future of IT > > Join SourceForge.net's Techsay panel and you'll get the chance to > > share your > > opinions on IT & business topics through brief surveys - and earn cash > > http://www.techsay.com/default.php? > > page=join.php&p=sourceforge&CID=DEVDEV > > _______________________________________________ > > DSpace-tech mailing list > > DSpace-tech@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/dspace-tech > > Mark R. Diggory > ~~~~~~~~~~~~~ > DSpace Systems Manager > MIT Libraries, Systems and Technology Services > Massachusetts Institute of Technology > > > > ------------------------------------------------------------------------- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to share your > opinions on IT & business topics through brief surveys - and earn cash > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > DSpace-tech mailing list > DSpace-tech@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/dspace-tech ------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
