--001485f5b0c04f2c430477a6b066
Content-Type: text/plain; charset=UTF-8
On Wed, Nov 4, 2009 at 2:23 PM, Gurgel, Flavio <flavio [at] 4linux.com.br> wrote:
>
> > 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.
>
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
the question is how to find where it happens and where transaction should be
split or started later. Developers undestand the problem, but asked me if I
could provide transaction history (what queries were run before 'idle in
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.
>
> > > 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?
>
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 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 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.
>
> > > 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.
>
Thank you for your help!
--
Vladimir Rusinov
http://greenmice.info/
--001485f5b0c04f2c430477a6b066
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
<br><br><div class=3D"gmail_quote">On Wed, Nov 4, 2009 at 2:23 PM, Gurgel, =
Flavio <span dir=3D"ltr"><<a href=3D"mailto:flavio [at] 4linux.com.br">flavio=
[at] 4linux.com.br</a>></span> wrote:<br><blockquote class=3D"gmail_quote" s=
tyle=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8e=
x; padding-left: 1ex;">
<div class=3D"im"><br>
> Vladimir Rusinov wrote:<br>
> > We are having a lot of '<IDLE> in transaction' proc=
esses in<br>
> production<br>
> > database. This does not seems to be big problem now, since they a=
re<br>
> not<br>
> > taking any big locks. But I'm aware that sometime it can beco=
me a<br>
<br>
</div>This *is* a big problem since your application is not handling transa=
ctions correctly.<br></blockquote><div><br><font color=3D"#000000">It belie=
ve application handling transactions correctly. But in addition to web OLTP=
load, application asynchronously processes various types of large binary b=
lobs</font><span style=3D"color: rgb(0, 0, 0);"> (and store some of them in=
database tables). It seems problem somewhere in this processing:<br>
<br></span><ul><li><span style=3D"color: rgb(0, 0, 0);">application starts =
transaction and executes some sql<br></span></li><li><span style=3D"color: =
rgb(0, 0, 0);">application processes this blob (this can take a lot of time=
, especially in "hot" hours")</span></li>
<li><span style=3D"color: rgb(0, 0, 0);">app executes some sql and finishes=
transaction</span></li></ul><span style=3D"color: rgb(0, 0, 0);">the quest=
ion is how to find where it happens and where transaction should be split o=
r started later. Developers undestand the problem, but asked me if I could =
provide transaction history (what queries were run before 'idle in tran=
saction')</span><br>
<span style=3D"color: rgb(0, 0, 0);"><br></span>=C2=A0</div><blockquote cla=
ss=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, 204, 204); marg=
in: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
For the database maybe you are not worried because you don't have expli=
cit 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 scal=
e up.<br>
<div class=3D"im"><br>
> > problem. Currently I'm just killing all processes that are in=
'idle<br>
> in<br>
> > transaction' for more than 1 minute.<br>
<br>
</div>This is worst. This way you cause a forced ROLLBACK, does your applic=
ation handle this?<br></blockquote><div>=C2=A0<br><span style=3D"color: rgb=
(0, 0, 0);">Yes, application handles this correctly. It prints some not ver=
y friendly exceptions to log and continues processing.</span><br>
<br></div><blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid=
rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div class=3D"im">> > Our application is quite big and complex, so it=
's not easy to find<br>
> peace<br>
> > of code where it happens. How can I get transaction history for<b=
r>
> > processes that idle in transaction to send it to developers so th=
ey<br>
> can<br>
> > find problem in code.<br>
<br>
</div>If your application is big and complex it should handle transactions =
more carefully.<br>
What I did when I had a problem like yours in a big and complex high perfor=
mance application was to find out the associated locks and show the affecte=
d tables and tuples to the developers.<br>
<br>
Check the entries in pg_locks related to the PID of the backend, a query li=
ke this can help you in 8.3:<br>
<br>
SELECT pg_locks.relation,page,tuple FROM pg_locks, pg_stat_activity WHERE p=
g_stat_activity.procpid=3Dpg_locks.pid AND pg_stat_activity.current_query=
=3D'<IDLE> in transaction' ;<br></blockquote><div><br><font c=
olor=3D"#000000">Thanks, I've checked several times and there were noth=
ing intresting. But I'll continue to watch this.</font><br>
=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"border-left: 1px sol=
id rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
In the beggining developers said "the application is big and complex t=
o find it out so the problem is in PostgreSQL". It was the worst excus=
e I heard from developers.<br></blockquote><div><br><font color=3D"#000000"=
>Huh, I've heard it a lot, usualy about web proxy.</font> :)<br>
=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"border-left: 1px sol=
id rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
In the end they fixed the problem since thousands of connections were never=
enough for their application.<br>
<div class=3D"im"><br>
> > We are running postgresql 8.3 with query logging off (turning it =
on<br>
><br>
> > causes performance disaster). Application is run under jboss and<=
br>
> AFAIK<br>
> > uses hibernate.<br>
<br>
</div>So you have a high performance environment. Good. Fix it on your appl=
ication asap.<br>
Ask your developers about nested transactions. This is terrible for perform=
ance and cause the errors you're talking about.<br></blockquote></div><=
br>Thank you for your help!<br><br>-- <br>Vladimir Rusinov<br><a href=3D"ht=
tp://greenmice.info/">http://greenmice.info/</a><br>
--001485f5b0c04f2c430477a6b066--
