mysql监控调优

运行中的 mysql 状态查看 对正在运行的 mysql 进行监控 其中一个方式就是查看 mysql 运 行状态 1 QPS 每秒 Query 量 QPS Questions or Queries seconds mysql show global status like Question 2 TPS 每秒事务量 TPS Com commit Com rollback seconds mysql show global status like Com commit mysql show global status like Com rollback 3 key Buffer 命中率 mysql show global status like key key buffer read hits 1 key reads key read requests 100 key buffer write hits 1 key writes key write requests 100 4 InnoDB Buffer 命中率 mysql show status like innodb buffer pool read innodb buffer read hits 1 innodb buffer pool reads innodb buffer pool read requests 100 5 Query Cache 命中率 mysql show status like Qcache Query cache hits Qcahce hits Qcache hits Qcache inserts 100 6 Table Cache 状态量 mysql show global status like open 比较 open tables 与 opend tables 值 7 Thread Cache 命中率 mysql show global status like Thread mysql show global status like Connections Thread cache hits 1 Threads created connections 100 8 锁定状态 mysql show global status like lock Table locks waited Table locks immediate 0 3 如果这个比值比较大的话 说明表锁造成 的阻塞比较严重 Innodb row lock waits innodb 行锁 太大可能是间隙锁造成的 9 复制延时量 mysql show slave status 查看延时时间 10 Tmp Table 状况 临时表状况 mysql show status like Create tmp Created tmp disk tables Created tmp tables 比值最好不要超过 10 如果 Created tmp tables 值比较大 可能是排序句子过多或者是连接句子不够优化 11 Binlog Cache 使用状况 mysql show status like Binlog cache 如果 Binlog cache disk use 值不为 0 可能需要调大 binlog cache size 大小 12 Innodb log waits 量 mysql show status like innodb log waits Innodb log waits 值不等于 0 的话 表明 innodb log buffer 因为空间不足而等待 性能指标性能指标 1 QPS 计算 每秒查询数 针对 MyISAM 引擎为主的 DB mysql show GLOBAL status like questions Variable name Value Questions 2009191409 1 row in set 0 00 sec mysql show global status like uptime Variable name Value Uptime 388402 1 row in set 0 00 sec QPS questions uptime 5172 mysql 自启动以来的平均 QPS 如果要计算某一时间段内的 QPS 可在高峰期间获取间隔时间 t2 t1 然后分别计算出 t2 和 t1 时刻的 q 值 QPS q2 q1 t2 t1 针对 InnnoDB 引擎为主的 DB mysql show global status like com update Variable name Value Com update 87094306 1 row in set 0 00 sec mysql show global status like com select Variable name Value Com select 1108143397 1 row in set 0 00 sec mysql show global status like com delete Variable name Value Com delete 379058 1 row in set 0 00 sec mysql show global status like uptime Variable name Value Uptime 388816 1 row in set 0 00 sec QPS com update com insert com delete com select uptime 3076 某一时间段内的 QPS 查询方法同上 2 TPS 计算 每秒事务数 mysql show global status like com commit Variable name Value Com commit 7424815 1 row in set 0 00 sec mysql show global status like com rollback Variable name Value Com rollback 1073179 1 row in set 0 00 sec mysql show global status like uptime Variable name Value Uptime 389467 1 row in set 0 00 sec TPS com commit com rollback uptime 22 3 线程连接数和命中率 mysql show global status like threads Variable name Value Threads cached 480 代表当前此时此刻线程缓存中有多少空闲线程 Threads connected 153 代表当前已建立连接的数量 因为一个连接就需要一个线程 所以也可以看成当前被使用的线程数 Threads created 20344 代表从最近一次服务启动 已创建线程的数量 Threads running 2 代表当前激活的 非睡眠状态 线程数 4 rows in set 0 00 sec mysql show global status like Connections Variable name Value Connections 381487397 1 row in set 0 00 sec 线程缓存命中率 1 Threads created Connections 99 994 我们设置的线程缓存个数 mysql show variables like thread cache size Variable name Value thread cache size 500 1 row in set 0 00 sec 根据 Threads connected 可预估 thread cache size 值应该设置多大 一般来说 250 是一个 不错的上限值 如果内存足够大 也可以设置成 thread cache size 值和 threaads connected 值相同 或者通过观察 threads created 值 如果该值很大或一直在增长 可以适当增加 thread cache size 的值 在休眠状态下每个线程大概占用 256KB 左右的内存 所以当内存 足够时 设置太小也不会节约太多内存 除非该值已经超过几千 4 表缓存 mysql show global status like open tables Variable name Value Open tables 2228 1 row in set 0 00 sec 我们设置的打开表的缓存和表定义缓存 mysql show variables like table open cache Variable name Value table open cache 16384 1 row in set 0 00 sec mysql show variables like table defi Variable name Value table definition cache 2000 1 row in set 0 00 sec 针对 MyISAM mysql 每打开一个表 都会读入一些数据到 table open cache 缓存 中 当 mysql 在这个缓 存中找不到相应的信息时 才会去磁盘上直接读取 所以该值要设置得足够大以避免需要 重新打开和重新解析表的定义 一般设置为 max connections 的 10 倍 但最好保持在 10000 以内 还有种依据就是根据状态 open tables 的值进行设置 如果发现 open tables 的值每秒变化 很大 那么可能需要增大 table open cache 的值 table definition cache 通常简单设置为服务器中存在的表的数量 除非有上万张表 针对 InnoDB 与 MyISAM 不同 InnoDB 的 open table 和 open file 并无直接联系 即打开 frm 表时其相应 的 ibd 文件可能处于关闭状态 故 InnoDB 只会用到 table definiton cache 不会使用 table open cache 其 frm 文件保存于 table definition cache 中 而 idb 则由 innodb open files 决定 前提是开 启了 innodb file per table 最好将 innodb open files 设置得足够大 使得服务器可以保 持所有的 ibd 文件同时打开 5 最大连接数 mysql show global status like Max used connections Variable name Value Max used connections 1785 1 row in set 0 00 sec 我们设置的 max connections 大小 mysql show variables like max connections Variable name Value max connections 4000 1 row in set 0 00 sec 通常 max connections 的大小应该设置为比 Max used connections 状态值大 Max used connections 状态值反映服务器连接在某个时间段是否有尖峰 如果该值大于 max connections 值 代表客户端至少被拒绝了一次 可以简单地设置为符合以下条件 Max used connections max connections 0 8 6 Innodb 缓存命中率 mysql show global status like innodb buffer pool read Variable name Value Innodb buffer pool read ahead rnd 0 Innodb buffer pool read ahead 268720 预读的页数 Innodb buffer pool read ahead evicted 0 Innodb buffer pool read requests 480291074970 从缓冲池中读取的次数 Innodb buffer pool reads 29912739 表示从物理磁盘读取的页数 5 rows in set 0 00 sec 缓冲池命中率 Innodb buffer pool read requests Innodb buffer pool read requests Innodb buffer pool read ahead Innodb buffer pool reads 99 994 如果该值小于 99 9 建议就应该增大 innodb buffer pool size 的值了 该值一般设置为内 存总大小的 75 85 或者计算出操作系统所需缓存 mysql 每个连接所需的内存 例如排 序缓冲和临时表 MyISAM 键缓存 剩下的内存都给 innodb buffer pool size 不过也不 宜设置太大 会造成内存的频繁交换 预热和关闭时间长等问题 7 MyISAM Key Buffer 命中率和缓冲区使用率 mysql show global status like key Variable name Value Key blocks not flushed 0 Key blocks unused 106662 Key blocks used 107171 Key read requests 883825678 Key reads 133294 Key write requests 217310758 Key writes 2061054 7 rows in set 0 00 sec mysql show variables like key cache block size Variable name Value key cache block size 1024 1 row in set 0 00 sec mysql show variables like key buffer size Variable name Value key buffer size 134217728 1 row in set 0 00 sec 缓冲区的使用率 1 Key blocks unused key cache block size key buffer size 18 6 读命中率 1 Key reads Key read requests 99 98 写命中率 1 Key writes Key write requests 99 05 可看到缓冲区的使用率并不高 如果很长一段时间后还没有使用完所有的键缓冲 可以考 虑把缓冲区调小一点 键缓存命中率可能意义不大 因为它和应用相关 有些应用在 95 的命中率下就工作良好 有些则需要 99 99 所以从经验上看 每秒的缓存未命中次数更重要 假设一个独立磁盘 每秒能做 100 个随机读 那么每秒有 5 个缓冲未命中可能不会导致 I O 繁忙 但每秒 80 个 就可能出现问题 每秒缓存未命中 Key reads uptime 0 33 8 临时表使用情况 mysql show global status like Created tmp Variable name Value Created tmp disk tables 19226325 Created tmp files 117 Created tmp tables 56265812 3 rows in set 0 00 sec mysql show variables like tmp table size Variable name Value tmp table size 67108864 1 row in set 0 00 sec 可看到总共创建了 56265812 张临时表 其中有 19226325 张涉及到了磁盘 IO 大概比例占 到了 0 34 证明数据库应用中排序 join 语句涉及的数据量太大 需要优化 SQL 或者增大 tmp table size 的值 我设的是 64M 该比值应该控制在 0 2 以内 9 binlog cache 使用情况 mysql show status like Binlog cache Variable name Value Binlog cache disk use 15 Binlog cache use 95978256 2 rows in set 0 00 sec mysql show variables like binlog cache size Variable name Value binlog cache size 1048576 1 row in set 0 00 sec Binlog cache disk use 表示因为我们 binlog cache size 设计的内存不足导致缓存二进制日志 用到了临时文件的次数 Binlog cache use 表示 用 binlog cache size 缓存的次数 当对应的 Binlog cache disk use 值比较大的时候 我们可以考虑适当的调高 binlog cache size 对应的值 10 Innodb log buffer size 的大小设置 mysql show variables like innodb log buffer size Variable name Value innodb log buffer size 8388608 1 row in set 0 00 sec mysql show status like innodb log waits Variable name Value Innodb log waits 0 1 row in set 0 00 sec innodb log buffer size 我设置了 8M 应该足够大了 Innodb log waits 表示因 log buffer 不 足导致等待的次数 如果该值不为 0 可以适当增大 innodb log buffer size 的值 11 表扫描情况判断 mysql show global status like Handler read Variable name Value Handler read first 19180695 Handler read key 30303690598 Handler read last 290721 Handler read next 51169834260 Handler read prev 1267528402 Handler read rnd 219230406 Handler read rnd next 344713226172 7 rows in set 0 00 sec Handler read first 使用索引扫描的次数 该值大小说不清系统性能是好是坏 Handler read key 通过 key 进行查询的次数 该值越大证明系统性能越好 Handler read next 使用索引进行排序的次数 Handler read prev 此选项表明在进行索引扫描时 按照索引倒序从数据文件里取数据的 次数 一般就是 ORDER BY DESC Handler read rnd 该值越大证明系统中有大量的没有使用索引进行排序的操作 或者 join 时没有使用到 index Handler read rnd next 使用数据文件进行扫描的次数 该值越大证明有大量的全表扫描 或者合理地创建索引 没有很好地利用已经建立好的索引 12 Innodb buffer pool wait free mysql show global status like Innodb buffer pool wait free Variable name Value Innodb buffer pool wait free 0 1 row in set 0 00 sec 该值不为 0 表示 buffer pool 没有空闲的空间了 可能原因是 innodb buffer pool size 设置 太大 可以适当减少该值