Re: Unpredictable shark slowdown after migrating to 8.4

Thank you for the hints.

> 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).


Something like this?

SELECT   granted,   pid,   virtualxid,   transactionid,   virtualtransaction,   count(1) AS locks,   current_query
FROM   pg_locks AS l   LEFT JOIN pg_stat_activity AS a ON       pid = procpid
GROUP BY 1, 2, 3, 4, 5, 7
ORDER BY 1, 6 DESC;


And two more queries to do extended analysis of its results after restarting PG:

SELECT   pg_stat_activity.datname,   pg_class.relname,   pg_locks.transactionid,   pg_locks.mode,   pg_locks.granted,
pg_stat_activity.usename,  pg_stat_activity.current_query,   pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start)AS "age",   pg_stat_activity.procpid 
FROM   pg_stat_activity,   pg_locks   LEFT OUTER JOIN pg_class ON       pg_locks.relation = pg_class.oid
WHERE   pg_locks.pid = pg_stat_activity.procpid
ORDER BY   query_start;


SELECT * FROM pg_locks;


Are there another things I should do when the problem rise up again?

--
Regards,
Sergey Konoplev