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