2019年3月14日 星期四

【MYSQL】工具percona-toolkit-3.0.4-RPM和TAR安裝


RPM安裝
版本查詢下載 https://www.percona.com/downloads/percona-toolkit/
# wget https://www.percona.com/downloads/percona-toolkit/3.0.4/binary/redhat/6/x86_64/percona-toolkit-3.0.4-r2e44c3a-el6-x86_64-bundle.tar
本範例是下載percona-toolkit-3.0.4,解壓後出現兩個檔

tar -xvf percona-toolkit-3.0.4-r2e44c3a-el6-x86_64-bundle.tar
percona-toolkit-3.0.4-1.el6.x86_64.rpm
percona-toolkit-debuginfo-3.0.4-1.el6.x86_64.rpm

執行rpm安裝主程式出現錯誤訊息

# rpm -ivh percona-toolkit-3.0.4-1.el6.x86_64.rpm
warning: percona-toolkit-3.0.4-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
perl(Time::HiRes) is needed by percona-toolkit-3.0.4-1.el6.x86_64
perl(IO::Socket::SSL) is needed by percona-toolkit-3.0.4-1.el6.x86_64
perl(Term::ReadKey) is needed by percona-toolkit-3.0.4-1.el6.x86_64
解決方式安裝依賴包
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL
yum install perl-TermReadKey.x86_64

另外也有可能缺少的↓
yum install perl-DBI
yum install perl-DBD-MySQL

安裝好缺少的依賴後,再次安裝主程式

# rpm -ivh percona-toolkit-3.0.4-1.el6.x86_64.rpm
warning: percona-toolkit-3.0.4-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing...                ########################################### [100%]

1:percona-toolkit        ########################################### [100%]

然後再下指令pt-query-digest --help只要有出現文字,即表示安裝完成!

# pt-query-digest --help
pt-query-digest analyzes MySQL queries from slow, general, and binary log files.
It can also analyze queries from C<SHOW PROCESSLIST> 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<FILES>
are given, the tool reads C<STDIN>.  The optional C<DSN> is used for certain
options like L<"--since"> and L<"--until">.  For more details, please use the
--help option, or try 'perldoc /usr/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 0)
  --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
  --expected-range=a           Explain items when there are more or fewer than
                               expected (default 5,10)
  --explain=d                  Run EXPLAIN for the sample query with this DSN
                               and print results
  --filter=s                   Discard events for which this Perl code doesn't
                               return true
  --group-by=A                 Which attribute of the events to group by (
                               default fingerprint)
  --help                       Show help and exit
  --history=d                  Save metrics for each query class in the given
                               table. pt-query-digest saves query metrics (
                               query time, lock time, etc.) to this table so
                               you can see how query classes change over time
  --host=s                 -h  Connect to host
  --ignore-attributes=a        Do not aggregate these attributes (default arg,
                               cmd, insert_id, ip, port, Thread_id, timestamp,
                               exptime, flags, key, res, val, server_id,
                               offset, end_log_pos, Xid)
  --inherit-attributes=a       If missing, inherit these attributes from the
                               last event that had them (default db,ts)
  --interval=f                 How frequently to poll the processlist, in
                               seconds (default .1)
  --iterations=i               How many times to iterate through the collect-
                               and-report cycle (default 1)
  --limit=A                    Limit output to the given percentage or count (
                               default 95%:20)
  --log=s                      Print all output to this file when daemonized
  --order-by=A                 Sort events by this attribute and aggregate
                               function (default Query_time:sum)
  --outliers=a                 Report outliers by attribute:percentile:count (
                               default Query_time:1:10)
  --output=s                   How to format and print the query analysis
                               results (default report)
  --password=s             -p  Password to use when connecting
  --pid=s                      Create the given PID file
  --port=i                 -P  Port number to use for connection
  --preserve-embedded-numbers  Preserve numbers in database/table names when
                               fingerprinting queries
  --processlist=d              Poll this DSN's processlist for queries, with --
                               interval sleep between
  --progress=a                 Print progress reports to STDERR (default time,
                               30)
  --read-timeout=m             Wait this long for an event from the input; 0 to
                               wait forever (default 0).  Optional suffix s=
                               seconds, m=minutes, h=hours, d=days; if no
                               suffix, s is used.
  --[no]report                 Print query analysis reports for each --group-by
                               attribute (default yes)
  --report-all                 Report all queries, even ones that have been
                               reviewed
  --report-format=A            Print these sections of the query analysis
                               report (default rusage,date,hostname,files,
                               header,profile,query_report,prepared)
  --report-histogram=s         Chart the distribution of this attribute's
                               values (default Query_time)
  --resume=s                   If specified, the tool writes the last file
                               offset, if there is one, to the given filename
  --review=d                   Save query classes for later review, and don't
                               report already reviewed classes
  --run-time=m                 How long to run for each --iterations.  Optional
                               suffix s=seconds, m=minutes, h=hours, d=days; if
                               no suffix, s is used.
  --run-time-mode=s            Set what the value of --run-time operates on (
                               default clock)
  --sample=i                   Filter out all but the first N occurrences of
                               each query
  --set-vars=A                 Set the MySQL variables in this comma-separated
                               list of variable=value pairs
  --show-all=H                 Show all values for these attributes
  --since=s                    Parse only queries newer than this value (parse
                               queries since this date)
  --slave-password=s           Sets the password to be used to connect to the
                               slaves
  --slave-user=s               Sets the user to be used to connect to the slaves
  --socket=s               -S  Socket file to use for connection
  --timeline                   Show a timeline of events
  --type=A                     The type of input to parse (default slowlog)
  --until=s                    Parse only queries older than this value (parse
                               queries until this date)
  --user=s                 -u  User for login if not current user
  --variations=A               Report the number of variations in these
                               attributes' values
  --version                    Show version and exit
  --[no]version-check          Check for the latest version of Percona Toolkit,
                               MySQL, and other programs (default yes)
  --[no]vertical-format        Output a trailing "\G" in the reported SQL
                               queries (default yes)
  --watch-server=s             This option tells pt-query-digest which server
                               IP address and port (like "10.0.0.1:3306") to
                               watch when parsing tcpdump (for --type tcpdump);
                               all other servers are ignored

Option types: s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=time

Rules:

  This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details.

DSN syntax is key=value[,key=value...]  Allowable DSN keys:

  KEY  COPY  MEANING
  ===  ====  =============================================
  A    yes   Default character set
  D    yes   Default database to use when connecting to MySQL
  F    yes   Only read default options from the given file
  P    yes   Port number to use for connection
  S    yes   Socket file to use for connection
  h    yes   Connect to host
  p    yes   Password to use when connecting
  t    no    The --review or --history table
  u    yes   User for login if not current user

  If the DSN is a bareword, the word is treated as the 'h' key.

Options and values after processing arguments:

  --ask-pass                   FALSE
  --attribute-aliases          db|Schema
  --attribute-value-limit      0
  --charset                    (No value)
  --config                     /etc/percona-toolkit/percona-toolkit.conf,/etc/percona-toolkit/pt-query-digest.conf,/root/.percona-toolkit.conf,/root/.pt-query-digest.conf
  --continue-on-error          TRUE
  --create-history-table       TRUE
  --create-review-table        TRUE
  --daemonize                  FALSE
  --database                   (No value)
  --defaults-file              (No value)
  --embedded-attributes        (No value)
  --expected-range             5,10
  --explain                    (No value)
  --filter                     (No value)
  --group-by                   fingerprint
  --help                       TRUE
  --history                    (No value)
  --host                       (No value)
  --ignore-attributes          arg,cmd,insert_id,ip,port,Thread_id,timestamp,exptime,flags,key,res,val,server_id,offset,end_log_pos,Xid
  --inherit-attributes         db,ts
  --interval                   .1
  --iterations                 1
  --limit                      95%:20
  --log                        (No value)
  --order-by                   Query_time:sum
  --outliers                   Query_time:1:10
  --output                     report
  --password                   (No value)
  --pid                        (No value)
  --port                       (No value)
  --preserve-embedded-numbers  FALSE
  --processlist                (No value)
  --progress                   time,30
  --read-timeout               0
  --report                     TRUE
  --report-all                 FALSE
  --report-format              rusage,date,hostname,files,header,profile,query_report,prepared
  --report-histogram           Query_time
  --resume                     (No value)
  --review                     (No value)
  --run-time                   (No value)
  --run-time-mode              clock
  --sample                     (No value)
  --set-vars                   
  --show-all                   
  --since                      (No value)
  --slave-password             (No value)
  --slave-user                 (No value)
  --socket                     (No value)
  --timeline                   FALSE
  --type                       slowlog
  --until                      (No value)
  --user                       (No value)
  --variations                 
  --version                    FALSE
  --version-check              TRUE
  --vertical-format            TRUE
  --watch-server               (No value)

TAR安裝


解壓
# tar xvf  percona-toolkit-3.0.4.tar
進去資料夾內
# cd   percona-toolkit-3.0.4
# perl Makefile.PL
可能問題一:
Can't locate ExtUtils/MakeMaker.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 Makefile.PL line 1.
BEGIN failed--compilation aborted at Makefile.PL line 1.

解決方式安裝依賴包
# type perl
perl is hashed (/usr/bin/perl)

# yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
Loaded plugins: fastestmirror
Setting up Install Process
Loading mirror speeds from cached hostfile
* extras: free.nchc.org.tw
* updates: free.nchc.org.tw
base                                                                                            | 3.7 kB     00:00     
extras                                                                                          | 3.4 kB     00:00     
updates                                                                                         | 3.4 kB     00:00     
Package perl-ExtUtils-MakeMaker-6.55-144.el6.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package perl-ExtUtils-CBuilder.x86_64 1:0.27-144.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================
Package                               Arch                  Version                         Repository           Size
=======================================================================================================================
Installing:
perl-ExtUtils-CBuilder                x86_64                1:0.27-144.el6                  base                 49 k

Transaction Summary
=======================================================================================================================
Install       1 Package(s)

Total download size: 49 k
Installed size: 59 k
Is this ok [y/N]: y
Downloading Packages:
perl-ExtUtils-CBuilder-0.27-144.el6.x86_64.rpm                                                  |  49 kB     00:00     
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : 1:perl-ExtUtils-CBuilder-0.27-144.el6.x86_64                                                        1/1
  Verifying  : 1:perl-ExtUtils-CBuilder-0.27-144.el6.x86_64                                                        1/1

Installed:
  perl-ExtUtils-CBuilder.x86_64 1:0.27-144.el6                                                                         

Complete!
可能問題二:
Checking if your kit is complete...
Warning: the following files are missing in your kit:
    README
Please inform the author.
Writing Makefile for percona-toolkit

解決方式
# yum install perl-DBD-MySQL
# perl Makefile.PL
Writing Makefile for percona-toolkit
# make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
Files=0, Tests=0,  0 wallclock secs ( 0.01 usr +  0.00 sys =  0.01 CPU)
Result: NOTESTS
# make install
Appending installation info to /usr/lib64/perl5/perllocal.pod

然後再下指令pt-query-digest --help只要有出現文字,即表示安裝完成!

# pt-query-digest --help

pt-query-digest analyzes MySQL queries from slow, general, and binary log files.
It can also analyze queries from C<SHOW PROCESSLIST> and MySQL protocol data
.
.
  --type                       slowlog
  --until                      (No value)
  --user                       (No value)
  --variations                 
  --version                    FALSE
  --version-check              TRUE
  --vertical-format            TRUE
  --watch-server               (No value)
如果執行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 3225.
BEGIN failed--compilation aborted at /usr/local/bin/pt-query-digest line 3225.

解決方式
# yum install perl-Time-HiRes

安裝完成後再輸入pt-query-digest --help指令
# pt-query-digest --help

pt-query-digest analyzes MySQL queries from slow, general, and binary log files.
It can also analyze queries from C<SHOW PROCESSLIST> and MySQL protocol data
.
.
  --type                       slowlog
  --until                      (No value)
  --user                       (No value)
  --variations                 
  --version                    FALSE
  --version-check              TRUE
  --vertical-format            TRUE
  --watch-server               (No value)

只要出現訊息就表示成功

沒有留言:

張貼留言

【MYSQL】MYSQL的SYS表說明(版本8.0)

mysql> use sys Reading table information for completion of table and column names You can turn off this feature to get a quicker s...