dbstat_example.txt

Demonstrations of dbstat, the Linux eBPF/bcc version.


dbstat traces queries performed by a MySQL or PostgreSQL database process, and
displays a histogram of query latencies. For example:

# dbstat mysql
Tracing database queries for pids 25776 slower than 0 ms...
     query latency (ms)  : count     distribution
         0 -> 1          : 990      |****************************************|
         2 -> 3          : 7        |                                        |
         4 -> 7          : 0        |                                        |
         8 -> 15         : 0        |                                        |
        16 -> 31         : 0        |                                        |
        32 -> 63         : 0        |                                        |
        64 -> 127        : 0        |                                        |
       128 -> 255        : 0        |                                        |
       256 -> 511        : 0        |                                        |
       512 -> 1023       : 0        |                                        |
      1024 -> 2047       : 2        |                                        |
^C

It's immediately evident that the vast majority of queries finish very quickly,
in under 1ms, but there are some super-slow queries occasionally, in the 1-2
seconds bucket.

We can filter out the shorter queries with the -m switch:

# dbstat mysql -m 1000
Tracing database queries for pids 25776 slower than 1000 ms...
     query latency (ms)  : count     distribution
         0 -> 1          : 0        |                                        |
         2 -> 3          : 0        |                                        |
         4 -> 7          : 0        |                                        |
         8 -> 15         : 0        |                                        |
        16 -> 31         : 0        |                                        |
        32 -> 63         : 0        |                                        |
        64 -> 127        : 0        |                                        |
       128 -> 255        : 0        |                                        |
       256 -> 511        : 0        |                                        |
       512 -> 1023       : 0        |                                        |
      1024 -> 2047       : 8        |****************************************|
^C

By default, dbstat will try to detect mysqld and postgres processes, but if
necessary, you can specify the process ids with the -p switch. Here, the -i
switch is also used to request histograms at 3 second intervals:

# dbstat mysql -p $(pidof mysql) -i 3
Tracing database queries for pids 25776 slower than 0 ms...
[06:14:36]
     query latency (ms)  : count     distribution
         0 -> 1          : 758      |****************************************|
         2 -> 3          : 1        |                                        |
         4 -> 7          : 0        |                                        |
         8 -> 15         : 0        |                                        |
        16 -> 31         : 0        |                                        |
        32 -> 63         : 0        |                                        |
        64 -> 127        : 0        |                                        |
       128 -> 255        : 0        |                                        |
       256 -> 511        : 0        |                                        |
       512 -> 1023       : 0        |                                        |
      1024 -> 2047       : 1        |                                        |

[06:14:39]
     query latency (ms)  : count     distribution
         0 -> 1          : 436      |****************************************|
         2 -> 3          : 2        |                                        |
         4 -> 7          : 0        |                                        |
         8 -> 15         : 0        |                                        |
        16 -> 31         : 0        |                                        |
        32 -> 63         : 0        |                                        |
        64 -> 127        : 0        |                                        |
       128 -> 255        : 0        |                                        |
       256 -> 511        : 0        |                                        |
       512 -> 1023       : 0        |                                        |
      1024 -> 2047       : 1        |                                        |

[06:14:42]
     query latency (ms)  : count     distribution
         0 -> 1          : 399      |****************************************|
         2 -> 3          : 0        |                                        |
         4 -> 7          : 0        |                                        |
         8 -> 15         : 0        |                                        |
        16 -> 31         : 0        |                                        |
        32 -> 63         : 0        |                                        |
        64 -> 127        : 0        |                                        |
       128 -> 255        : 0        |                                        |
       256 -> 511        : 0        |                                        |
       512 -> 1023       : 0        |                                        |
      1024 -> 2047       : 1        |                                        |
^C


USAGE:
# dbstat -h
usage: dbstat.py [-h] [-v] [-p [PID [PID ...]]] [-m THRESHOLD] [-u]
                 [-i INTERVAL]
                 {mysql,postgres}

positional arguments:
  {mysql,postgres}      the database engine to use

optional arguments:
  -h, --help            show this help message and exit
  -v, --verbose         print the BPF program
  -p [PID [PID ...]], --pid [PID [PID ...]]
                        the pid(s) to trace
  -m THRESHOLD, --threshold THRESHOLD
                        trace queries slower than this threshold (ms)
  -u, --microseconds    display query latencies in microseconds (default:
                        milliseconds)
  -i INTERVAL, --interval INTERVAL
                        print summary at this interval (seconds)

    dbstat postgres     # display a histogram of PostgreSQL query latencies
    dbstat mysql -v     # display MySQL latencies and print the BPF program
    dbstat mysql -u     # display query latencies in microseconds (default: ms)
    dbstat mysql -m 5   # trace only queries slower than 5ms
    dbstat mysql -p 408 # trace queries in a specific process