Mysql Performance Analysis (Mysqlreport tools)

First, the role of Mysqlreport

For MySQL configuration optimization, you must first identify the MySQL performance bottleneck, and the show status output report is the reference data used to calculate the performance bottleneck. Mysqlreport is not as simple as Show status to list the data, but to the reference data to the fusion calculation, organized into a single optimization reference point, and then can be based on the value of the optimization reference point and the point of measurement, the corresponding adjustment.

First, Mysqlreport installation

[[email protected] ~]# yum -y install perl-DBD-MySQL   #依赖包[[email protected] ~]# yum -y install perl-DBI     #依赖包[[email protected] ~]#wget http://pkgs.fedoraproject.org/repo/pkgs/mysqlreport/mysqlreport-3.5.tgz/33a345f5e2c89b083a9ff0423f7fd7b4/mysqlreport-3.5.tgz[[email protected] ~]#tar -zxf mysqlreport-3.5.tgz[[email protected] mysqlreport-3.5]# ll总用量 144-rw-r--r-- 1 121813 18980 35147 4月  16 2008 COPYING-rwxr-xr-x 1 121813 18980 38873 4月  16 2008 mysqlreport     #分析报告工具-rw-r--r-- 1 121813 18980 12538 4月  16 2008 mysqlreportdoc.html-rw-r--r-- 1 121813 18980 52932 4月  16 2008 mysqlreportguide.html

First, Mysqlreport use

[[email protected] mysqlreport-3.5]#./mysqlreport--user Test--password xxxxx--host 10.3.151.185--socket/tmp/ Mysqld.sock use of uninitialized value in Formline on./mysqlreport line 1099.Use of uninitialized value $is in Multiplica tion (*) at./mysqlreport Line 829.Use of uninitialized value in Formline on./mysqlreport Line 1227.Use of uninitialized Value in Formline at./mysqlreport Line 1235.MySQL 5.6.37-log uptime 8 22:42:13 Thu 21:57:27 2018__ Ke Y _________________________________________________________________buffer used 424.00k of 8.00M%used:5.18 Curr ENT 1.46M%usage:18.24write hit 100.00%read hits 100.00%__ Questions __________________________           _________________________________total 1.82M 2.4/s com_ 1.43M 1.9/s%total:78.94 DMS             1.01M 1.3/s 55.75-unknown 644.84k 0.8/s 35.49 com_quit 14.44k 0.0/s 0.79Slow ms    46.73k 0.1/s 2.57%dms:4.61 log:dms 1.01M 1.3/s 55.75 SELECT 971       .21k 1.3/s 53.45 95.87 INSERT 24.23k 0.0/s 1.33 2.39 UPDATE            17.20k 0.0/s 0.95 1.70 DELETE 388 0.0/s 0.02 0.04 REPLACE      0 0/s 0.00 0.00com_ 1.43M 1.9/s 78.94 admin_comma 631.92k 0.8/s 34.78 set_option 529.08k 0.7/s 29.12 commit 256.63k 0.3/s 14. 12__ SELECT and Sort _____________________________________________________scan 825.18k 1.1/s%select:84.96r Ange 3 0.0/s 0.00Full join 538.09k 0.7/s 55.40Range Check 0 0     /s 0.00Full rng join 0 0/s 0.00Sort scan 1.87M 2.4/ssort range 3.29k      0.0/ssort MRG Pass 0 0/s__ Query Cache _________________________________________________________memory usage 16.81k of 1.00M%use D:1.64block fragmnt 100.00%hits 0 0/sinserts 1 0.0/sinsrt:prune 1:1 0/       Shit:insert 0.00:1__ Table Locks _________________________________________________________waited 0 0/s%total:0.00immediate 1.63M 2.1/s__ Tables ______________________________________________________________ Open 135 of%cache:6.75opened 521 0.0/s__ Connections _____________________________ ____________________________max used Notoginseng%max:1.85total 14.53k 0.0/s__ Created Temp    ________________________________________________________disk table 45.90k 0.1/stable 1.67M 2.2/s              Size:512.0mfile 0.0/s__ Threads _____________________________________________________________running 3 of 9CaChed%hit:99.74created 0.0/sslow 0 0/s__ aborted ____ _________________________________________________________clients 187 0.0/sconnects 12.89k 0.0/s__   Bytes _______________________________________________________________sent 5.83G 7.5k/sreceived 370.77M   479.7/s__ InnoDB Buffer Pool __________________________________________________usage 73.45M of 7.86G%used:                      0.91Read hits 100.00%pages free 510.36k%total:99.09 Data 4.66k 0.91%drty:0.00 Misc Notoginseng 0.01 latched 0.00Re Ads 1.20G 1.6k/s from File 1.22k 0.0/s 0.00 Ahead Rnd 0 0/s Ahead S QL 0/swrites 290.67k 0.4/sflushes 99.48k 0.1/swait free 0 0/s__ Inno DB Lock _________________________________________________________waits 4 0.0/scurrent 0Time acquiring total 9 Ms Average 2 MS Max 3 ms__ InnoDB Data, Pages, Rows _______________________________ _____________data Reads 1.33k 0.0/s writes 205.19k 0.3/s fsync 182.03k 0.2/s Pe    Nding Reads 0 writes 0 fsync 0Pages Created 1.53k 0.0/s      Read 3.13k 0.0/s written 99.48k 0.1/srows Deleted 1.04k 0.0/s Inserted 25.13k 0.0/s Read 7.65G 9.9k/s Updated 18.03k 0.0/s

Mysql performance Analysis (Mysqlreport tools)