Re: Re: sudden spurt in swap utilization (was:cpu bound postgresql setup.)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 25/06/10 20:00, Rajesh Kumar Mallah wrote:
Dear tom, we have autocommit off in dbi. Any commit or rollback from
the persistent modperl process immediately issues begin work; if the
modperl process is waiting for request the database backend remains in
idle in transaction state. Unless we modify data in a http request we
neighter issue a commit nor rollback.

The backend shouldn't go to 'idle in transaction' state until there is some activity within the transaction. I've attached an example script to demonstrate this - note that even SELECT queries will leave the handle as 'IDLE in transaction' unless you've changed the transaction isolation level from the default.

Any queries that are idle in transaction will block connection pooling and cause old versions of table rows to hang around, as described in other replies. Note that this is nothing to do with mod_perl, it's purely due to the way transactions are handled - a one-off script would also have this issue, but on exit issues an implicit rollback and disconnects.

Typically your database wrapper would handle this (I think DBIx::Class should take care of this automatically, although I haven't used it myself).

Tom

Attachment: dbiPgConnectionHandling.pl
Description: Perl program

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux