Database Monitoring, Troubleshooting, and Diagnostics Questions

Situation: As an SRE responsible for Postgres reliability, I monitor a set of metrics that signal saturation and set pragmatic alert thresholds tied to SLOs and capacity. For each metric below I give what to watch, how to set thresholds, and immediate actionable mitigations.

• CPU utilization

  • Monitor: per-core %usage, steal time, postgres backend CPU
  • Thresholds: warn at 60–75%, critical at sustained >85% for 5–10m
  • Mitigation: identify expensive queries, add indexes, offload read replicas, scale vertically, tune parallel_workers, move cron/analytics off primary

• Disk I/O (throughput & latency)

  • Monitor: read/write IOPS, MB/s, avg latency (ms), fsync times
  • Thresholds: warn when avg write latency >5–10ms, critical when >20ms or IOPS near device limit
  • Mitigation: move to faster storage (NVMe), add RAID/striping, separate WAL on dedicated disk, tune checkpoint settings, throttle background IO

• Locks / lock wait

  • Monitor: lock count, longest wait time, blocked_backends
  • Thresholds: warn if blocked_backends >1–2% of connections or any wait >5s; critical for >10s or growing queue
  • Mitigation: kill or rollback offending transactions, enforce shorter transactions, add indexes to avoid full-table scans, use statement_timeout, improve application retry logic

• Connection count

  • Monitor: active connections, idle, max_connections usage
  • Thresholds: warn at 70% of max_connections, critical at 90–95%
  • Mitigation: use connection pooling (PgBouncer), increase max_connections cautiously, scale read replicas, reduce application idle connections

• Query latency / p95-p99

  • Monitor: statement latency distribution, slow query log, p50/p95/p99
  • Thresholds: warn if p95 exceeds SLO (e.g., 200ms), critical if p99 > 1s or rapid increase
  • Mitigation: optimize queries, add indexes, rewrite queries, cache results, push heavy analytics to separate cluster

• Queue length / CPU run queue and postgres WAL backlog

  • Monitor: number of ready-to-run processes, backlog in pg_stat_activity, replication lag
  • Thresholds: warn when run queue length > number of cores; critical when consistently >2×cores or replication lag grows
  • Mitigation: scale horizontally, improve query performance, reduce background jobs, tune autovacuum, add replicas

Operational notes:

• Use composite alerts (e.g., high CPU + rising p95 latency) to reduce noise.
• Alert on trends: sustained violations for 5–10 minutes or rate-of-change thresholds.
• Include actionable runbook steps in alerts: SQL to identify top queries, command to kill transaction, how to enable maintenance mode.
• Regular capacity planning: load-test, baseline metrics, and set thresholds based on historical percentiles and SLOs.

An index scan reads rows by using a database index to locate matching records; a sequential (full table) scan reads every row in the table. The optimizer picks between them based on cost estimates (I/O, CPU, and expected rows returned) derived from table statistics.

Cost trade-offs:

• Index scan: lower logical read if few rows match (highly selective predicate). But can cause many random I/Os if matched rows are scattered, plus CPU to traverse the index.
• Sequential scan: cheaper when most rows are needed (low selectivity) because it streams data sequentially (better throughput, prefetching, fewer seeks).

When an index helps most:

• Highly selective queries (e.g., WHERE id = ... or WHERE email = ... returning <1–5% of rows).
• Range queries on ordered columns when the index is used to avoid sorting.
• Covering indexes that include all requested columns, avoiding heap lookups.

When adding an index will not improve or may worsen performance:

• Poor selectivity: index adds overhead but still requires reading most rows → planner may still choose seq scan.
• Small tables: full table scan is cheaper than index traversal.
• High write workload: every INSERT/UPDATE/DELETE must maintain the index, increasing write latency and CPU.
• Random I/O patterns: using an index causes many random fetches from disk (higher latency) versus sequential reads.
• Wide or many indexes: increased storage, cache pressure, and longer recovery/replication times.

SRE implications: balance read latency vs write throughput, monitor index usage (pg_stat_user_indexes), and add indexes during maintenance windows. Use EXPLAIN/ANALYZE to validate optimizer choice before and after changes.

Situation: A replica is 5 minutes behind and clients read stale data. I would triage quickly with targeted diagnostics, identify likely root causes, then apply safe corrective actions to catch up or resync.

Diagnostics & exact commands

• Check replication status (look for IO/SQL thread state, Seconds_Behind_Master, Last_SQL_Error):
  mysql> SHOW SLAVE STATUS\G
  (or MySQL 8+: SHOW REPLICA STATUS\G)
• Inspect relay / binlog files and positions:
  mysql> SHOW RELAYLOG EVENTS\G
  shell> ls -l /var/lib/mysql/relay-log*
  shell> mysqlbinlog --start-position=POS relay-log.00000X | head
• Check running queries and long transactions on replica:
  mysql> SHOW PROCESSLIST;
  mysql> SELECT * FROM information_schema.PROCESSLIST WHERE Command='Query' ORDER BY Time DESC;
  mysql> SELECT trx_id, trx_started, TIME_TO_SEC(TIMEDIFF(NOW(),trx_started)) AS age_s FROM information_schema.INNODB_TRX\G
• Check InnoDB and lock state:
  mysql> SHOW ENGINE INNODB STATUS\G
  mysql> SELECT * FROM performance_schema.data_locks\G
• Check disk and IO:
  shell> iostat -xz 1 3; vmstat 1 3; iotop -o
  shell> df -h
• Check network:
  shell> ss -tunp | grep mysql; ping -c 5 <master>; traceroute <master>
• Confirm clock skew:
  shell> date; on master date
• Verify GTID / skip / error flags:
  mysql> SHOW GLOBAL VARIABLES LIKE 'gtid_mode';
  mysql> SHOW SLAVE STATUS\G (Last_IO_Error/Last_SQL_Error, Replica_IO_Running, Replica_SQL_Running)
• Heartbeat / perceived lag:
  If using pt-heartbeat: SELECT UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(max(ts)) as lag FROM heartbeat;

Likely root causes

• Long-running transaction on replica blocking SQL_THREAD (e.g., big ALTER, long SELECT in REPEATABLE READ).
• Heavy disk I/O/IOPS saturation on replica (checkpointing, backups).
• Network issues causing IO_THREAD reconnects or high latency.
• Relay log corruption / format mismatch / version issues.
• Master binlog retention/gap (replica missing events) or GTID mismatch.
• Resource exhaustion (CPU, swap), file descriptor limits.

Safe corrective actions & exact commands

1. Non-disruptive fixes (preferred first)

• If SQL thread blocked by a single long query: identify PID then kill:
  mysql> KILL <thread_id>;
• If long-running transaction in InnoDB: evaluate and kill the trx carefully:
  mysql> SELECT trx_id,trx_started FROM information_schema.INNODB_TRX;
  mysql> CALL mysql.rds_kill? (or KILL QUERY <id>)
• Reduce pressure: stop non-essential backups or heavy jobs on replica.
• Increase replication parallelism if safe (row-based / multi-source considerations):
  mysql> SET GLOBAL slave_parallel_workers = N;
• Tune net and timeout:
  mysql> SET GLOBAL replica_net_timeout = 60;
• Allow catch-up (if IO thread healthy) and monitor:
  mysql> START SLAVE; watch SHOW SLAVE STATUS\G / pt-heartbeat

2. If relay log corrupted or IO thread failing

• Stop slave, inspect relay logs, purge and re-fetch:
  mysql> STOP SLAVE;
  shell> mv /var/lib/mysql/relay-log.* /tmp/ # keep copy for analysis
  mysql> RESET SLAVE ALL; # careful: removes master info (use with caution)
  mysql> CHANGE MASTER TO MASTER_HOST='x', MASTER_USER='y', MASTER_LOG_FILE='mysql-bin.00000N', MASTER_LOG_POS=pos; START SLAVE;
• Prefer safer sequence: STOP SLAVE; RESET SLAVE; CHANGE MASTER ... START SLAVE only if you can reconfigure positions or use GTID.

3. When to skip errors (dangerous)

• Only for benign, known non-reproducible errors. Use as last resort:
  mysql> STOP SLAVE;
  mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
  mysql> START SLAVE;
  Monitor for divergence. Avoid if using GTID.

Catch-up strategies

• Let replica catch up naturally after killing blockers and freeing IO; monitor Seconds_Behind_Master and pt-heartbeat.
• If large gap but no data divergence: use mysqldump with --single-transaction (for InnoDB) or XtraBackup to create a fresh copy, then restore and start replication.
• Use GTID-based rebootstrap where possible:
  • On replica: STOP SLAVE; RESET SLAVE ALL; CHANGE MASTER TO MASTER_HOST='m', MASTER_AUTO_POSITION=1; START SLAVE;
  • Only if master has all required GTIDs and replica won't miss transactions.

When to resync from primary / full rebootstrap

• Relay log corruption that cannot be fixed, or replication positions lost.
• Replica has inconsistent data (manual edits, skipped statements) or divergent GTID sets.
• Replica disk failure or outdated schema that can't be reconciled.
• If seconds behind remains high after fixes or replica cannot catch up within SLA window.

Rebootstrap process (safe)

• Take a consistent backup on master (XtraBackup preferred for hot InnoDB):
  shell on backup host> xtrabackup --backup --target-dir=/backup/...
  or use mysqldump --single-transaction --master-data=2 > dump.sql
• Transfer and prepare restore on replica, stop mysql, restore files, set ownership.
• Configure replication coordinates or use GTID (CHANGE MASTER TO ... MASTER_AUTO_POSITION=1).
• Start replica and verify (SHOW SLAVE STATUS\G, pt-heartbeat).

Post-recovery checks

• Confirm Replica_IO_Running=Yes and Replica_SQL_Running=Yes
• Validate Seconds_Behind_Master ~ 0 and pt-heartbeat lag ~ 0
• Run consistency checks (pt-table-checksum) before promoting or trusting data for reads
• Add monitoring/alerts for replication lag, IO, long transactions, and pt-heartbeat.

Key cautions

• Never skip statements unless you understand consequences.
• Avoid RESET SLAVE without preserving master coordinates unless you intend to rebootstrap.
• Prefer non-destructive fixes first (kill, free IO, tune), then rebootstrap when necessary.

This sequence balances fast remediation (kill/block cleanup, tuning) with safe fallbacks (rebuild from backup or GTID resync) and provides commands you can run immediately to diagnose and act.

Start by profiling so you know the real hotspots:

• Enable and query pg_stat_statements to find heavy queries.
• Use EXPLAIN (ANALYZE, BUFFERS, VERBOSE) on representative queries to see planning and actual costs.
• Capture slow queries from your APM or PostgreSQL log_min_duration_statement and correlate with load.

Indexing options and examples:

1. GIN on whole JSONB for containment queries (@>):

• Good when queries test existence/containment across many keys.
• CREATE INDEX CONCURRENTLY idx_table_data_gin ON mytable USING GIN (data jsonb_path_ops);
  jsonb_path_ops is smaller for containment; default gin__gin_ops handles existence.

2. Expression index for frequently filtered nested key:

• Faster than GIN for single-path equality.
• CREATE INDEX CONCURRENTLY idx_table_data_userid ON mytable ((data->>'user_id'));
  Use ->> to index text; cast where appropriate.

3. Partial index when predicate narrows rows:

• Reduces index size and write cost if only a subset is queried.
• CREATE INDEX CONCURRENTLY idx_table_active_userid ON mytable ((data->>'user_id')) WHERE (data->>'status') = 'active';

4. GIN for presence/array queries:

• CREATE INDEX CONCURRENTLY idx_table_tags_gin ON mytable USING GIN ((data->'tags'));

Materialized views vs denormalization:

• Materialized views: good for complex aggregations and read-heavy queries. Refresh strategy matters (CONCURRENTLY where possible). Adds operational complexity (refresh scheduling, stale data).
• Denormalization: store hot-nested fields as separate columns kept in application/schema migration or via triggers. Lowest read latency and simplest plans but increases write latency, storage, and eventual schema complexity.

Trade-offs summary:

• GIN: versatile, good for many keys, larger index and slower writes.
• Expression/partial: precise, smaller, faster reads for targeted filters, lower write cost.
• Materialized views: precompute expensive joins/aggregates; manage staleness.
• Denormalize: best read performance at cost of write complexity and duplication.

Operational notes for SREs:

• Monitor index bloat (pg_stat_user_indexes), index hit ratios, and write latency impact.
• Use CONCURRENTLY for production index builds; schedule during low load.
• Benchmark with realistic workloads (pgbench or replayed queries) before rolling changes.

Situation: You need to add a NOT NULL column with a default to a very large Postgres table without blocking writes. The safe pattern is: add a nullable column (no default), backfill in controlled batches, set the server-side default for new rows, validate, then enforce NOT NULL. Steps, commands, checkpoints and trade‑offs follow.

Step-by-step plan

1. Add column as NULL without default (instant, no table rewrite)

   sql

   ALTER TABLE my_table ADD COLUMN new_col bigint;
   

2. Backfill in id-ordered batches to avoid long transactions and heavy WAL spikes. Use primary key ranges or ctid. Example using PK ranges:

   sql

   -- run repeatedly until no rows remain
   BEGIN;
   WITH chunk AS (
     SELECT id FROM my_table
     WHERE new_col IS NULL AND id > :last_id
     ORDER BY id
     LIMIT 10000
   )
   UPDATE my_table m
   SET new_col = <computed_default_or_value>
   FROM chunk c
   WHERE m.id = c.id;
   COMMIT;
   

   • Tune LIMIT (1k–100k) based on IO and replication lag.
   • Run multiple workers in parallel if safe.
   • Monitor replication lag, CPU, WAL, and table bloat. Pause if lag spikes.

3. Set default for future writes (fast metadata-only change)

   sql

   ALTER TABLE my_table ALTER COLUMN new_col SET DEFAULT 0;
   

   • This does not rewrite rows and is safe while writes continue.

4. Validation checkpoints

   • Ensure zero NULLs remain:
     sql

     SELECT count(*) FROM my_table WHERE new_col IS NULL;
     

   • Row counts and checksums: compare summed values or hash across source and target for sampled ranges:
     sql

     -- sample integrity check
     SELECT id, md5(concat(...)) FROM my_table WHERE id BETWEEN a AND b;
     

   • Monitor application logs for insert/update anomalies.
   • If you computed default via function, validate identical results on random samples taken pre/backfill.

5. Enforce NOT NULL (fast, but requires brief lock)

   • Because there are no NULLs, this is quick; it still acquires a brief ACCESS EXCLUSIVE lock:
     sql

     ALTER TABLE my_table ALTER COLUMN new_col SET NOT NULL;
     

   • Schedule during low traffic window or use connection draining to minimize impact. For extremely latency-sensitive systems, consider performing this on a failover replica and promote.

Rollback and safety

• If backfill reveals issues, stop the batchers and fix logic; missing backfill can be resumed since we only update rows with new_col IS NULL.
• Keep a runbook to drop default or revert column if required:
  sql

  ALTER TABLE my_table ALTER COLUMN new_col DROP DEFAULT;
  

Trade-offs and alternatives

• Pros: Minimal lock time, controlled IO, safe for writes.
• Cons: Backfill generates WAL and can cause replication lag and bloat; requires operational coordination and monitoring.
• Alternatives: CREATE TABLE AS + swap (downtime or complex sync), logical replication or dual-write + cutover, or using a trigger to fill defaults for reads while backfilling (adds runtime overhead).
• For extremely large tables or strict zero-impact SLAs consider using a replica promotion strategy: apply change on a promoted read replica after backfill, then cut traffic.

Operational recommendations

• Automate chunking with idempotent worker (record last processed id).
• Throttle based on replica lag and I/O metrics.
• Run a final quick validation and perform NOT NULL during low load; keep an emergency rollback plan.