更新debian源
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.list
mysqlreport安装
安装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::MD5
pt-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 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
...
