问题回报

[987william987]

小弟测试版本如下，遇到些问题 MySQL Version: 5.7.25 pt-query-digest Version: 3.0.13

---

pt-query-digest 分析 mysql5.7 生成 checksum 包含数字和字母 故 mysql_slow_query_review 和 mysql_slow_query_review_history 需修改字段 checksum 为 varchar(32) 或 char(32)

另外以下字段 default 不应该为 '0000-00-00 00:00:00'，建议直接 not null

1. ts_min
2. ts_max

感谢大佬维护，给个赞先 !

[987william987]

MySQL5.7.5 后，默认开启 ONLY_FULL_GROUP_BY，因此需修改部份 sql

修改代码文件: get_top_data.php

修改代码位置: line 4 $result_echarts 位置

• 原本 SELECT db_max, user_max, SUM(ts_cnt) AS top_count FROM (SELECT h.db_max, h.user_max, SUM(h.ts_cnt) AS ts_cnt FROM mysql_slow_query_review AS r JOIN mysql_slow_query_review_history AS h ON r.checksum = h.checksum WHERE r.last_seen >= SUBDATE(NOW(),INTERVAL 14 DAY) GROUP BY r.checksum) AS tmp GROUP BY tmp.db_max;
• 修改 SELECT db_max, user_max, SUM(ts_cnt) AS top_count FROM ( select h.db_max, h.user_max, SUM(h.ts_cnt) AS ts_cnt from mysql_slow_query_review AS r JOIN mysql_slow_query_review_history AS h ON r.checksum = h.checksum WHERE r.last_seen >= SUBDATE(NOW(),INTERVAL 14 DAY) GROUP BY r.checksum,h.db_max,h.user_max ) AS tmp GROUP BY db_max, user_max;

---

修改代码文件: slowquery.php

修改代码位置: line 136 (分支 沒选择 DB name)

• 原始 SELECT r.checksum, r.fingerprint, h.db_max, h.user_max, r.last_seen, SUM(h.ts_cnt) AS ts_cnt, ROUND(MIN(h.Query_time_min),3) AS Query_time_min, ROUND(MAX(h.Query_time_max),3) AS Query_time_max, ROUND(SUM(h.Query_time_sum)/SUM(h.ts_cnt),3) AS Query_time_avg, r.sample FROM mysql_slow_query_review AS r JOIN mysql_slow_query_review_history AS h ON r.checksum=h.checksum WHERE r.last_seen >= SUBDATE(NOW(),INTERVAL 31 DAY) GROUP BY r.checksum ORDER BY r.last_seen DESC,ts_cnt DESC ; -- LIMIT $startCount,$perNumber;

• 修改 SELECT r.checksum, r.fingerprint, h.db_max, h.user_max, r.last_seen, SUM(h.ts_cnt) AS ts_cnt, ROUND(MIN(h.Query_time_min),3) AS Query_time_min, ROUND(MAX(h.Query_time_max),3) AS Query_time_max, ROUND(SUM(h.Query_time_sum)/SUM(h.ts_cnt),3) AS Query_time_avg, r.sample FROM mysql_slow_query_review AS r JOIN mysql_slow_query_review_history AS h ON r.checksum=h.checksum WHERE r.last_seen >= SUBDATE(NOW(),INTERVAL 31 DAY) GROUP BY r.checksum, h.db_max, h.user_max ORDER BY r.last_seen DESC,ts_cnt DESC ;

---

修改代码文件: slowquery.php

修改代码位置: line 126 (分支 有选择 DB name)

• 原始 SELECT r.checksum, r.fingerprint, h.db_max, h.user_max, r.last_seen, SUM(h.ts_cnt) AS ts_cnt, ROUND(MIN(h.Query_time_min),3) AS Query_time_min, ROUND(MAX(h.Query_time_max),3) AS Query_time_max, ROUND(SUM(h.Query_time_sum)/SUM(h.ts_cnt),3) AS Query_time_avg, r.sample FROM mysql_slow_query_review AS r JOIN mysql_slow_query_review_history AS h ON r.checksum=h.checksum WHERE h.db_max = 'lv' -- '${select_dbname}' AND r.last_seen >= SUBDATE(NOW(),INTERVAL 31 DAY) GROUP BY r.checksum ORDER BY r.last_seen DESC,ts_cnt DESC; -- LIMIT $startCount,$perNumber";

• 修改 SELECT r.checksum, r.fingerprint, h.db_max, h.user_max, r.last_seen, SUM(h.ts_cnt) AS ts_cnt, ROUND(MIN(h.Query_time_min),3) AS Query_time_min, ROUND(MAX(h.Query_time_max),3) AS Query_time_max, ROUND(SUM(h.Query_time_sum)/SUM(h.ts_cnt),3) AS Query_time_avg, r.sample FROM mysql_slow_query_review AS r JOIN mysql_slow_query_review_history AS h ON r.checksum=h.checksum WHERE h.db_max = 'lv' -- '${select_dbname}' AND r.last_seen >= SUBDATE(NOW(),INTERVAL 31 DAY) GROUP BY r.checksum, h.user_max ORDER BY r.last_seen DESC,ts_cnt DESC; -- LIMIT $startCount,$perNumber";

  ---

修改代码文件: get_top100_slowsql.php

修改代码位置: line 29 $sql 位置

• 原本 SELECT r.checksum,r.fingerprint,h.db_max,h.user_max,r.last_seen,SUM(h.ts_cnt) AS ts_cnt, ROUND(MIN(h.Query_time_min),3) AS Query_time_min,ROUND(MAX(h.Query_time_max),3) AS Query_time_max, ROUND(SUM(h.Query_time_sum)/SUM(h.ts_cnt),3) AS Query_time_avg,r.sample FROM mysql_slow_query_review AS r JOIN mysql_slow_query_review_history AS h ON r.checksum=h.checksum WHERE r.last_seen >= SUBDATE(NOW(),INTERVAL 1 DAY) GROUP BY r.checksum ORDER BY r.last_seen DESC,ts_cnt DESC LIMIT 100
• 修改 SELECT r.checksum, r.fingerprint, h.db_max, h.user_max, r.last_seen, SUM(h.ts_cnt) AS ts_cnt, ROUND(MIN(h.Query_time_min),3) AS Query_time_min, ROUND(MAX(h.Query_time_max),3) AS Query_time_max, ROUND(SUM(h.Query_time_sum)/SUM(h.ts_cnt),3) AS Query_time_avg, r.sample FROM mysql_slow_query_review AS r JOIN mysql_slow_query_review_history AS h ON r.checksum=h.checksum WHERE r.last_seen >= SUBDATE(NOW(),INTERVAL 1 DAY) GROUP BY r.checksum, h.db_max, h.user_max ORDER BY r.last_seen DESC,ts_cnt DESC LIMIT 100;

---

[987william987]

soar 需要可执行权限

chmod +x ./soar/soar

发现 explain 下方出现错误如下图

将代码 slowquery_explain.php line 92 修改路径后，依旧报错，目前尚不明白为何

确定挡案权限为可读

[hcymysql]

多谢兄弟！

[987william987]

修改代码文件: slowquery.php: slowquery_explain.php 修改代码位置: $get_sql = "select sample,db_max from mysql_slow_query_review_history where checksum='${checksum}' limit 1";

这句 sql 应该要再加上 db_max 作为判断条件，因为不同的库有可能会执行相同的语法，会导致判断错误，但目前还未确认该如何修正，回报先

[987william987]

db_max 为 null 之解决方式

pt-query-digest 在执行 --history 选项时，偶尔会出现 db_max 为 null 的情况，导致查询会出错。 主因是 slow log 里，sql 缺少了 「use db_name; 」 所以 pt-工具无法辨认该 sql 的数据库，只能给 null。 这边建议手动在 slow log 里加上 use 语句，可以解决此问题。

提供个简单思路： 可在 slowquery_analysis 里头增加以下代码 agent_db=`mysql -h$agent_host -P$agent_port -u$agent_user -p$agent_pass -Nse " select SCHEMA_NAME from information_schema.SCHEMATA where SCHEMA_NAME not in ('information_schema','mysql','performance_schema','sys','test');"` file_num=`cat -n $slowquery_file | grep -m 1 Query_time | cut -f1` if [ -z "$file_num" ]; then exit 0 fi sed -i "$file_num a\use $agent_db;" $slowquery_file