From: Tom Molesworth <tom@audioboundary.com>
To: pgsql-performance@postgresql.org
To: mallah.rajesh@gmail.com
Subject: Re: Re: sudden spurt in swap utilization (was:cpu bound postgresql setup.)
Date: Fri, 25 Jun 2010 21:18:52 +0100
Message-ID: <4C250F2C.2070709@audioboundary.com> (raw)
In-Reply-To: <AANLkTils-kuUrmSMRmB1eplblsAf-hbbr6iU65Gtahj-@mail.gmail.com>
References: <AANLkTinDBPQL-SjLaCr7AVgzouWbRrjwA6MAgBih_ciy@mail.gmail.com>
<4C2492F9.2020706@gmail.com>
<AANLkTikUDoXMF-7N7csuYPWIzn9gSKm_g4NnmjvAYu8N@mail.gmail.com>
<4C2477A20200002500032B03@gw.wicourts.gov>
<AANLkTimPhkr2nvuLo-fpBfUdN08KsDFTwzL9ubJP5VG2@mail.gmail.com>
<AANLkTikJEyqi_8WRI6FjH3OBnnIj4SV9EbTBLl2EGCZw@mail.gmail.com>
<4C24DBA9.7030303@audioboundary.com>
<AANLkTils-kuUrmSMRmB1eplblsAf-hbbr6iU65Gtahj-@mail.gmail.com>
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
Attachments:
dbiPgConnectionHandling.pl (application/x-perl, 1.9K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tom@audioboundary.com, mallah.rajesh@gmail.com
Subject: Re: Re: sudden spurt in swap utilization (was:cpu bound postgresql setup.)
In-Reply-To: <4C250F2C.2070709@audioboundary.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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