MySQL监控

Mysql 监控
mysql大多数问题出现在SQL语句和索引上。

一、慢查询日志分析

show variables like '%slow%'     /*查看是否开启了慢查询日志*/

set @@global.slow_query_log = ON    /*开启全局慢查询日志,重启mysql失效*/

如果想重启之后有效，需要修改mysql的配置文件my.cnf。在my.cnf的最后添加上：

1 slow_query_log      //开启慢查询日志
2 slow_query_log_file=/usr/local/mysql/data/zhoucentos-slow.log //日志的位置
3 long_query_time=0.1     //设定时间为0.1秒，如果查询时间大于0.1的sql语句都会被记录到 日志文件里。

Linux系统使用mysql自带命令mysqldumpslow查看

可通过 mysqldumpslow –help查看帮助信息

-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语句。

图例中的命令：mysqldumpslow –s at- t 50 host-slow.log 显示出耗时最长的50个SQL语句的执行信息

以Count: 32 Time=0.26s (8s) Lock=0.00s (0s) Rows=10.0 (320), wos_20120719[wos_20120719]@2host 为例：

Count: 32 该SQL总共执行32次

Time = 0.26s (8s) 平均每次执行该SQL耗时0.26秒，总共耗时32（次）*0.26（秒）=8秒。

Lock=0.00s(0s) lock时间0秒

Rows =10.0(320) 每次执行SQL影响数据库表中的10行记录，总共影响 10（行）*32（次）=320行记录

批注：1次查询10条记录所用时间小于 1次1条查询10次所用时间！

二、连接数

1 show variables like '%connections%' /*查看最大的连接数*/
2 
3 show status like '%connection%' /*查看连接的状态*/
4 Connections ：试图连接MySQL服务器的次数；
5 max_connections：整个MySQL允许的最大连接数；

1 show status like '%thread%'
2 其中：
3 Threads_connected 当前打开的连接的数量
4 Threads_cached 线程缓存内的线程的数量 
5 Threads_created 创建的线程数 
6 Threads_running 激活的（非睡眠状态）线程数

三、缓冲池

1 show variables like '%buffer_pool_size%' 默认是134兆，如果服务器的内存是32G，设置为20G对服务器的性能提升是非常大的！
2 show status like '%buffer%';
3 其中：
4 Innodb_buffer_pool_reads：缓冲池中没有读到数据，而从磁盘内读取的次数
5 Innodb_buffer_pool_read_requests：来缓冲池中读数据的次数
6 Innodb_buffer_pool_pages_total：缓冲池的总页数（内存是以页为单位）
7 Innodb_buffer_pool_pages_free：缓冲池中处于空闲状态的页数

命中率：innodb_buffer_read_hits=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100%

========================================================================================
四、查询缓存（一般默认）
show variables like '%query_cache%'; 查询缓存的本身存在一种算法，如果更新了查询语句就直接去硬盘读取数据，本身也消耗一些性能，所以查询缓存的size很小
show status like '%Qcache%'
查询缓存百分比：Query_cache_hits=(Qcache_hits/(Qcache_hits+Qcache_inserts))*100%

五、线程缓存（连接池，一般默认）
show variables like '%thread%'
show status like 'connections' 试图连接mysql的次数
show status like '%thread%'
线程缓存命中率：Threads_Cache_Hit=(Connections-Threads_created)/Connections*100%

六、表锁和行锁
show status like '%lock%';
存储引擎：早期mysql用的，MyISAM（表锁），现在都用Innodb（行锁）
MyISAM：支持表锁
Innodb：支持行锁