MySQL 5.5 5.1 服务器状态分析


在MySQL中使用show status能够看到服务器当前状态,但是不方便分析,特意写了个分析脚本(没有冒犯mysqlreport企图)

目前支持linux平台下5.5和5.1版本的mysql

使用前需要修改 脚本头部的参数

  1.  host="192.168.60.130"  
  2. user="dba"  
  3. pwd="123456"  
  4. port=3306  
  5. dbname="test"

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

 
  1. =============MySQL info=============  
  2. Connection id        : 4944  
  3. Current database     : test  
  4. Current user         : user@%  
  5. SSL                  : NO  
  6. MySQL VERSION        : 5.1.45-community Source distribution  
  7. MySQL client info    : 5.1.61  
  8. Protocol version     : 10  
  9. Connection           : XXX.XX.XX.XX via TCP/IP  
  10. Server characterset  : latin1  
  11. Db     characterset  : latin1  
  12. Client characterset  : latin1  
  13. Conn.  characterset  : latin1  
  14. collation_connection : latin1_swedish_ci  
  15. collation_database   : latin1_swedish_ci  
  16. collation_server     : latin1_swedish_ci  
  17. Uptime               : 473274s  
  18. ============processlist=============  
  19. processlist rowcount =80  
  20. status time>1 threads list:  
  21. Id  User        Host            db      Command     Time        State   Info      
  22. status time>1 threads count=0  
  23. =========MySQL status pulse=========  
  24. ------------------------------------  
  25. Reads/Writes status  
  26. ------------------------------------  
  27. Reads:Writes ratio                  : 0.0857164519801%  
  28. QPS/TPS                             : 51.5407755338/s   0.00167556214793/s  
  29. Table locks waited ratio            : 4.10179506858e-08%  
  30. SQL PS                              : select=0.0441203193076/s insert=29.5896816643/s update=13.3331706369/s delete=8.54955269041/s replace=0.0/s  
  31. Bytes sent/received per second      : 47641.6740937/s 73689.5984504/s  
  32. ------------------------------------  
  33. Slow and Sort queries status  
  34. ------------------------------------  
  35. Slow queries Ratio                  : 1.09458039629e-05%  
  36. Slow queries per second             : 0.000564155225092/s  
  37. Full join per second                : 0.000564155225092/s  
  38. Sort per second                     : merge passes=0.0003296187832/s range=0.00809467665665/s rows=1.20635192299/s scan=0.000232423500974/s  
  39. ------------------------------------  
  40. connections status  
  41. ------------------------------------  
  42. Thread cache hits(created/cached)        : 98.301314459% (84/16)  
  43. Connections used ratio(Max used/Max)     : 27.6666666667%(83/300)  
  44. Aborted connects ratio                   : 2.64914054601%  
  45. Threads running                          : 1  
  46. ------------------------------------  
  47. temp table and Open tables/files status  
  48. ------------------------------------  
  49. Temp tables to Disk ratio                 : 5.54827435561%  
  50. Open tables/table open cache/Opened tables: 191/400/511  
  51. Opened files per second                   : 0.00244678558298/s  
  52. ------------------------------------  
  53. InnoDB Status  
  54. ------------------------------------  
  55. Innodb buffer read hits(Disk/total)            : 99.8768291196%    (5216485/4235160926)  
  56. Innodb_dblwr_pages_written:Innodb_dblwr_writes : 70  
  57. Innodb buffer pages used ratio(free/total)     : 100.0%    (0/131072)  
  58. Innodb buffer pages dirty ratio(dirty)         : 65.9233093262%    (86407)  
  59. Innodb buffer pages flushed per second(flushed): 28.4868427169/s    (13482082/473274s)  
  60. Innodb buffer pool pages misc                  : 10254.0  
  61. Innodb row lock waits per second(waits): 0.0/s    (0)  
  62. Innodb row lock current waits          : 0  
  63. Innodb row lock time(avg/max/total)    : 8574/43757/214358  
  64. Innodb rows PS        : read=2803.58150458/s inserted=326.822963019/s updated=13.3331748628/s deleted=319.585339571/s  
  65. Innodb data PS        : reads=14.1728554706/s writes=19.1432109095/s fsyncs=2.91195797783/s  
  66. Innodb data pending PS: reads=0.0/s writes=0.0/s fsyncs=0.0/s  
  67. ------------------------------------  
  68. key buffer Status  
  69. ------------------------------------  
  70. key buffer used ratio     : 0.0732421875%  
  71. key buffer read hit ratio : 75.0%  
  72. key buffer write hit ratio: 0%  
  73. ------------------------------------  
  74. Qcache Status  
  75. ------------------------------------  
  76. Qcache queries hits ratio(hits/inserts): 0.00138882237441%    (29/20881)  
  77. Qcache hits inserts ratio(hits/inserts): 0.165581820258%    (29/17485)  
  78. Qcache memory used ratio(free/total)   : 0.118613243103%    (33514632/33554432)  
  79. Qcache prune ratio(prunes/inserts)     : 0.0%    (0/17485)  
  80. Qcache block Fragmnt ratio(free/total) : 3.44827586207%    (2/58)  
  81. ------------------------------------  
  82. Slave Status  
  83. ------------------------------------  
  84. Slave running status                               : OFF  
  85. Slave open temp tables                             : 0  
  86. Slave transactions per second(transactions/Uptime) : 0.0/s    (0/473274)  
  87. Slave received per second (received/period)        : 0.0/s    (0/0.0)  
  88. ------------------------------------  
  89. table status  
  90. ------------------------------------  
  91. 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  
  92. 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                          
  93. 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                          
  94. 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                          
  95. 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                          
  96. 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                          
  97. 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                          
  98. 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                          
  99. 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                          
  100. 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                          
  101. 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                          
  102. 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                          
  103. 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                          
  104. 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                          
  105. 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                          
  106. 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                          
  107. 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                          
  108. 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                          
  109. 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                          
  110. 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                          
  111. 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                          
  112. 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                          
  113. 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                          
  114. 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                          
  115. 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                          
  116. 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                          
  117. 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                          
  118. 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                          
  119. 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                          
  120. 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                          
  121. 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                          
  122. 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                          
  123. 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                          
  124. 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                          
  125. 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                          
  126. 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                          
  127. 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                          
  128. 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                          
  129. 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                          
  130. 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                          
  131. 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                          
  132. 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                          
  133. 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                          
  134. 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                          
  135. 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                          
  136. 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                          
  137. 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                          
  138. 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                          
  139. 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                          
  140. 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                          
  141. 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                          
  142. 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                          
  143. 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                          
  144. ldb     nummary_log     InnoDB      10      Compact     332669      82      27328512        0       28442624        208666624       3458457     2012-03-08 08:07:46     None        None        latin1_swedish_ci       None                          
  145. ldb     pet_level_up_log        InnoDB      10      Compact     229747      141     32587776        0       42123264        111149056       1311541     2012-03-08 08:09:53     None        None        latin1_swedish_ci       None                          
  146. ldb     pet_log     InnoDB      10      Compact     378913      1037        393232384       0       95715328        1098907648      2899053     2012-03-08 08:08:51     None        None        latin1_swedish_ci       None                          
  147. ldb     property_bind_log       InnoDB      10      Compact     714777      1215        868614144       0       54738944        74448896        830305      2012-03-08 08:08:40     None        None        latin1_swedish_ci       None                          
  148. ldb     purchase_platform_log       InnoDB      10      Compact     161405      283     45678592        0       24690688        5242880     160977      2012-03-08 08:08:14     None        None        latin1_swedish_ci       None                          
  149. ldb     safe_log        InnoDB      10      Compact     332886      699     233013248       0       36225024        28311552        405505      2012-03-08 08:06:31     None        None        latin1_swedish_ci       None                          
  150. ldb     sale_log        InnoDB      10      Compact     40585       103     4210688     0       6356992     8388608     483544      2012-03-08 08:09:53     None        None        latin1_swedish_ci       None                          
  151. ldb     stat        InnoDB      10      Compact     42627       98      4210688     0       2113536     5242880     195505      2012-03-08 08:08:40     None        None        latin1_swedish_ci       None                          
  152. ldb     switch_log      InnoDB      10      Compact     657658      244     160612352       0       54673408        314572800       7808502     2012-03-08 08:08:14     None        None        latin1_swedish_ci       None                          
  153. ldb     task_log        InnoDB      10      Compact     3181905     208     662732800       0       315326464       560988160       8187393     2012-03-08 08:08:14     None        None        latin1_swedish_ci       None                          
  154. ldb     user_log        InnoDB      10      Compact     3131691     391     1224753152      0       93503488        270532608       15998092        2012-03-08 08:07:46     None        None        latin1_swedish_ci       None                          
  155. mdb     acc_info        InnoDB      10      Compact     0       0       16384       0       0       0       None        2012-03-08 08:11:55     None        None        latin1_swedish_ci       None                          
  156. mdb     account     InnoDB      10      Compact     0       0       16384       0       0       0       None        2012-03-08 08:11:55     None        None        latin1_swedish_ci       None                          
  157. mdb     admin_log       InnoDB      10      Compact     0       0       16384       0       0       0       1       2012-03-08 08:11:55     None        None        latin1_swedish_ci       None                          
  158. mdb     char_info       InnoDB      10      Compact     2884564     432     1248854016      0       462422016       7340032     None        2012-03-08 16:40:42     None        None        latin1_swedish_ci       None                          
  159. mdb     child_info      InnoDB      10      Compact     17619       3409        60063744        0       5111808     6291456     None        2012-03-08 08:11:31     None        None        latin1_swedish_ci       None                          
  160. mdb     csc_sign_info       InnoDB      10      Compact     0       0       16384       0       0       0       None        2012-03-08 08:11:32     None        None        latin1_swedish_ci       None                          
  161. mdb     csl_dist_info       InnoDB      10      Compact     0       0       16384       0       16384       0       None        2012-03-08 08:11:32     None        None        latin1_swedish_ci       None                          
  162. mdb     csl_match_info      InnoDB      10      Compact     0       0       16384       0       49152       0       None        2012-03-08 08:11:55     None        None        latin1_swedish_ci       None                          
  163. mdb     csl_person_info     InnoDB      10      Compact     0       0       16384       0       16384       0       None        2012-03-08 08:11:56     None        None        latin1_swedish_ci       None                          
  164. mdb     csw_sign_info       InnoDB      10      Compact     0       0       16384       0       0       0       None        2012-03-08 08:11:31     None        None        latin1_swedish_ci       None                          
  165. mdb     draft_info      InnoDB      10      Compact     0       0       16384       0       0       0       None        2012-03-08 08:11:55     None        None        latin1_swedish_ci       None                          
  166. mdb     item_info       InnoDB      10      Compact     6533479     551     3600859136      0       1890320384      5242880     None        2012-03-08 08:11:29     None        None        latin1_swedish_ci       None                          
  167. mdb     login_info      InnoDB      10      Compact     0       0       16384       0       0       0       None        2012-03-08 08:11:55     None        None        latin1_swedish_ci       None                          
  168. mdb     party_basic_info        InnoDB      10      Compact     307     693     212992      0       0       0       None        2012-03-08 08:11:41     None        None        latin1_swedish_ci       None                          
  169. mdb     party_members_info      InnoDB      10      Compact     31658       179     5668864     0       0       4194304     None        2012-03-08 08:11:32     None        None        latin1_swedish_ci       None                          
  170. mdb     party_skills_info       InnoDB      10      Compact     3844        413     1589248     0       0       4194304     None        2012-03-08 08:11:31     None        None        latin1_swedish_ci       None                          
  171. mdb     pet_info        InnoDB      10      Compact     273678      3422        936542208       0       103694336       6291456     None        2012-03-08 08:11:32     None        None        latin1_swedish_ci       None                          
  172. mdb     server_status       InnoDB      10      Compact     10      1638        16384       0       0       0       None        2012-03-08 08:11:32     None        None        latin1_swedish_ci       None                          
  173. mdb     shop_info       InnoDB      10      Compact     593     248     147456      0       0       0       None        2012-03-08 08:11:55     None        None        latin1_swedish_ci       None                          
  174. mdb     sync_info       InnoDB      10      Compact     0       0       16384       0       0       0       None        2012-03-08 08:11:31     None        None        latin1_swedish_ci       None                          
  175. mdb     upgrade_char_info       InnoDB      10      Compact     6378        413     2637824     0       1622016     4194304     None        2012-03-08 08:11:32     None        None        latin1_swedish_ci       None                          
  176. test        game_maintenance        InnoDB      10      Compact     12      1365        16384       0       0       0       13      2012-03-08 08:11:57     None        None        latin1_swedish_ci       None                          
  177. test        ipdb        InnoDB      10      Compact     39107       67      2637824     0       0       4194304     None        2012-03-08 08:11:57     None        None        latin1_swedish_ci       None                          
  178. test        pushaccount     InnoDB      10      Compact     0       0       16384       0       0       0       None        2012-03-08 08:11:57     None        None        latin1_swedish_ci       None        

  • 1
  • 2
  • 下一页

相关内容