欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-05-06 20:48:44
...
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