Re: fighting ' in transaction' - Mailing list pgsql-admin
| From | Vladimir Rusinov |
|---|---|
| Subject | Re: fighting ' |
| Date | |
| Msg-id | f6fdfb550911051348j61602cd0x8f0d23516164754c@mail.gmail.com Whole thread Raw |
| In response to |
Re: fighting ' |
| List | pgsql-admin |
On Wed, Nov 4, 2009 at 2:23 PM, Gurgel, Flavio <flavio@4linux.com.br> wrote:
It believe application handling transactions correctly. But in addition to web OLTP load, application asynchronously processes various types of large binary blobs (and store some of them in database tables). It seems problem somewhere in this processing:
Yes, application handles this correctly. It prints some not very friendly exceptions to log and continues processing.
Thanks, I've checked several times and there were nothing intresting. But I'll continue to watch this.
Huh, I've heard it a lot, usualy about web proxy. :)
This *is* a big problem since your application is not handling transactions correctly.
> 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
It believe application handling transactions correctly. But in addition to web OLTP load, application asynchronously processes various types of large binary blobs (and store some of them in database tables). It seems problem somewhere in this processing:
- application starts transaction and executes some sql
- application processes this blob (this can take a lot of time, especially in "hot" hours")
- app executes some sql and finishes transaction
For the database maybe you are not worried because you don't have explicit locks at this moment, but you *will* have problems caused by excessive use of available connections (and threads in the JVM) when you need to scale up.This is worst. This way you cause a forced ROLLBACK, does your application handle this?
> > problem. Currently I'm just killing all processes that are in 'idle
> in
> > transaction' for more than 1 minute.
Yes, application handles this correctly. It prints some not very friendly exceptions to log and continues processing.
> > Our application is quite big and complex, so it's not easy to findIf your application is big and complex it should handle transactions more carefully.
> 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.
What I did when I had a problem like yours in a big and complex high performance application was to find out the associated locks 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' ;
Thanks, I've checked several times and there were nothing intresting. But I'll continue to watch this.
In the beggining developers said "the application is big and complex to find it out so the problem is in PostgreSQL". It was the worst excuse I heard from developers.
Huh, I've heard it a lot, usualy about web proxy. :)
In the end they fixed the problem since thousands of connections were never enough for their application.So you have a high performance environment. Good. Fix it on your application asap.
> > 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.
Ask your developers about nested transactions. This is terrible for performance and cause the errors you're talking about.
Thank you for your help!
--
Vladimir Rusinov
http://greenmice.info/
pgsql-admin by date: