Performance Tuning

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

-- POSTGRESQL BUILT-IN MONITORING
 
-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- Current activity (who's doing what)
SELECT pid, usename, state, query_start, 
       now() - query_start as duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;
 
-- Database-level statistics
SELECT datname, 
       numbackends as connections,
       xact_commit as commits,
       xact_rollback as rollbacks,
       blks_hit, blks_read,
       round(blks_hit::numeric / nullif(blks_hit + blks_read, 0) * 100, 2) 
           as cache_hit_pct
FROM pg_stat_database
WHERE datname NOT LIKE 'template%';
 
-- Table-level statistics
SELECT schemaname, relname,
       seq_scan, idx_scan,
       n_tup_ins, n_tup_upd, n_tup_del,
       n_live_tup, n_dead_tup,
       last_vacuum, last_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 20;
 
-- Top queries by time (requires pg_stat_statements)
SELECT round(total_exec_time::numeric, 2) as total_ms,
       calls,
       round(mean_exec_time::numeric, 2) as avg_ms,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
 
-- Lock monitoring
SELECT blocked.pid, blocked.query as blocked_query,
       blocking.pid, blocking.query as blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking 
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.state = 'active';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

# PROMETHEUS + GRAFANA SETUP
 
# PostgreSQL Exporter configuration
# (postgres_exporter for Prometheus)
---
# prometheus.yml scrape config:
scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['localhost:9187']
    
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']
    
  - job_name: 'sqlserver'
    static_configs:
      - targets: ['localhost:4000']
 
# Key metrics exposed by postgres_exporter:
# - pg_stat_activity_count (connections by state)
# - pg_stat_database_blks_* (buffer cache metrics)
# - pg_stat_statements_* (query statistics)
# - pg_replication_lag (replication delay)
 
# Alerting rules example:
groups:
  - name: database_alerts
    rules:
      - alert: HighConnectionCount
        expr: pg_stat_activity_count > 180
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Database connection count high"
          
      - alert: ReplicationLag
        expr: pg_replication_lag > 30
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "Replication lag exceeds 30 seconds"
          
      - alert: LowCacheHitRatio
        expr: pg_stat_database_blks_hit / 
              (pg_stat_database_blks_hit + pg_stat_database_blks_read) < 0.95
        for: 10m
        labels:
          severity: warning

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

-- POSTGRESQL SLOW QUERY LOGGING
 
-- Configuration (postgresql.conf)
/*
# Log queries exceeding threshold
log_min_duration_statement = 1000  # ms (1 second)
 
# Or log all statements (high overhead)
# log_statement = 'all'
 
# Include useful context
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
 
# Auto-explain for slow queries
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '3s'
auto_explain.log_analyze = true
*/
 
-- Using pg_stat_statements (preferred approach)
SELECT 
    round(total_exec_time::numeric, 0) as total_ms,
    round(mean_exec_time::numeric, 0) as avg_ms,
    round(stddev_exec_time::numeric, 0) as stddev_ms,
    calls,
    rows,
    query
FROM pg_stat_statements
WHERE mean_exec_time > 1000  -- Over 1 second average
ORDER BY total_exec_time DESC
LIMIT 20;
 
-- Reset statistics periodically
SELECT pg_stat_statements_reset();