tools/mysqld_qslower_example.txt

Demonstrations of mysqld_qslower, the Linux eBPF/bcc version.


mysqld_qslower traces queries served by a MySQL server, and prints those that
exceed a latency (query time) threshold. By default a threshold of 1 ms is
used. For example:

# ./mysqld_qslower.py `pgrep -n mysqld`
Tracing MySQL server queries for PID 14371 slower than 1 ms...
TIME(s)        PID          MS QUERY
0.000000       18608   130.751 SELECT * FROM words WHERE word REGEXP '^bre.*n$'
2.921535       18608   130.590 SELECT * FROM words WHERE word REGEXP '^alex.*$'
4.603549       18608    24.164 SELECT COUNT(*) FROM words
9.733847       18608   130.936 SELECT count(*) AS count FROM words WHERE word REGEXP '^bre.*n$'
17.864776      18608   130.298 SELECT * FROM words WHERE word REGEXP '^bre.*n$' ORDER BY word

This traced 5 queries, 4 of which took about 130 milliseconds.

A pgrep command was used to specify the PID of mysqld.


In this example, a lower threshold is used of 0.1 ms:

# ./mysqld_qslower.py `pgrep -n mysqld` 0.1
Tracing MySQL server queries for PID 14371 slower than 0.1 ms...
TIME(s)        PID          MS QUERY
0.000000       18608    24.201 SELECT COUNT(*) FROM words
13.242390      18608   130.378 SELECT * FROM words WHERE word REGEXP '^bre.*n$'
23.601751      18608   119.198 SELECT * FROM words WHERE word REGEXP '^zzzzzzzz$'

It worked, but I'm not catching any faster queries in this example. Notice I
added a query that searched for "zzzzzzzz": it returned an empty set, and ran
11 ms faster.


A 0 ms threshold can be specified to trace all queries:

# ./mysqld_qslower.py `pgrep -n mysqld` 0
Tracing MySQL server queries for PID 14371 slower than 0 ms...
TIME(s)        PID          MS QUERY
0.000000       18608     0.105 select @@version_comment limit 1
2.049312       18608     0.099 SELECT DATABASE()
2.050666       18608     0.274 show databases
2.051040       18608     0.176 show tables
5.730044       18608   130.365 SELECT count(*) AS count FROM words WHERE word REGEXP '^bre.*n$'
9.273837       18608     0.096 select 1
9.553742       18608     0.059 select 1
9.986087       18608     0.080 select 1

This includes an initialization of a mysql client command, and selecting the
database. I also added some "select 1;" queries, which do no work and return
quickly.


USAGE:

# ./mysqld_qslower.py -h
USAGE: mysqld_latency PID [min_ms]