MySQL observability with DataKit: metrics, slow logs, and DBM
Collect MySQL instance metrics, parse slow query logs, and use Performance Schema digests with Guance DataKit. Cross-check DBM with application traces.
An orders endpoint slows down. The connection count can tell you whether the database is under pressure. A slow query log can show one expensive execution. A statement digest can show whether the same query shape accumulated time across many executions. Start with the signal that can answer the question, then check the other two in the same window.
This guide configures those three paths in Guance DataKit for a self-managed MySQL 8.x instance. The examples follow the documented collector settings and use placeholder credentials and paths.
What each MySQL signal tells you
| Signal | Collection path | What it helps you check |
|---|---|---|
| Instance metrics | MySQL status and configuration queries | Connections, query throughput, slow query counts, InnoDB reads, and replication status where configured. |
| Slow query log | A readable MySQL log file and the MySQL Pipeline | The elapsed time, lock time, rows examined, and SQL text of executions that qualify for slow logging. |
| Statement-level DBM | Performance Schema digests, with optional activity and plan samples | The query shapes accounting for time, execution count, and rows across a window. |
In Guance APM, first check whether the slow request has a recorded database span. Use its endpoint and time to look up the database instance and environment. A standard MySQL slow log has no application trace_id, so a matching tag alone cannot assign an execution to a particular request.
Prepare a collector account
The host example uses a Unix socket, with DataKit and MySQL on the same Linux machine. DataKit must already report to the intended workspace. For a remote or Kubernetes deployment, use a reachable database endpoint, scope the account to the collector's source, configure TLS and protect its credentials. File collection also requires a readable file in the collector's environment.
Have a database administrator replace the placeholder and create the dedicated account. These grants cover the example's InnoDB status, binary log or replication status, and Performance Schema reads. PROCESS exposes more session information, so review it with the database owner.
CREATE USER 'datakit'@'localhost' IDENTIFIED BY '<MONITOR_PASSWORD>';
GRANT PROCESS ON *.* TO 'datakit'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'datakit'@'localhost';
GRANT SELECT ON performance_schema.* TO 'datakit'@'localhost';
The example excludes user and schema collectors that would need access beyond this scope. If you later need user statistics, application schema metadata, or plans, review their grants separately. DBM requires Performance Schema and the relevant instrumentation. The slow log path requires MySQL to write to a file DataKit can read.
Configure the three collection paths
1. Start with instance metrics
In the default Linux installation, prepare a configuration from the installed sample. Check for an existing configuration for this instance first. The example handles two documented sample locations and leaves an existing db/mysql.conf in place.
cd /usr/local/datakit/conf.d || exit 1
if [ ! -e db/mysql.conf ]; then
sample=db/mysql.conf.sample
[ -f "$sample" ] || sample=samples/mysql.conf.sample
[ -f "$sample" ] || exit 1
mkdir -p db && cp "$sample" db/mysql.conf
fi
Use the actual socket path and credentials. Enable replication on replicas where you need replication status. MySQL 8.4 removed SHOW SLAVE STATUS. For an 8.4 replica, check that your DataKit version uses SHOW REPLICA STATUS before enabling collection.
[[inputs.mysql]]
sock = "/var/lib/mysql/mysql.sock"
user = "datakit"
pass = "<MONITOR_PASSWORD>"
interval = "10s"
innodb = true
replication = false
dbm = false
measurement_version = "v2"
metric_exclude_list = ["mysql_schema", "mysql_table_schema", "mysql_user_status"]
[inputs.mysql.object]
enabled = true
[inputs.mysql.object.collect_schemas]
enabled = false
[inputs.mysql.tags]
database_instance = "orders-mysql-01"
env = "prod"
Give each instance its own stable database_instance and use the same environment convention as the application. Restart DataKit according to your installation and check its errors and the mysql_metric measurement in the workspace. This configuration uses v2, which combines several metric families. Older configurations may use separate measurements. Use the change in Questions over time for client statement throughput. Define your transaction counter before charting TPS.
2. Read and parse the slow query log
Check the server variables first.
SHOW GLOBAL VARIABLES
WHERE Variable_name IN ('slow_query_log', 'log_output', 'slow_query_log_file');
Set the collector path to the actual slow_query_log_file. MySQL needs slow_query_log set to ON, with FILE among the log_output destinations. Put this subtable in the same [[inputs.mysql]] block, replacing any existing subtable with that name. The expression matches standard slow log entries headed by # Time:.
[inputs.mysql.log]
files = ["/var/log/mysql/slow.log"]
multiline_match = "^# Time:"
pipeline = "mysql.p"
Check an actual entry for a complete statement and parsed query_time in seconds, lock_time, and rows_examined. You can then filter for statements over two seconds on the relevant instance. Use representative records if the file format varies. Decide how to redact and restrict access before ingesting SQL text that may contain parameter values.
3. Start DBM with statement summaries
MySQL's Performance Schema groups statements by digest, so calls like SELECT * FROM orders WHERE id = 123 with different IDs can be analyzed together. The collector reads events_statements_summary_by_digest. Check that Performance Schema is enabled and the statement instruments you need are enabled and timed. Confirm the digest consumer and review the statement and wait consumers before adding activity or plan collection.
SHOW GLOBAL VARIABLES LIKE 'performance_schema';
SELECT NAME, ENABLED
FROM performance_schema.setup_consumers
WHERE NAME IN (
'statements_digest', 'events_statements_current',
'events_statements_history', 'events_statements_history_long',
'events_waits_current'
);
Set dbm = true in the existing [[inputs.mysql]] block. Merge or replace these subtables to start with digest metrics. Read the DataKit MySQL configuration before enabling activity or plan samples. The plan path needs sufficient privileges for EXPLAIN on the target statement or a reviewed procedure described in the integration documentation. Scope the definer and the grant to execute any SQL SECURITY DEFINER routine.
[inputs.mysql.dbm_metric]
enabled = true
interval = "10s"
limit = 10000
[inputs.mysql.dbm_sample]
enabled = false
[inputs.mysql.dbm_activity]
enabled = false
The first observation of a query establishes a baseline. The next eligible interval can report deltas. Divide delta_timer_wait by delta_count_star over the same window for a mean when the count is positive. DataKit reports delta_timer_wait in nanoseconds, so the mean is in nanoseconds. Raw Performance Schema timers are in picoseconds, and slow log query_time is in seconds. That mean cannot give you P95. To compare query shapes across instances, start with normalized_query_hash and retain instance, schema, and time as separate filters. Use query_signature to look for a collected plan for the relevant statement. Session activity and plans are sampled, so coverage depends on settings and timing.
Set up a database dashboard and alerts
Start in the Guance Database Explorer and confirm the instance and collected signals. A MySQL view can put connections, throughput, InnoDB, and replication in one time range. Treat these monitor examples as starting points and tune them to the workload.
| Signal | Example trigger | How to interpret it |
|---|---|---|
| Connection usage | Sustained above 80% | Compare Threads_connected / max_connections on the same instance and check connection errors. |
| Slow queries | Rate rises above baseline | Use the change in Slow_queries, accounting for resets. |
| Replica lag | Sustained above 30 seconds | Check Seconds_Behind_Source or the older Seconds_Behind_Master together with thread state, errors, and null values. |
| Availability | Repeated connection failure | Run a MySQL connection check from a reachable location. Track missing collector data separately. |
| Buffer pool hit ratio | Falls below its normal range | Use 1 - Δbuffer_pool_reads / Δbuffer_pool_read_requests over the same window. Skip a zero denominator or reset. |
For a slow request, take the target and time from the database span and compare connection pressure, waits, and expensive digests on that instance. If the span only names a proxy, resolve the backend first. A slowdown on both sides is useful evidence. Check query samples and changes to the application or database before settling on the cause.
Frequently asked questions
How much load does collection add?
Status queries, Performance Schema instrumentation, session samples, plan collection, and file reads all have a cost. Enable the signals you need, observe database and collector load, then adjust intervals, row limits, and optional collectors. Read-only access still consumes resources.
What about RDS or PolarDB?
Cloud metrics, slow logs, and DBM have different paths. Guance documents cloud collection for Alibaba Cloud RDS for MySQL (Simplified Chinese) and PolarDB for MySQL (Simplified Chinese). Check the provider's log export path and whether a host file is accessible. For DBM, check network access, Performance Schema, privileges, and available subfeatures separately.
Will older slow query logs be imported?
A file collector's starting offset depends on its version, cached offsets, and configuration. For a deliberate backfill, follow the generic log collector's file offset rules and plan a separate import. Check duplicate ingestion, event timestamps, volume, and retention. The generic collector's options do not belong in the MySQL log subtable.
Further reading. MySQL monitoring metrics and alerts · MySQL slow query log reference
Contact us
Join the community
to join the community
Try Guance
Start online and pay only for what you use.
Get startedChoose a Guance plan
