Database Administrator Responsibilities

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- PostgreSQL disk I/O statistics
SELECT 
    datname,
    blks_read,           -- Blocks read from disk
    blks_hit,            -- Blocks found in buffer cache
    round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) 
        AS cache_hit_ratio   -- Target: > 99%
FROM pg_stat_database 
WHERE datname NOT IN ('template0', 'template1');
 
-- Table I/O statistics
SELECT 
    schemaname,
    tablename,
    heap_blks_read,      -- Table blocks from disk
    heap_blks_hit,       -- Table blocks from cache
    idx_blks_read,       -- Index blocks from disk
    idx_blks_hit         -- Index blocks from cache
FROM pg_statio_user_tables
ORDER BY heap_blks_read 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

# Prometheus configuration for database monitoring
global:
  scrape_interval: 15s          # How often to scrape targets
  evaluation_interval: 15s      # How often to evaluate rules
 
scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: 
        - 'db-primary:9187'     # postgres_exporter
        - 'db-replica:9187'
    relabel_configs:
      - source_labels: [__address__]
        target_label: instance
        regex: '([^:]+).*'
        replacement: '${1}'
 
  - job_name: 'mysql'
    static_configs:
      - targets: 
        - 'mysql-primary:9104'  # mysqld_exporter
    params:
      collect[]:
        - global_status
        - slave_status
        - innodb_metrics

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

-- Active queries and their state
SELECT pid, usename, application_name, client_addr,
       state, wait_event_type, wait_event,
       query_start, now() - query_start AS duration,
       left(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;
 
-- Table statistics (sequential scans indicate missing indexes)
SELECT schemaname, relname,
       seq_scan, seq_tup_read,           -- Sequential scan stats
       idx_scan, idx_tup_fetch,          -- Index scan stats
       n_live_tup, n_dead_tup,           -- Tuple counts
       last_vacuum, last_autovacuum,     -- Vacuum timing
       last_analyze, last_autoanalyze    -- Statistics timing
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 20;
 
-- Lock contention analysis
SELECT blocked.pid AS blocked_pid,
       blocked.usename AS blocked_user,
       blocking.pid AS blocking_pid,
       blocking.usename AS blocking_user,
       blocked.query AS blocked_query,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
    AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
    AND blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page
    AND blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
    AND blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
    AND blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
    AND blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
    AND blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE blocked_locks.granted = false AND blocking_locks.granted = true;
 
-- Replication lag monitoring
SELECT client_addr, state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
       replay_lag
FROM pg_stat_replication;

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

-- Global status overview
SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';
 
-- InnoDB buffer pool efficiency
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;
 
-- Currently running queries
SELECT id, user, host, db, command, time, state, 
       LEFT(info, 100) AS query_preview
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;
 
-- Table lock waits (Performance Schema)
SELECT * FROM sys.innodb_lock_waits\G
 
-- Query digest (most resource-intensive queries)
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT/1000000000000 AS total_latency_sec,
    AVG_TIMER_WAIT/1000000000 AS avg_latency_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
38
39
40
41
42
43
44
45
46
47
48

-- PostgreSQL: Create baseline snapshots table
CREATE TABLE IF NOT EXISTS dba_baseline_snapshots (
    snapshot_id SERIAL PRIMARY KEY,
    snapshot_time TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    snapshot_type VARCHAR(20), -- 'hourly', 'daily', 'pre-change'
    
    -- Query performance
    total_queries BIGINT,
    avg_query_time_ms NUMERIC(10,2),
    p95_query_time_ms NUMERIC(10,2),
    slow_queries_count INTEGER,
    
    -- Resource utilization
    active_connections INTEGER,
    idle_connections INTEGER,
    cpu_percent NUMERIC(5,2),
    memory_used_mb BIGINT,
    
    -- I/O metrics
    buffer_hit_ratio NUMERIC(6,3),
    blocks_read BIGINT,
    blocks_hit BIGINT,
    
    -- Transaction metrics
    commits_per_sec NUMERIC(10,2),
    rollbacks_per_sec NUMERIC(10,2),
    
    -- Replication (if applicable)
    max_replication_lag_bytes BIGINT,
    
    -- Context
    notes TEXT
);
 
-- Capture hourly baseline
INSERT INTO dba_baseline_snapshots (
    snapshot_type, total_queries, active_connections, 
    buffer_hit_ratio, blocks_read, blocks_hit
)
SELECT 
    'hourly',
    sum(calls),
    (SELECT count(*) FROM pg_stat_activity WHERE state = 'active'),
    (SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit + blks_read), 0), 3) 
     FROM pg_stat_database),
    (SELECT sum(blks_read) FROM pg_stat_database),
    (SELECT sum(blks_hit) FROM pg_stat_database)
FROM pg_stat_statements;

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

groups:
  - name: postgresql_alerts
    rules:
      - alert: PostgreSQLHighQueryLatency
        expr: |
          histogram_quantile(0.95, 
            rate(pg_stat_statements_seconds_bucket[5m])
          ) > 2
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "High query latency on {{ $labels.instance }}"
          description: "95th percentile query latency is {{ $value | humanizeDuration }}"
 
      - alert: PostgreSQLDiskSpaceCritical
        expr: |
          (pg_database_size_bytes / pg_tablespace_size_bytes) * 100 > 90
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "Disk space critical on {{ $labels.instance }}"
          description: "Database {{ $labels.datname }} is at {{ $value }}% capacity"
 
      - alert: PostgreSQLReplicationLag
        expr: pg_stat_replication_replay_lag_seconds > 60
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Replication lag on {{ $labels.instance }}"
          description: "Replica lag is {{ $value }} seconds"
 
      - alert: PostgreSQLConnectionsHigh
        expr: |
          pg_stat_activity_count / pg_settings_max_connections > 0.9
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "Connection count approaching limit"
          description: "{{ $value | humanizePercentage }} of max connections in use"

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

-- Enable pg_stat_statements (in postgresql.conf)
-- shared_preload_libraries = 'pg_stat_statements'
 
-- Create extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- Top queries by total execution time
SELECT 
    substring(query, 1, 80) AS query_preview,
    calls,
    total_exec_time::numeric(12,2) AS total_time_ms,
    mean_exec_time::numeric(10,2) AS avg_time_ms,
    stddev_exec_time::numeric(10,2) AS stddev_ms,
    rows,
    shared_blks_hit + shared_blks_read AS total_blocks,
    CASE WHEN shared_blks_hit + shared_blks_read > 0 
         THEN round(100.0 * shared_blks_hit / 
                    (shared_blks_hit + shared_blks_read), 2)
         ELSE 100 END AS cache_hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
 
-- Identify queries with high variance (unpredictable performance)
SELECT 
    substring(query, 1, 60) AS query_preview,
    calls,
    mean_exec_time::numeric(10,2) AS avg_ms,
    stddev_exec_time::numeric(10,2) AS stddev_ms,
    (stddev_exec_time / nullif(mean_exec_time, 0))::numeric(6,2) AS cv,
    min_exec_time::numeric(10,2) AS min_ms,
    max_exec_time::numeric(10,2) AS max_ms
FROM pg_stat_statements
WHERE calls > 100  -- Sufficient sample size
ORDER BY (stddev_exec_time / nullif(mean_exec_time, 0)) DESC NULLS LAST
LIMIT 10;
 
-- Queries scanning most rows (potential for optimization)
SELECT 
    substring(query, 1, 60) AS query_preview,
    calls,
    rows AS total_rows_returned,
    (rows::numeric / nullif(calls, 0))::numeric(12,2) AS rows_per_call,
    shared_blks_read / nullif(calls, 0) AS disk_reads_per_call
FROM pg_stat_statements
WHERE calls > 10
ORDER BY rows DESC
LIMIT 10;