bitscn.com
[mysql工具] pt-query-digest使用出错问题分析can't locate time/hires.pm in @inc
pt-query-digest ：分析查询执行日志，并产生一个查询报告
[root@472322 percona-toolkit-2.2.5]# pt-query-digest --help
can't locate time/hires.pm in @inc (@inc contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/bin/pt-query-digest line 3187.
begin failed--compilation aborted at /usr/local/bin/pt-query-digest line 3187.
解决方案：
.pm实际上是perl的包，只需安装如下软件包即可：
yum install -y perl-time-hires
[root@472322 percona-toolkit-2.2.5]# yum install perl-time-hires
loaded plugins: dellsysid, fastestmirror, presto
loading mirror speeds from cached hostfile
* base: mirror.rackspace.com
* dell-community: linux.dell.com
* epel: fedora-epel.mirror.lstn.net
* extras: mirrordenver.fdcservers.net
* updates: centos.mirror.lstn.net
setting up install process
resolving dependencies
--> running transaction check
---> package perl-time-hires.x86_64 4:1.9721-131.el6_4 will be installed
--> finished dependency resolution
dependencies resolved
=======================================================================================================================================================================
package arch version repository size
=======================================================================================================================================================================
installing:
perl-time-hires x86_64 4:1.9721-131.el6_4 updates 47 k
............................... 此处省略..............................
running transaction
installing : 4:perl-time-hires-1.9721-131.el6_4.x86_64 1/1
verifying : 4:perl-time-hires-1.9721-131.el6_4.x86_64 1/1
installed:
perl-time-hires.x86_64 4:1.9721-131.el6_4
complete!
ok，可以查看 pt-query-digest工具的功能介绍了：
[root@472322 percona-toolkit-2.2.5]# pt-query-digest --help
pt-query-digest analyzes mysql queries from slow, general, and binary log files.
it can also analyze queries from c and mysql protocol data
from tcpdump. by default, queries are grouped by fingerprint and reported in
descending order of query time (i.e. the slowest queries first). if no c
are given, the tool reads c. the optional c is used for certain
options like l and l. for more details, please use the
--help option, or try 'perldoc /usr/local/bin/pt-query-digest' for complete
documentation.
usage: pt-query-digest [options] [files] [dsn]
options:
--ask-pass prompt for a password when connecting to mysql
--attribute-aliases=a list of attribute|alias,etc (default db|schema)
--attribute-value-limit=i a sanity limit for attribute values (default
4294967296)
--charset=s -a default character set
--config=a read this comma-separated list of config files;
if specified, this must be the first option on
the command line
--[no]continue-on-error continue parsing even if there is an error (
default yes)
--[no]create-history-table create the --history table if it does not exist (
default yes)
--[no]create-review-table create the --review table if it does not exist (
default yes)
--daemonize fork to the background and detach from the shell
--database=s -d connect to this database
--defaults-file=s -f only read mysql options from the given file
--embedded-attributes=a two perl regex patterns to capture pseudo-
attributes embedded in queries
....................................
分析本地的慢查询文件:
pt-query-digest --user=root --password=m@123 /data/dbdata/localhost-slow.log
重新回顾满查询日志，并将结果保存到query_review中，注意query_review表的表结构必须先建好，表结构如下：
create table query_review (
checksum bigint unsigned not null primary key,
fingerprint text not null,
sample text not null,
first_seen datetime,
last_seen datetime,
reviewed_by varchar(20),
reviewed_on datetime,
comments text
);
命令如下：
pt-query-digest --user=root --password=m@123 --review h=localhost,d=test,t=query_review /data/dbdata/localhost-slow.log
bitscn.com