Skip site navigation (1) Skip section navigation (2)

Re: Long running transactions again ...

From: "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
To: Tobias Brox <tobias(at)nordicbet(dot)com>
Cc: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Long running transactions again ...
Date: 2007-04-18 18:25:53
Message-ID: 20070418182553.GX72669@nasby.net (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)nasby(dot)net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

In response to

pgsql-performance by date

Next:From: Jeff DavisDate: 2007-04-18 19:08:44
Subject: Re: Basic Q on superfluous primary keys
Previous:From: Dave DutcherDate: 2007-04-18 18:09:57
Subject: Re: Basic Q on superfluous primary keys

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group