MySQL监控主要指标及采集方法

共计 5611 个字符，预计需要花费 15 分钟才能阅读完成。

MySQL 监控属于 DB 监控的模块之一，包括采集、展示、监控告警。本文主要介绍 MySQL 监控的主要指标和采集方法。

MySQL 监控和 Redis 监控的逻辑类似，可参考文章《Redis 监控主要指标及采集方法 http://www.linuxidc.com/Linux/2016-11/136783.htm》。

DBA 前台添加 MySQL 监控时系统会调用自动调度平台接口将 Mysql 监控的加密账户密码和 ip 端口等信息发送至目标，同时发送采集 Agent。

一、采集指标和命令

1、MySQL 服务运行状态

约定所有 MySQL 服务都必须以 ip1（内网 ip）来绑定，每个机器只有一个 ip1，可以有多个端口，即多个 MySQL Server。采集程序读取 ip 端口信息文件来判断 server 是否存在。

sockParam=`ps aux | grep -P “mysqld.*–port=${port}” | grep -oP ” –socket.*\.sock”`  # 空则获取不到该服务器端口 mysql socket 配置，请检查 mysql 配置是否正确
MYSQL=”/usr/local/mysql/bin/mysql -hlocalhost –port=${port} ${sockParam} -u${user} -p${password} “
MYSQL_ADMIN=”/usr/local/mysql/bin/mysqladmin -hlocalhost –port=${port} ${sockParam} -u${user} -p${password} “
curStatus=`${MYSQL} -e”show global status”`  # 空则是获取不到该服务器 mysql 状态，请检查 mysql 是否正常运行
if [-z “${curStatus}” ]
then
    portExists=0
else
    echo “${curStatus}” >> ${curFile}
    portExists=1

2、连接数

${MYSQL_ADMIN} processlist -v | wc -l

3、线程数

grep ‘Threads_connected’ ${curFile} | awk ‘{print $2}’

4、慢查询数

grep ‘Slow_queries’ ${curFile} | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值，等于最近 1 分钟的慢查询次数。上次数据保存在 last.cache。

5、打开表数

grep ‘Open_tables’ ${curFile} | awk -F ‘ ‘ ‘{print $2}’

6、每秒执行 select 数

grep ‘Com_select’ ${curFile} | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值除以时间差，等于最近 1 分钟的执行数量。上次数据保存在 last.cache。

7、每秒执行 delete 数

grep ‘Com_delete’ ${curFile} | grep -v ‘multi’ | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值除以时间差，等于最近 1 分钟的执行数量。上次数据保存在 last.cache。

8、每秒执行 insert 数

grep ‘Com_insert’ ${curFile} | grep -v ‘select’ | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值除以时间差，等于最近 1 分钟的执行数量。上次数据保存在 last.cache。

9、每秒执行 update 数

grep ‘Com_update’ ${curFile} | grep -v ‘multi’ | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值除以时间差，等于最近 1 分钟的执行数量。上次数据保存在 last.cache。

10、每秒钟执行 replace 数

grep ‘Com_replace’ ${curFile} | grep -v ‘select’ | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值除以时间差，等于最近 1 分钟的执行数量。上次数据保存在 last.cache。

11、每秒钟执行的 Innodb_rows_deleted

grep ‘Innodb_rows_deleted’ ${curFile} | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值除以时间差，等于最近 1 分钟的执行数量。上次数据保存在 last.cache。

12、每秒钟执行的 Innodb_rows_inserted

grep ‘Innodb_rows_inserted’ ${curFile} | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值除以时间差，等于最近 1 分钟的执行数量。上次数据保存在 last.cache。

13、每秒钟执行的 Innodb_rows_read

grep ‘Innodb_rows_read’ ${curFile} | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值除以时间差，等于最近 1 分钟的执行数量。上次数据保存在 last.cache。

14、每秒钟执行的 Innodb_rows_updated

grep ‘Innodb_rows_updated’ ${curFile} | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值除以时间差，等于最近 1 分钟的执行数量。上次数据保存在 last.cache。

15、每秒钟执行的 innodb rows total

expr ${innodbRowsDeletedPS} + ${innodbRowsInsertedPS} + ${innodbRowsReadPS} + ${innodbRowsUpdatedPS}

等于前面四个 Innodb_rows_* 执行次数的总和

16、每秒处理命令数 qps

expr ${mysqlSelectNumPS} + ${mysqlInsertNumPS} + ${mysqlUpdateNumPS} + ${mysqlDeleteNumPS} + ${mysqlReplaceNumPS}

等于前面五个 mysql 命令 Com_* 的数量总和

17、每秒接收字节数 KByte/s

grep ‘Bytes_received’ ${curFile} | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值除以时间差，等于最近 1 分钟的执行数量，除以 1024 得到单位 KByte/s。上次数据保存在 last.cache。

18、每秒发送字节数

grep ‘Bytes_sent’ ${curFile} | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值除以时间差，等于最近 1 分钟的执行数量，除以 1024 得到单位 KByte/s。上次数据保存在 last.cache。

19、可立即获得锁的次数

grep ‘Table_locks_immediate’ ${curFile} | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值，等于最近 1 分钟的可立即获得锁数量。上次数据保存在 last.cache。

20、不可立即获得锁的次数

grep ‘Table_locks_waited’ ${curFile} | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值，等于最近 1 分钟的不可立即获得锁数量。上次数据保存在 last.cache。

21、一行锁定需等待时间

grep ‘Innodb_row_lock_waits’ ${curFile} | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值，等于最近 1 分钟的一行锁定需等待时间。上次数据保存在 last.cache。

22、当前脏页数

grep ‘Innodb_buffer_pool_pages_dirty’ ${curFile} | awk -F ‘ ‘ ‘{print $2}’

23、要求清空的缓冲池页数

grep ‘Innodb_buffer_pool_pages_flushed’ ${curFile} | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值，等于最近 1 分钟的要求清空的缓冲池页数。上次数据保存在 last.cache。

24、Innodb 写入日志字节数 KByte

grep ‘Innodb_os_log_written’ ${curFile} | awk -F ‘ ‘ ‘{print $2}’

需要计算两次的慢查询次数得到差值，等于最近 1 分钟的写入日志字节数，除以 1024 得到 KByte。上次数据保存在 last.cache。

25、占用内存大小 MByte

pid=`ps aux | grep ‘mysqld’ | grep -Ev ‘safe|grep’ | awk ‘{print $2}’ `
mem=`cat /proc/${pid}/status | grep ‘VmRSS’ | awk ‘{print $2}’`
mysqlMem=`echo “scale=2;${mem} / 1024” | bc`

除以 1024 得到 MByte

26、handler socket 每秒处理数

curHsTableLock=`grep ‘Hs_table_lock’ ${curFile} | awk ‘{print $2}’`
preHsTableLock=`grep ‘Hs_table_lock’ ${preFile} | awk ‘{print $2}’`
if [-n “${curHsTableLock}” ]
then
    hsQPS=`echo “scale=0;(${curHsTableLock} – ${preHsTableLock}) / ${intervalTime}” | bc`
else
    hsQPS=0
fi

27、主从同步和状态

# 主从信息
# 是否为从服务器
slave_running=`grep ‘Slave_running’ ${curFile} | awk ‘{print $2}’`
if [“${slave_running}A” = “ONA” ]
then
    slaveRunning=1
    slaveStatus=`${MYSQL} -e’show slave status\G’`
    echo “${slaveStatus}” > ${slaveFile}
   
    slaveIoRunning=`grep ‘Slave_IO_Running’ ${slaveFile} | awk -F ‘:’ ‘{print $2}’`
    slaveSqlRunning=`grep ‘Slave_SQL_Running’ ${slaveFile} | awk -F ‘:’ ‘{print $2}’`

    if [“${slaveIoRunning}A” == “NoA” -o “${slaveSqlRunning}A” == “NoA” ]
    then
        slaveRunning=3
    fi
   
    secondsBehindMaster=`grep ‘Seconds_Behind_Master’ ${slaveFile} | awk -F ‘:’ ‘{print $2}’`
    if [“${secondsBehindMaster}A” = “NULLA” ]
    then
        secondsBehindMaster=8888  # 表示主从不同步
    fi

    #是从库时 获取主库 ip
    master=`grep ‘Master_Host’ ${slaveFile} | awk -F ‘:’ ‘{print $2}’`
    masterPort=`grep ‘Master_Port’ ${slaveFile} | awk -F ‘:’ ‘{print $2}’`
else
    master=””
    masterPort=””
    slaveRunning=0
    secondsBehindMaster=10000  # 不用检测
fi

注：Seconds_Behind_Master，该值作为判断主从延时的指标，那么它又是怎么得到这个值的呢，同时，它为什么又受到很多人 的质疑？

Seconds_Behind_Master 是通过比较 sql_thread 执行的 event 的 timestamp 和 io_thread 复制好的 event 的 timestamp(简写为 ts) 进行比较，而得到的这么一个差值。我们都知道的 relay-log 和主库的 bin-log 里面的内容完全一样，在记录 sql 语句的同时会被记录上当时的 ts，所以比较参考的值来自于 binlog，其实主从没有必要与 NTP 进行同步，也就是说无需保证主从时钟的 一致。你也会发现，其实比较真正是发生在 io_thread 与 sql_thread 之间，而 io_thread 才真正与主库有关联，于是，问题就出来了，当主库 I / O 负载很大或是网络阻塞，io_thread 不能及时复制 binlog（没有中断，也在复制），而 sql_thread 一直都能跟上 io_thread 的脚本，这时 Seconds_Behind_Master 的值是 0，也就是我们认为的无延时，但是，实际上不是，你懂得。这也就是为什 么大家要批判用这个参数来监控数据库是否发生延时不准的原因，但是这个值并不是总是不准，如果当 io_thread 与 master 网络很好的情况下，那么 该值也是很有价值的。

之前，提到 Seconds_Behind_Master 这个参数会有负值出现，我们已经知道该值是 io_thread 的最近跟新的 ts 与 sql_thread 执行到 的 ts 差值，前者始终是大于后者的，唯一的肯能就是某个 event 的 ts 发生了错误，比之前的小了，那么当这种情况发生时，负值出现就成为可能。

28、检测采集 Agent 心跳情况

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-11/136788.htm