From: Jim C. Nasby <jim@nasby.net>
To: Tobias Brox <tobias@nordicbet.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Long running transactions again ...
Date: Wed, 18 Apr 2007 13:25:53 -0500
Message-ID: <20070418182553.GX72669@nasby.net> (raw)
In-Reply-To: <20070410225037.GC19909@oppetid.no>
References: <20070410225037.GC19909@oppetid.no>
On Wed, Apr 11, 2007 at 12:50:37AM +0200, Tobias Brox wrote:
> We had problems again, caused by long running transactions. I'm
> monitoring the pg_stat_activity view, checking the query_start of all
> requests that are not idle - but this one slipped under the radar as the
> application was running frequent queries towards the database.
>
> That's not what concerns me most. We had two databases running under
> postgres at this host - like, main production database (A) and a
> separate smaller database for a separate project (B). As far as I
> understood postgres philosophy, the databases should be isolated from
> each other, i.e. one are not allowed to create a query that goes across
> the database borders (select * from A.customers join B.logins ...). So,
> I was surprised to see that the application working towards database B
> managed to jam up database A, to the extent that we couldn't get A
> vacuumed properly.
Vacuums do ignore other databases, except for shared relations such as
pg_database. If one of the databases wasn't being vacuumed properly it
means there was in fact a transaction open. Note that until recently,
vacuums wouldn't ignore other vacuums, so a long-running vacuum would
prevent repeated vacuums on the same table from accomplishing much.
Are you sure that your monitoring doesn't accidentally ignore
backends marked as <IDLE> in transaction?
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
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: jim@nasby.net, tobias@nordicbet.com
Subject: Re: Long running transactions again ...
In-Reply-To: <20070418182553.GX72669@nasby.net>
* 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