xref: /aosp_15_r20/external/bcc/tools/dbslower.py (revision 387f9dfdfa2baef462e92476d413c7bc2470293e) 
1#!/usr/bin/env python
2#
3# dbslower      Trace MySQL and PostgreSQL queries slower than a threshold.
4#
5# USAGE: dbslower [-v] [-p PID [PID ...]] [-b PATH_TO_BINARY] [-m THRESHOLD]
6#                 {mysql,postgres}
7#
8# By default, a threshold of 1ms is used. Set the threshold to 0 to trace all
9# queries (verbose).
10#
11# Script works in two different modes:
12# 1) USDT probes, which means it needs MySQL and PostgreSQL built with
13# USDT (DTrace) support.
14# 2) uprobe and uretprobe on exported function of binary specified by
15# PATH_TO_BINARY parameter. (At the moment only MySQL support)
16#
17# If no PID or PATH_TO_BINARY is provided, the script attempts to discover
18# all MySQL or PostgreSQL database processes and uses USDT probes.
19#
20# Strongly inspired by Brendan Gregg's work on the mysqld_qslower script.
21#
22# Copyright 2017, Sasha Goldshtein
23# Licensed under the Apache License, Version 2.0
24#
25# 15-Feb-2017   Sasha Goldshtein   Created this.
26
27from bcc import BPF, USDT
28import argparse
29import re
30import subprocess
31
32examples = """examples:
33    dbslower postgres            # trace PostgreSQL queries slower than 1ms
34    dbslower postgres -p 188 322 # trace specific PostgreSQL processes
35    dbslower mysql -p 480 -m 30  # trace MySQL queries slower than 30ms
36    dbslower mysql -p 480 -v     # trace MySQL queries & print the BPF program
37    dbslower mysql -x $(which mysqld)  # trace MySQL queries with uprobes
38"""
39parser = argparse.ArgumentParser(
40    description="",
41    formatter_class=argparse.RawDescriptionHelpFormatter,
42    epilog=examples)
43parser.add_argument("-v", "--verbose", action="store_true",
44    help="print the BPF program")
45parser.add_argument("db", choices=["mysql", "postgres"],
46    help="the database engine to use")
47parser.add_argument("-p", "--pid", type=int, nargs='*',
48    dest="pids", metavar="PID", help="the pid(s) to trace")
49parser.add_argument("-x", "--exe", type=str,
50    dest="path", metavar="PATH", help="path to binary")
51parser.add_argument("-m", "--threshold", type=int, default=1,
52    help="trace queries slower than this threshold (ms)")
53parser.add_argument("--ebpf", action="store_true",
54    help=argparse.SUPPRESS)
55args = parser.parse_args()
56
57threshold_ns = args.threshold * 1000000
58
59mode = "USDT"
60if args.path and not args.pids:
61    if args.db == "mysql":
62        regex = "\\w+dispatch_command\\w+"
63        symbols = BPF.get_user_functions_and_addresses(args.path, regex)
64
65        if len(symbols) == 0:
66            print("Can't find function 'dispatch_command' in %s" % (args.path))
67            exit(1)
68
69        (mysql_func_name, addr) = symbols[0]
70
71        if mysql_func_name.find(b'COM_DATA') >= 0:
72            mode = "MYSQL57"
73        else:
74            mode = "MYSQL56"
75    else:
76        # Placeholder for PostrgeSQL
77        # Look on functions initStringInfo, pgstat_report_activity, EndCommand,
78        # NullCommand
79        print("Sorry at the moment PostgreSQL supports only USDT")
80        exit(1)
81
82program = """
83#include <uapi/linux/ptrace.h>
84
85DEFINE_THRESHOLD
86DEFINE_USDT
87DEFINE_MYSQL56
88DEFINE_MYSQL57
89
90struct temp_t {
91    u64 timestamp;
92#ifdef USDT
93    char *query;
94#else
95    /*
96    MySQL clears query packet before uretprobe call - so copy query in advance
97    */
98    char query[256];
99#endif //USDT
100};
101
102struct data_t {
103    u32 pid;
104    u64 timestamp;
105    u64 duration;
106    char query[256];
107};
108
109BPF_HASH(temp, u64, struct temp_t);
110BPF_PERF_OUTPUT(events);
111
112int query_start(struct pt_regs *ctx) {
113
114#if defined(MYSQL56) || defined(MYSQL57)
115/*
116Trace only packets with enum_server_command == COM_QUERY
117*/
118    #ifdef MYSQL56
119    u64 command  = (u64) PT_REGS_PARM1(ctx);
120    #else //MYSQL57
121    u64 command  = (u64) PT_REGS_PARM3(ctx);
122    #endif
123    if (command != 3) return 0;
124#endif
125
126    struct temp_t tmp = {};
127    tmp.timestamp = bpf_ktime_get_ns();
128
129#if defined(MYSQL56)
130    bpf_probe_read_user(&tmp.query, sizeof(tmp.query), (void*) PT_REGS_PARM3(ctx));
131#elif defined(MYSQL57)
132    void* st = (void*) PT_REGS_PARM2(ctx);
133    char* query;
134    bpf_probe_read_user(&query, sizeof(query), st);
135    bpf_probe_read_user(&tmp.query, sizeof(tmp.query), query);
136#else //USDT
137    bpf_usdt_readarg(1, ctx, &tmp.query);
138#endif
139
140    u64 pid = bpf_get_current_pid_tgid();
141    temp.update(&pid, &tmp);
142    return 0;
143}
144
145int query_end(struct pt_regs *ctx) {
146    struct temp_t *tempp;
147    u64 pid = bpf_get_current_pid_tgid();
148    tempp = temp.lookup(&pid);
149    if (!tempp)
150        return 0;
151
152    u64 delta = bpf_ktime_get_ns() - tempp->timestamp;
153#ifdef THRESHOLD
154    if (delta >= THRESHOLD) {
155#endif //THRESHOLD
156        struct data_t data = {};
157        data.pid = pid >> 32;   // only process id
158        data.timestamp = tempp->timestamp;
159        data.duration = delta;
160#if defined(MYSQL56) || defined(MYSQL57)
161	// We already copied string to the bpf stack. Hence use bpf_probe_read_kernel()
162        bpf_probe_read_kernel(&data.query, sizeof(data.query), tempp->query);
163#else
164	// USDT - we didnt copy string to the bpf stack before.
165        bpf_probe_read_user(&data.query, sizeof(data.query), tempp->query);
166#endif
167        events.perf_submit(ctx, &data, sizeof(data));
168#ifdef THRESHOLD
169    }
170#endif //THRESHOLD
171    temp.delete(&pid);
172    return 0;
173};
174""".replace("DEFINE_USDT", "#define USDT" if mode == "USDT" else "") \
175   .replace("DEFINE_MYSQL56", "#define MYSQL56" if mode == "MYSQL56" else "") \
176   .replace("DEFINE_MYSQL57", "#define MYSQL57" if mode == "MYSQL57" else "") \
177   .replace("DEFINE_THRESHOLD",
178            "#define THRESHOLD %d" % threshold_ns if threshold_ns > 0 else "")
179
180if mode.startswith("MYSQL"):
181    # Uprobes mode
182    bpf = BPF(text=program)
183    bpf.attach_uprobe(name=args.path, sym=mysql_func_name,
184                      fn_name="query_start")
185    bpf.attach_uretprobe(name=args.path, sym=mysql_func_name,
186                         fn_name="query_end")
187else:
188    # USDT mode
189    if not args.pids or len(args.pids) == 0:
190        if args.db == "mysql":
191            args.pids = map(int, subprocess.check_output(
192                                            "pidof mysqld".split()).split())
193        elif args.db == "postgres":
194            args.pids = map(int, subprocess.check_output(
195                                            "pidof postgres".split()).split())
196
197    usdts = list(map(lambda pid: USDT(pid=pid), args.pids))
198    for usdt in usdts:
199        usdt.enable_probe("query__start", "query_start")
200        usdt.enable_probe("query__done", "query_end")
201    if args.verbose:
202        print('\n'.join(map(lambda u: u.get_text(), usdts)))
203
204    bpf = BPF(text=program, usdt_contexts=usdts)
205
206if args.verbose or args.ebpf:
207    print(program)
208    if args.ebpf:
209        exit()
210
211start = BPF.monotonic_time()
212
213def print_event(cpu, data, size):
214    event = bpf["events"].event(data)
215    print("%-14.6f %-7d %8.3f %s" % (
216        float(event.timestamp - start) / 1000000000,
217        event.pid, float(event.duration) / 1000000, event.query))
218
219if mode.startswith("MYSQL"):
220    print("Tracing database queries for application %s slower than %d ms..." %
221        (args.path, args.threshold))
222else:
223    print("Tracing database queries for pids %s slower than %d ms..." %
224        (', '.join(map(str, args.pids)), args.threshold))
225
226print("%-14s %-7s %8s %s" % ("TIME(s)", "PID", "MS", "QUERY"))
227
228bpf["events"].open_perf_buffer(print_event, page_cnt=64)
229while True:
230    try:
231        bpf.perf_buffer_poll()
232    except KeyboardInterrupt:
233        exit()
234