Monitoring Tools

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

-- Active session monitoring
SELECT pid, usename, datname, state, 
       query_start, now() - query_start AS duration,
       query
FROM pg_stat_activity
WHERE state = 'active' AND query NOT LIKE '%pg_stat_activity%';
 
-- Table I/O statistics
SELECT schemaname, relname, 
       heap_blks_read, heap_blks_hit,
       ROUND(100.0 * heap_blks_hit / NULLIF(heap_blks_hit + heap_blks_read, 0), 2) AS hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC LIMIT 10;
 
-- Index usage analysis
SELECT schemaname, tablename, indexname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
 
-- Query statistics (requires pg_stat_statements extension)
SELECT query, calls, total_exec_time, mean_exec_time,
       rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

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

-- Global status variables
SHOW GLOBAL STATUS WHERE Variable_name IN (
    'Queries', 'Threads_connected', 'Threads_running',
    'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads',
    'Innodb_row_lock_waits', 'Innodb_row_lock_time'
);
 
-- Process list with query text
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep';
 
-- InnoDB buffer pool hit ratio
SELECT 
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 
    AS buffer_pool_hit_ratio
FROM (
    SELECT 
        Variable_value AS Innodb_buffer_pool_reads 
    FROM performance_schema.global_status 
    WHERE Variable_name = 'Innodb_buffer_pool_reads'
) reads,
(
    SELECT 
        Variable_value AS Innodb_buffer_pool_read_requests 
    FROM performance_schema.global_status 
    WHERE Variable_name = 'Innodb_buffer_pool_read_requests'
) requests;
 
-- Slow query digest from Performance Schema
SELECT DIGEST_TEXT, COUNT_STAR, 
       SUM_TIMER_WAIT/1000000000000 AS total_time_sec,
       AVG_TIMER_WAIT/1000000000 AS avg_time_ms,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

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

-- Currently executing queries
SELECT r.session_id, r.status, r.command,
       r.cpu_time, r.total_elapsed_time,
       r.logical_reads, r.writes,
       t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50;
 
-- Wait statistics
SELECT wait_type, waiting_tasks_count,
       wait_time_ms, max_wait_time_ms,
       signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;
 
-- Buffer pool usage by database
SELECT DB_NAME(database_id) AS database_name,
       COUNT(*) * 8 / 1024 AS buffer_pool_mb
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY buffer_pool_mb DESC;
 
-- Top queries by CPU
SELECT TOP 10 
    qs.total_worker_time/1000000 AS total_cpu_sec,
    qs.execution_count,
    qs.total_worker_time/qs.execution_count/1000 AS avg_cpu_ms,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset 
            WHEN -1 THEN DATALENGTH(st.text) 
            ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_cpu_sec DESC;

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

# CPU, memory, and process overview
top -c         # Interactive process viewer
htop           # Enhanced interactive viewer (if installed)
 
# Virtual memory statistics
vmstat 1       # Per-second memory/CPU stats
 
# Disk I/O monitoring
iostat -xz 1   # Extended disk statistics per second
iotop          # Per-process I/O activity
 
# Network monitoring
netstat -an    # All connections
ss -s          # Socket statistics summary
iftop          # Per-connection bandwidth
 
# Memory usage breakdown
free -h        # Human-readable memory summary
cat /proc/meminfo  # Detailed memory information
 
# File system usage
df -h          # Disk space by mount point
du -sh /var/lib/postgresql  # Database directory size
 
# Database-specific file activity
lsof -p $(pgrep postgres)  # Files open by PostgreSQL

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

# Docker Compose setup for PostgreSQL monitoring
version: '3.8'
services:
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://monitor:password@postgres:5432/postgres?sslmode=disable"
    ports:
      - "9187:9187"
    
  prometheus:
    image: prom/prometheus
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
    ports:
      - "9090:9090"
    command:
      - '--config.file=/etc/prometheus/prometheus.yml'
      - '--storage.tsdb.retention.time=30d'
 
---
# prometheus.yml
global:
  scrape_interval: 15s
  evaluation_interval: 15s
 
scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['postgres_exporter:9187']
    relabel_configs:
      - source_labels: [__address__]
        target_label: instance
        replacement: 'prod-db-1'

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

# Current active connections
pg_stat_activity_count{state="active"}
 
# Query rate over 5 minutes
rate(pg_stat_database_xact_commit[5m])
 
# Buffer cache hit ratio
sum(pg_stat_database_blks_hit) / 
(sum(pg_stat_database_blks_hit) + sum(pg_stat_database_blks_read)) * 100
 
# 95th percentile query latency (histogram)
histogram_quantile(0.95, sum(rate(pg_query_duration_seconds_bucket[5m])) by (le))
 
# Connections approaching limit (>80%)
pg_stat_activity_count / pg_settings_max_connections * 100 > 80
 
# Disk space growth rate (MB/hour)
deriv(pg_database_size_bytes[1h]) / 1024 / 1024
 
# Replication lag in seconds
pg_replication_lag_seconds > 30
 
# Transaction rollback ratio
rate(pg_stat_database_xact_rollback[5m]) / 
(rate(pg_stat_database_xact_commit[5m]) + rate(pg_stat_database_xact_rollback[5m])) * 100