Relational Database Monitoring in the agent

Embed
Preview of the card
<a href="https://watchtowerops.net/docs/dbm-relational"><img src="https://watchtowerops.net/docs/_api/public/orgs/aa5959cb-1a0b-4a9a-bc9a-913e7ac31cd9/spaces/b7aac181-9916-4805-b253-becc5fd2ca62/pages/815cc3cb-10b7-4506-b9b9-92cec055202c/social-card.svg?v=3e7ae33eec8b" alt="Relational Database Monitoring in the agent" width="600" height="315"></a>

The agent reads each engine's own statistics views over a read-only session inside your network. Nothing needs the database to be reachable from Watchtower, and no exporter runs next to it. DSNs live only in the agent's own config file. Postgres is covered in integrations.md. All engines share one model:

  • Per-statement series (dbm: true): the engine's cumulative statement statistics are read every round and turned into deltas. The agent keeps its own running totals, so a reset, eviction or window rollover on the engine side does not break rate(). Up to 200 statements per round, busiest first. Labels: database, the engine's statement id (digest, query_hash, sql_id, fingerprint_id, queryid) and the bind-safe query.

  • Activity samples (collect_samples: true): the running statements are read every second. Every 10 s the agent ships db.activity.sessions{db.namespace,db.session.state,db.wait_event_type,db.wait_event} (average active sessions; CPU when not waiting) and one db.query.sample log record per statement and wait.

  • Plans (explain: true): a sampled SELECT is planned at most once per 10 minutes, at most 5 per 10 s, with a 2 s timeout. Plans arrive as db.query.plan log records with db.plan.total_cost.

  • Join key: db.query.hash (see sql-normalization.md) is computed from the raw statement before it is made bind-safe. It is stamped on activity samples, on sampled plans, on slow-log samples, and on the per-statement series of the engines whose statistics keep raw or $n text (mssql, oracle, yugabytedb). It is the same hash spans carry. MySQL, TiDB and CockroachDB digests are already normalized by the engine and have no raw text, so their series join through the samples instead.

  • Privacy: statement text is bind-safe (every literal becomes ?). Every string in a plan goes through the same scrubber, and so do the SQL Server showplan attributes that carry literals. Deadlock and lock records never include row data.

Module

Always

dbm

collect_samples

explain

mysql (MariaDB too)

status counters, InnoDB buffer pool (mysql.innodb.buffer_pool.*), INNODB_METRICS deadlocks, lock timeouts and history list length, replica lag and IO/SQL threads per channel, mysql.replica.gtid_behind (MySQL GTID sets including tagged GTIDs, or MariaDB domain positions), InnoDB lock waits as blocking chains

mysql.query.{calls,total_time,rows_sent,rows_examined,no_index_used,mean_time} from events_statements_summary_by_digest; a db.deadlock record for each new LATEST DETECTED DEADLOCK (statements, tables, indexes, lock modes)

threads + events_statements_current + events_waits_current

EXPLAIN FORMAT=JSON in the statement's schema, in a read-only session (max_execution_time or max_statement_time)

mssql

performance counters, wait stats, blocked requests

mssql.query.{calls,total_time,rows,logical_reads,physical_reads,mean_time} from sys.dm_exec_query_stats; Query Store state and storage use, plan regressions (mssql.query_store.plans, .regression_ratio) every 10 rounds; blocking chains with both statements

sys.dm_exec_requests, with wait types grouped (Lock, Buffer IO, Tran Log IO, Parallelism and so on)

the cached plan from sys.dm_exec_query_plan (nothing runs)

oracle (dsn)

v$sysstat (commits, executions, hard parses, reads, redo), wait time per class, sessions, tablespace use, blocking chains

oracle.query.{calls,total_time,rows,buffer_gets,disk_reads,mean_time} from v$sqlarea

v$session, sampled every second (the free equivalent of ASH)

v$sql_plan of the cached cursor, as a tree

cockroachdb

live/total nodes, per-store ranges, unavailable and under-replicated ranges, leaseholders, capacity, contention per table

cockroachdb.query.* from crdb_internal.cluster_statement_statistics

n/a

the plan CockroachDB samples per fingerprint

tidb

TiKV/TiFlash stores (up, regions, leaders, capacity), data_lock_waits as blocking chains, db.deadlock from cluster_deadlocks

tidb.query.* from cluster_statements_summary (total_keys much higher than processed_keys means old MVCC versions are scanned)

n/a

EXPLAIN FORMAT='tidb_json' (TiDB 6.5+) of the digest's sample statement

yugabytedb

tablet servers and connections (yb_servers()), local tablets by table and state

yugabytedb.query.* from pg_stat_statements; db.activity.sessions from yb_active_session_history

n/a

n/a

sqlite

header-only reads of the files in paths: size, pages, freelist ratio, WAL mode, WAL size and frames, leftover rollback journal, sqlite.wal.generation_age

db_slow_log

tails path (engine: mysql or postgresql) and sends each slow statement as a bind-safe db.query.sample (db.sample.source: slow_log, duration_log, auto_explain). With auto_explain.log_format = json, the plan also becomes a db.query.plan record. Text-format plans are not sent, because their filters carry literals. Add db=%d,user=%u to log_line_prefix to get the database and user.

Grants

-- MySQL / MariaDB
CREATE USER 'wt_monitor'@'%' IDENTIFIED BY '...';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON performance_schema.* TO 'wt_monitor'@'%';
GRANT SELECT ON sys.* TO 'wt_monitor'@'%';
-- waits in samples need: UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME='events_waits_current';
-- explain needs SELECT on the application schemas (EXPLAIN never runs the statement).

-- SQL Server
GRANT VIEW SERVER STATE TO wt_monitor; -- plus VIEW DATABASE STATE in each database with Query Store

-- Oracle
GRANT CREATE SESSION, SELECT_CATALOG_ROLE TO wt_monitor;

-- CockroachDB
GRANT SYSTEM VIEWACTIVITY TO wt_monitor;

Oracle reads no Diagnostics Pack views (v$active_session_history, DBA_HIST_*). Without dsn, the oracle module keeps scraping oracledb_exporter at url. YugabyteDB views are per node, so configure one module per tserver to cover a universe. The sqlite module never opens the database through SQLite and takes no lock. The WAL generation age counts from when the agent first saw the current WAL generation, so it reads low right after an agent restart.

Updated