Re: Unpredictable shark slowdown after migrating to 8.4

Sergey Konoplev escribió:
> On Mon, Nov 16, 2009 at 9:56 PM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
> > Sergey Konoplev escribió:
> >
> >> I tried to get locks with this queries
> >
> > Did you try pg_locks?
> >
>
> I tried monitor locks with pgrowlocks. Isn't it better way? If it
> isn't what points should I pay attention with pg_lock?

pgrowlocks shows row locks only. pg_locks shows all regular locks, i.e.
locks on tables, pages, transactions, etc. You should be concerned with
pgrowlocks only if you see a transaction waiting for locktype=transaction.

> I've just write the query
>
> SELECT pid, count(1) AS locks, current_query
> FROM pg_locks AS l LEFT JOIN pg_stat_activity AS a ON pid = procpid
> WHERE l.mode IN ('RowExclusiveLock', 'ShareUpdateExclusiveLock',
> 'ExclusiveLock')
> GROUP BY 1,3 ORDER BY 2 DESC LIMIT 10;

Why only those modes? I'd search for locks with granted=false, then see
all the other locks held by the process that's holding the conflicting
lock with granted=true (i.e. the one you're waiting on).

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support