Tracing Database Query Execution Paths

In this lesson, you will learn how to bridge the gap between low-level Linux I/O events and high-level database query IDs using custom tracepoints.

The Observability Gap

When debugging database performance, standard system-level tools like iostat or iotop show you that a disk is saturated, but they rarely tell you which specific SQL query is responsible for that I/O spike. To solve this, we must inject a "correlation token"—the Query ID—into the kernel context during execution.

Custom Tracepoints for Context Injection

The most effective way to track this is by using uprobes (User-space probes) at the database engine's query execution entry point. By placing a probe on the function responsible for processing a query, you can capture the Query ID into a BPF map. When the database subsequently triggers a system-level I/O request (such as a read or write syscall), a kernel-level kprobe can look up the currently executing Query ID associated with that process thread.

Consider a simplified logic flow using BPF:

// BPF program pseudo-code
struct {
    __uint(type, BPF_MAP_TYPE_HASH);
    __uint(max_entries, 10240);
    __type(key, u32); // PID
    __type(value, u64); // Query ID
} active_queries;

// Hook onto database query entry
int trace_query_start(struct pt_regs *ctx) {
    u32 pid = bpf_get_current_pid_tgid();
    u64 query_id = get_arg(ctx, 1); // Extract Query ID from registers
    bpf_map_update_elem(&active_queries, &pid, &query_id, BPF_ANY);
    return 0;
}

// Hook onto sys_read
int trace_disk_io(struct pt_regs *ctx) {
    u32 pid = bpf_get_current_pid_tgid();
    u64 *qid = bpf_map_lookup_elem(&active_queries, &pid);
    if (qid) {
        bpf_trace_printk("I/O event for Query ID: %llu\n", *qid);
    }
    return 0;
}

By linking the PID to the Query ID in a BPF hash map, you effectively "tag" every subsequent kernel I/O request. This allows you to differentiate between background housekeeping tasks (like checkpoints or log flushes) and the actual application queries that are causing performance bottlenecks, enabling precise root-cause analysis without guessing.

Key Takeaway: By using BPF maps to correlate PID-specific execution contexts with system-level syscalls, you can transform opaque kernel I/O events into actionable, query-specific performance insights.