mysql性能监控相关

原文地址:http://www.cnblogs.com/qmfsun/p/4958044.html

目录

• 一，获取mysql用户下的进程总数

• 二，主机性能状态

• 三，CPU使用率

• 四，磁盘IO量

• 五，swap进出量[内存]

• 六，数据库性能状态

• 七、querylog

• 八、mysqladmin的extended-status指令查看mysql各状态值

• 九、开源监控软件  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341

一，获取mysql用户下的进程总数 ps -ef | awk  '{print $1}'  | grep  "mysql"  | grep -v  "grep"  | wc-1    二，主机性能状态 # uptime [root@ ~]# uptime   13:05:52 up 53 days, 52 min,  1 user,  load average: 0.00, 0.00, 0.00     三，CPU使用率 # top 或 # vmstat    四，磁盘IO量 # vmstat 或 # iostat    五，swap进出量[内存]   # free    六，数据库性能状态 (1)QPS(每秒Query量)  QPS = Questions(or Queries) / seconds  mysql > show  /*50000 global */  status like  'Question' ; (2)TPS(每秒事务量) TPS = (Com_commit + Com_rollback) / seconds mysql >  show status like  'Com_commit' ; mysql > show status like  'Com_rollback' ;   (3)key Buffer 命中率 key_buffer_read_hits = (1-key_reads / key_read_requests)  * 100% key_buffer_write_hits = (1-key_writes / key_write_requests) * 100% mysql> show status like  'Key%' ;   (4)InnoDB Buffer命中率 innodb_buffer_read_hits = (1 -  innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100% mysql> show status like  'innodb_buffer_pool_read%' ;   (5)Query Cache命中率 Query_cache_hits = (Qcahce_hits / (Qcache_hits +  Qcache_inserts )) * 100%; mysql> show status like  'Qcache%' ; (6)Table Cache状态量 mysql> show  status like  'open%' ;   (7)Thread Cache 命中率 Thread_cache_hits = (1 - Threads_created / connections  ) * 100% mysql> show status like  'Thread%' ; mysql> show status like  'Connections' ;   (8)锁定状态 mysql> show status like  '%lock%' ; (9)复制延时量 mysql > show slave status   (10) Tmp Table 状况(临时表状况) mysql > show status like  'Create_tmp%' ;   (11) Binlog Cache 使用状况 mysql > show status like  'Binlog_cache%' ;   (12) Innodb_log_waits 量 mysql > show status like  'innodb_log_waits' ;         七、querylog mysql有一个功能就是可以 log 下来运行的比较慢的sql语句，默认是没有这个 log 的，为了开启这个功能，要修改my.cnf或者在mysql启动的时候加入一些参数。如果在my.cnf里面修改，需增加如下几行 引用 long_query_time = 1  log -slow-queries = /var/youpath/slow. log  log -queries-not- using -indexes[这个在mysql4.10以后才被引入] long_query_time 是指执行超过多久的sql会被 log 下来，这里是1秒。 log -slow-queries  设置把日志写在那里，可以为空，系统会给一个缺省的文件host_name-slow. log ，我生成的 log 就在mysql的data目录 log -queries-not- using -indexes  就是字面意思， log 下来没有使用索引的query。  把上述参数打开，运行一段时间，就可以关掉了，省得影响生产环境。   接下来就是分析了，我这里的文件名字叫host-slow. log 。 先mysqldumpslow  –help以下，我主要用的是  引用 -s ORDER what to sort by (t, at, l, al, r, ar etc), ‘at’ is  default  -t  NUM just show the top n queries  -g PATTERN grep: only consider stmts that  include  this  string   -s，是order的顺序，说明写的不够详细，俺用下来，包括看了代码，主要有  c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时倒叙  -t，是top  n的意思，即为返回前面多少条的数据  -g，后边可以写一个正则匹配模式，大小写不敏感的   mysqldumpslow -s c  -t 20 host-slow. log mysqldumpslow -s r -t 20  host-slow. log  上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。 mysqldumpslow -t 10  -s t -g “left join” host-slow. log 这个是按照时间返回前10条里面含有左连接的sql语句。     八、mysqladmin的extended-status指令查看mysql各状态值 /usr/local/mysql/bin/mysqladmin  -u User --host=IP --password=Passwd extended-status|grep $VARIABLE|awk '{print  $4}'     附网友总结的mysql状态值含义   Aborted_clients  由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。 Aborted_connects  尝试已经失败的MySQL服务器的连接的次数。 Connections  试图连接MySQL服务器的次数。 Created_tmp_tables  当执行语句时，已经被创造了的隐含临时表的数量。 Delayed_insert_threads  正在使用的延迟插入处理器线程的数量。 Delayed_writes  用INSERT DELAYED写入的行数。 Delayed_errors  用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。 Flush_commands  执行FLUSH命令的次数。 Handler_delete  请求从一张表中删除行的次数。 Handler_read_first  请求读入表中第一行的次数。 Handler_read_key  请求数字基于键读行。 Handler_read_next  请求读入基于一个键的一行的次数。 Handler_read_rnd  请求读入基于一个固定位置的一行的次数。 Handler_update  请求更新表中一行的次数。 Handler_write  请求向表中插入一行的次数。 Key_blocks_used  用于关键字缓存的块的数量。 Key_read_requests  请求从缓存读入一个键值的次数。 Key_reads  从磁盘物理读入一个键值的次数。 Key_write_requests  请求将一个关键字块写入缓存次数。 Key_writes  将一个键值块物理写入磁盘的次数。 Max_used_connections  同时使用的连接的最大数目。 Not_flushed_key_blocks  在键缓存中已经改变但是还没被清空到磁盘上的键块。 Not_flushed_delayed_rows  在INSERT DELAY队列中等待写入的行的数量。 Open_tables  打开表的数量。 Open_files  打开文件的数量。 Open_streams  打开流的数量(主要用于日志记载） Opened_tables  已经打开的表的数量。 Questions  发往服务器的查询的数量。 Slow_queries  要花超过long_query_time时间的查询数量。 Threads_connected  当前打开的连接的数量。 Threads_running  不在睡眠的线程数量。 Uptime  服务器工作了多少秒。      九、开源监控软件 1，RRDTool  2，Nagios  3，MRTG  4，Cacti      1.系统mysql的进程数 ps -ef | grep  "mysql"  | grep -v  "grep"  | wc –l   2.Slave_running mysql > show status like  'Slave_running' ; 如果系统有一个从复***务器，这个值指明了从服务器的健康度   3.Threads_connected mysql > show status like  'Threads_connected' ; 当前客户端已连接的数量。这个值会少于预设的值，但你也能监视到这个值较大，这可保证客户端是处在活跃状态。   4.Threads_running mysql  > show status like  'Threads_running' ; 如果数据库超负荷了，你将会得到一个正在（查询的语句持续）增长的数值。这个值也可以少于预先设定的值。这个值在很短的时间内超过限定值是没问题的。当Threads_running值超过预设值时并且该值在5秒内没有回落时，  要同时监视其他的一些值。   5.Aborted_clients mysql > show status like  'Aborted_clients' ; 客户端被异常中断的数值，即连接到mysql服务器的客户端没有正常地断开或关闭。对于一些应用程序是没有影响的，但对于另一些应用程序可能你要跟踪该值，因为异常中断连接可能表明了一些应用程序有问题。   6.Questions mysql> show status like  'Questions' ; 每秒钟获得的查询数量，也可以是全部查询的数量，根据你输入不同的命令会得到你想要的不同的值。   7.Handler_* mysql> show status like  'Handler_%' ; 如果你想监视底层（low-level）数据库负载，这些值是值得去跟踪的。 如果Handler_read_rnd_next值相对于你认为是正常值相差悬殊，可能会告诉你需要优化或索引出问题了。Handler_rollback表明事务被回滚的查询数量。你可能想调查一下原因。   8.Opened_tables mysql>  show status like  'Opened_tables' ; 表缓存没有命中的数量。如果该值很大，你可能需要增加table_cache的数值。典型地，你可能想要这个值每秒打开的表数量少于1或2。 9.Select_full_join mysql> show status like  'Select_full_join' ; 没有主键（key）联合（Join）的执行。该值可能是零。这是捕获开发错误的好方法，因为一些这样的查询可能降低系统的性能   10.Select_scan mysql> show status like  'Select_scan' ; 执行全表搜索查询的数量。在某些情况下是没问题的，但占总查询数量该比值应该是常量（即Select_scan/总查询数量商应该是常数）。如果你发现该值持续增长，说明需要优化，缺乏必要的索引或其他问题。 11.Slow_queries mysql>  show status like  'Slow_queries' ; 超过该值（-- long -query- time ）的查询数量，或没有使用索引查询数量。对于全部查询会有小的冲突。如果该值增长，表明系统有性能问题。   12.Threads_created mysql> show status like  'Threads_created' ; 该值应该是低的。较高的值可能意味着你需要增加thread_cache的数值，或你遇到了持续增加的连接，表明了潜在的问题。   13.客户端连接进程数 shell> mysqladmin processlist mysql> show processlist; 你可以通过使用其他的统计信息得到已连接线程数量和正在运行线程的数量，检查正在运行的查询花了多长时间是一个好主意。如果有一些长时间的查询，管理员可以被通知。你可能也想了解多少个查询是在 "Locked" 的状态—---该值作为正在运行的查询不被计算在内而是作为非活跃的。一个用户正在等待一个数据库响应。   14.innodb状态 mysql> show engine innodb status\G; 该语句产生很多信息，从中你可以得到你感兴趣的。首先你要检查的就是“从最近的XX秒计算出来的每秒的平均负载”。   (1)Pending  normal aio reads: 该值是innodb  io请求查询的大小（size）。如果该值大到超过了10—20，你可能有一些瓶颈。   (2)reads/s,  avg bytes/read, writes/s,  fsyncs/s:这些值是io统计。对于reads/writes大值意味着io子系统正在被装载。适当的值取决于你系统的配置。   (3)Buffer  pool hit rate:这个命中率非常依赖于你的应用程序。当你觉得有问题时请检查你的命中率   (4)inserts/s,  updates/s, deletes/s,  reads/s:有一些Innodb的底层操作。你可以用这些值检查你的负载情况查看是否是期待的数值范围。   15.主机性能状态 shell> uptime   16.CPU使用率 shell> top shell> vmstat   17.磁盘IO shell> vmstat shell> iostat   18.swap进出量(内存) shell>  free   19.MySQL错误日志 在服务器正常完成初始化后，什么都不会写到错误日志中，因此任何在该日志中的信息都要引起管理员的注意。   20.InnoDB表空间信息 InnoDB仅有的危险情况就是表空间填满----日志不会填满。检查的最好方式就是：show  table status;你可以用任何InnoDB表来监视InnoDB表的剩余空间。   21.QPS每秒Query量 QPS = Questions(or Queries) / seconds mysql > show  /* global */  status like  'Question' ;   22.TPS(每秒事务量) TPS = (Com_commit + Com_rollback) / seconds mysql > show status like  'Com_commit' ; mysql > show status like  'Com_rollback' ;   23.key  Buffer 命中率 key_buffer_read_hits = (1-key_reads / key_read_requests) * 100% key_buffer_write_hits = (1-key_writes / key_write_requests) * 100% mysql> show status like  'Key%' ;   24.InnoDB  Buffer命中率 Innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100% mysql> show status like  'innodb_buffer_pool_read%' ;   25.Query  Cache命中率 Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%; mysql> show status like  'Qcache%' ;   26.Table  Cache状态量 mysql> show status like  'open%' ;   27.Thread  Cache 命中率 Thread_cache_hits = (1 - Threads_created / connections ) * 100% mysql> show status like  'Thread%' ; mysql> show status like  'Connections' ;   28.锁定状态 mysql> show status like  '%lock%' ;   29.复制延时量 mysql > show slave status   30.Tmp  Table状况(临时表状况) mysql > show status like  'Create_tmp%' ;   31.Binlog  Cache使用状况 mysql > show status like  'Binlog_cache%' ;   32.Innodb_log_waits量 mysql > show status like  'innodb_log_waits' ;