Slow Queries Causing Outages? Build a Cloud‑Native Distributed MySQL Slow‑Log Platform from Scratch

Problem Overview

A single 3‑second query can cascade through connection pools, thread pools, cache misses and retry storms, turning a "slow" SQL into a system‑wide outage. The root cause is not the query itself but the inability to discover, locate, quantify and remediate it within minutes.

MySQL Slow‑Log Fundamentals

MySQL writes a slow‑log entry only after a statement finishes, based on thresholds such as Query_time > long_query_time, log_queries_not_using_indexes or log_slow_admin_statements. The log is a result‑oriented view , not a step‑by‑step trace, and is suitable for low‑intrusion offline or near‑real‑time analysis.

slow_query_log = ON

long_query_time = 0.1~0.5

(core transaction workloads typically start at 0.2‑0.3 s) log_output = FILE (production‑preferred for zero‑intrusion collection) log_queries_not_using_indexes – enable cautiously, can flood logs

log_slow_admin_statements = ON

min_examined_row_limit = 100

(filter trivial queries) log_timestamps = UTC For deeper insight combine the slow log with performance_schema, events_statements_summary_by_digest or APM tracing.

Choosing the Log Destination

FILE : Simple, container‑friendly, works with sidecars or DaemonSets; requires parsing.

TABLE : Directly queryable via SQL; higher write overhead and storage growth.

performance_schema : Structured aggregation, no raw SQL text; best used as a supplement.

Production practice: use FILE for raw detail collection and performance_schema for aggregated metrics.

Platform Architecture

┌──────────────────────────────┐
                     │      Operations Layer          │
                     │ Grafana / Kibana / Alerting   │
                     │ Ticket / DingTalk / Auto‑Limit │
                     └──────────────┬───────────────┘
                                    │
                     ┌──────────────▼───────────────┐
                     │      Query & Service Layer    │
                     │ Query API / Aggregation API   │
                     └──────────────┬───────────────┘
                                    │
        ┌─────────────────────┴─────────────────────┐
        │                                             │
 ┌──────▼───────┐                         ┌───────▼───────┐
 │ Detail Store │                         │ Aggregate Store│
 │ Elasticsearch│                         │ ClickHouse /   │
 │ / OpenSearch│                         │ Doris          │
 └──────┬───────┘                         └───────┬───────┘
        │                                         │
        └──────────────────┬──────────────────────┘
                           │
                     ┌─────▼─────┐
                     │ Stream    │
                     │ Processing│
                     │ Go Consumer│
                     │ / Flink   │
                     │ Parse /   │
                     │ Fingerprint│
                     │ Enrich /  │
                     │ Aggregate │
                     └─────┬─────┘
                           │
                     ┌─────▼─────┐
                     │ Message   │
                     │ Bus       │
                     │ Kafka /   │
                     │ Pulsar /  │
                     │ Redpanda  │
                     └─────┬─────┘
                           │
        ┌──────────────────┴───────────────────┐
        │                                      │
 ┌──────▼───────┐                    ┌───────▼───────┐
 │ Collector    │                    │ Metadata      │
 │ Filebeat /   │                    │ (CMDB / K8s)  │
 │ Fluent Bit   │                    │               │
 └──────┬───────┘                    └───────┬───────┘
        │                                      │
        │                                      │
 ┌──────▼───────┐                    ┌─────▼─────┐
 │ MySQL Pods   │                    │ MySQL      │
 │ (multiple)   │                    │ Instances  │
 └──────────────┘                    └────────────┘

The design evolves from a naive Filebeat → Elasticsearch pipeline to a buffered, scalable flow that separates raw detail storage from aggregated analytics.

Collector Strategies in Kubernetes

DaemonSet : Low operational cost, automatically picks up new pods, high resource utilization – suitable for large fleets.

Sidecar : Strong isolation, per‑pod configuration, direct file access – higher pod resource consumption, best for small or custom deployments.

Agentless : Minimal changes, pulls logs from a shared location – higher latency and bottleneck, only a transitional approach.

Recommendation: prefer DaemonSet for cloud‑native clusters, Sidecar for isolated cases, Agentless only as a temporary solution.

Capacity Planning for Peak Loads

Assume 200 MySQL instances, each producing up to 80 slow‑log entries per second during a spike (≈16 000 entries/s, ≈32 MiB/s). Enabling log_queries_not_using_indexes or a query storm can multiply traffic 3‑10×, so design for the worst‑case “fault‑traffic”.

Kafka as the Critical Buffer

Smooths upstream spikes.

Provides time for downstream parsers to scale.

Supports replay and new consumer onboarding.

Best practices: partition by instance or pod hash, separate topics per environment, retain at least 24‑72 hours, use snappy or lz4 compression.

Stateless Parsing Layer

The parser must not keep state in memory; each message is processed independently, while minute‑level aggregates are stored in an external state store (e.g., Flink state or ClickHouse).

Go Parsing Service Highlights

Kafka consumer group with configurable workers.

Batch writes to downstream storage.

Graceful handling of malformed messages.

Extensive logging for observability.

type Consumer struct {
    reader      *kafka.Reader
    writer      Writer
    logger      *slog.Logger
    workerNum   int
    batchSize   int
    flushEvery  time.Duration
}

func (c *Consumer) Run(ctx context.Context) error {
    // worker pool, parsing, batch flushing logic ...
}

The parser extracts timestamps, user/host, metrics, optional DB name, raw SQL, normalizes the SQL into a template (replacing literals with ?), generates a SHA‑1 digest, and enriches the event with Kubernetes labels (service, tenant, cluster, etc.).

func NormalizeSQL(sql string) string {
    normalized := commentPattern.ReplaceAllString(sql, "")
    normalized = stringPattern.ReplaceAllString(normalized, "?")
    normalized = numberPattern.ReplaceAllString(normalized, "?")
    normalized = blankPattern.ReplaceAllString(normalized, " ")
    return strings.TrimSpace(strings.ToLower(normalized))
}

func DigestSQL(template string) string {
    sum := sha1.Sum([]byte(template))
    return hex.EncodeToString(sum[:])
}

Normalization removes comments, replaces numbers and strings with placeholders, collapses whitespace, and lower‑cases the query, ensuring that semantically identical statements map to the same sql_digest.

Elasticsearch Detail Index Mapping (Key Fields)

event_time

(date) cluster, namespace, pod, instance (keyword) db, user, client_ip (keyword / ip) query_time_ms, lock_time_ms, rows_sent, rows_examined (long) sql_digest (keyword) – used for aggregation sql_template (text, stored as raw keyword with ignore_above:2048) sql_text (text, not indexed to save space) trace_id, service, tenant_id (keyword)

Important settings: dynamic: false to prevent mapping explosion, refresh_interval: 15s, codec: best_compression, and an ILM policy for hot‑cold tiering.

ClickHouse Minute‑Level Aggregation Table

CREATE TABLE mysql_slowlog_agg_minute (
    bucket_time       DateTime,
    cluster           String,
    service           String,
    tenant_id         String,
    instance          String,
    db                String,
    sql_digest        String,
    sample_sql        String,
    total_count       UInt64,
    avg_query_time_ms UInt64,
    p95_query_time_ms UInt64,
    p99_query_time_ms UInt64,
    max_query_time_ms UInt64,
    avg_rows_examined UInt64,
    total_lock_time_ms UInt64
) ENGINE = ReplacingMergeTree
PARTITION BY toDate(bucket_time)
ORDER BY (cluster, service, db, sql_digest, bucket_time)
TTL bucket_time + INTERVAL 90 DAY;

This table stores only minute‑level aggregates, enabling fast Top‑N queries, trend analysis, and low storage cost compared to raw detail storage.

Alerting – Regression Detection

Static thresholds generate noise. A regression rule compares a recent window to a baseline:

rule_name: sql_digest_regression
window: 5m
baseline_window: 30m
group_by:
  - cluster
  - service
  - db
  - sql_digest
conditions:
  - metric: count
    op: ">="
    value: 20
  - metric: avg_query_time_ms
    compare_to: baseline.avg_query_time_ms
    op: ">="
    value: 3.0
  - metric: avg_rows_examined
    compare_to: baseline.avg_rows_examined
    op: ">="
    value: 2.0
actions:
  - type: dingtalk
  - type: jira
  - type: webhook

The rule fires only when a specific sql_digest shows a ≥3× increase in average latency and a ≥2× increase in rows examined, reducing false positives.

Integration with Observability Stack

APM/Trace : Applications embed a comment like /* trace_id:7fdac9a1 service:order-api tenant:t-001 */ before the SQL. The parser extracts trace_id so operators can jump from the slow‑log UI directly to the full request trace.

CMDB / Service Registry : Enrich instance with business line, team, environment, tenant, and role (master/replica) for actionable alerts.

Governance Actions : Alerts can trigger DingTalk, Jira tickets, webhooks, or automated rate‑limiting/killing of offending queries after human confirmation.

Real Incident Walk‑through (5‑Minute Resolution)

02:03

– Gray‑release begins. 02:08 – Pagination change drops a composite index, causing full scans. 02:10 – Platform detects a sql_digest whose avg latency jumps from 120 ms to 2.4 s and rows examined rise from 800 to 180 k. 02:11 – Regression alert fires with business, cluster, instance, digest, 5‑min avg = 2430 ms, 18.7× increase, and a sanitized SQL sample. 02:12 – On‑call clicks the alert, sees the issue only on gray‑release pods, follows the trace_id to APM, confirming the new code path. 02:14 – Team rolls back the release and DBA adds the missing index. 02:18 – Platform shows the digest’s metrics returning to normal; alert auto‑clears.

Without the platform, engineers would have spent >30 minutes chasing timeouts, connection counts, DB process lists and manually parsing logs.

Common Pitfalls and Remedies

Multiline merging errors : Incomplete SQL leads to unstable fingerprints. Fix by using # Time: as the multiline boundary and set appropriate max_lines and timeout in Filebeat.

Log rotation / inode switch : Lost logs during rotation. Prefer container stdout logging or ensure collectors support copytruncate vs rename.

Insufficient Kafka partitions : Consumption bottleneck. Pre‑plan partitions based on peak throughput.

Elasticsearch write overload : Indexing slowdown. Increase refresh_interval, batch writes, separate detail and aggregate indices, and use ILM for hot‑cold tiers.

Lack of business tags : Alerts only show IPs. Enrich events with service, tenant_id, cluster, etc., from K8s or CMDB.

Static thresholds only : Alert fatigue. Adopt regression, percentile, and anomaly‑based rules.

Roadmap – Three‑Phased Rollout

Phase 1: Visibility

Collect slow logs via Filebeat → Kafka → Go parser → Elasticsearch → Kibana.

Enable per‑instance, per‑DB, per‑SQL template search.

Phase 2: Understanding

Implement SQL fingerprint aggregation and minute‑level stats.

Support business‑, tenant‑, and service‑level dashboards.

Add CMDB/Trace enrichment and regression alert rules.

Phase 3: Governance

Automated alert routing, ticket creation, and rate‑limit/kill actions.

Integrate with incident‑response playbooks.

Build historical SQL portraits and automated optimization suggestions.

Key Takeaways

The value of a MySQL slow‑log platform lies in turning raw logs into an observable, searchable, and actionable system that can detect regressions within minutes, enrich events with business context, and close the loop with automated remediation. By following the architecture, configuration, and operational guidance above, teams can transform a night‑time debugging nightmare into a real‑time governance capability.