Re: slow queue-like empty table

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@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)