From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Stephen Sugden <stephen@aers.ca>
To: pgsql-admin@postgresql.org
Subject: Re: When does postgres set the connection state back to idle?
Date: Wed, 11 May 2011 17:33:33 -0500
Message-ID: <4DCAC86D020000250003D5AE@gw.wicourts.gov> (raw)
In-Reply-To: <BANLkTi=FWP9UrLFqCL-cgKqcRe0cXOhByA@mail.gmail.com>
References: <BANLkTi=FWP9UrLFqCL-cgKqcRe0cXOhByA@mail.gmail.com>
Stephen Sugden <stephen@aers.ca> wrote:
> We're trying to diagnose a system issue where we ended up with
> 100's of non-idle queries in pg_stat_activity, but the actual
> system load didn't correspond to what we should have seen with
> that query load. Is it possible that a client could cause this by
> not acknowledging that it has received results, so that postgres
> will think the query is active even when it's done the hard work
> of getting the queried data off disk? These were all SELECT
> queries if that makes any difference.
You need to look at pg_stat_activity and pg_locks. Look for
processes that have waiting true, and locks that have granted false.
There are queries on the Wiki to help sort it out.
http://wiki.postgresql.org/wiki/Lock_Monitoringhttp://wiki.postgresql.org/wiki/Lock_dependency_information
By the way, it sounds like you should seriously consider using a
connection pooler.
-Kevin
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-admin@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, stephen@aers.ca
Subject: Re: When does postgres set the connection state back to idle?
In-Reply-To: <4DCAC86D020000250003D5AE@gw.wicourts.gov>
* 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