1. pt-query-digest分析慢查询日志
pt-query-digest --report slow.log |
2. 报告最近半个小时的慢查询:
pt-query-digest --report --since 1800s slow.log |
3. 报告一个时间段的慢查询:
pt-query-digest --report --since '2013-02-10 21:48:59' --until '2013-02-16 02:33:50' slow.log |
4. 报告只含select语句的慢查询:
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log |
5. 报告针对某个用户的慢查询:
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log |
6. 报告所有的全表扫描或full join的慢查询:
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' slow.log |
更多filter的事件属性
将慢查询日志的分析结果可视化
使用pt-query-digest分析慢查询日志并将查询分析数据保存到MySQL数据库表中.然后使用Query-Digest-UI来展示分析结果.
由于Query-Digest-UI是基于PHP的Web应用程序,因此需要LAMP环境的支持.
查询分析结果可视化步骤如下:
1)创建相关数据库表
DROP DATABASE IF EXISTS slow_query_log; |
CREATE DATABASE slow_query_log; |
CREATE TABLE `global_query_review` ( |
`checksum` bigint (20) unsigned NOT NULL , |
`fingerprint` text NOT NULL , |
`sample` longtext NOT NULL , |
`first_seen` datetime DEFAULT NULL , |
`last_seen` datetime DEFAULT NULL , |
`reviewed_by` varchar (20) DEFAULT NULL , |
`reviewed_on` datetime DEFAULT NULL , |
`reviewed_status` varchar (24) DEFAULT NULL , |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
CREATE TABLE `global_query_review_history` ( |
`hostname_max` varchar (64) NOT NULL , |
`db_max` varchar (64) DEFAULT NULL , |
`checksum` bigint (20) unsigned NOT NULL , |
`sample` longtext 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_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_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_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 , |
`Filesort_cnt` float DEFAULT NULL , |
`Filesort_sum` float DEFAULT NULL , |
`Tmp_table_on_disk_cnt` float DEFAULT NULL , |
`Tmp_table_on_disk_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 , |
UNIQUE KEY `hostname_max` (`hostname_max`,`checksum`,`ts_min`,`ts_max`), |
KEY `checksum` (`checksum`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
2) 创建数据库账号
$ mysql -uroot -p -h 192.168.1.190 < install .sql |
$ mysql -uroot -p -h 192.168.1.190 -e "grant ALL ON slow_query_log.* to 'slowlog'@'%' IDENTIFIED BY '123456';" |
3) 配置Query-Digest-UI
cp config.php.example config.php |
'dsn' => 'mysql:host=192.168.1.190;port=3306;dbname=slow_query_log' , |
'review_table' => 'global_query_review' , |
'history_table' => 'global_query_review_history' , |
4)使用pt-query-digest分析日志并将分析结果导入数据库
pt-query-digest --user=slowlog --password= 123456 --review h= 192.168 . 1.190 ,D=slow_query_log,t=global_query_review --review-history h= 192.168 . 1.190 ,D=slow_query_log,t=global_query_review_history --no-report --limit= 0 % --filter= " \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" /usr/local/mysql/data/slow.log |
5)访问web界面查看可视化结果
转自:http://www.ttlsa.com/mysql/mysql-slow-query-log-analysis-and-visualization-of-results/