search

hcymysql / slowquery

Slowquery图形化显示MySQL慢日志工具
https://dbaplus.cn/news-11-2520-1.html
145 stars 58 forks source link

页面无法显示查询数据 #6

Open wangfan618 opened 1 year ago

wangfan618 commented 1 year ago

大佬，按文档部署遇到以下问题。 运维监控主机环境：centos7.8 mysql5.7.40 php5.4.16 被监控mysql主机环境：centos7.8 mysql5.7.25 percona-toolkit-3.0.12 pt-query-digest 3.0.12

目前，运维监控主机的Mysql sql_db库中mysql_slow_query_review_history，mysql_slow_query_review 表，显示已采集到被监控主机mysql-slow相关记录，但在直接 select sample,db_max from mysql_slow_query_review_history where checksum=${checksum} limit 1 查询报错 ，参考以前issues，检查对于表字段checksum已是varchar类型。 最头疼是，前端页面也查询，展示不到记录。 看http 日志报以下错误。 [Tue Feb 21 09:49:48.523692 2023] [:error] [pid 10716] [client X.X.X.X:52279] PHP Notice: Undefined index: action in /var/www/html/slowquery/slowquery.php on line 4, referer: http://X.X.X.X:/slowquery/slowquery.php [Tue Feb 21 09:49:48.525082 2023] [:error] [pid 10716] [client X.X.X.X:52279] PHP Notice: Undefined index: dbname in /var/www/html/slowquery/slowquery.php on line 61, referer: http://X.X.X.X:/slowquery/slowquery.php [Tue Feb 21 09:49:48.525151 2023] [:error] [pid 10716] [client X.X.X.X:52279] PHP Notice: A session had already been started - ignoring session_start() in /var/www/html/slowquery/slowquery.php on line 79, referer: http://10.38.1.200/slowquery/slowquery.php [Tue Feb 21 09:49:48.525344 2023] [:error] [pid 10716] [client X.X.X.X::52279] PHP Notice: A session had already been started - ignoring session_start() in /var/www/html/slowquery/slowquery.php on line 109, referer: http://10.38.1.200/slowquery/slowquery.php [Tue Feb 21 09:49:48.526400 2023] [:error] [pid 10716] [client X.X.X.X::52279] PHP Notice: Undefined index: page in /var/www/html/slowquery/slowquery.php on line 113, referer: http://X.X.X.X:/slowquery/slowquery.php [Tue Feb 21 09:49:48.530640 2023] [:error] [pid 10716] [client X.X.X.X::52279] PHP Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /var/www/html/slowquery/slowquery.php on line 152, referer: http://10.38.1.200/slowquery/slowquery.php

hcymysql commented 1 year ago

`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，有数据返回结果吗?

wangfan618 commented 1 year ago

直接在数据库里查询 报‘Unknown column 'h.db_max' in 'field list'’

hcymysql commented 1 year ago

直接在数据库里查询 报‘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
hcymysql commented 1 year ago 
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;
wangfan618 commented 1 year ago

感谢大佬，已经可以正常查询！