Monitoring query latency, wait events, and locks in PostgreSQL helps identify and diagnose performance bottlenecks. Here’s a guide on how to monitor these aspects:
- Query Latency:
- Use the
pg_stat_statementsextension to track query latencies. You can check themean_timecolumn in thepg_stat_statementsview to find the average query execution time. - Configure
log_min_duration_statementinpostgresql.confto log queries that take longer than a certain threshold (in milliseconds). Analyze the log to identify slow queries.
- Wait Events:
- Monitor the
pg_stat_activityview to check wait events for each backend process. Thewait_event_typeandwait_eventcolumns indicate the type of wait event and its specific name, respectively. - Use tools like
pg_activityor PgHero to visualize wait events and identify patterns causing performance issues.
- Locks:
- Examine the
pg_locksview to monitor the current lock statuses in the database. Join this view withpg_stat_activityto gather more information about the processes holding or waiting for locks. - Monitor the
pg_stat_databaseview to track lock-related metrics such asdeadlocksorconflicts. A high number of deadlocks or conflicts could indicate potential lock contention issues.
Here’s a sample query to gather information about locks, wait events, and query latency:
-- Retrieve the top 25 most time-consuming queries and related information
SELECT
-- Client hostname
psa.client_hostname AS client_hostname,
-- Database name
psa.datname AS database_name,
-- Query text
pss.query AS query,
-- Timestamp when the active query started
to_char(psa.query_start, 'DD-Mon-YYYY HH24:MI:SS') AS query_start,
-- Timestamp when the state of the backend last changed
to_char(psa.state_change, 'DD-Mon-YYYY HH24:MI:SS') AS state_change,
-- Number of times the query has been executed
pss.calls AS total_times,
-- Total execution time of the query in milliseconds
to_char(pss.total_exec_time / 1000, 'FM999,999,999,999.00') || ' ms' AS total_exec_time_ms,
-- Timestamp when the backend process started
to_char(psa.backend_start, 'DD-Mon-YYYY HH24:MI:SS') AS backend_start,
-- Timestamp when the current transaction started
to_char(psa.xact_start, 'DD-Mon-YYYY HH24:MI:SS') AS xact_start,
-- Lock type held by the backend process
pl.locktype AS lock_type,
-- Wait event for which the backend is waiting
psa.wait_event AS wait_event,
-- Total shared blocks hit in a human-readable format
pg_size_pretty(pss.shared_blks_hit * 8 * 1024) AS shared_blks_hit,
-- Total shared blocks read in a human-readable format
pg_size_pretty(pss.shared_blks_read * 8 * 1024) AS shared_blks_read,
-- Total shared blocks written in a human-readable format
pg_size_pretty(pss.shared_blks_written * 8 * 1024) AS shared_blks_written,
-- Total shared blocks dirtied in a human-readable format
pg_size_pretty(pss.shared_blks_dirtied * 8 * 1024) AS shared_blks_dirtied
-- Join pg_stat_statements with pg_stat_activity using the query text
FROM
pg_stat_statements pss
LEFT OUTER JOIN pg_stat_activity psa ON pss.query = psa.query
-- Join the result with pg_locks using the process ID (pid)
LEFT OUTER JOIN pg_locks pl ON psa.pid = pl.pid
-- Order the result by the total execution time in descending order
ORDER BY total_exec_time DESC
-- Limit the result to the top 25 most time-consuming queries
LIMIT 25;
Conclusion
Monitoring query latency, wait events, and locks, and analyzing their trends over time can help you identify and resolve performance bottlenecks in PostgreSQL.
