PSQL Debug Slow Queries
Useful PostgreSQL Queries and Commands · GitHub
Exploring Query Locks in Postgres - Big elephants
Resources with locks¶
MVCC: Multi Version Concurrency Control - PostgreSQL: Documentation: 9.3: Concurrency Control
pg_locks
table
- locks held by open transactions
pg_class
- catalog of tables, indexes, sequences, views, composite types, TOAST
pg_stat_activity
- all server processes
- query: the last query the process ran
- pid
: process ID
-- find_locks.sql
SELECT mode, *
FROM pg_locks l
JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r';
-- blocking_pids.sql
-- https://stackoverflow.com/questions/26489244/how-to-detect-query-which-holds-the-lock-in-postgres
SELECT pid,
usename,
pg_blocking_pids(pid) as blocked_by,
query as blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
Lock Monitoring - PostgreSQL wiki
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
Long running PSQL queries¶
Finding and killing long running queries on PostgreSQL
```sql TI:"kill_long_running_pids.sql" WITH long_running_queries AS ( SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '2 minutes' AND state = 'active' ) SELECT pg_cancel_backend(pid) FROM long_running_queries
| pid | duration | query | state | | 14183 | 1:52:02.686858 | COMMIT | idle | | 24731 | 5 days, 4:01:05.452130 | COMMIT | idle | | 12640 | 4 days, 1:46:53.366550 | COMMIT | idle | | 19825 | 4:53:19.809795 | SELECT * ...| active |If that doesn't work
- may lead to full database restart
Find large queries¶
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 10;
-- queries that are currently running
SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>'
AND query NOT ILIKE '%pg_stat_activity%'
AND query NOT IN ('', chr(10), 'COMMIT')
ORDER BY query_start desc;
SELECT (table_name.*)::text, count(*)
FROM table_name
GROUP BY table_name.*
HAVING count(*) > 1;
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE pid = 17081;
order by duration desc
17081 13484 17656