Unlock MySQL Performance: Diagnose Slow Queries with eBPF and System Observation Tools

1. MySQL Slow Diagnosis

The presenter outlines a three‑step diagnostic approach: internal MySQL observation (processlist, EXPLAIN, profiling, performance_schema, sys_schema, innodb_metrics), external resource observation (uptime, dmesg, vmstat, mpstat, pidstat, iostat, free, sar, top), and external demand refactoring (rewriting costly queries, removing correlated subqueries).

2. System Observation Tools

An overview of four categories of system‑level tracing tools is given:

ftrace – kernel‑provided tracepoints accessed via sysfs.

perf – uses a system call to collect performance data.

eBPF – injects compiled bytecode into the kernel for low‑overhead tracing.

SystemTap – compiles C‑style scripts into kernel modules.

The speaker compares their intrusiveness and suitability for MySQL performance monitoring.

3. bcc (eBPF Script Collection) Examples

Several ready‑made bcc scripts are demonstrated:

MySQL request latency distribution.

Alternative slow‑query extraction.

VFS latency analysis.

Ext4 file‑IO latency per file.

Block‑device latency without cache.

Per‑thread MySQL IO pressure.

Short‑lived temporary‑file detection.

Short‑connection pressure analysis.

Long‑connection usage analysis.

CPU off‑CPU flame‑graph generation.

Each example shows how bcc can quickly surface evidence of performance problems that MySQL’s own tools may miss.

4. eBPF Usage and Limitations

To create custom probes, two programs are needed: a kernel‑resident eBPF program that records timestamps at query start/end, and a user‑space loader that attaches the program to MySQL tracepoints and prints results. Typical scripts are ~45 lines of code.

Limitations include kernel version requirements (Linux 4.9+ for reliable statistics, some features only in 4.13+), the need for MySQL to be compiled with DTrace/eBPF tracepoints, and potential stability concerns when disabling safety checks. When eBPF is unsuitable, SystemTap is suggested as an alternative.

Resources such as the bcc GitHub repository, a recommended book by the script author, and Red Hat’s Performance Tuning Guide are provided for further study.