pt-query-digest 慢日志监控

一、安装percona-toolkit，以centos为例

yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

yum -y install percona-toolkit

二、 创建慢日志收集表 mysql_slow_query_review.sql

CREATE TABLE `mysql_slow_query_review` (

  `checksum` CHAR(32) NOT NULL,

  `fingerprint` longtext NOT NULL,

  `sample` longtext NOT NULL,

  `first_seen` datetime(6) DEFAULT NULL,

  `last_seen` datetime(6) DEFAULT NULL,

  `reviewed_by` varchar(20) DEFAULT NULL,

  `reviewed_on` datetime(6) DEFAULT NULL,

  `comments` longtext,

  `reviewed_status` varchar(24) DEFAULT NULL,

  PRIMARY KEY (`checksum`),

  KEY `idx_last_seen` (`last_seen`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `mysql_slow_query_review_history` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `hostname_max` varchar(64) NOT NULL,

  `client_max` varchar(64) DEFAULT NULL,

  `user_max` varchar(64) NOT NULL,

  `db_max` varchar(64) DEFAULT NULL,

  `checksum` CHAR(32) NOT NULL,

  `sample` longtext NOT NULL,

  `ts_min` datetime(6) NOT NULL,

  `ts_max` datetime(6) NOT NULL,

  `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,

  `Bytes_sum` float DEFAULT NULL,

  `Bytes_min` float DEFAULT NULL,

  `Bytes_max` float DEFAULT NULL,

  `Bytes_pct_95` float DEFAULT NULL,

  `Bytes_stddev` float DEFAULT NULL,

  `Bytes_median` float DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY (checksum, ts_min, ts_max),

  KEY `idx_hostname_max_ts_min` (`hostname_max`,`ts_min`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

三、部署脚本

#!/bin/bash

DIR="$( cd "$( dirname "$0"  )" && pwd  )"

cd $DIR

#配置archery数据库的连接地址

monitor_db_host="127.0.0.1"

monitor_db_port=

monitor_db_user="root"

monitor_db_password=""

monitor_db_database="archery"

#实例慢日志位置

slowquery_file="/home/mysql/log_slow.log"

pt_query_digest="/usr/bin/pt-query-digest"

#实例连接信息

hostname="mysql_host:mysql_port" # 和archery实例配置内容保持一致，用于archery做筛选

#获取上次分析时间，初始化时请删除last_analysis_time_$hostname文件，可分析全部日志数据

if [ -s last_analysis_time_$hostname ]; then

    last_analysis_time=`cat last_analysis_time_$hostname`

else

    last_analysis_time='1000-01-01 00:00:00'

fi

#收集日志

#RDS需要增加--no-version-check选项

$pt_query_digest \

--user=$monitor_db_user --password=$monitor_db_password --port=$monitor_db_port \

--review h=$monitor_db_host,D=$monitor_db_database,t=mysql_slow_query_review  \

--history h=$monitor_db_host,D=$monitor_db_database,t=mysql_slow_query_review_history  \

--no-report --limit=% --charset=utf8 \

--since "$last_analysis_time" \

--filter="\$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$hostname\"  and \$event->{client}=\$event->{ip} " \

$slowquery_file > /tmp/analysis_slow_query.log

echo `date +"%Y-%m-%d %H:%M:%S"`>last_analysis_time_$hostname

巴特西

pt-query-digest 慢日志监控

最新文章

热门文章