MySQL状态检查

检查mysql 相关状态值

　　1.关注连接数

　　如果连接数达到了最大连接数，那不管 有多少资源，用户都会阻塞在外面。

　　修改mysql最大连接数：

　　打开my.ini，修改max_connections=100(默认为100)。

　　请根据硬件情况调整到合适的大小，一般经验值可设为3000。Windows服务器大概支持量为1500-1800个连接，linux服务器可以支持到8000个左右。

　　请将max_user_connections设0——–这个0代表不限制单用户的最大连接数，其最大连接值可以等于max_connections值。　　

mysql >  show global status  like  ‘Max_used_connections’;

检查下最大的过往使用连接数，这个值在max_connections的85%左右是比较合适的，如果过高则是max_connections过少或者系统负荷过高了。

　　①mysqladmin -uroot status

[ root@mysql1 ~ ] # mysqladmin  - uroot status
　　Uptime:  1742276  Threads:  2  Questions:  2538  Slow queries:  0  Opens:  145  Flush tables:  1   Open  tables:  23  Queries per second  avg :  0.1

　　②show full processlist

　　显示所有进程

mysql >  show  full  processlist;
　　 + —– + —— + ———– + —— + ——— + —— + —— -+ ———————– +
　　 |  Id  |   User   |  Host  |  db  |  Command  |  Time  |  State  |  Info  |
　　 + —– + —— + ———– + —— + ——— + —— + —— -+ ———————– +
　　 |   629   |  root  |  localhost  |   NULL   |  Query  |   0   |   NULL   |  show  full  processlist  |
　　 |   633   |  root  |  localhost  |   NULL   |  Sleep  |   11   |   |   NULL   |
　　 + —– + —— + ———– + —— + ——— + —— + —— -+ ———————– +
　　 2  rows  in   set  ( 0.00  sec)

　　如果正在运行的语句太多，运行时间太长，表示MySQL效率有问题。必要的时候可以将对应的进程kill掉。

　　杀死休眠的进程kill ID号

        mysql >   kill   633 ;
　　Query OK,  0  rows affected ( 0.00  sec)

　　关注TIME参数，看看正在运行的用户进程有多少是长时间占用的，具体分析下。

　　③使用mysqlreport关注Connections，Threads

__ Connections _________________________________________________________

　　 Max  used  3   of   200   % Max :  1.50
　　Total  30 .16k  0.7 / s
　　。。。。。。
　　__ Threads _____________________________________________________________

　　Running  1   of   2
　　Cached  1   of   300   % Hit:  99.99
　　Created  3   0.0 / s
　　Slow  0   0 / s

　　2.关注下系统锁情况

　　① mysql> show status like ‘%lock%’;

+ —————————— -+ ——— +
　　 |  Variable_name  |  Value  |
　　 + —————————— -+ ——— +
　　 |  Com_lock_tables  |   0   |
　　 |  Com_unlock_tables  |   0   |
　　 |  Innodb_row_lock_current_waits  |   0   |
　　 |  Innodb_row_lock_time  |   0   |
　　 |  Innodb_row_lock_time_avg  |   0   |
　　 |  Innodb_row_lock_time_max  |   0   |
　　 |  Innodb_row_lock_waits  |   0   |
　　 |  Table_locks_immediate  |   2667760   |
　　 |  Table_locks_waited  |   0   |

　　②使用mysqlreport关注Table Locks，InnoDB Lock

__ Questions ___________________________________________________________
　　Total  3 .38M  81.4 / s
　　DMS  2 .88M  69.3 / s  % Total:  85.11
　　QC Hits  382 .70k  9.2 / s  11.32
　　Com_  90 .50k  2.2 / s  2.68
　　COM_QUIT  30 .15k  0.7 / s  0.89
　　 + Unknown  18   0.0 / s  0.00
　　Slow  1  s  92   0.0 / s  0.00   % DMS:  0.00   Log :  OFF
　　。。。。。。
　　__  Table  Locks _________________________________________________________
　　Waited  0   0 / s  % Total:  0.00
　　Immediate  2 .67M  64.2 / s
　　。。。。。。
　　__ InnoDB Lock _________________________________________________________
　　Waits  0   0 / s
　　 Current   0
　　Time acquiring
　　Total  0  ms
　　Average  0  ms
　　 Max   0  ms
　　。。。。。。

        如果wait过多，平均时间过长，那就是查询设计的有问题，仔细关注下超长时间的查询，并打开slow_query_log。
 

　　3. 关注慢查询(slow query)日志

　　日志必然会拖慢系统速度，特别是CPU资源，所以如果CPU资源充分，可以一直打开，如果不充足，那就在需要调整的时候，或者在replication从服务器上打开(针对select)

mysql >  show variables  like  ‘ % slow % ’;
　　 + ——————— + ————————————— -+
　　 |  Variable_name  |  Value  |
　　 + ——————— + ————————————— -+
　　 |  log_slow_queries  |   OFF   |
　　 |  slow_launch_time  |   2   |
　　 |  slow_query_log  |   OFF   |
　　 |  slow_query_log_file  |   / data0 / mysql / 3306 / data / mysql1 - slow. log   |
　　 + ——————— + ————————————— -+

　　 4  rows  in   set  ( 0.00  sec)
　　mysql >   set  GLOBAL slow_query_log = on ;
　　Query OK,  0  rows affected ( 0.00  sec)

        ①关注慢查询涉及的表的相关状态

　　表内记录数。尽量控制在500万行以内(有索引)，建议控制在200万行

　　表内索引的使用。

　　表如果update，delete，insert频繁，可以考虑optimize table优化下文件存放，索引，存储空间。

　　表内update，insert，delete查询的锁定时间。

　　select for update如果条件字段无索引的话，会引起的是锁全表而不是行锁，请关注。

　　如果查询包括GROUP BY但你想要避免排序结果的消耗，你可以指定ORDER BY NULL禁止排序。

　　②定期分析表

　　ANALYZE TABLE

　　语法：

ANALYZE  [ LOCAL | NO_WRITE_TO_BINLOG ]   TABLE  tbl_name  [ , tbl_name ]  …

 　　本语句用于分析和存储表的关键字分布。在分析期间，使用一个读取锁定对表进行锁定。这对于MyISAM, BDB和InnoDB表有作用。对于MyISAM表，本语句与使用myisamchk -a相当。

　　CHECK TABLE

　　语法：

        CHECK   TABLE  tbl_name  [ , tbl_name ]  …  [ option ]  …
　　 option   =  {QUICK  |  FAST  |  MEDIUM  |  EXTENDED  |  CHANGED}

 　　检查一个或多个表是否有错误。CHECK TABLE对MyISAM和InnoDB表有作用。对于MyISAM表，关键字统计数据被更新。

　　CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。

　　CHECKSUM TABLE

　　语法：

CHECKSUM  TABLE  tbl_name  [ , tbl_name ]  …  [  QUICK | EXTENDED  ]

 　　报告一个表校验和。

　　③使用optimize table

　　OPTIMIZE TABLE

　　语法：

OPTIMIZE  [ LOCAL | NO_WRITE_TO_BINLOG ]   TABLE  tbl_name  [ , tbl_name ]  …

 　　如果已经删除了表的一大部分，或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。

　　OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。