Обсуждение: 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
Faster Hotmail access now on the new MSN homepage.
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
Faster Hotmail access now on the new MSN homepage.
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.org
>
> 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.
>
> --
> 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!
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.org >> >> 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. >> >> -- >> 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.