Alexey Ivanov | cc01a9c | 2019-01-16 09:50:46 -0800 | [diff] [blame] | 1 | #!/usr/bin/python |
Brendan Gregg | d602d6b | 2016-08-01 16:18:40 -0700 | [diff] [blame] | 2 | # |
| 3 | # mysqld_qslower MySQL server queries slower than a threshold. |
| 4 | # For Linux, uses BCC, BPF. Embedded C. |
| 5 | # |
| 6 | # USAGE: mysqld_qslower PID [min_ms] |
| 7 | # |
| 8 | # By default, a threshold of 1.0 ms is used. Set this to 0 ms to trace all |
| 9 | # queries (verbose). |
| 10 | # |
| 11 | # This uses USDT probes, and needs a MySQL server with -DENABLE_DTRACE=1. |
| 12 | # |
| 13 | # Copyright 2016 Netflix, Inc. |
| 14 | # Licensed under the Apache License, Version 2.0 (the "License") |
| 15 | # |
| 16 | # 30-Jul-2016 Brendan Gregg Created this. |
| 17 | |
| 18 | from __future__ import print_function |
| 19 | from bcc import BPF, USDT |
| 20 | import sys |
Brendan Gregg | d602d6b | 2016-08-01 16:18:40 -0700 | [diff] [blame] | 21 | |
| 22 | # arguments |
| 23 | def usage(): |
joelcollin | 1f7b6cd | 2019-12-03 12:29:04 -0500 | [diff] [blame] | 24 | print("USAGE: mysqld_qslower PID [min_ms]") |
Brendan Gregg | d602d6b | 2016-08-01 16:18:40 -0700 | [diff] [blame] | 25 | exit() |
| 26 | if len(sys.argv) < 2: |
| 27 | usage() |
| 28 | if sys.argv[1][0:1] == "-": |
| 29 | usage() |
| 30 | pid = int(sys.argv[1]) |
| 31 | min_ns = 1 * 1000000 |
| 32 | min_ms_text = 1 |
| 33 | if len(sys.argv) == 3: |
| 34 | min_ns = float(sys.argv[2]) * 1000000 |
| 35 | min_ms_text = sys.argv[2] |
| 36 | debug = 0 |
| 37 | QUERY_MAX = 128 |
| 38 | |
| 39 | # load BPF program |
| 40 | bpf_text = """ |
| 41 | #include <uapi/linux/ptrace.h> |
| 42 | |
| 43 | #define QUERY_MAX """ + str(QUERY_MAX) + """ |
| 44 | |
| 45 | struct start_t { |
| 46 | u64 ts; |
| 47 | char *query; |
| 48 | }; |
| 49 | |
| 50 | struct data_t { |
| 51 | u64 pid; |
| 52 | u64 ts; |
| 53 | u64 delta; |
| 54 | char query[QUERY_MAX]; |
| 55 | }; |
| 56 | |
| 57 | BPF_HASH(start_tmp, u32, struct start_t); |
| 58 | BPF_PERF_OUTPUT(events); |
| 59 | |
| 60 | int do_start(struct pt_regs *ctx) { |
Hengqi Chen | f0a0dc7 | 2021-05-20 22:49:25 +0800 | [diff] [blame] | 61 | u32 tid = bpf_get_current_pid_tgid(); |
Brendan Gregg | d602d6b | 2016-08-01 16:18:40 -0700 | [diff] [blame] | 62 | struct start_t start = {}; |
| 63 | start.ts = bpf_ktime_get_ns(); |
| 64 | bpf_usdt_readarg(1, ctx, &start.query); |
Hengqi Chen | f0a0dc7 | 2021-05-20 22:49:25 +0800 | [diff] [blame] | 65 | start_tmp.update(&tid, &start); |
Brendan Gregg | d602d6b | 2016-08-01 16:18:40 -0700 | [diff] [blame] | 66 | return 0; |
| 67 | }; |
| 68 | |
| 69 | int do_done(struct pt_regs *ctx) { |
Hengqi Chen | f0a0dc7 | 2021-05-20 22:49:25 +0800 | [diff] [blame] | 70 | u64 pid_tgid = bpf_get_current_pid_tgid(); |
| 71 | u32 pid = pid_tgid >> 32; |
| 72 | u32 tid = (u32)pid_tgid; |
Brendan Gregg | d602d6b | 2016-08-01 16:18:40 -0700 | [diff] [blame] | 73 | struct start_t *sp; |
| 74 | |
Hengqi Chen | f0a0dc7 | 2021-05-20 22:49:25 +0800 | [diff] [blame] | 75 | sp = start_tmp.lookup(&tid); |
Brendan Gregg | d602d6b | 2016-08-01 16:18:40 -0700 | [diff] [blame] | 76 | if (sp == 0) { |
| 77 | // missed tracing start |
| 78 | return 0; |
| 79 | } |
| 80 | |
| 81 | // check if query exceeded our threshold |
| 82 | u64 delta = bpf_ktime_get_ns() - sp->ts; |
| 83 | if (delta >= """ + str(min_ns) + """) { |
| 84 | // populate and emit data struct |
| 85 | struct data_t data = {.pid = pid, .ts = sp->ts, .delta = delta}; |
Sumanth Korikkar | 023154c | 2020-04-20 05:54:57 -0500 | [diff] [blame] | 86 | bpf_probe_read_user(&data.query, sizeof(data.query), (void *)sp->query); |
Brendan Gregg | d602d6b | 2016-08-01 16:18:40 -0700 | [diff] [blame] | 87 | events.perf_submit(ctx, &data, sizeof(data)); |
| 88 | } |
| 89 | |
Hengqi Chen | f0a0dc7 | 2021-05-20 22:49:25 +0800 | [diff] [blame] | 90 | start_tmp.delete(&tid); |
Brendan Gregg | d602d6b | 2016-08-01 16:18:40 -0700 | [diff] [blame] | 91 | |
| 92 | return 0; |
| 93 | }; |
| 94 | |
| 95 | """ |
| 96 | |
| 97 | # enable USDT probe from given PID |
| 98 | u = USDT(pid=pid) |
| 99 | u.enable_probe(probe="query__start", fn_name="do_start") |
| 100 | u.enable_probe(probe="query__done", fn_name="do_done") |
| 101 | if debug: |
| 102 | print(u.get_text()) |
| 103 | print(bpf_text) |
| 104 | |
| 105 | # initialize BPF |
Sasha Goldshtein | 54c1d6f | 2016-10-06 16:12:14 -0700 | [diff] [blame] | 106 | b = BPF(text=bpf_text, usdt_contexts=[u]) |
Brendan Gregg | d602d6b | 2016-08-01 16:18:40 -0700 | [diff] [blame] | 107 | |
| 108 | # header |
| 109 | print("Tracing MySQL server queries for PID %d slower than %s ms..." % (pid, |
| 110 | min_ms_text)) |
xingfeng2510 | 03e4948 | 2022-03-17 13:07:16 +0800 | [diff] [blame] | 111 | print("%-14s %-7s %8s %s" % ("TIME(s)", "PID", "MS", "QUERY")) |
Brendan Gregg | d602d6b | 2016-08-01 16:18:40 -0700 | [diff] [blame] | 112 | |
Brendan Gregg | d602d6b | 2016-08-01 16:18:40 -0700 | [diff] [blame] | 113 | # process event |
| 114 | start = 0 |
| 115 | def print_event(cpu, data, size): |
| 116 | global start |
Xiaozhou Liu | 51d62d3 | 2019-02-15 13:03:05 +0800 | [diff] [blame] | 117 | event = b["events"].event(data) |
Brendan Gregg | d602d6b | 2016-08-01 16:18:40 -0700 | [diff] [blame] | 118 | if start == 0: |
| 119 | start = event.ts |
xingfeng2510 | 03e4948 | 2022-03-17 13:07:16 +0800 | [diff] [blame] | 120 | print("%-14.6f %-7d %8.3f %s" % (float(event.ts - start) / 1000000000, |
Brendan Gregg | d602d6b | 2016-08-01 16:18:40 -0700 | [diff] [blame] | 121 | event.pid, float(event.delta) / 1000000, event.query)) |
| 122 | |
| 123 | # loop with callback to print_event |
Mark Drayton | 5f5687e | 2017-02-20 18:13:03 +0000 | [diff] [blame] | 124 | b["events"].open_perf_buffer(print_event, page_cnt=64) |
Brendan Gregg | d602d6b | 2016-08-01 16:18:40 -0700 | [diff] [blame] | 125 | while 1: |
Jerome Marchand | 5167127 | 2018-12-19 01:57:24 +0100 | [diff] [blame] | 126 | try: |
| 127 | b.perf_buffer_poll() |
| 128 | except KeyboardInterrupt: |
| 129 | exit() |