MySQL 慢查询日志分析及可视化结果

MySQL 慢查询日志分析

1. pt-query-digest分析慢查询日志

pt-query-digest --report slow.log

1	pt - query - digest -- report slow . log

2. 报告最近半个小时的慢查询：

pt-query-digest --report --since 1800s slow.log

1	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

1	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

1	pt - query - digest -- filter '$event->{fingerprint} =~ m/^select/i' slow . log

5. 报告针对某个用户的慢查询:

pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log

1	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

1	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)创建相关数据库表

-- install.sql -- Create the database needed for the Query-Digest-UI DROP DATABASE IF EXISTS slow_query_log; CREATE DATABASE slow_query_log; USE slow_query_log; -- Create the global query review table 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, `comments` text, `reviewed_status` varchar(24) DEFAULT NULL, PRIMARY KEY (`checksum`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- Create the historical query review table 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 `ts_min` (`ts_min`), KEY `checksum` (`checksum`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125

-- install . sql -- Create the database needed for the Query - Digest - UI DROP DATABASE IF EXISTS slow_query_log ; CREATE DATABASE slow_query_log ; USE slow_query_log ;   -- Create the global query review table 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 ,    ` comments ` text ,    ` reviewed_status ` varchar ( 24 ) DEFAULT NULL ,    PRIMARY KEY ( ` checksum ` ) ) ENGINE = InnoDB DEFAULT CHARSET = utf8 ;   -- Create the historical query review table 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 ` ts_min ` ( ` ts_min ` ) ,    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';"

1 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

git clone https://github.com/kormoc/Query-Digest-UI.git cd Query-Digest-UI cp config.php.example config.php vi config.php $reviewhost = array( // Replace hostname and database in this setting // use host=hostname;port=portnum if not the default port 'dsn' => 'mysql:host=192.168.1.190;port=3306;dbname=slow_query_log', 'user' => 'slowlog', 'password' => '123456', // See http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html#cmdoption-pt-query-digest--review 'review_table' => 'global_query_review', // This table is optional. You don't need it, but you lose detailed stats // Set to a blank string to disable // See http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html#cmdoption-pt-query-digest--review-history 'history_table' => 'global_query_review_history', );

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

git clone https : //github.com/kormoc/Query-Digest-UI.git cd Query - Digest - UI cp config . php . example config . php vi config . php $ reviewhost = array ( // Replace hostname and database in this setting // use host=hostname;port=portnum if not the default port      'dsn'            = > 'mysql:host=192.168.1.190;port=3306;dbname=slow_query_log' ,      'user'            = > 'slowlog' ,      'password'        = > '123456' , // See http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html#cmdoption-pt-query-digest--review      'review_table'    = > 'global_query_review' , // This table is optional. You don't need it, but you lose detailed stats // Set to a blank string to disable // See http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html#cmdoption-pt-query-digest--review-history      '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

1

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.zrwm.com/?p=2668