记录一些有用的pt-query-digest filter

记录一些有用的pt-query-digest filter

自己写了一些和整理了些filter, 百度是搜不到的哈, 除了最后那俩anemometer的

不输出Database 为 mysql|performance_schema|information_schema|sys|db_monitor 的查询

((\$event->{db} || '') =~ m/^(?!(mysql|performance_schema|information_schema|sys|db_monitor))/ 

不输出 Users 为 pmm|mysqlcheck|dbms_monitor_r 用户的查询

(\$event->{user} || '') =~ m/^(?!(pmm|mysqlcheck|dbms_monitor_r|proxysql))/i 

不输出cheksum='64EF0EA126730002088884A136067321’的 也就是throttle: 396 ‘index not used’ warning(s) suppressed.\G

\$event->{fingerprint} && make_checksum(\$event->{fingerprint}) ne '64EF0EA126730002088884A136067321'

$event->{Bytes} = length($event->{arg}) 和 $event->{hostname}="$HOSTNAME" 增加 Bytes 和 hostname 内容

# Query 19: 0 QPS, 0x concurrency, ID 0xE3B736208C9A5A96A9D7E7DFF3BEF268 at byte 2298558
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2019-07-03T03:09:30
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0       1
# Exec time      0   448us   448us   448us   448us   448us       0   448us
# Lock time      0   132us   132us   132us   132us   132us       0   132us
# Rows sent      0      10      10      10      10      10       0      10
# Rows examine   0     172     172     172     172     172       0     172
# Query size     0     117     117     117     117     117       0     117
# Bytes          0     117     117     117     117     117       0     117  --增加的
# String:
# Databases    mydb
# Hosts        192.168.x.110
# hostname     node00xxx        --增加的
# Users        xxx
# Query_time distribution
...

对比没有使用
# Query 5: 0.00 QPS, 0.00x concurrency, ID 0x39226EA9FD5344CDD6F89A75B34D97F3 at byte 2168772
# Scores: V/M = 0.00
# Time range: 2019-07-02T22:54:11 to 2019-07-03T14:58:30
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     157
# Exec time      1     11s    60ms    94ms    70ms    75ms     5ms    68ms
# Lock time      0    15ms    48us   321us    95us   167us    42us    84us
# Rows sent      0     157       1       1       1       1       0       1
# Rows examine  31  29.50M 192.11k 192.78k 192.42k 192.13k     339 192.13k
# Query size     0  12.42k      80      81   80.99   80.10    0.43   80.10
# String:
# Databases    mydb
# Hosts        192.168.x.50 (90/57%), 192.168.x.211 (67/42%)
# Users        xxx
# Query_time distribution
...

最终

/bin/pt-query-digest --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\" and ((\$event->{db} || '') =~ m/^(?!(mysql|performance_schema|information_schema|sys|db_monitor))/ and (\$event->{user} || '') =~ m/^(?!(pmm|mysqlcheck|dbms_monitor_r|proxysql))/i and \$event->{fingerprint} && make_checksum(\$event->{fingerprint}) ne '64EF0EA126730002088884A136067321' )" slow-queries.log

不过上面的命令不能直接在终端执行, 只能写到shell脚本里再去执行脚本