MYSQL 常用监控指标及监控方法

发表于 更新于 分类于 阅读次数：

【MySQL】常用监控指标及监控方法

       对之前生产中使用过的 MySQL 数据库监控指标做个小结。
指标分类 指标名称 指标说明
性能类指标 QPS 数据库每秒处理的请求数量
TPS 数据库每秒处理的事务数量
并发数 数据库实例当前并行处理的会话数量
连接数 连接到数据库会话的数量
缓存命中率 查询命中缓存的比例
高可用指标 可用性 数据库是否可以正常对外服务
阻塞 当前阻塞的会话数
慢查询 慢查询情况
主从延迟 主从延迟时间
主从状态 主从链路是否正常
死锁 查看死锁信息

【QPS 指标】

1
show global status where variable\_name in ('Queries', 'uptime');

QPS = (Queries2 -Queries1) / (uptime2 - uptime1)

【TPS 指标】

1
show global status where variable\_name in ('com\_insert' , 'com\_delete' , 'com\_update', 'uptime');

事务数 TC ≈’com_insert’ , ‘com_delete’ , ‘com_update’
TPS  ≈ (TC2 -TC1) / (uptime2 - uptime1)

【并发数】

1
show global status like '**Threads\_running**';

【连接数】

  • 当前连接数 
    1
    show global status like 'Threads\_connected';
  • 最大连接数 
    1
    show global status like 'max\_connections';
    生产中配置报警阈值：Threads_connected /  max_connections > 0.8

【缓存命中率】

  • innodb 缓冲池查询总数 
    1
    show global status like 'innodb\_buffer\_pool\_read\_requests';
  • innodb 从磁盘查询数 
    1
    show global status like 'innodb\_buffer\_pool\_reads';
    生产中配置报警阈值：(innodb_buffer_pool_read_requests - innodb_buffer_pool_reads) /innodb_buffer_pool_read_requests > 0.95

【可用性】

  • 方法 1：周期性连接数据库并执行  select @@version;

  • 方法 2：mysqladmin -u 数据库用户名 -p 数据库密码 -h 数据库实例 IP ping

【阻塞】

  • MySQL5.7 之前 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    select b.trx\_mysql\_thread\_id as '被阻塞线程',  
    b.trx\_query as '被阻塞SQL',  
    c.trx\_mysql\_thread\_id as '阻塞线程',  
    c.trx\_query as '阻塞SQL',  
    (unix\_timestamp()-unix\_timestamp(c.trx\_started)) as '阻塞时间'  
    from information\_schema.innodb\_lock\_waits a  
    join information\_schema.innodb\_trx b on a.requesting\_trx\_id=b.trx\_id  
    join information\_schema.innodb\_trx c on a.blocking\_trx\_id=c.trx.id  
    where(unix\_timestamp()-unix\_timestamp(c.trx\_started))>阻塞秒数
  • MySQL5.7 及之后
    为方便查询阻塞指标，MySQL 将 2 张表 join 构造了一个 view sys.innodb_lock_waits，查询语句得以大大简化。
    1
    2
    3
    4
    5
    6
    7
    8
    select waiting\_pid as '被阻塞线程',  
    waiting\_query as '被阻塞SQL',  
    blocking\_pid as '阻塞线程',  
    blocking\_query as '阻塞SQL',  
    wait\_age as '阻塞时间',  
    sql\_kill\_blocking\_query as '建议操作'  
    from sys.innodb\_lock\_waits  
    where(unix\_timestamp()-unix\_timestamp(wait\_started))>阻塞秒数

【慢查询】

  • 方法 1：开启慢查询日志。

    my.inf

    1
    2
    3
    4
    slow\_query\_log=on  
    slow\_query\_log\_file=存放目录  
    long\_query\_time=0.1秒  
    log\_queries\_not\_using\_indexes=on

    注：只对新建连接生效，实时生效使用命令 set global 上述配置项。

  • 方法 2：

    1
    select \* from information\_schema.'processlist';

【主从延迟】

  • 方法 1：
    1
    show slave status;

问题：
该方法是基于 relaylog 的时间与 master 的时间差值，并不太准，例如大事务时，主从延时已发生，但 relaylog 还未生成。

  • 方法 2：使用 Percona 的 pt-heartbeat 工具

Master 后台周期写入：

1
pt-heartbeat --user=Master用户名 --password=Master密码 --h MasterIP --create-table --database 测试库名 --updatte --daemonize --interval=1

–create-table   在 Master 上创建心跳监控表 heartbeat，通过更新该表知道主从延迟的差距。
–daemonize   后台执行。
–interval=1 默认 1 秒执行一次。

Slave 后台周期同步读取：

1
pt-heartbeat --user=Slave用户名 --password=Slave密码 --h SlaveIP --database 库名 --monitor --daemonize --log /slave\_lag.log

–monitor 参数是持续监测并输出结果

【主从状态】

1
show slave status;

【死锁】

  • 方法 1：查看最近一次死锁信息：

    1
    show engine innodb status;

  • 方法 2：使用 Percona 的 pt-deadlock-logger 工具

  1. 打开死锁打印全局开关 
    1
    set global innodb\_print\_all\_deadlocks=on;
  2. 使用 pt-deadlock-logger 工具
    监控到的死锁结果可以输出到文件、指定表、或者界面打印。
1
pt-deadlock-logger h=数据库IP,u=数据库用户名,p=数据库密码

输出结果非常详尽：

server: 数据库服务器地址，即死锁产生的数据库主机
ts: 检测到死锁的时间戳
thread: 产生死锁的线程 id，这个 id 和 show processlist 里面的线程 id 是一致的
txn_id:innodb 的事务 ID
txd_time: 死锁检查到前，事务执行时间
user: 执行 transcation 的用户名
hostname: 客户端主机名
ip: 客户端 ip
db: 发生死锁的 DB 名
tbl: 死锁发生的表名
idx: 产生死锁的索引名 (在上面这个 demo 里面， 我们直接走的主键，加的记录锁)
lock_type: 锁的类型 (记录锁，gap 锁，next-key 锁)
lock_mode: 锁模式 (S,X)
wait_hold: 是否等着锁释放，一般死锁都是两个 wait
victim: 该会话是否做了牺牲，终止了执行
query: 造成死锁的 SQL 语句