Обсуждение: fighting ' in transaction'

Hi!

We are having a lot of '<IDLE> in transaction' processes in production database. This does not seems to be big problem now, since they are not taking any big locks. But I'm aware that sometime it can become a problem. Currently I'm just killing all processes that are in 'idle in transaction' for more than 1 minute.

Our application is quite big and complex, so it's not easy to find peace of code where it happens. How can I get transaction history for processes that idle in transaction to send it to developers so they can find problem in code.

We are running postgresql 8.3 with query logging off (turning it on causes performance disaster). Application is run under jboss and AFAIK uses hibernate.

--
Vladimir Rusinov
http://greenmice.info/

Please see these earlier threads:

Is IDLE session really idle?
http://archives.postgresql.org/pgsql-admin/2009-06/msg00096.php

Idle connections
http://archives.postgresql.org/pgsql-admin/2009-10/msg00017.php

Lewis Kapell


Vladimir Rusinov wrote:
> Hi!
>
> We are having a lot of '<IDLE> in transaction' processes in production
> database. This does not seems to be big problem now, since they are not
> taking any big locks. But I'm aware that sometime it can become a
> problem. Currently I'm just killing all processes that are in 'idle in
> transaction' for more than 1 minute.
>
> Our application is quite big and complex, so it's not easy to find peace
> of code where it happens. How can I get transaction history for
> processes that idle in transaction to send it to developers so they can
> find problem in code.
>
> We are running postgresql 8.3 with query logging off (turning it on
> causes performance disaster). Application is run under jboss and AFAIK
> uses hibernate.
>
> --
> Vladimir Rusinov
> http://greenmice.info/

> Vladimir Rusinov wrote:
> > We are having a lot of '<IDLE> in transaction' processes in
> production
> > database. This does not seems to be big problem now, since they are
> not
> > taking any big locks. But I'm aware that sometime it can become a

This *is* a big problem since your application is not handling transactions correctly.

For the database maybe you are not worried because you don't have explicit locks at this moment, but you *will* have
problemscaused by excessive use of available connections (and threads in the JVM) when you need to scale up. 

> > problem. Currently I'm just killing all processes that are in 'idle
> in
> > transaction' for more than 1 minute.

This is worst. This way you cause a forced ROLLBACK, does your application handle this?

> > Our application is quite big and complex, so it's not easy to find
> peace
> > of code where it happens. How can I get transaction history for
> > processes that idle in transaction to send it to developers so they
> can
> > find problem in code.

If your application is big and complex it should handle transactions more carefully.
What I did when I had a problem like yours in a big and complex high performance application was to find out the
associatedlocks and show the affected tables and tuples to the developers. 

Check the entries in pg_locks related to the PID of the backend, a query like this can help you in 8.3:

SELECT pg_locks.relation,page,tuple FROM pg_locks, pg_stat_activity WHERE pg_stat_activity.procpid=pg_locks.pid AND
pg_stat_activity.current_query='<IDLE>in transaction' ; 

In the beggining developers said "the application is big and complex to find it out so the problem is in PostgreSQL".
Itwas the worst excuse I heard from developers. 
In the end they fixed the problem since thousands of connections were never enough for their application.

> > We are running postgresql 8.3 with query logging off (turning it on
>
> > causes performance disaster). Application is run under jboss and
> AFAIK
> > uses hibernate.

So you have a high performance environment. Good. Fix it on your application asap.
Ask your developers about nested transactions. This is terrible for performance and cause the errors you're talking
about.


Flavio Henrique A. Gurgel
Consultor -- 4Linux
tel. 55-11-2125.4765
fax. 55-11-2125.4777
www.4linux.com.br

--
Vladimir Rusinov
http://greenmice.info/

On Thu, Nov 5, 2009 at 7:16 AM, Vladimir Rusinov
<vladimir@greenmice.info> wrote:
>
>
> On Tue, Nov 3, 2009 at 5:54 PM, Lewis Kapell <lkapell@setonhome.org> wrote:
>>
>> Please see these earlier threads:
>>
>> Is IDLE session really idle?
>> http://archives.postgresql.org/pgsql-admin/2009-06/msg00096.php
>>
>> Idle connections
>> http://archives.postgresql.org/pgsql-admin/2009-10/msg00017.php
>
> Thanks - that was useful reading, but not related to my issue. There are not
> a lot of IDLE sessions: connection pools are configured accurate. There are
> problems with 'IDLE in transaction' sessions, which I believe may cause
> strong lock problems.

The real issue with idle in transaction isn't locking so much.  A
simple idle in transaction that just ran a select * from table limit
1; will have made it so that vacuum cannot reclaim space that it
normally could until that transaction is committed or rolled back.  So
it's much worse than simple locking of a few records, it's causing
your data store to bloat with all the otherwise reclaimable space
since that transaction began.  Let it run for a day or a week and a
busy database will be expanding until it's slow and possibly running
out of space.

Scott Marlowe escribió:

> The real issue with idle in transaction isn't locking so much.  A
> simple idle in transaction that just ran a select * from table limit
> 1; will have made it so that vacuum cannot reclaim space that it
> normally could until that transaction is committed or rolled back.

That's not a problem in 8.4 either, because when the select finishes the
snapshot is deleted and vacuum knows that it can remove those tuples.
That's what we have the new snapshot management module for.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Thu, Nov 5, 2009 at 10:57 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Scott Marlowe escribió:
>
>> The real issue with idle in transaction isn't locking so much.  A
>> simple idle in transaction that just ran a select * from table limit
>> 1; will have made it so that vacuum cannot reclaim space that it
>> normally could until that transaction is committed or rolled back.
>
> That's not a problem in 8.4 either, because when the select finishes the
> snapshot is deleted and vacuum knows that it can remove those tuples.
> That's what we have the new snapshot management module for.

I know.  The OP is running 8.3.  The advantage of 8.4 was, I think,
pointed out upstream, but I wouldn't bet on it one way or the other.

This is one of the very cool things about 8.4 that you can't measure
in a simple benchmark, but in everyday operation I'm sure it can make
a huge difference.

Thank you for your help!

--
Vladimir Rusinov
http://greenmice.info/