如何分析慢查询日志？mysql慢查询分析工具percona-toolkit

最近针对自己的一个网站进行优化，根据判断主要是mysql查询的问题，开启慢查询日志后发现果然网站并发性能低下是由于大量不合理的查询导致

percona-toolkit是个很不错的工具，percona开发了一系列的数据库性能测试管理调优工具，想做DBA的童鞋一定要去看看

percona-toolkit安装步骤：
依次执行以下命令，当然，你也可以用;隔开每条，一并粘贴然后回车。。。
wget https://www.percona.com/downloads/percona-toolkit/2.2.14/deb/percona-toolkit_2.2.14.tar.gz tar -xzvf percona-toolkit_2.2.14.tar.gz cd percona-toolkit-2.2.14 perl Makefile.PL
进行到这一步有可能报以下错误
Warning: prerequisite DBD::mysql 3 not found. Writing Makefile for percona-toolkit
导致报错原因为缺少软件依赖的包perl-DBD-MySQL，yum安装
yum install perl-DBD-MySQL
然后重新执行
perl Makefile.PL
如果只显示Writing Makefile for percona-toolkit就OK了
最后使用以下命令安装（注意必须以root权限安装，你可以使用root用户，或者sudo，随便你了）
make && make test && make install

Percona Toolkit如何使用？
这里有本电子书，是Percona-Toolkit用户手册，免费下载了PerconaToolkit使用手册，不过是英文版的，自己琢磨吧

使用Percona-Toolkit分析慢查询日志主要使用工具pt-query-digest
1. pt-query-digest分析慢查询日志
pt-query-digest --report /usr/local/mysql/slow_querys.log
2. 报告最近半个小时的慢查询：
pt-query-digest --report --since 1800s /usr/local/mysql/slow_querys.log
3. 报告一个时间段的慢查询：
pt-query-digest --report --since '2013-02-10 21:48:59' --until '2013-02-16 02:33:50' /usr/local/mysql/slow_querys.log
4. 报告只含select语句的慢查询:
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /usr/local/mysql/slow_querys.log
5. 报告针对某个用户的慢查询:
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /usr/local/mysql/slow_querys.log
6. 报告所有的全表扫描或full join的慢查询:
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' /usr/local/mysql/slow_querys.log
更多filter的事件属性点此去看看

其他高级用法请参照上面下载链接中的使用手册

Percona Toolkit结合Query-Digest-UI可以图形化显示
Query-Digest-UI如何安装，请自行百度，也可以去 https://nodeload.github.com/kormoc/Query-Digest-UI/直接看
查询分析结果可视化步骤如下:
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;

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', );

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界面查看可视化结果（如何访问，请参照Query-Digest-UI安装文档）

如何分析慢查询日志？mysql慢查询分析工具percona-toolkit

Leave a Comment 取消回复