自己写了一些和整理了些filter, 百度是搜不到的哈, 除了最后那俩anemometer的
不输出Database 为 mysql|performance_schema|information_schema|sys|db_monitor 的查询1
((\$event->{db} || '') =~ m/^(?!(mysql|performance_schema|information_schema|sys|db_monitor))/
不输出 Users 为 pmm|mysqlcheck|dbms_monitor_r 用户的查询1
(\$event->{user} || '') =~ m/^(?!(pmm|mysqlcheck|dbms_monitor_r|proxysql))/i
不输出cheksum=’64EF0EA126730002088884A136067321’的 也就是throttle: 396 ‘index not used’ warning(s) suppressed.\G1
\$event->{fingerprint} && make_checksum(\$event->{fingerprint}) ne '64EF0EA126730002088884A136067321'
\$event->{Bytes} = length(\$event->{arg}) 和 \$event->{hostname}=\”$HOSTNAME\” 增加 Bytes 和 hostname 内容1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39# 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
...
最终1
/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脚本里再去执行脚本