Locks in postgres causing system load and crash.
We have a situation where the database locks escalate and load causes problems or the system crashes in some circumstances.
We have munin installed and notice that the locks (access share locks) climbed to 2.7k.
I'm wondering what or how I can get a snapshot of the table(s) and perhaps the culprit that is causing this either from postgres internally or some other means?
Any help would be appreciated.
Chris
_________________________________________________________________
Click less, chat more: Messenger on MSN.ca
http://go.microsoft.com/?linkid=9677404
On Mon, Sep 14, 2009 at 8:58 AM, Chris Barnes
<compuguruchrisbarnes@hotmail.com> wrote:
We have a situation where the database locks escalate and load causes
problems or the system crashes in some circumstances.We have munin installed and notice that the locks (access share locks)
climbed to 2.7k.I'm wondering what or how I can get a snapshot of the table(s) and perhaps
the culprit that is causing this either from postgres internally or some
other means?
The access share locks are likely a symptom, not the cause. Look for
what they're waiting on for the lock. It's usually an exclusive lock
of some kind that causes this problem. We had an issue with a wayward
update with no where clause causing an issue like this a year ago.
Thanks Scott,
How were you able to determine the resource that was causing it. There must be a way of comparing the information to a table?
Chris
Date: Mon, 14 Sep 2009 09:53:08 -0600
Subject: Re: [GENERAL] Locks in postgres causing system load and crash.
From: scott.marlowe@gmail.com
To: compuguruchrisbarnes@hotmail.com
CC: pgsql-general@postgresql.orgOn Mon, Sep 14, 2009 at 8:58 AM, Chris Barnes
<compuguruchrisbarnes@hotmail.com> wrote:We have a situation where the database locks escalate and load causes
problems or the system crashes in some circumstances.We have munin installed and notice that the locks (access share locks)
climbed to 2.7k.I'm wondering what or how I can get a snapshot of the table(s) and perhaps
the culprit that is causing this either from postgres internally or some
other means?The access share locks are likely a symptom, not the cause. Look for
what they're waiting on for the lock. It's usually an exclusive lock
of some kind that causes this problem. We had an issue with a wayward
update with no where clause causing an issue like this a year ago.--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
_________________________________________________________________
New! Open Messenger faster on the MSN homepage
http://go.microsoft.com/?linkid=9677405
Yes, you can join pg_locks to pg_stat_activity and look for waiting
queries, and what they're waiting on in locks.
On Mon, Sep 14, 2009 at 11:20 AM, Chris Barnes
<compuguruchrisbarnes@hotmail.com> wrote:
Thanks Scott,
How were you able to determine the resource that was causing it. There must
be a way of comparing the information to a table?Chris
Date: Mon, 14 Sep 2009 09:53:08 -0600
Subject: Re: [GENERAL] Locks in postgres causing system load and crash.
From: scott.marlowe@gmail.com
To: compuguruchrisbarnes@hotmail.com
CC: pgsql-general@postgresql.orgOn Mon, Sep 14, 2009 at 8:58 AM, Chris Barnes
<compuguruchrisbarnes@hotmail.com> wrote:We have a situation where the database locks escalate and load causes
problems or the system crashes in some circumstances.We have munin installed and notice that the locks (access share locks)
climbed to 2.7k.I'm wondering what or how I can get a snapshot of the table(s) and
perhaps
the culprit that is causing this either from postgres internally or some
other means?The access share locks are likely a symptom, not the cause. Look for
what they're waiting on for the lock. It's usually an exclusive lock
of some kind that causes this problem. We had an issue with a wayward
update with no where clause causing an issue like this a year ago.--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general________________________________
New! Open Hotmail faster on the new MSN homepage!
--
When fascism comes to America, it will be intolerance sold as diversity.