Re: slow queue-like empty table

От: Jim Nasby
Тема: Re: slow queue-like empty table
Дата: ,
Msg-id: 4A75D25E-64A3-448F-A870-618021DDD709@decibel.org
(см: обсуждение, исходный текст)
Ответ на: Re: slow queue-like empty table  (Tobias Brox)
Список: pgsql-performance

Скрыть дерево обсуждения

slow queue-like empty table  (Tobias Brox, )
 Re: slow queue-like empty table  (Tobias Brox, )
  Re: slow queue-like empty table  (Csaba Nagy, )
   Re: slow queue-like empty table  (Tobias Brox, )
    Re: slow queue-like empty table  (Jim Nasby, )
 Re: slow queue-like empty table  (Andrew Sullivan, )

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




В списке pgsql-performance по дате сообщения:

От: Jim Nasby
Дата:
Сообщение: Re: slow queue-like empty table
От: "Carlo Stonebanks"
Дата:
Сообщение: Re: Performance Optimization for Dummies 2 - the SQL