[MySQL工具] pt-query-digest使用出错问题分析Can't locate Tim_MySQL

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

Browse AI

AI驱动的网页内容抓取和数据采集工具

105

查看详情

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