Mysql 之 效能分析（mysqlreport工具）

標籤：cts   2.3   set   衡量標準   .so   sock   known   host   http   

一、mysqlreport 作用

進行MySQL的配置最佳化，首先必須找出MySQL的效能瓶頸所在；而SHOW STATUS輸出的報告正是用來計算效能瓶頸的參考資料。mysqlreport不像SHOW STATUS那樣簡單的羅列資料，而是對這些參考資料加以融合計算，整理成一個個最佳化參考點，然後就可以根據這個最佳化參考點的值以及該點的衡量標準，進行對應調整。

一、mysqlreport 安裝

[[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

一、mysqlreport 使用

[[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 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.37-log         uptime 8 22:42:13      Thu Aug 23 21:57:27 2018__ Key _________________________________________________________________Buffer used   424.00k of   8.00M  %Used:   5.18    Current       1.46M            %Usage:  18.24Write hit     100.00%Read hit      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 100 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.96Range               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  %Used:   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 2000    %Cache:   6.75Opened            521     0.0/s__ Connections _________________________________________________________Max used           37 of 2000      %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               26     0.0/s__ Threads _____________________________________________________________Running             3 of    9Cached             24 of   28      %Hit:  99.74Created            38     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 hit      100.00%Pages    Free        510.36k            %Total:  99.09    Data          4.66k                      0.91 %Drty:   0.00    Misc             37                      0.01    Latched                                  0.00Reads           1.20G    1.6k/s    From file     1.22k     0.0/s            0.00    Ahead Rnd         0       0/s    Ahead Sql                 0/sWrites        290.67k     0.4/sFlushes        99.48k     0.1/sWait Free           0       0/s__ InnoDB 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    Pending        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 之 效能分析（mysqlreport工具）