Data publikacji: Aug 06, 2015 7:13:7 AM
Zapytanie, do znajdowania blokad:
SELECT
waiting.locktype AS waiting_locktype,
waiting.relation::regclass AS waiting_table,
waiting_stm.query AS waiting_query,
waiting.mode AS waiting_mode,
waiting.pid AS waiting_pid,
other.locktype AS other_locktype,
other.relation::regclass AS other_table,
other_stm.query AS other_query,
other.mode AS other_mode,
other.pid AS other_pid,
other.GRANTED AS other_granted
FROM
pg_catalog.pg_locks AS waiting
JOIN
pg_catalog.pg_stat_activity AS waiting_stm
ON (
waiting_stm.pid = waiting.pid
)
JOIN
pg_catalog.pg_locks AS other
ON (
(
waiting."database" = other."database"
AND waiting.relation = other.relation
)
OR waiting.transactionid = other.transactionid
)
JOIN
pg_catalog.pg_stat_activity AS other_stm
ON (
other_stm.pid = other.pid
)
WHERE
NOT waiting.GRANTED
AND
waiting.pid <> other.pid
Dodatkowe oprogramowanie, do analizowania wyniku:
https://github.com/joyent/pglockanalyze
# echo "select 'vacuum analyze verbose ' || schemaname || '.' || relname || ';' AS sql from pg_stat_user_tables where (last_autoanalyze is null and last_analyze is null) or (greatest(last_analyze, last_autoanalyze) < now() - '1 MONTH'::interval) order by schemaname, relname limit 500;" | psql -X -A -t ... | psql -A -t ...
Linki, do sprawdzena
http://postgres-toolkit.readthedocs.org/en/latest/index.html - zbiór narzedzi, m.in. do gromadzenia snapshotów tabel pg_stat_*