Comments (5)
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;
from slowquery.
soar 需要可执行权限
chmod +x ./soar/soar
发现 explain 下方出现错误如下图
将代码 slowquery_explain.php line 92 修改路径后,依旧报错,目前尚不明白为何
from slowquery.
多谢兄弟!
from slowquery.
修改代码文件: 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 作为判断条件,因为不同的库有可能会执行相同的语法,会导致判断错误,但目前还未确认该如何修正,回报先
from slowquery.
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
from slowquery.
Related Issues (11)
- slow log 抽象
- 部署起来太多坑了 HOT 1
- 点击进入SQL详情,报错 mysqli_connect() expects parameter 5 to be long HOT 3
- explain数据不展示 HOT 8
- 页面无法显示查询数据 HOT 5
- 页面显示不了 HOT 2
- 老板,我打开slowquery.php页面报了这几个错,您知道咋回事儿不 HOT 25
- 你好,请问能搞一个docker容器一键部署的吗? HOT 10
- 容器化部署后,sql_helper 显示 数据库链接错误,无法显示explain信息,及sql优化建议 HOT 2
- 监控页面能否增加账号密码 HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from slowquery.