Query performance monitoring and profiling

9111 views

                  -- Enable pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all
-- pg_stat_statements.max = 10000

-- View slowest queries
SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  max_exec_time,
  stddev_exec_time,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Most frequently called queries
SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

-- Queries using most total time
SELECT
  query,
  calls,
  total_exec_time,
  (total_exec_time / SUM(total_exec_time) OVER ()) * 100 AS pct_total_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Reset statistics
SELECT pg_stat_statements_reset();

-- Current running queries
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  query_start,
  NOW() - query_start AS duration,
  query
FROM pg_stat_activity
WHERE state != 'idle'
  AND pid != pg_backend_pid()
ORDER BY duration DESC;

-- Long-running queries (> 5 minutes)
SELECT
  pid,
  NOW() - query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE state != 'idle'
  AND NOW() - query_start > INTERVAL '5 minutes'
ORDER BY duration DESC;

-- Kill a running query
SELECT pg_cancel_backend(pid);  -- Graceful
SELECT pg_terminate_backend(pid);  -- Forceful

-- Blocking queries
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_query,
  blocking_activity.query AS blocking_query
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;

-- Cache hit ratio (should be > 99%)
SELECT
  'index hit rate' AS name,
  (SUM(idx_blks_hit) - SUM(idx_blks_read)) / NULLIF(SUM(idx_blks_hit + idx_blks_read), 0) AS ratio
FROM pg_statio_user_indexes
UNION ALL
SELECT
  'table hit rate' AS name,
  (SUM(heap_blks_hit) - SUM(heap_blks_read)) / NULLIF(SUM(heap_blks_hit + heap_blks_read), 0) AS ratio
FROM pg_statio_user_tables;

-- Index usage statistics
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- Unused indexes (never scanned)
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Table bloat estimation
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
  n_live_tup,
  n_dead_tup,
  ROUND(100 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

                  -- Auto EXPLAIN for slow queries
-- postgresql.conf:
-- session_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = 1000  # Log queries > 1 second
-- auto_explain.log_analyze = on
-- auto_explain.log_buffers = on
-- auto_explain.log_timing = on

-- Database size growth monitoring
SELECT
  datname,
  pg_size_pretty(pg_database_size(datname)) AS size,
  pg_database_size(datname) AS size_bytes
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Table size with indexes
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) -
                pg_relation_size(schemaname||'.'||tablename)) AS indexes_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

-- Vacuum and analyze statistics
SELECT
  schemaname,
  relname,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count,
  analyze_count,
  autoanalyze_count
FROM pg_stat_user_tables
ORDER BY last_autovacuum ASC NULLS FIRST;

-- Connection statistics
SELECT
  datname,
  count(*) AS connections,
  max(state) AS state
FROM pg_stat_activity
GROUP BY datname
ORDER BY connections DESC;

-- Connection state breakdown
SELECT
  state,
  count(*) AS count
FROM pg_stat_activity
GROUP BY state
ORDER BY count DESC;

-- Wait events (what queries are waiting for)
SELECT
  wait_event_type,
  wait_event,
  count(*) AS count
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
GROUP BY wait_event_type, wait_event
ORDER BY count DESC;

-- Transaction ID wraparound monitoring
SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  2147483647 - age(datfrozenxid) AS xids_remaining,
  ROUND(100.0 * age(datfrozenxid) / 2147483647, 2) AS pct_toward_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Replication lag monitoring
SELECT
  client_addr,
  application_name,
  state,
  sync_state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag_bytes,
  pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_lag_bytes,
  pg_wal_lsn_diff(write_lsn, flush_lsn) AS flush_lag_bytes,
  pg_wal_lsn_diff(flush_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

-- Lock types and counts
SELECT
  locktype,
  mode,
  count(*) AS count
FROM pg_locks
GROUP BY locktype, mode
ORDER BY count DESC;

-- Checkpoint statistics
SELECT
  checkpoints_timed,
  checkpoints_req,
  checkpoint_write_time,
  checkpoint_sync_time,
  buffers_checkpoint,
  buffers_clean,
  buffers_backend,
  buffers_backend_fsync,
  maxwritten_clean,
  stats_reset
FROM pg_stat_bgwriter;

-- Custom monitoring view
CREATE OR REPLACE VIEW database_health AS
SELECT
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'active') AS active_connections,
  (SELECT count(*) FROM pg_stat_activity) AS total_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction') AS idle_in_transaction,
  (SELECT pg_size_pretty(pg_database_size(current_database()))) AS database_size,
  (SELECT count(*) FROM pg_stat_user_tables WHERE n_dead_tup > 1000) AS tables_needing_vacuum,
  (SELECT count(*) FROM pg_stat_user_indexes WHERE idx_scan = 0) AS unused_indexes;

SELECT * FROM database_health;

-- MySQL slow query log
-- my.cnf:
-- slow_query_log = 1
-- slow_query_log_file = /var/log/mysql/slow-query.log
-- long_query_time = 2
-- log_queries_not_using_indexes = 1

-- Analyze slow query log with pt-query-digest
-- pt-query-digest /var/log/mysql/slow-query.log

-- MySQL process list
SHOW FULL PROCESSLIST;

-- Kill MySQL query
KILL QUERY 123;
KILL CONNECTION 123;

-- MySQL status variables
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Questions';

-- InnoDB buffer pool hit ratio
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Performance monitoring identifies slow queries and bottlenecks. I use EXPLAIN ANALYZE to profile query execution. pgstatstatements tracks query statistics over time. Slow query logs capture problematic queries. Query execution time, I/O, and buffer usage reveal performance issues. Understanding wait events diagnoses contention. Connection pooling metrics show saturation. Cache hit ratios indicate memory effectiveness. Lock monitoring finds blocking queries. Real-time monitoring with pgstatactivity shows current database state. APM tools like DataDog or New Relic provide observability. Auto-explain logs slow queries automatically. Regular performance reviews prevent degradation. Proactive monitoring catches issues before users complain.

Maria Garcia

More from Maria Garcia

Query performance monitoring and profiling

0 Comments

More from Maria Garcia