echo "deb http://ftp.cn.debian.org/debian/ stretch main" > /etc/apt/sources.list echo "deb http://ftp.cn.debian.org/debian/ stretch-updates main" >> /etc/apt/sources.list echo "deb http://ftp.cn.debian.org/debian-security stretch/updates main" >> /etc/apt/sources.listmysqlreport安装 安装DBI、DBD::mysql
rm -f /root/.cpan/sources/modules/02packages.details.txt.gz rm -f /root/.cpan/sources/modules/03modlist.data.gz apt install make gcc g++ wget libmysqlclient-dev perl -MCPAN -e shell install DBI install DBD::mysql安装&运行mysqlreport
#下载mysqlreport tgz wget http://pkgs.fedoraproject.org/repo/pkgs/mysqlreport/mysqlreport-3.5.tgz/33a345f5e2c89b083a9ff0423f7fd7b4/mysqlreport-3.5.tgz #解压到目录mysqlreport tar -zxvf mysqlreport-3.5.tgz #使用mysqlreport收集信息 mv mysqlreport-3.5 mysqlreport cd mysqlreport && mkdir report ./mysqlreport --user=root --port 3306 --outfile=../mysqlreport/report/mysqlreport20201217.txt
# ./mysqlreport --user=root --port 3306 --outfile=../mysqlreport/report/mysqlreport20201217.txt
Use of uninitialized value in formline at ./mysqlreport line 1099.
Use of uninitialized value $is in multiplication (*) at ./mysqlreport line 829.
Use of uninitialized value in formline at ./mysqlreport line 1227.
Use of uninitialized value in formline at ./mysqlreport line 1235.
MySQL 5.6.42-log uptime 0 1:45:48 Wed Mar 9 04:43:05 2022
__ Key _________________________________________________________________
Buffer used 104.00k of 8.00M %Used: 1.27
Current 1.56M %Usage: 19.51
Write hit 92.75%
Read hit 100.00%
__ Questions ___________________________________________________________
Total 2.35k 0.4/s
Com_ 1.51k 0.2/s %Total: 64.52
DMS 1.33k 0.2/s 56.52
-Unknown 591 0.1/s 25.17
COM_QUIT 97 0.0/s 4.13
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log:
DMS 1.33k 0.2/s 56.52
INSERT 884 0.1/s 37.65 66.62
SELECT 421 0.1/s 17.93 31.73
UPDATE 17 0.0/s 0.72 1.28
DELETE 5 0.0/s 0.21 0.38
REPLACE 0 0/s 0.00 0.00
Com_ 1.51k 0.2/s 64.52
show_warnin 527 0.1/s 22.44
stmt_execut 254 0.0/s 10.82
stmt_prepar 156 0.0/s 6.64
__ SELECT and Sort _____________________________________________________
Scan 245 0.0/s %SELECT: 58.19
Range 0 0/s 0.00
Full join 3 0.0/s 0.71
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 4 0.0/s
Sort range 0 0/s
Sort mrg pass 0 0/s
__ Query Cache _________________________________________________________
Memory usage 16.82k of 1.00M %Used: 1.64
Block Fragmnt 100.00%
Hits 0 0/s
Inserts 1 0.0/s
Insrt:Prune 1:1 0/s
Hit:Insert 0.00:1
__ Table Locks _________________________________________________________
Waited 0 0/s %Total: 0.00
Immediate 1.39k 0.2/s
__ Tables ______________________________________________________________
Open 81 of 2000 %Cache: 4.05
Opened 216 0.0/s
__ Connections _________________________________________________________
Max used 2 of 151 %Max: 1.32
Total 101 0.0/s
__ Created Temp ________________________________________________________
Disk table 99 0.0/s
Table 335 0.1/s Size: 1.0M
File 9 0.0/s
__ Threads _____________________________________________________________
Running 1 of 1
Cached 1 of 9 %Hit: 98.02
Created 2 0.0/s
Slow 0 0/s
__ Aborted _____________________________________________________________
Clients 4 0.0/s
Connects 5 0.0/s
__ Bytes _______________________________________________________________
Sent 3.12M 490.8/s
Received 516.43M 81.4k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 4.02M of 5.00M %Used: 80.31
Read hit 97.05%
Pages
Free 63 %Total: 19.69
Data 256 80.00 %Drty: 0.00
Misc 1 0.31
Latched 0.00
Reads 35.49k 5.6/s
From file 1.05k 0.2/s 2.95
Ahead Rnd 0 0/s
Ahead Sql 0/s
Writes 15.71k 2.5/s
Flushes 1.38k 0.2/s
Wait Free 0 0/s
__ InnoDB Lock _________________________________________________________
Waits 1 0.0/s
Current 0
Time acquiring
Total 2000 ms
Average 2000 ms
Max 2000 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 1.06k 0.2/s
Writes 2.62k 0.4/s
fsync 1.22k 0.2/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 747 0.1/s
Read 1.05k 0.2/s
Written 1.38k 0.2/s
Rows
Deleted 10 0.0/s
Inserted 331 0.1/s
Read 846 0.1/s
Updated 96 0.0/s
pt-query-digest
安装Digest::MD5
perl -MCPAN -e shell install Digest::MD5pt-query-digest安装与使用
官方：https://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html
wget percona.com/get/pt-query-digest chmod u+x pt-query-digest mv pt-query-digest /usr/bin/ 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 Cand 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<"--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 ...
