Lock Monitoring
From PostgreSQL wiki
(Difference between revisions)
Rhodiumtoad (Talk | contribs) (bad code) |
(Simplify and fix initial, simplest example query.) |
||
| Line 1: | Line 1: | ||
| − | |||
* Looking at [http://www.postgresql.org/docs/current/static/view-pg-locks.html pg_locks] shows you what locks are granted and what processes are waiting for locks to be acquired. A good query to start looking for lock problems: | * Looking at [http://www.postgresql.org/docs/current/static/view-pg-locks.html pg_locks] shows you what locks are granted and what processes are waiting for locks to be acquired. A good query to start looking for lock problems: | ||
| − | select | + | select relation::regclass, * from pg_locks where not granted; |
* Figuring out what the processes holding or waiting for locks is easier if you cross-reference against the information in [http://www.postgresql.org/docs/current/static/monitoring-stats.html pg_stat_activity] | * Figuring out what the processes holding or waiting for locks is easier if you cross-reference against the information in [http://www.postgresql.org/docs/current/static/monitoring-stats.html pg_stat_activity] | ||
* The following query may be helpful to see what processes are blocking SQL statements: | * The following query may be helpful to see what processes are blocking SQL statements: | ||
Revision as of 14:59, 2 November 2011
- Looking at pg_locks shows you what locks are granted and what processes are waiting for locks to be acquired. A good query to start looking for lock problems:
select relation::regclass, * from pg_locks where not granted;
- Figuring out what the processes holding or waiting for locks is easier if you cross-reference against the information in pg_stat_activity
- The following query may be helpful to see what processes are blocking SQL statements:
select bl.pid as blocked_pid, a.usename as blocked_user,
kl.pid as blocking_pid, ka.usename as blocking_user, a.current_query as blocked_statement
from pg_catalog.pg_locks bl
join pg_catalog.pg_stat_activity a
on bl.pid = a.procpid
join pg_catalog.pg_locks kl
join pg_catalog.pg_stat_activity ka
on kl.pid = ka.procpid
on bl.transactionid = kl.transactionid and bl.pid != kl.pid
where not bl.granted;
- Here's an alternate view of that same data that includes an idea how old the state is:
select
pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted,
pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,30), 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;
- If you suspect intermittent locks are causing problems only sometimes, but are having trouble catching them in one of these live views, setting the log_lock_waits and related deadlock_timeout parameters can be helpful. Then slow lock acquisition will appear in the database logs for later analysis.
See also Lock dependency information
