> 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
