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.

Best practices
Illustration of metrics, logs, and query analysis

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

Get a tailored plan

Contact us

Join the community

Scan with WeChat
to join the community

Try Guance

Start online and pay only for what you use.

Get started

Choose a Guance plan

Code hosting