Comments (5)
hcymysql
commented on July 18, 2024
`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 = 'yourDB'
AND r.last_seen >= SUBDATE(NOW(), INTERVAL 31 DAY)
GROUP BY r.checksum
ORDER BY r.last_seen DESC,
ts_cnt DESC
LIMIT 10 ;
`
你执行这条SQL，有数据返回结果吗?
from slowquery.
wangfan618
commented on July 18, 2024
直接在数据库里查询 报‘Unknown column 'h.db_max' in 'field list'’
from slowquery.
hcymysql
commented on July 18, 2024
直接在数据库里查询 报‘Unknown column 'h.db_max' in 'field list'’
表结构不对引起的。
进入到slowquery/slowquery_table_schema目录下
导入dbinfo_table_schema.sql和slowquery_table_schema.sql表结构文件到你的运维管理机MySQL里。
例：
mysql -uroot -p123456 sql_db < ./dbinfo_table_schema.sql
mysql -uroot -p123456 sql_db < ./slowquery_table_schema.sql
from slowquery.
hcymysql
commented on July 18, 2024
CREATE TABLE mysql_slow_query_review (
checksum varchar(200) NOT NULL,
fingerprint text NOT NULL,
sample text NOT NULL,
first_seen datetime DEFAULT NULL,
last_seen datetime DEFAULT NULL,
reviewed_by varchar(20) DEFAULT NULL,
reviewed_on datetime DEFAULT NULL,
comments text DEFAULT NULL,
PRIMARY KEY (checksum),
KEY idx_last_seen (last_seen) USING BTREE
) ENGINE=InnoDB;
CREATE TABLE mysql_slow_query_review_history (
serverid_max int(4) NOT NULL,
db_max varchar(100) DEFAULT NULL,
user_max varchar(100) DEFAULT NULL,
checksum varchar(200) NOT NULL,
sample text NOT NULL,
ts_min datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
ts_max datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
ts_cnt float DEFAULT NULL,
Query_time_sum float DEFAULT NULL,
Query_time_min float DEFAULT NULL,
Query_time_max float DEFAULT NULL,
Query_time_pct_95 float DEFAULT NULL,
Query_time_stddev float DEFAULT NULL,
Query_time_median float DEFAULT NULL,
Lock_time_sum float DEFAULT NULL,
Lock_time_min float DEFAULT NULL,
Lock_time_max float DEFAULT NULL,
Lock_time_pct_95 float DEFAULT NULL,
Lock_time_stddev float DEFAULT NULL,
Lock_time_median float DEFAULT NULL,
Rows_sent_sum float DEFAULT NULL,
Rows_sent_min float DEFAULT NULL,
Rows_sent_max float DEFAULT NULL,
Rows_sent_pct_95 float DEFAULT NULL,
Rows_sent_stddev float DEFAULT NULL,
Rows_sent_median float DEFAULT NULL,
Rows_examined_sum float DEFAULT NULL,
Rows_examined_min float DEFAULT NULL,
Rows_examined_max float DEFAULT NULL,
Rows_examined_pct_95 float DEFAULT NULL,
Rows_examined_stddev float DEFAULT NULL,
Rows_examined_median float DEFAULT NULL,
Rows_affected_sum float DEFAULT NULL,
Rows_affected_min float DEFAULT NULL,
Rows_affected_max float DEFAULT NULL,
Rows_affected_pct_95 float DEFAULT NULL,
Rows_affected_stddev float DEFAULT NULL,
Rows_affected_median float DEFAULT NULL,
Rows_read_sum float DEFAULT NULL,
Rows_read_min float DEFAULT NULL,
Rows_read_max float DEFAULT NULL,
Rows_read_pct_95 float DEFAULT NULL,
Rows_read_stddev float DEFAULT NULL,
Rows_read_median float DEFAULT NULL,
Merge_passes_sum float DEFAULT NULL,
Merge_passes_min float DEFAULT NULL,
Merge_passes_max float DEFAULT NULL,
Merge_passes_pct_95 float DEFAULT NULL,
Merge_passes_stddev float DEFAULT NULL,
Merge_passes_median float DEFAULT NULL,
InnoDB_IO_r_ops_min float DEFAULT NULL,
InnoDB_IO_r_ops_max float DEFAULT NULL,
InnoDB_IO_r_ops_pct_95 float DEFAULT NULL,
InnoDB_IO_r_ops_stddev float DEFAULT NULL,
InnoDB_IO_r_ops_median float DEFAULT NULL,
InnoDB_IO_r_bytes_min float DEFAULT NULL,
InnoDB_IO_r_bytes_max float DEFAULT NULL,
InnoDB_IO_r_bytes_pct_95 float DEFAULT NULL,
InnoDB_IO_r_bytes_stddev float DEFAULT NULL,
InnoDB_IO_r_bytes_median float DEFAULT NULL,
InnoDB_IO_r_wait_min float DEFAULT NULL,
InnoDB_IO_r_wait_max float DEFAULT NULL,
InnoDB_IO_r_wait_pct_95 float DEFAULT NULL,
InnoDB_IO_r_wait_stddev float DEFAULT NULL,
InnoDB_IO_r_wait_median float DEFAULT NULL,
InnoDB_rec_lock_wait_min float DEFAULT NULL,
InnoDB_rec_lock_wait_max float DEFAULT NULL,
InnoDB_rec_lock_wait_pct_95 float DEFAULT NULL,
InnoDB_rec_lock_wait_stddev float DEFAULT NULL,
InnoDB_rec_lock_wait_median float DEFAULT NULL,
InnoDB_queue_wait_min float DEFAULT NULL,
InnoDB_queue_wait_max float DEFAULT NULL,
InnoDB_queue_wait_pct_95 float DEFAULT NULL,
InnoDB_queue_wait_stddev float DEFAULT NULL,
InnoDB_queue_wait_median float DEFAULT NULL,
InnoDB_pages_distinct_min float DEFAULT NULL,
InnoDB_pages_distinct_max float DEFAULT NULL,
InnoDB_pages_distinct_pct_95 float DEFAULT NULL,
InnoDB_pages_distinct_stddev float DEFAULT NULL,
InnoDB_pages_distinct_median float DEFAULT NULL,
QC_Hit_cnt float DEFAULT NULL,
QC_Hit_sum float DEFAULT NULL,
Full_scan_cnt float DEFAULT NULL,
Full_scan_sum float DEFAULT NULL,
Full_join_cnt float DEFAULT NULL,
Full_join_sum float DEFAULT NULL,
Tmp_table_cnt float DEFAULT NULL,
Tmp_table_sum float DEFAULT NULL,
Tmp_table_on_disk_cnt float DEFAULT NULL,
Tmp_table_on_disk_sum float DEFAULT NULL,
Filesort_cnt float DEFAULT NULL,
Filesort_sum float DEFAULT NULL,
Filesort_on_disk_cnt float DEFAULT NULL,
Filesort_on_disk_sum float DEFAULT NULL,
PRIMARY KEY (checksum,ts_min,ts_max),
KEY idx_serverid_max (serverid_max) USING BTREE,
KEY idx_query_time_max (Query_time_max) USING BTREE,
KEY idx_db_ts (db_max,ts_max)
) ENGINE=InnoDB;
from slowquery.
wangfan618
commented on July 18, 2024
感谢大佬，已经可以正常查询！
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.