首页 / 数据库 / MySQL / MySQL 5.5 5.1 服务器状态分析

在MySQL中使用show status能够看到服务器当前状态,但是不方便分析,特意写了个分析脚本（没有冒犯mysqlreport企图）目前支持linux平台下5.5和5.1版本的mysql使用前需要修改 脚本头部的参数

host="192.168.60.130"   user="dba"   pwd="123456"   port=3306   dbname="test"

这种参数非常不方便,命令行参数正在组织中,先出个生产库的报表吧,总体而言,硬盘写入比高达70（已经大于64）,说明硬盘已经不堪重负了,Innodb buffer pages used ratio已经100%,内存不够了,Qcache就不应该开

=============MySQL info=============   Connection id        : 4944   Current database     : test   Current user         : user@%   SSL                  : NO   MySQL VERSION        : 5.1.45-community Source distribution   MySQL client info    : 5.1.61   Protocol version     : 10   Connection           : XXX.XX.XX.XX via TCP/IP   Server characterset  : latin1   Db     characterset  : latin1   Client characterset  : latin1   Conn.  characterset  : latin1   collation_connection : latin1_swedish_ci   collation_database   : latin1_swedish_ci   collation_server     : latin1_swedish_ci   Uptime               : 473274s   ============processlist=============   processlist rowcount =80   status time>1 threads list:   Id  User        Host            db      Command     Time        State   Info       status time>1 threads count=0   =========MySQL status pulse=========   ------------------------------------   Reads/Writes status   ------------------------------------   Reads:Writes ratio                  : 0.0857164519801%   QPS/TPS                             : 51.5407755338/s   0.00167556214793/s   Table locks waited ratio            : 4.10179506858e-08%   SQL PS                              : select=0.0441203193076/s insert=29.5896816643/s update=13.3331706369/s delete=8.54955269041/s replace=0.0/s   Bytes sent/received per second      : 47641.6740937/s 73689.5984504/s   ------------------------------------   Slow and Sort queries status   ------------------------------------   Slow queries Ratio                  : 1.09458039629e-05%   Slow queries per second             : 0.000564155225092/s   Full join per second                : 0.000564155225092/s   Sort per second                     : merge passes=0.0003296187832/s range=0.00809467665665/s rows=1.20635192299/s scan=0.000232423500974/s   ------------------------------------   connections status   ------------------------------------   Thread cache hits（created/cached）        : 98.301314459% （84/16）   Connections used ratio（Max used/Max）     : 27.6666666667%（83/300）   Aborted connects ratio                   : 2.64914054601%   Threads running                          : 1   ------------------------------------   temp table and Open tables/files status   ------------------------------------   Temp tables to Disk ratio                 : 5.54827435561%   Open tables/table open cache/Opened tables: 191/400/511   Opened files per second                   : 0.00244678558298/s   ------------------------------------   InnoDB Status   ------------------------------------   Innodb buffer read hits（Disk/total）            : 99.8768291196%    （5216485/4235160926）   Innodb_dblwr_pages_written:Innodb_dblwr_writes : 70   Innodb buffer pages used ratio（free/total）     : 100.0%    （0/131072）   Innodb buffer pages dirty ratio（dirty）         : 65.9233093262%    （86407）   Innodb buffer pages flushed per second（flushed）: 28.4868427169/s    （13482082/473274s）   Innodb buffer pool pages misc                  : 10254.0   Innodb row lock waits per second（waits）: 0.0/s    （0）   Innodb row lock current waits          : 0   Innodb row lock time（avg/max/total）    : 8574/43757/214358   Innodb rows PS        : read=2803.58150458/s inserted=326.822963019/s updated=13.3331748628/s deleted=319.585339571/s   Innodb data PS        : reads=14.1728554706/s writes=19.1432109095/s fsyncs=2.91195797783/s   Innodb data pending PS: reads=0.0/s writes=0.0/s fsyncs=0.0/s   ------------------------------------   key buffer Status   ------------------------------------   key buffer used ratio     : 0.0732421875%   key buffer read hit ratio : 75.0%   key buffer write hit ratio: 0%   ------------------------------------   Qcache Status   ------------------------------------   Qcache queries hits ratio（hits/inserts）: 0.00138882237441%    （29/20881）   Qcache hits inserts ratio（hits/inserts）: 0.165581820258%    （29/17485）   Qcache memory used ratio（free/total）   : 0.118613243103%    （33514632/33554432）   Qcache prune ratio（prunes/inserts）     : 0.0%    （0/17485）   Qcache block Fragmnt ratio（free/total） : 3.44827586207%    （2/58）   ------------------------------------   Slave Status   ------------------------------------   Slave running status                               : OFF   Slave open temp tables                             : 0   Slave transactions per second（transactions/Uptime） : 0.0/s    （0/473274）   Slave received per second （received/period）        : 0.0/s    （0/0.0）   ------------------------------------   table status   ------------------------------------   db      Name        Engine      Version     Row_format      Rows        Avg_row_length      Data_length     Max_data_length     Index_lengtht       Data_free       Auto_increment      Create_time     Update_time     Check_time      Collation       Checksum        Create_options      Comment   ldb     common_hist     MyISAM      10      Dynamic     0       0       0       281474976710655     1024        0       None        2012-01-12 09:35:10     2012-01-12 09:35:10     None        latin1_swedish_ci       None                           test        ip_dict     MyISAM      10      Dynamic     83084       22      1876144     281474976710655     1024        0       None        2012-01-17 09:18:59     2012-01-17 09:19:01     None        latin1_swedish_ci       None                           test        push_spreadaccount      MyISAM      10      Dynamic     308     24      7460        281474976710655     1024        0       None        2012-02-03 10:02:42     2012-03-12 18:13:14     None        latin1_swedish_ci       None                           dmdb        account_basic       InnoDB      10      Compact     0       0       16384       0       0       0       None        2012-03-08 08:24:49     None        None        latin1_swedish_ci       None                           dmdb        char_basic      InnoDB      10      Compact     0       0       16384       0       0       0       None        2012-03-08 08:24:49     None        None        latin1_swedish_ci       None                           dmdb        char_cost       InnoDB      10      Compact     0       0       16384       0       0       0       None        2012-03-08 08:24:49     None        None        latin1_swedish_ci       None                           dmdb        char_incre      InnoDB      10      Compact     0       0       16384       0       0       0       None        2012-03-08 08:24:49     None        None        latin1_swedish_ci       None                           dmdb        char_salary     InnoDB      10      Compact     0       0       16384       0       0       0       1       2012-03-08 08:24:49     None        None        latin1_swedish_ci       None                           dmdb        charge_item_cost        InnoDB      10      Compact     0       0       16384       0       16384       0       1       2012-03-08 08:24:49     None        None        latin1_swedish_ci       None                           dmdb        charge_item_sale        InnoDB      10      Compact     0       0       16384       0       16384       0       1       2012-03-08 08:24:49     None        None        latin1_swedish_ci       None                           dmdb        coin_cost       InnoDB      10      Compact     0       0       16384       0       0       0       1       2012-03-08 08:24:49     None        None        latin1_swedish_ci       None                           dmdb        game_baiguidi       InnoDB      10      Compact     0       0       16384       0       0       0       1       2012-03-08 08:24:49     None        None        latin1_swedish_ci       None                           dmdb        game_chongwutaowang     InnoDB      10      Compact     0       0       16384       0       0       0       1       2012-03-08 08:24:49     None        None        latin1_swedish_ci       None                           dmdb        game_wanyaoku       InnoDB      10      Compact     0       0       16384       0       0       0       1       2012-03-08 08:24:49     None        None        latin1_swedish_ci       None                           dmdb        game_yabiao     InnoDB      10      Compact     0       0       16384       0       0       0       1       2012-03-08 08:24:50     None        None        latin1_swedish_ci       None                           dmdb        item_exchange       InnoDB      10      Compact     0       0       16384       0       0       0       1       2012-03-08 08:24:50     None        None        latin1_swedish_ci       None                           dmdb        party_contrib_convert       InnoDB      10      Compact     0       0       16384       0       0       0       1       2012-03-08 08:24:50     None        None        latin1_swedish_ci       None                           dmdb        reputation_convert      InnoDB      10      Compact     0       0       16384       0       16384       0       1       2012-03-08 08:24:50     None        None        latin1_swedish_ci       None                           ldb     antiaddiction_log       InnoDB      10      Compact     33426       78      2637824     0       3178496     4194304     31667       2012-03-08 08:06:31     None        None        latin1_swedish_ci       None                           ldb     apply_log       InnoDB      10      Compact     72898       144     10502144        0       12632064        114294784       907793      2012-03-08 08:06:18     None        None        latin1_swedish_ci       None                           ldb     behavior_log        InnoDB      10      Compact     0       0       16384       0       32768       0       1       2012-03-08 08:08:51     None        None        latin1_swedish_ci       None                           ldb     block_log       InnoDB      10      Compact     44026       202     8929280     0       7405568     4194304     43655       2012-03-08 08:08:40     None        None        latin1_swedish_ci       None                           ldb     campaign_log        InnoDB      10      Compact     736936      180     133332992       0       106119168       290455552       10360528        2012-03-08 08:10:23     None        None        latin1_swedish_ci       None                           ldb     client_action_log       InnoDB      10      Compact     0       0       16384       0       49152       0       1       2012-03-08 08:08:40     None        None        latin1_swedish_ci       None                           ldb     client_performance_log      InnoDB      10      Compact     0       0       16384       0       49152       0       1       2012-03-08 08:09:05     None        None        latin1_swedish_ci       None                           ldb     coin_consume_log        InnoDB      10      Compact     63228       116     7356416     0       6389760     9437184     353287      2012-03-08 08:10:23     None        None        latin1_swedish_ci       None                           ldb     coin_order_log      InnoDB      10      Compact     911115      224     204144640       0       138788864       5242880     921668      2012-03-08 08:08:51     None        None        latin1_swedish_ci       None                           ldb     collect_data_log        InnoDB      10      Compact     366246      372     136478720       0       10502144        138412032       1809067     2012-03-08 08:06:19     None        None        latin1_swedish_ci       None                           ldb     combat_log      InnoDB      10      Compact     18852       223     4210688     0       491520      4194304     47986       2012-03-08 08:08:57     None        None        latin1_swedish_ci       None                           ldb     common_log      InnoDB      10      Compact     2058455     219     452313088       0       57786368        136314880       10274133        2012-03-08 08:10:23     None        None        latin1_swedish_ci       None                           ldb     cost_coin_log       InnoDB      10      Compact     55519       189     10502144        0       0       7340032     262758      2012-03-08 08:08:57     None        None        latin1_swedish_ci       None                           ldb     csc_combat_log      InnoDB      10      Compact     0       0       16384       0       49152       0       1       2012-03-08 08:08:57     None        None        latin1_swedish_ci       None                           ldb     csl_combat_log      InnoDB      10      Compact     0       0       16384       0       32768       0       1       2012-03-08 08:08:14     None        None        latin1_swedish_ci       None                           ldb     csl_match_log       InnoDB      10      Compact     0       0       16384       0       32768       0       1       2012-03-08 08:07:42     None        None        latin1_swedish_ci       None                           ldb     csp_combat_log      InnoDB      10      Compact     0       0       16384       0       32768       0       1       2012-03-08 08:09:05     None        None        latin1_swedish_ci       None                           ldb     csp_match_log       InnoDB      10      Compact     0       0       16384       0       32768       0       1       2012-03-08 08:08:14     None        None        latin1_swedish_ci       None                           ldb     debug_log       InnoDB      10      Compact     1872729     233     437649408       0       135692288       98566144        13829084        2012-03-08 08:08:14     None        None        latin1_swedish_ci       None                           ldb     encourage_detail_log        InnoDB      10      Compact     5587        378     2113536     0       2310144     9437184     114217      2012-03-08 08:08:40     None        None        latin1_swedish_ci       None                           ldb     equipment_log       InnoDB      10      Compact     978444      386     377864192       0       149405696       27262976        3169644     2012-03-08 08:09:02     None        None        latin1_swedish_ci       None                           ldb     errand_log      InnoDB      10      Compact     2921571     159     465600512       0       309919744       813694976       30593577        2012-03-08 08:09:05     None        None        latin1_swedish_ci       None                           ldb     gbuy_action_log     InnoDB      10      Compact     0       0       16384       0       65536       0       1       2012-03-08 08:08:40     None        None        latin1_swedish_ci       None                           ldb     gift_log        InnoDB      10      Compact     63817       197     12599296        0       2113536     13631488        178135      2012-03-08 08:10:23     None        None        latin1_swedish_ci       None                           ldb     gm_log      InnoDB      10      Compact     4802        330     1589248     0       557056      4194304     4383        2012-03-08 08:08:40     None        None        latin1_swedish_ci       None                           ldb     gs_log      InnoDB      10      Compact     34300       275     9453568     0       3178496     7340032     358648      2012-03-08 08:07:46     None        None        latin1_swedish_ci       None                           ldb     history_credit_log      InnoDB      10      Compact     0       0       16384       0       0       0       None        2012-03-08 08:07:42     None        None        latin1_swedish_ci       None                           ldb     important_log       InnoDB      10      Compact     12589921        476     6002049024      0       1199570944      7340032     17086401        2012-03-08 08:08:40     None        None        latin1_swedish_ci       None                           ldb     important_pet_log       InnoDB      10      Compact     1493        1766        2637824     0       327680      4194304     1424        2012-03-08 08:08:40     None        None        latin1_swedish_ci       None                           ldb     item_transfer_log       InnoDB      10      Compact     4916635     343     1689272320      0       795607040       257949696       8857762     2012-03-08 08:06:18     None        None        latin1_swedish_ci       None                           ldb     level_up_log        InnoDB      10      Compact     93675       201     18890752        0       8437760     160432128       975939      2012-03-08 08:06:19     None        None        latin1_swedish_ci       None                           ldb     login_log       InnoDB      10      Compact     2169745     353     766541824       0       255852544       337641472       3885253     2012-03-08 08:08:51     None        None        latin1_swedish_ci       None                           ldb     material_log        InnoDB      10      Compact     44701       211     9453568     0       2113536     7340032     223641      2012-03-08 08:08:02     None        None        latin1_swedish_ci       None                           ldb     money_log       InnoDB      10      Compact     659037      144     95551488        0       72564736        174063616       2399885     2012-03-08 08:08:14     None        None        latin1_swedish_ci       None                           ldb     nummary_log     InnoDB      10      Compact     332669      82      27328512        0       28442624        208666624       3458457     2012-03-08 08:07:46     None   &nbs 版权所有©石家庄振强科技有限公司2024 冀ICP备08103738号-5 网站地图