1Demonstrations of mysqld_qslower, the Linux eBPF/bcc version.
2
3
4mysqld_qslower traces queries served by a MySQL server, and prints those that
5exceed a latency (query time) threshold. By default a threshold of 1 ms is
6used. For example:
7
8# ./mysqld_qslower.py `pgrep -n mysqld`
9Tracing MySQL server queries for PID 14371 slower than 1 ms...
10TIME(s)        PID          MS QUERY
110.000000       18608   130.751 SELECT * FROM words WHERE word REGEXP '^bre.*n$'
122.921535       18608   130.590 SELECT * FROM words WHERE word REGEXP '^alex.*$'
134.603549       18608    24.164 SELECT COUNT(*) FROM words
149.733847       18608   130.936 SELECT count(*) AS count FROM words WHERE word REGEXP '^bre.*n$'
1517.864776      18608   130.298 SELECT * FROM words WHERE word REGEXP '^bre.*n$' ORDER BY word
16
17This traced 5 queries, 4 of which took about 130 milliseconds.
18
19A pgrep command was used to specify the PID of mysqld.
20
21
22In this example, a lower threshold is used of 0.1 ms:
23
24# ./mysqld_qslower.py `pgrep -n mysqld` 0.1
25Tracing MySQL server queries for PID 14371 slower than 0.1 ms...
26TIME(s)        PID          MS QUERY
270.000000       18608    24.201 SELECT COUNT(*) FROM words
2813.242390      18608   130.378 SELECT * FROM words WHERE word REGEXP '^bre.*n$'
2923.601751      18608   119.198 SELECT * FROM words WHERE word REGEXP '^zzzzzzzz$'
30
31It worked, but I'm not catching any faster queries in this example. Notice I
32added a query that searched for "zzzzzzzz": it returned an empty set, and ran
3311 ms faster.
34
35
36A 0 ms threshold can be specified to trace all queries:
37
38# ./mysqld_qslower.py `pgrep -n mysqld` 0
39Tracing MySQL server queries for PID 14371 slower than 0 ms...
40TIME(s)        PID          MS QUERY
410.000000       18608     0.105 select @@version_comment limit 1
422.049312       18608     0.099 SELECT DATABASE()
432.050666       18608     0.274 show databases
442.051040       18608     0.176 show tables
455.730044       18608   130.365 SELECT count(*) AS count FROM words WHERE word REGEXP '^bre.*n$'
469.273837       18608     0.096 select 1
479.553742       18608     0.059 select 1
489.986087       18608     0.080 select 1
49
50This includes an initialization of a mysql client command, and selecting the
51database. I also added some "select 1;" queries, which do no work and return
52quickly.
53
54
55USAGE:
56
57# ./mysqld_qslower.py -h
58USAGE: mysqld_latency PID [min_ms]
59