MySQL & PostgreSQL Monitoring: Critical Metrics & Alert Thresholds Explained

Introduction: A Database Saved by Monitoring

During a Q2 promotion at 1 AM, an order database's P99 latency jumped from 80ms to 1.2s. Business didn't crash but order volume dropped. The on-call engineer opened Grafana and saw three anomalies: Threads_running rose from 30 to 380, InnoDB row lock wait time ( Innodb_row_lock_time) from 50ms/s to 4s/s, and Slow_queries per minute from 12 to 900+. Yet CPU was only 60% and IOPS 50% — looking at CPU alone would have missed the problem.

Running SHOW ENGINE INNODB STATUS\G revealed a deadlock on the orders table (80M rows) where the hot status column lacked a proper index, causing range scans and lock escalation. The fix path: 1) emergency kill of the long transaction ( KILL 37281973), 2) temporary isolation of hot orders via a separate connection pool with rate limiting, 3) root-cause fix by adding index

CREATE INDEX idx_orders_status_updated ON orders(status, updated_at)

, 4) archiving data older than 90 days to orders_archive. From alert to fix took 17 minutes; without proper thresholds it would have taken over an hour.

Monitoring Objectives & MySQL vs PostgreSQL Differences

Database monitoring targets four layers: availability (service online, connection pool health, replication), performance (QPS/TPS/latency, lock waits, buffer hit ratio), capacity (connections, tablespace, disk, binlog/WAL growth), and change (DDL, parameter changes, slow queries, anomalous SQL). MySQL and PostgreSQL differ significantly in built-in monitoring interfaces:

MySQL: SHOW STATUS, SHOW ENGINE INNODB STATUS, performance_schema, information_schema PostgreSQL: pg_stat_* views, pg_stat_statements extension, EXPLAIN (ANALYZE) Replication: MySQL uses SHOW REPLICA STATUS (8.0.22+) / SHOW SLAVE STATUS; PostgreSQL uses pg_stat_replication Locks: MySQL 8.0+ uses performance_schema.data_locks; PostgreSQL uses pg_locks + pg_stat_activity Slow queries: MySQL slow log + performance_schema.events_statements_summary_by_digest; PostgreSQL log_min_duration_statement + pg_stat_statements Buffer pool: MySQL via SHOW ENGINE INNODB STATUS; PostgreSQL via pg_stat_database (blks_hit/blks_read)

Deadlocks: MySQL in SHOW ENGINE INNODB STATUS; PostgreSQL in logs with deadlock detected Exporters: mysqld_exporter vs postgres_exporter / pgbouncer_exporter Grafana dashboards: Percona MySQL Overview, MySQL InnoDB Metrics vs PostgreSQL Overview, pganalyze, Crunchy

HA solutions: MHA/Orchestrator/Group Replication/InnoDB Cluster vs Patroni/repmgr/PgBouncer+keepalived

Common Pain Points

Too many metrics: MySQL 400+ status variables, PostgreSQL 200+ system views

Version differences: MySQL 5.7/8.0 field changes; PostgreSQL 12-16 view adjustments

Threshold setting: copying "CPU > 80%" alerts when CPU stays at 30%

Alert storms: one slow query triggering 20 alerts

Unclear semantics: confusing Threads_connected (includes sleep) with Threads_running (actively executing)

Reluctance to install new components in production

Applicable Scenarios

Single DB < 100 QPS, few tables: slow query log + SHOW STATUS + custom scripts

Small-medium, 1-10 instances: Prometheus + mysqld_exporter/postgres_exporter + Grafana

Large scale, 50+ instances, multi-cluster: Prometheus + multiple exporters + custom aggregation layer + alert tiering

Finance/gov: commercial monitoring (Datadog/New Relic/custom APM) + localized exporters

Multi-cloud/cross-region: Thanos/Cortex/VictoriaMetrics aggregation

Not recommended for Prometheus: tiny workload with DBA on-site, strict zero-overhead requirements, or mandatory commercial DB monitoring.

Core Knowledge Points

3.1 MySQL Version Differences (5.7 vs 8.0)

Key changes affecting monitoring scripts:

Replication query: SHOW SLAVE STATUS → SHOW REPLICA STATUS (8.0.22+)

Row lock monitoring: information_schema.innodb_trx + innodb_locks + innodb_lock_waits → performance_schema.data_locks + data_lock_waits (5.7's innodb_locks removed in 8.0)

Performance Schema: 5.7 enables events_statements_% partially; 8.0 enables more by default but consumes more memory

System tables: mysql.user plaintext passwords visible in 5.7; 8.0 requires ALTER USER reset after upgrade

Default charset: latin1 → utf8mb4

Histograms: none → ANALYZE TABLE ... UPDATE HISTOGRAM Invisible indexes, resource groups, window functions, CTEs, Group Replication stability, InnoDB Cluster built-in ( mysqlsh), binlog expiration (never → 30 days via binlog_expire_logs_seconds), default authentication ( mysql_native_password → caching_sha2_password)

When upgrading 5.7→8.0, monitoring scripts must change innodb_locks to data_locks and SHOW SLAVE STATUS to SHOW REPLICA STATUS (backward compatible).

3.2 PostgreSQL Version Differences (12-16)

Each major version adds monitoring fields: pg_stat_statements: fields added/split across versions (e.g., total_exec_time split in 13, shared_blks_* in 14, wal_records / wal_fpi in 15) pg_stat_activity: leader_pid (13+), query_id (16+) pg_stat_replication: replay_lag added in 14

New views: pg_stat_io (16+), pg_stat_wal (13+), pg_stat_progress_* expanded

B-tree deduplication (14+), JIT enhancements

Monitoring PostgreSQL requires checking actual version for pg_stat_statements field names; pg_stat_io (PG 16+) is key for I/O bottleneck analysis.

3.3 MySQL Key Metrics Categories

Connection Metrics

Threads_connected

: current connections (includes sleep) — alert when approaching

max_connections

Threads_running

: actively executing threads — sustained >30 indicates backlog Threads_created: continuously growing suggests connection pool not reusing Connection_errors_*, Aborted_connects, Max_used_connections, Connection_errors_max_connections Note: Threads_connected includes sleeping threads; Threads_running reflects real pressure.

Throughput Metrics

Questions

, Com_select, Com_insert/update/delete, Innodb_rows_inserted/updated/deleted/read, Bytes_received/sent, Created_tmp_tables, Created_tmp_disk_tables QPS/TPS calculation via mysqladmin extended-status -r -i 1 sampling.

InnoDB Metrics

Buffer pool: Innodb_buffer_pool_pages_total/free/data/misc, Innodb_buffer_pool_read_requests (logical reads), Innodb_buffer_pool_reads (physical reads) — hit rate < 99% indicates memory pressure

I/O: Innodb_data_reads/writes, Innodb_log_waits (>0 means redo log full), Innodb_os_log_fsyncs, Innodb_os_log_pending_fsyncs/writes (>0 = stall), Innodb_data_pending_fsyncs/reads/writes (>0 = I/O saturation)

Locks: Innodb_row_lock_waits, Innodb_row_lock_time, Innodb_deadlocks, Innodb_mutex_os_waits History list: Innodb_history_list_length >1000 indicates purge lag (causes: long transactions, innodb_purge_threads default 4, innodb_purge_batch_size too small)

Transaction ID counter growth indicates write rate

Replication Metrics (5.7 / 8.0.22-)

Key fields from SHOW SLAVE STATUS\G: Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master, Relay_Log_Space, Last_IO_Error, Last_SQL_Error, Master_Log_File / Read_Master_Log_Pos, Relay_Master_Log_File / Exec_Master_Log_Pos. In 8.0.22+ use SHOW REPLICA STATUS with renamed fields ( Replica_IO_Running, Seconds_Behind_Source).

Group Replication Monitoring (8.0+)

Query performance_schema.replication_group_members for MEMBER_STATE (must be ONLINE), MEMBER_ROLE (single-primary: only one PRIMARY), MEMBER_VERSION consistency. Monitor replication lag via replication_group_member_stats: COUNT_TRANSACTIONS_IN_QUEUE (queue_count) >0 and growing means secondary falling behind.

Temporary Table / Sort Metrics

Created_tmp_disk_tables

spike → memory temporary table insufficient Sort_merge_passes spike → sort_buffer_size too small Select_full_join, Select_range_check, Select_scan >0 indicates missing indexes

Performance Schema Tables (8.0+)

Critical for lock and transaction analysis:

-- 8.0+ row locks
SELECT * FROM performance_schema.data_locks LIMIT 10;
-- 8.0+ lock waits
SELECT * FROM performance_schema.data_lock_waits LIMIT 10;
-- Long-running transactions (>30s)
SELECT trx_id, trx_state, trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
  trx_mysql_thread_id, trx_query, trx_rows_modified,
  trx_tables_in_use, trx_tables_locked, trx_lock_structs
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30
ORDER BY trx_started;

SQL statistics by digest:

SELECT SCHEMA_NAME, digest, digest_text,
  count_star AS exec_count,
  sum_timer_wait/1e9 AS total_ms,
  avg_timer_wait/1e9 AS avg_ms,
  max_timer_wait/1e9 AS max_ms,
  sum_lock_time/1e9 AS lock_ms,
  sum_rows_examined AS rows_examined,
  sum_rows_sent AS rows_sent,
  sum_no_index_used AS no_index_count,
  sum_errors AS error_count,
  sum_warnings AS warning_count
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY sum_timer_wait DESC
LIMIT 20;

InnoDB History List & Purge

From SHOW ENGINE INNODB STATUS\G, the History list length >1000 signals purge lag. Common causes: large uncommitted transactions, innodb_purge_threads (default 4, can increase to 8/16), innodb_purge_batch_size too small. Trx id counter rapid growth indicates high write rate.

3.4 PostgreSQL Key System Views

pg_stat_activity (Active Sessions)

Fields: pid, usename, application_name, client_addr, state (active/idle/idle in transaction/fastpath function call), query_start, state_change, wait_event_type (Lock/IO/LWLock/Activity), wait_event, query, backend_xid / backend_xmin, leader_pid (13+), query_id (16+). state = 'idle in transaction' is a critical alert: transaction hanging, locks not released.

Active lock wait query:

SELECT pg_class.relname, pg_locks.locktype, pg_locks.mode, pg_locks.granted,
  pg_stat_activity.usename, pg_stat_activity.query, pg_stat_activity.query_start,
  pg_stat_activity.state_change,
  EXTRACT(EPOCH FROM (now() - pg_stat_activity.query_start)) AS duration_sec
FROM pg_locks
JOIN pg_class ON pg_locks.relation = pg_class.oid
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE NOT pg_locks.granted
ORDER BY pg_stat_activity.query_start;

pg_stat_statements (SQL Statistics)

Enable in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top
pg_stat_statements.track_utility = on
pg_stat_statements.save = on

Then CREATE EXTENSION pg_stat_statements;. Key fields: query (parameterized template), calls, total_exec_time, mean_exec_time, max_exec_time, rows, shared_blks_hit/read, temp_blks_read/written, queryid, total_plan_time (13+), wal_records / wal_fpi (13+). Cache hit rate:

sum(shared_blks_hit) / nullif(sum(shared_blks_hit)+sum(shared_blks_read),0)

. Top slow queries: order by total_exec_time DESC.

pg_stat_database (Database-Level)

Fields: datname, numbackends, xact_commit/rollback, blks_read/hit, tup_returned/fetched/inserted/updated/deleted, conflicts, deadlocks, blk_read_time / blk_write_time (requires track_io_timing=on), session_time / idle_in_transaction_time (13+), active_time (14+).

pg_stat_replication (Replication Status)

Fields: pid, usename, application_name, state (startup/catchup/streaming/backup/stopping), sent_lsn, write_lsn, flush_lsn, replay_lsn, replay_lag / write_lag / flush_lag (PG 10+), sync_state / sync_priority. Lag in bytes: pg_wal_lsn_diff(sent_lsn, replay_lsn).

pg_locks (Lock View)

Join with pg_class and pg_stat_activity to see ungranted locks.

pg_stat_progress_vacuum (Vacuum Progress, 9.6+)

Phases: initializing, scanning heap, vacuuming indexes, vacuuming heap, cleaning up indexes, truncating heap, performing final cleanup. Monitor heap_blks_scanned / heap_blks_total.

pg_stat_io (I/O Details, PG 16+)

Fields: backend_type, object, context, reads, writes, extends, hits, read_time, write_time. Directly shows I/O distribution per object type.

3.5 Key Alert Thresholds (Baseline Reference)

All thresholds are experience-based and must be tuned to business baselines. Critical thresholds include: Threads_running >30 sustained 5 min (or > CPU cores × 2)

Buffer Pool hit rate <99% sustained 10 min Innodb_row_lock_waits >100/min Innodb_deadlocks >0/min → immediate alert Innodb_history_list_length >1000 Slow_queries > N/min (business-dependent)

Replication lag >60s → immediate

Replication IO/SQL thread = No → immediate

Disk space >80% (warn 1-2 days before impact)

Connection usage >80% idle in transaction >30s → immediate

PostgreSQL replay_lag >60s

Cache hit rate <99% sustained 10 min

WAL accumulation >10 GB

Checkpoint interval <30s (too frequent) select_full_join increment >0 Innodb_log_waits >0

Group Replication queue_count >100 sustained 5 min

PostgreSQL active sessions > max_connections × 0.8 tmp_blks_written rate >100 MB/s

Overall Implementation Roadmap

Week 1: Basic resource monitoring (CPU, memory, disk, network, connections) via node_exporter.

Weeks 1-2: DB internal metrics: deploy mysqld_exporter / postgres_exporter, configure slow query log, enable performance_schema / pg_stat_statements.

Week 1: Connection pool monitoring: ProxySQL/MaxScale (MySQL), pgbouncer_exporter (PG), application pools (HikariCP/Druid).

Weeks 1-2: Replication & HA: replica lag, standby health, MHA/Orchestrator/Patroni/Group Replication status.

Weeks 2-4: SQL dimension: slow query analysis, full table scan monitoring, index usage.

Ongoing: Alert tiering: P0 (business outage), P1 (performance degradation), P2 (resource alerts), P3 (optimization suggestions).

Advanced: Self-healing: auto-kill long transactions, auto-scale connection pools, auto-throttle slow queries.

Practical Implementation Steps

5.1 Enable MySQL Slow Query Log (5.7/8.0)

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_slow_extra = ON  # 8.0 only
log_throttle_queries_not_using_indexes = 100
min_examined_row_limit = 1000

Apply dynamically:

SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; SET GLOBAL log_queries_not_using_indexes = 'ON';

5.2 Enable MySQL performance_schema

[mysqld]
performance_schema = ON
performance_schema_max_table_instances = 200
performance_schema_max_mutex_classes = 200
performance_schema_max_digest_length = 4096
performance_schema_max_index_stat = 10000

5.7 enables by default; 8.0 enables more. Verify with SHOW VARIABLES LIKE 'performance_schema'; and enable consumers:

UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name LIKE 'events_statements_%';

. Note: default overhead 5-10%, test in staging before enabling on critical production.

5.3 Enable PostgreSQL pg_stat_statements

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top
pg_stat_statements.track_utility = on
pg_stat_statements.save = on
track_io_timing = on
log_min_duration_statement = 2000
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0
log_checkpoint = on
log_connections = on
log_disconnections = on
log_line_prefix = '%m [%p] %q%u@%d/%a from %h '

Restart, then CREATE EXTENSION pg_stat_statements;.

5.4 Install mysqld_exporter

Download v0.15.1, create monitoring user with minimal grants (

PROCESS, REPLICATION CLIENT, SELECT ON *.*, SELECT ON performance_schema.*

), configure .my.cnf (chmod 600), run with collectors for global status, InnoDB metrics, processlist, performance_schema events. Use systemd unit with EnvironmentFile for password (chmod 600).

5.5 Install postgres_exporter

Download v0.15.0, create user with pg_read_all_stats, set DATA_SOURCE_NAME, run on port 9187. Systemd unit with Environment for DSN.

5.6 Install pgbouncer_exporter

PgBouncer 1.21+ exposes /metrics natively; configure stats_users and verify with curl localhost:6432/metrics.

5.7 Install ProxySQL Exporter (MySQL Connection Pool)

ProxySQL 2.0+ has built-in metrics:

SET admin-metrics_password = 'xxx'; LOAD ADMIN VARIABLES TO RUNTIME; SAVE ADMIN VARIABLES TO DISK;

then scrape http://admin:xxx@localhost:6032/metrics.

5.8 Prometheus Scrape Configuration

scrape_configs:
- job_name: 'mysqld'
  static_configs:
  - targets: ['mysql-prod-1:9104', 'mysql-prod-2:9104']
    labels: {env: 'prod', role: 'master'}
- job_name: 'postgres'
  static_configs:
  - targets: ['pg-prod-1:9187', 'pg-prod-2:9187']
    labels: {env: 'prod', role: 'master'}
- job_name: 'pgbouncer'
  static_configs:
  - targets: ['pgbouncer-1:6432', 'pgbouncer-2:6432']
- job_name: 'proxysql'
  static_configs:
  - targets: ['proxysql-1:6032']

5.9 Grafana Dashboard Selection

7362: MySQL Overview (general)

11157: MySQL InnoDB Metrics (detailed)

12740: Percona MySQL (Percona branch)

9628: PostgreSQL Database (general)

455: PostgreSQL Overview (general)

14881: pgBouncer (connection pool)

11807: PG Replication

17378: MySQL Group Replication

12227: Patroni HA

Dashboard IDs may change with updates; verify in official Grafana repository.

5.10 MySQL Alert Rules (Prometheus)

Key alerts: MySQLDown (mysql_up==0), MySQLTooManyConnections (usage >80%), MySQLHighThreadsRunning (>50), MySQLReplicationBroken (IO/SQL thread not running), MySQLReplicationLag (>60s), MySQLBufferPoolLowHitRate (<99%), MySQLDeadlocks (increase >0), MySQLSlowQueries (increase >50/5m), MySQLHistoryListLong (>1000), MySQLLogWaits (increase >0), MySQLGroupReplicationOffline (online members <3).

5.11 PostgreSQL Alert Rules

Key alerts: PostgresDown, PostgresTooManyConnections (>80%), PostgresIdleInTransaction (>5 sessions >2m), PostgresReplicationLag (>60s), PostgresLowCacheHitRate (<99%), PostgresDeadlocks (increase >0), PostgresLongRunningQuery (>600s), PostgresWALLag (>100MB), PostgresLockWaits (>10 sessions), PatroniReplicaLag (>60s).

5.12 MySQL Long Transaction Auto-Kill Script

kill_long_tx.sh

with DRY_RUN=true default, threshold 60s, queries information_schema.innodb_trx, kills via KILL <thread_id>. Schedule via cron every 5 minutes after 1-2 weeks dry-run with mis-kill rate <0.1%.

5.13 PostgreSQL Long Transaction Auto-Kill

Similar script targeting idle in transaction >300s, using pg_terminate_backend(pid).

5.14 Slow Query Auto-Analysis

analyze_slow_queries.sh

uses pt-query-digest to parse slow log, top 10 emailed daily.

5.15 pt-query-digest Full Examples

Real-time: pt-query-digest --processlist h=localhost,u=root,p=xxx; file: pt-query-digest /var/log/mysql/slow.log; time range: --since/--until; general log: --type genlog; binlog: mysqlbinlog ... | pt-query-digest --type binlog; filter: --filter '($event->{arg} =~ m/^SELECT/i)'; JSON output: --output json.

5.16 PostgreSQL Slow Query Tool Comparison

pg_stat_statements

: built-in, no extra components, but no parameter values log_min_duration_statement + grep: full SQL visible, text analysis cumbersome pg_query_governor: auto SQL interception, commercial pgBadger: full HTML reports, requires log parsing auto_explain: auto EXPLAIN, large log volume pg_stat_plans: plan-level stats, needs extra install auto_explain config:

shared_preload_libraries='auto_explain'; auto_explain.log_min_duration='2s'; auto_explain.log_analyze=on; auto_explain.log_buffers=on; auto_explain.log_format='json'; auto_explain.log_nested_statements=on

.

5.17 Complete Monitoring Architecture

MySQL/Postgres → (connections + slow log + binlog)
  → mysqld_exporter / postgres_exporter / pgbouncer_exporter
    → /metrics
      → Prometheus + AlertManager + alert rules
        → Grafana dashboards
        → DingTalk/Feishu/Slack
        → Slow query analyzer (pt-query-digest / pg_query_governor)

5.18 Daily MySQL Inspection Script

daily_check.sh

collects: connection counts, QPS/TPS, buffer pool stats, long transactions (>10s), replication status, top 20 schemas by size, slow query count. Output to timestamped file.

5.19 Daily PostgreSQL Inspection Script

daily_check.sh

outputs CSV with: connection count, per-database commit/rollback, long transactions (>5min), replication lag per replica, table bloat (dead tuples >10k), cache hit rate.

Common Commands

6.1 Connection / Status

# MySQL
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
SELECT * FROM information_schema.innodb_trx\G;
SELECT SUBSTRING_INDEX(host, ':', 1) AS client_ip, count(*) FROM information_schema.processlist GROUP BY client_ip ORDER BY count DESC LIMIT 10;
# PostgreSQL
SELECT client_addr, state, count(*) FROM pg_stat_activity GROUP BY client_addr, state ORDER BY count DESC;

6.2 Locks / Waits

# MySQL 5.7
SELECT r.trx_id AS waiting_trx, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query,
  b.trx_id AS blocking_trx, b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
# MySQL 8.0+
SELECT waiting_pid, waiting_query, blocking_pid, blocking_query
FROM performance_schema.data_lock_waits
JOIN performance_schema.events_statements_current w ON w.thread_id = waiting_pid;
# PostgreSQL
SELECT pg_class.relname, pg_locks.mode, pg_stat_activity.usename, pg_stat_activity.query,
  EXTRACT(EPOCH FROM (now() - pg_stat_activity.query_start)) AS wait_sec
FROM pg_locks
JOIN pg_class ON pg_locks.relation = pg_class.oid
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE NOT pg_locks.granted
ORDER BY query_start;

6.3 Replication Status

# MySQL
SHOW SLAVE STATUS\G
SHOW REPLICA STATUS\G  # 8.0.22+
SELECT * FROM performance_schema.replication_group_members;
SELECT * FROM performance_schema.replication_group_member_stats;
# PostgreSQL
SELECT * FROM pg_stat_replication;
SELECT pg_is_in_recovery();
SELECT now() - pg_last_xact_replay_timestamp() AS replay_lag;

6.4 Tables / Indexes

# MySQL table sizes
SELECT table_schema, table_name, ROUND((data_length+index_length)/1024/1024,2) AS total_mb, table_rows
FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY (data_length+index_length) DESC LIMIT 20;
# MySQL index usage
SELECT object_schema, object_name, index_name, count_star, count_read, count_write, count_fetch, count_insert, count_update, count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema NOT IN ('mysql','sys') ORDER BY count_star DESC LIMIT 20;
# PostgreSQL table sizes
SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size, pg_size_pretty(pg_indexes_size(relid)) AS index_size,
  n_live_tup, n_dead_tup FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;
# PostgreSQL index usage
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes ORDER BY idx_scan DESC LIMIT 20;

6.5 Kill Threads

# MySQL
KILL 12345;
KILL CONNECTION 12345;  # also disconnect
# PostgreSQL
SELECT pg_terminate_backend(12345);  # terminate session
SELECT pg_cancel_backend(12345);    # cancel query only

Risk: kill does not notify application; app receives connection exception and must retry.

6.6 Binlog / WAL

# MySQL
SHOW BINARY LOGS;
SHOW MASTER STATUS;
mysqlbinlog --verbose --base64-output=DECODE-ROWS /var/lib/mysql/mysql-bin.000123
# PostgreSQL
SELECT pg_current_wal_lsn();
SELECT pg_walfile_name();
SELECT * FROM pg_ls_waldir();
SELECT * FROM pg_stat_archiver;

6.7 Performance Benchmarks

# MySQL sysbench
sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=xxx --mysql-db=sbtest --tables=10 --table-size=1000000 --threads=64 --time=60 --report-interval=5 run
# PostgreSQL pgbench
pgbench -i -s 100 pgbench
pgbench -c 64 -j 8 -T 60 pgbench

Configuration Examples

7.1 MySQL Production my.cnf

Key settings: innodb_buffer_pool_size=64G, innodb_log_file_size=4G, innodb_flush_log_at_trx_commit=1, innodb_flush_method=O_DIRECT, innodb_io_capacity=2000, innodb_purge_threads=8, binlog_format=ROW, sync_binlog=1, expire_logs_days=7, gtid_mode=ON, slave_parallel_workers=8, slow_query_log=1, long_query_time=2, performance_schema=ON.

7.2 PostgreSQL Production postgresql.conf

Key settings: shared_buffers=16GB, work_mem=64MB, maintenance_work_mem=2GB, wal_level=replica, max_wal_size=4GB, wal_keep_size=1GB, random_page_cost=1.1, effective_cache_size=48GB, autovacuum_vacuum_scale_factor=0.05, checkpoint_completion_target=0.9, log_min_duration_statement=2000, shared_preload_libraries='pg_stat_statements,auto_explain', track_io_timing=on, idle_in_transaction_session_timeout=10min.

7.3 mysqld_exporter systemd Unit

With security hardening: NoNewPrivileges=true, ProtectSystem=strict, PrivateTmp=true, TLS certs, dedicated user.

7.4 AlertManager Routing

Group by alertname, instance, severity; group_wait=30s, group_interval=5m, repeat_interval=4h. Critical → PagerDuty, warning → Slack, critical also → DingTalk.

7.5 ProxySQL Configuration

Admin interface on 6032, MySQL interface on 6033, hostgroups for writer (0) and reader (1), query rules routing SELECT ... FOR UPDATE to writer, other SELECTs to reader.

7.6 pgBouncer Configuration

Transaction pooling, max_client_conn=4000, default_pool_size=20, min_pool_size=5, reserve_pool_size=5, server_idle_timeout=600, stats on port 6432.

7.7 Patroni Configuration

Etcd-backed HA, maximum_lag_on_failover=1048576, use_pg_rewind=true, superuser/replication credentials, tags for failover/load balancing.

Log & Metric Observation Methods

8.1 MySQL Error Log

Locations: /var/log/mysqld.log, /var/log/mysql/error.log. Grep for error|warning|innodb|deadlock|abort|kill, restart marker ready for connections, binlog dump issues.

8.2 MySQL Slow Query Log Analysis

Count: grep -c "Query_time:" /var/log/mysql/slow.log. Top 10: pt-query-digest /var/log/mysql/slow.log --limit 10. Time-slice:

grep "Time: 2026-06-17T0[0-3]" /var/log/mysql/slow.log | head

.

8.3 performance_schema Common Queries

-- Current top wait events
SELECT digest_text, count_star, sum_timer_wait/1e9 AS total_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text IS NOT NULL ORDER BY sum_timer_wait DESC LIMIT 10;
-- Tables with full table scans
SELECT object_schema, object_name, count_read, count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NULL AND count_read > 0 ORDER BY count_read DESC LIMIT 10;
-- Unused indexes
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0 AND object_schema NOT IN ('mysql','sys')
ORDER BY object_schema, object_name;
-- File I/O hotspots
SELECT file_name, event_name, count_star, sum_timer_wait/1e9 AS total_ms
FROM performance_schema.file_io_summary_by_instance ORDER BY sum_timer_wait DESC LIMIT 10;

8.4 PostgreSQL Logs

Default: /var/log/postgresql/postgresql-16-main.log. Grep for error|fatal|panic, duration: (slow queries), checkpoint, automatic vacuum, deadlock detected.

8.5 PG Key View Inspections

-- Table bloat top 10
SELECT schemaname||'.'||relname AS table_name, n_live_tup, n_dead_tup,
  ROUND(n_dead_tup*100.0/nullif(n_live_tup+n_dead_tup,0),2) AS dead_pct,
  last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
-- Long transactions top 10
SELECT pid, usename, state, now()-xact_start AS xact_duration, LEFT(query,100) AS query_preview
FROM pg_stat_activity WHERE xact_start IS NOT NULL AND now()-xact_start > interval '1 second' ORDER BY xact_start LIMIT 10;
-- Unused indexes
SELECT schemaname||'.'||relname AS table_name, indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, idx_scan
FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC LIMIT 20;
-- Replication slots
SELECT slot_name, plugin, slot_type, active,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_size
FROM pg_replication_slots;

8.6 Buffer Pool Status Observation

SHOW ENGINE INNODB STATUS\G

→ BUFFER POOL AND MEMORY section. Free buffers / Buffer pool size <5% indicates memory pressure. 8.0+ can query information_schema.innodb_buffer_pool_stats for pool_id, hot_size, warm_size.

Troubleshooting Paths

9.1 MySQL Connection Exhaustion

Symptom: application reports Too many connections. Steps: 1) check Threads_connected, Max_used_connections, max_connections; 2) identify top client IPs via information_schema.processlist; 3) count sleep threads >600s; 4) check Connection_errors%; 5) remediate: tune app pool (HikariCP maxLifetime), temporarily raise max_connections, add ProxySQL/RDS Proxy, kill sleep threads.

9.2 MySQL Replication Lag

Symptom: replica falls behind, stale reads. Steps: 1) check Seconds_Behind_Master; 2) compare master SHOW MASTER STATUS; 3) check Relay_Log_Space growth; 4) measure replica replay rate via Exec_Master_Log_Pos delta; 5) common causes: underpowered replica, single-threaded replay (enable slave_parallel_workers>1), large transactions, network jitter, heavy DDL on master; 6) fixes: parallel replication, split large transactions, upgrade replica I/O (SSD), dedicated network.

9.3 MySQL Deadlocks

Symptom:

Deadlock found when trying to get lock; try restarting transaction

. Steps: 1) SHOW ENGINE INNODB STATUS\G → LATEST DETECTED DEADLOCK; 2) enable innodb_print_all_deadlocks=ON to log all; 3) ensure app retries on deadlock; 4) add proper indexes to reduce lock granularity; 5) reduce transaction scope.

9.4 PostgreSQL Long Transactions / Lock Waits

Symptom: app requests hang. Queries: active sessions, lock waits (join pg_locks, pg_class, pg_stat_activity where not granted), idle in transaction sessions. Kill with pg_terminate_backend(pid) or cancel query with pg_cancel_backend(pid).

9.5 Database Disk Growth

Steps: 1) du -sh /var/lib/mysql/* | sort -h | tail; 2) MySQL binlog size via SHOW BINARY LOGS; 3) PG WAL size via pg_ls_waldir(); 4) PG table bloat via pg_stat_user_tables; 5) PG index bloat via pg_stat_user_indexes. Remediation: PURGE BINARY LOGS TO ..., clean relay logs, VACUUM FULL (off-peak), increase wal_keep_size and enable archiving.

9.6 Patroni Split-Brain After Failover

Check: patronictl list, etcd cluster health, each node's /patroni endpoint for role/state/timeline. Only one leader expected per cluster.

9.7 Fault Tree: MySQL Slow Query Spike

MySQL slow query spike
├─ Business volume surge → verify QPS rise
├─ Index missing → EXPLAIN shows full scan
├─ Stale statistics → plan rows vs actual large deviation
├─ Severe lock waits → Innodb_row_lock_time spike
├─ Buffer pool hit rate low → Innodb_buffer_pool_reads spike
├─ Disk IO saturated → iostat await >10ms
├─ Temp tables on disk → Created_tmp_disk_tables spike
└─ New bad SQL deployed → pt-query-digest top 1 is new SQL

Risk Reminders

10.1 KILL Risks

KILL <thread_id>

: cancels query, keeps connection; transaction rolls back. KILL CONNECTION <thread_id>: disconnects, transaction rolls back.

App receives Query execution was interrupted or Lost connection — must retry.

Requirement: dry-run 1-2 weeks, confirm mis-kill <0.1% before enabling.

10.2 DROP / TRUNCATE Risks

DROP TABLE

: irreversible, backup first. TRUNCATE TABLE: non-transactional, cannot rollback.

Double-confirm + backup:

mysqldump --single-transaction --master-data=2 db table > backup.sql

, verify size >0KB before drop.

10.3 Parameter Change Risks

innodb_buffer_pool_size

: check free memory first. max_connections increase requires open_files_limit adjustment. long_query_time reduction causes slow log explosion.

10.4 Replication Operation Risks

RESET SLAVE

wipes all replication info.

Before failover, ensure Seconds_Behind_Master = 0.

GTID mode: avoid arbitrary SET GTID_PURGED.

10.5 Database Version Upgrade

5.7→8.0 irreversible (system table format changes).

Run mysql_upgrade --check first.

Review release notes for breaking changes.

Upgrade replicas first, then master; rehearse in test.

Backup before upgrade window.

10.6 Exporter Risks

Credentials from env vars or secret manager, not config files.

Least privilege: only SELECT, no DELETE/DROP.

Monitor exporter itself (alert if down).

10.7 Auto-Kill Risks

Default DRY_RUN=true.

Whitelist: replication threads, internal DBA sessions.

Audit logging mandatory.

10.8 Data Migration Risks

Schema changes: use gh-ost / pt-online-schema-change, not direct ALTER TABLE.

Backups: parallel mysqldumper / mydumper, not single-threaded mysqldump.

PG large table ALTER: use pg_repack (non-blocking).

Verification Methods

11.1 Exporter Verification

ss -lntp | grep 9104
curl -s http://localhost:9104/metrics | head -20
curl -s http://localhost:9104/metrics | grep -E "mysql_up|mysql_global_status"
curl -s http://localhost:9104/metrics | grep "mysql_global_status_threads_connected"

11.2 Prometheus Verification

mysql_up
mysql_global_status_threads_connected
mysql_global_status_threads_running
rate(mysql_global_status_questions[1m])

11.3 Grafana Verification

Dashboard shows data

Variables dropdown lists targets

Time range switching works

Alert states correct (green/red)

11.4 Alert Drills (Quarterly)

Create test deadlock in staging: two sessions updating same row with SELECT SLEEP(30) in between; verify AlertManager fires deadlock alert.

11.5 Self-Healing Verification

1) Create long transaction ( SELECT SLEEP(120)); 2) Run kill script with DRY_RUN=true, check log; 3) After confirming no mis-kills, set DRY_RUN=false.

11.6 Database Health Check Script

db_health_check.sh

outputs: connection usage %, buffer pool hit rate, cumulative slow queries, replication status (IO/SQL running, seconds behind).

Rollback Plans

12.1 Exporter Upgrade Rollback

Backup binary, stop service, replace, start; on failure restore backup and restart.

12.2 Alert Rule Rollback

Tar rules directory, edit, validate with promtool check rules, hot-reload via curl -X POST http://prometheus:9090/-/reload; on failure extract backup and reload.

12.3 Parameter Change Rollback

Backup my.cnf, apply dynamically with SET GLOBAL; on issue revert via SET GLOBAL to previous value.

12.4 Version Upgrade Rollback

5.7→8.0 cannot rollback (system table format). Must validate on replicas first, rehearse failover in test.

12.5 Auto-Heal Script Rollback

Remove cron file, run dry-run to confirm no kills needed, restore original cron from backup.

Production Considerations

13.1 Performance Overhead

mysqld_exporter

: 1 connection + few SHOW commands, negligible. postgres_exporter: 1 connection, dedicated account recommended. pgbouncer_exporter: scrapes every 60s, negligible. performance_schema: 5-10% overhead; test on critical DBs.

13.2 Least Privilege

-- mysqld_exporter
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
GRANT SELECT ON performance_schema.* TO 'exporter'@'localhost';
-- postgres_exporter
GRANT pg_read_all_stats TO postgres_exporter;

13.3 Credential Management

.my.cnf

chmod 600.

Systemd EnvironmentFile with 600 permissions.

Production: HashiCorp Vault / cloud KMS.

Rotate quarterly.

13.4 Cross-Instance Labels

Prometheus scrape config labels: env, role, cluster, biz for multi-dimensional querying.

13.5 Alert Convergence

group_by: ['alertname', 'instance']

to deduplicate. group_wait: 30s, group_interval: 5m to prevent storms.

Critical → PagerDuty/DingTalk on-call; warning → Slack channel.

13.6 HA Exporter

Alert on exporter down: up{job="mysqld"} == 0 for 2m. Deploy active-passive with Keepalived VIP or Consul service discovery.

13.7 Long-Term Archiving

Prometheus long-term storage: Thanos/Cortex/VictoriaMetrics.

Retain key metrics 1 year.

Archive slow logs to object storage.

13.8 Multi-Tenancy / Compliance

Mask sensitive data (passwords, IDs) in monitoring.

Quarterly audit of monitoring accounts.

Cross-region monitoring over dedicated lines with encryption.

Summary

Database monitoring's core is detecting anomalies before business impact. Using Prometheus + Grafana + Exporters, three things matter:

Right metrics: MySQL — buffer pool hit rate, Threads_running, row lock waits, replication lag; PostgreSQL — cache hit rate, idle in transaction, pg_stat_replication lag, WAL accumulation.

Right thresholds: All thresholds are baselines; tune to business. Common red lines: connection pool >80%, hit rate <99%, replication lag >60s, deadlocks >0.

Closed loop: Alert → diagnose → fix → verify → rollback → retrospective; every step needs concrete actions.

Common pitfalls:

Watching CPU misses real DB issues (lock/IO waits don't raise CPU).

Confusing Threads_connected with Threads_running.

Routing SELECT FOR UPDATE to replica causing dirty reads.

Hardcoding exporter passwords in config.

Killing long transactions without notifying application.

Database monitoring differs from stateless services: DB is stateful (restart/failover costly), bottlenecks are often locks/IO not CPU, "fake death" (connections up but queries slow) is more dangerous than hard down, alert tiers (P0-P3) essential, not one-size-fits-all.

Monitoring isn't set-and-forget; it requires continuous tuning as business grows, data volume increases, SQL evolves. Quarterly alert reviews: delete never-fired alerts, adjust always-firing ones (threshold or add auto-heal). The DBA's real job isn't firefighting — it's preventing fires.