How to kill process "idle in transaction"

Поиск
Список
Период
Сортировка
Искать

How to kill process "idle in transaction"

От:
"Patrick De Zlio" <pchemla01@gmail.com>
Дата:
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

Re: How to kill process "idle in transaction"

От:
Achilleas Mantzios <achill@matrix.gatewaynet.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,  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

Re: How to kill process "idle in transaction"

От:
"Scott Marlowe" <scott.marlowe@gmail.com>
Дата:
On 10/30/07, Achilleas Mantzios  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,  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.

Re: How to kill process "idle in transaction"

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Scott Marlowe"  writes:
> On 10/30/07, Achilleas Mantzios  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,  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

Re: How to kill process "idle in transaction"

От:
Achilleas Mantzios <achill@matrix.gatewaynet.com>
Дата:
Στις Τετάρτη 31 Οκτώβριος 2007 23:47, ο/η Scott Marlowe έγραψε:
> On 10/30/07, Achilleas Mantzios  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,  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

Re: How to kill process "idle in transaction"

От:
"Scott Marlowe" <scott.marlowe@gmail.com>
Дата:
On 11/1/07, Achilleas Mantzios  wrote:
> Στις Î¤ÎµÏ„Î¬Ï Ï„Î· 31 ÎŸÎºÏ„ÏŽÎ²Ï Î¹Î¿Ï‚ 2007 23:47, ο/η Scott Marlowe Î­Î³Ï Î±ÏˆÎµ:
> > On 10/30/07, Achilleas Mantzios  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,  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.
FAQ