pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLong running transactions again ...
2+ messages / 2 participants
[nested] [flat]
* Long running transactions again ...
@ 2007-04-10 22:50 Tobias Brox <tobias@nordicbet.com>
2007-04-18 18:25 ` Re: Long running transactions again ... Jim C. Nasby <jim@nasby.net>
0 siblings, 1 reply; 2+ messages in thread
From: Tobias Brox @ 2007-04-10 22:50 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Long running transactions again ...
2007-04-10 22:50 Long running transactions again ... Tobias Brox <tobias@nordicbet.com>
@ 2007-04-18 18:25 ` Jim C. Nasby <jim@nasby.net>
0 siblings, 0 replies; 2+ messages in thread
From: Jim C. Nasby @ 2007-04-18 18:25 UTC (permalink / raw)
To: Tobias Brox <tobias@nordicbet.com>; +Cc: 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@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-04-18 18:25 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-10 22:50 Long running transactions again ... Tobias Brox <tobias@nordicbet.com>
2007-04-18 18:25 ` Jim C. Nasby <jim@nasby.net>
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