24/7 Emergency DBA Coverage

PostgreSQL: lock chains and wraparound headroom

The blocking tree uses pg_blocking_pids(), documented in the PostgreSQL system information functions. The second query shows how close each database is to transaction ID wraparound, the one PostgreSQL emergency that forces downtime if it is ignored.

-- PostgreSQL 14+: who is blocking whom, longest wait first
SELECT
    blocked.pid                                AS blocked_pid,
    blocked.usename                            AS blocked_user,
    NOW() - blocked.query_start                AS blocked_for,
    blocking.pid                               AS blocking_pid,
    blocking.state                             AS blocking_state,
    NOW() - blocking.xact_start                AS blocking_xact_age,
    LEFT(blocking.query, 120)                  AS blocking_query
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL UNNEST(pg_blocking_pids(blocked.pid)) AS b(pid)
JOIN pg_stat_activity AS blocking
    ON blocking.pid = b.pid
ORDER BY blocked_for DESC;

-- Transaction ID wraparound headroom per database
SELECT
    datname,
    AGE(datfrozenxid)                                   AS xid_age,
    ROUND(100.0 * AGE(datfrozenxid) / 2147483647, 1)    AS pct_of_hard_limit
FROM pg_database
ORDER BY xid_age DESC;

MySQL and MariaDB: InnoDB lock waits, replication and Galera

sys.innodb_lock_waits pairs every waiting transaction with its blocker; see the MySQL 8.4 reference. Note the column names that differ between MySQL and MariaDB.

-- MySQL 8.0 / 8.4 (MariaDB 10.6+ ships the sys schema too): current InnoDB lock waits
SELECT
    wait_age,
    locked_table,
    waiting_pid,
    LEFT(waiting_query, 100)   AS waiting_query,
    blocking_pid,
    LEFT(blocking_query, 100)  AS blocking_query,
    sql_kill_blocking_query
FROM sys.innodb_lock_waits
ORDER BY wait_age_secs DESC;

-- Replication health. MySQL 8.0.22+ and MariaDB 10.5.1+ accept the REPLICA keyword;

-- MySQL reports Seconds_Behind_Source, MariaDB reports Seconds_Behind_Master.
SHOW REPLICA STATUS\G
-- MariaDB Galera: is this node part of the primary component, and is flow control throttling writes?
SHOW GLOBAL STATUS
WHERE Variable_name IN (
    'wsrep_cluster_status',
    'wsrep_cluster_size',
    'wsrep_local_state_comment',
    'wsrep_flow_control_paused'
);

SQL Server: blocking and availability group queues

Blocking comes from sys.dm_exec_requests; the availability group query shows whether a secondary is safe to fail over to before anyone presses the button.

-- SQL Server 2016+: requests that are blocked, and the sessions blocking them
SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time / 1000.0        AS wait_s,
    r.status,
    DB_NAME(r.database_id)      AS database_name,
    LEFT(t.text, 200)           AS sql_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

-- Always On availability group health: send and redo queues per database (KB)
SELECT
    ag.name                           AS ag_name,
    ar.replica_server_name,
    DB_NAME(drs.database_id)          AS database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.log_send_queue_size           AS log_send_queue_kb,
    drs.redo_queue_size               AS redo_queue_kb
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS ar
    ON ar.replica_id = drs.replica_id
JOIN sys.availability_groups AS ag
    ON ag.group_id = drs.group_id
ORDER BY drs.log_send_queue_size DESC;

Oracle: blocking sessions and Data Guard lag

We start from V$SESSION because it needs no extra licence. Active Session History and AWR require the Oracle Diagnostics Pack, so we confirm that entitlement before we query them.

-- Oracle 19c / 23ai: blocking chains from V$SESSION (no Diagnostics Pack required)
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.blocking_session,
    s.event,
    ROUND(s.wait_time_micro / 1e6, 1)   AS wait_s,
    s.sql_id
FROM v$session s
WHERE s.blocking_session IS NOT NULL
ORDER BY s.wait_time_micro DESC;

-- Data Guard: transport and apply lag on the standby
SELECT name, value, time_computed
FROM v$dataguard_stats
WHERE name IN ('transport lag', 'apply lag');

Db2: lock waits and HADR state

On Db2 for LUW the monitoring views answer the locking question directly. On Db2 for z/OS, triage runs through DISPLAY THREAD and DISPLAY DATABASE commands with your systems programmers.

-- Db2 11.5 / 12.1 for LUW: current lock waits, longest first
SELECT
    hld_application_handle,
    req_application_handle,
    tabschema,
    tabname,
    lock_object_type,
    lock_mode,
    lock_wait_elapsed_time
FROM sysibmadm.mon_lockwaits
ORDER BY lock_wait_elapsed_time DESC;

-- HADR state, log gap and standby replay delay (run on the primary host)
-- db2pd -db ${DB2_DBNAME} -hadr

MongoDB: long-running operations and replica set lag

The $currentOp aggregation stage finds what is holding the cluster, and the replica set view shows whether a secondary is far enough behind to make failover lose writes.

// MongoDB 6.0+ (mongosh): operations running longer than 30 seconds
db.getSiblingDB("admin").aggregate([
  { $currentOp: { allUsers: true, idleConnections: false } },
  { $match: { active: true, secs_running: { $gt: 30 } } },
  { $project: { opid: 1, secs_running: 1, op: 1, ns: 1, waitingForLock: 1, "command.comment": 1 } },
  { $sort: { secs_running: -1 } }
]);

// Replica set health and secondary lag
rs.status().members.map(m => ({ name: m.name, state: m.stateStr, optime: m.optimeDate }));
rs.printSecondaryReplicationInfo();

ClickHouse: runaway queries and replication backlog

ClickHouse emergencies are handled with our sister company ChistaDATA, whose engineers run ClickHouse in production every day. These are the first two system tables they read.

-- ClickHouse 24.x / 25.x: queries running longer than 60 seconds
SELECT
    query_id,
    user,
    elapsed,
    formatReadableSize(memory_usage)   AS memory,
    substring(query, 1, 120)           AS query_head
FROM system.processes
WHERE elapsed > 60
ORDER BY elapsed DESC;

-- Replication backlog per table, with failing entries counted separately
SELECT
    database,
    table,
    count()                            AS queued,
    countIf(last_exception != '')      AS with_errors,
    max(num_tries)                     AS max_tries
FROM system.replication_queue
GROUP BY database, table
ORDER BY queued DESC;

Redis and Valkey: replication, memory and slow commands

Most Redis emergencies are memory pressure, an eviction storm or one slow command blocking the single-threaded event loop. Five commands usually tell us which.

# Redis 7.x / Valkey 8.x: credentials come from the environment, never the command line
export REDISCLI_AUTH="${REDIS_PASSWORD}"
redis-cli -h "${REDIS_HOST}" INFO replication          # role, connected replicas, link status, offsets
redis-cli -h "${REDIS_HOST}" INFO memory | grep -E 'used_memory_human|maxmemory_human|maxmemory_policy|mem_fragmentation_ratio'
redis-cli -h "${REDIS_HOST}" INFO stats  | grep -E 'evicted_keys|rejected_connections|instantaneous_ops_per_sec'
redis-cli -h "${REDIS_HOST}" SLOWLOG GET 10               # slowest recent commands
redis-cli -h "${REDIS_HOST}" LATENCY DOCTOR               # needs latency-monitor-threshold > 0

Alert on leading indicators, not on CPU

Most of the incidents we are called into were visible minutes or hours earlier in lock waits, replication lag or connection saturation. On PostgreSQL, replication lag is exposed as time, which makes it straightforward to set a service level objective on it.

-- PostgreSQL 10+: replication lag as time, the leading indicator to alert on
SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication
ORDER BY replay_lag DESC NULLS LAST;

What the root cause analysis contains

• A timeline from first symptom to full recovery, built from the incident record.
• The technical cause and the conditions that allowed it, including any change that shipped beforehand.
• The permanent fix, with its own blast radius and rollback path.
• The service level objective and alert that would have warned earlier.
• Failover and restore drills scheduled so the recovery path stays proven.

Customers who want this discipline running continuously use our consultative database support, where the same engineers own reliability between incidents.

Measure the incident window

After every incident that involved a scale-up, we pull the spend for the incident window by usage type and agree which changes stay and which are rolled back.

# Daily Amazon RDS spend across the incident window, split by usage type
aws ce get-cost-and-usage \
  --time-period Start="${WINDOW_START}",End="${WINDOW_END}" \
  --granularity DAILY \
  --metrics UnblendedCost \
  --filter '{"Dimensions":{"Key":"SERVICE","Values":["Amazon Relational Database Service"]}}' \
  --group-by Type=DIMENSION,Key=USAGE_TYPE

Right-size once the system is stable

• Return instance classes and storage performance to what the measured workload needs.
• Remove replicas and snapshots that were created only for the recovery.
• Fix the query or the index that caused the spike, so the capacity is not needed next time.
• Review reserved capacity and savings plans against the post-incident baseline.

Ongoing cost work sits with our cloud database optimization and FinOps practice.