On Oct 4, 2006, at 5:59 AM, Tobias Brox wrote:
[Csaba Nagy - Thu at 10:45:35AM +0200]
So you should check for "idle in transaction" sessions, those are
bad...
or any other long running transaction.
Thank you (and others) for pointing this out, you certainly set us on
the right track. We did have some few unclosed transactions;
transactions not beeing ended by "rollback" or "commit". We've been
fixing this, beating up the programmers responsible and continued
monitoring.
I don't think it's only due to those queue-like tables, we've really
seen a significant improvement on the graphs showing load and cpu
usage
on the database server after we killed all the "idle in
transaction". I
can safely relax still some weeks before I need to do more
optimization
work :-)
Leaving transactions open for a long time is murder on pretty much
any database. It's about one of the worst programming mistakes you
can make (from a performance standpoint). Further, mishandling
transaction close is a great way to lose data:
BEGIN;
...useful work
--COMMIT should have happened here
...more work
...ERROR!
ROLLBACK;
You just lost that useful work.
(oh, btw, we didn't really beat up the programmers ... too big
geographical distances ;-)
This warrants a plane ticket. Seriously. If your app programmers
aren't versed in transaction management, you should probably be
defining a database API that allows the use of autocommit.
--
Jim Nasby jim@xxxxxxxxx
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
