pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to kill process "idle in transaction"
6+ messages / 4 participants
[nested] [flat]
* How to kill process "idle in transaction"
@ 2007-10-30 08:45 Patrick De Zlio <pchemla01@gmail.com>
2007-10-30 09:11 ` Re: How to kill process "idle in transaction" Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Patrick De Zlio @ 2007-10-30 08:45 UTC (permalink / raw)
To: pgsql-admin
Hi,
We are running PostgreSQL 8.2.4 with quite highly updated millions rows
tables.
It comes sometimes to get some "idle in transaction" processes, which
doesn't have first really viewable effect out of giving very bad estimated
numbers for pgclass.reltuples and, if we let them run as it is, block the
autovacuum from releasing rows, and after few days some worse problems.
Now we are able to detect these "idle in transaction" processes, but what to
do with them ?
What to do to avoid such situation? How to find the bad request which put
the process in this state? From the time it is idle in transaction, we can't
see the query in the pg_lock table anymore.
How to deal with such process when it is already idle in transaction? can we
simply kill it? It is a backend from pgpool. Can we simply restart it (how)
without restarting all the database?
Thanks for help
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to kill process "idle in transaction"
2007-10-30 08:45 How to kill process "idle in transaction" Patrick De Zlio <pchemla01@gmail.com>
@ 2007-10-30 09:11 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-10-31 21:47 ` Re: How to kill process "idle in transaction" Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Achilleas Mantzios @ 2007-10-30 09:11 UTC (permalink / raw)
To: pgsql-admin; +Cc: Patrick De Zlio <pchemla01@gmail.com>
Στις Τρίτη 30 Οκτώβριος 2007 10:45, ο/η Patrick De Zlio έγραψε:
> Hi,
>
> We are running PostgreSQL 8.2.4 with quite highly updated millions rows
> tables.
>
> It comes sometimes to get some "idle in transaction" processes, which
> doesn't have first really viewable effect out of giving very bad estimated
> numbers for pgclass.reltuples and, if we let them run as it is, block the
> autovacuum from releasing rows, and after few days some worse problems.
>
> Now we are able to detect these "idle in transaction" processes, but what
> to do with them ?
>
> What to do to avoid such situation? How to find the bad request which put
> the process in this state? From the time it is idle in transaction, we
> can't see the query in the pg_lock table anymore.
>
> How to deal with such process when it is already idle in transaction? can
> we simply kill it? It is a backend from pgpool. Can we simply restart it
> (how) without restarting all the database?
Some connection pools issue a BEGIN on the connection just
after the previous user of the connection issues COMMIT and
then closes it (Jboss does it). So, <IDLE> in transaction is not
apriori bad.
However if in your system you have lock problems,
and transactions that dont get rollbacked or commited,
then it is a problem with your application.
Have you done any monitoring on the size of the pool?
You should look at:
1) Connections get closed eventually
2) Transactions either have the autocommit flag on, are commited or are
rollbacked.
>
> Thanks for help
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to kill process "idle in transaction"
2007-10-30 08:45 How to kill process "idle in transaction" Patrick De Zlio <pchemla01@gmail.com>
2007-10-30 09:11 ` Re: How to kill process "idle in transaction" Achilleas Mantzios <achill@matrix.gatewaynet.com>
@ 2007-10-31 21:47 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-11-01 00:01 ` Re: How to kill process "idle in transaction" Tom Lane <tgl@sss.pgh.pa.us>
2007-11-01 08:24 ` Re: How to kill process "idle in transaction" Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 6+ messages in thread
From: Scott Marlowe @ 2007-10-31 21:47 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin@postgresql.org, "Patrick De Zlio" <pchemla01@gmail.com>
On 10/30/07, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
> Óôéò Ôñßôç 30 Ïêôþâñéïò 2007 10:45, ï/ç Patrick De Zlio Ýãñáøå:
> > Hi,
> >
> > We are running PostgreSQL 8.2.4 with quite highly updated millions rows
> > tables.
> >
> > It comes sometimes to get some "idle in transaction" processes, which
> > doesn't have first really viewable effect out of giving very bad estimated
> > numbers for pgclass.reltuples and, if we let them run as it is, block the
> > autovacuum from releasing rows, and after few days some worse problems.
> >
> > Now we are able to detect these "idle in transaction" processes, but what
> > to do with them ?
> >
> > What to do to avoid such situation? How to find the bad request which put
> > the process in this state? From the time it is idle in transaction, we
> > can't see the query in the pg_lock table anymore.
> >
> > How to deal with such process when it is already idle in transaction? can
> > we simply kill it? It is a backend from pgpool. Can we simply restart it
> > (how) without restarting all the database?
>
> Some connection pools issue a BEGIN on the connection just
> after the previous user of the connection issues COMMIT and
> then closes it (Jboss does it). So, <IDLE> in transaction is not
> apriori bad.
You can turn off that behaviour, IIRC. It is broken behaviour as far
as pgsql is concerned, I believe.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to kill process "idle in transaction"
2007-10-30 08:45 How to kill process "idle in transaction" Patrick De Zlio <pchemla01@gmail.com>
2007-10-30 09:11 ` Re: How to kill process "idle in transaction" Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-10-31 21:47 ` Re: How to kill process "idle in transaction" Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-11-01 00:01 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2007-11-01 00:01 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-admin@postgresql.org, "Patrick De Zlio" <pchemla01@gmail.com>
"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> On 10/30/07, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
>> Some connection pools issue a BEGIN on the connection just
>> after the previous user of the connection issues COMMIT and
>> then closes it (Jboss does it). So, <IDLE> in transaction is not
>> apriori bad.
> You can turn off that behaviour, IIRC. It is broken behaviour as far
> as pgsql is concerned, I believe.
As of 8.3 it should be harmless, but in existing releases it's bad news.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to kill process "idle in transaction"
2007-10-30 08:45 How to kill process "idle in transaction" Patrick De Zlio <pchemla01@gmail.com>
2007-10-30 09:11 ` Re: How to kill process "idle in transaction" Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-10-31 21:47 ` Re: How to kill process "idle in transaction" Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-11-01 08:24 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-11-01 15:00 ` Re: How to kill process "idle in transaction" Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Achilleas Mantzios @ 2007-11-01 08:24 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin@postgresql.org, "Patrick De Zlio" <pchemla01@gmail.com>
ΣÏÎ¹Ï Î¤ÎµÏάÏÏη 31 ÎκÏÏβÏÎ¹Î¿Ï 2007 23:47, ο/η Scott Marlowe ÎγÏαÏε:
> On 10/30/07, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
> > Óôéò Ôñßôç 30 Ïêôþâñéïò 2007 10:45, ï/ç Patrick De
Zlio Ýãñáøå:
> > > Hi,
> > >
> > > We are running PostgreSQL 8.2.4 with quite highly updated millions rows
> > > tables.
> > >
> > > It comes sometimes to get some "idle in transaction" processes, which
> > > doesn't have first really viewable effect out of giving very bad
> > > estimated numbers for pgclass.reltuples and, if we let them run as it
> > > is, block the autovacuum from releasing rows, and after few days some
> > > worse problems.
> > >
> > > Now we are able to detect these "idle in transaction" processes, but
> > > what to do with them ?
> > >
> > > What to do to avoid such situation? How to find the bad request which
> > > put the process in this state? From the time it is idle in transaction,
> > > we can't see the query in the pg_lock table anymore.
> > >
> > > How to deal with such process when it is already idle in transaction?
> > > can we simply kill it? It is a backend from pgpool. Can we simply
> > > restart it (how) without restarting all the database?
> >
> > Some connection pools issue a BEGIN on the connection just
> > after the previous user of the connection issues COMMIT and
> > then closes it (Jboss does it). So, <IDLE> in transaction is not
> > apriori bad.
>
> You can turn off that behaviour, IIRC. It is broken behaviour as far
> as pgsql is concerned, I believe.
Note nice surely, but why broken?
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to kill process "idle in transaction"
2007-10-30 08:45 How to kill process "idle in transaction" Patrick De Zlio <pchemla01@gmail.com>
2007-10-30 09:11 ` Re: How to kill process "idle in transaction" Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-10-31 21:47 ` Re: How to kill process "idle in transaction" Scott Marlowe <scott.marlowe@gmail.com>
2007-11-01 08:24 ` Re: How to kill process "idle in transaction" Achilleas Mantzios <achill@matrix.gatewaynet.com>
@ 2007-11-01 15:00 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Scott Marlowe @ 2007-11-01 15:00 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin@postgresql.org, "Patrick De Zlio" <pchemla01@gmail.com>
On 11/1/07, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
> Στις Î¤ÎµÏ„Î¬Ï Ï„Î· 31 ÎŸÎºÏ„ÏŽÎ²Ï Î¹Î¿Ï‚ 2007 23:47, ο/η Scott Marlowe ÎÎ³Ï Î±ÏˆÎµ:
> > On 10/30/07, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
> > > Óôéò Ôñßôç 30 Ïêôþâñéïò 2007 10:45, ï/ç Patrick De
> Zlio Ýãñáøå:
> > > > Hi,
> > > >
> > > > We are running PostgreSQL 8.2.4 with quite highly updated millions rows
> > > > tables.
> > > >
> > > > It comes sometimes to get some "idle in transaction" processes, which
> > > > doesn't have first really viewable effect out of giving very bad
> > > > estimated numbers for pgclass.reltuples and, if we let them run as it
> > > > is, block the autovacuum from releasing rows, and after few days some
> > > > worse problems.
> > > >
> > > > Now we are able to detect these "idle in transaction" processes, but
> > > > what to do with them ?
> > > >
> > > > What to do to avoid such situation? How to find the bad request which
> > > > put the process in this state? From the time it is idle in transaction,
> > > > we can't see the query in the pg_lock table anymore.
> > > >
> > > > How to deal with such process when it is already idle in transaction?
> > > > can we simply kill it? It is a backend from pgpool. Can we simply
> > > > restart it (how) without restarting all the database?
> > >
> > > Some connection pools issue a BEGIN on the connection just
> > > after the previous user of the connection issues COMMIT and
> > > then closes it (Jboss does it). So, <IDLE> in transaction is not
> > > apriori bad.
> >
> > You can turn off that behaviour, IIRC. It is broken behaviour as far
> > as pgsql is concerned, I believe.
>
> Note nice surely, but why broken?
Because you're not really IN a transaction. Plus, in some snapshot
systems, you could pick up an idle connection that is idle in
transaction that gives you a three day old snapshot. The time to
issue a begin is when you are beginning your transaction, not right
after finishing the last one.
Also, the connection that's idle in transaction will prevent
postgresql from being able to properly vacuum (in versions before 8.3)
meaning that it can cause your datastore to suffer from bloat.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-11-01 15:00 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-30 08:45 How to kill process "idle in transaction" Patrick De Zlio <pchemla01@gmail.com>
2007-10-30 09:11 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-10-31 21:47 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-11-01 00:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-01 08:24 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-11-01 15:00 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox