dba 之 mysql监控指标和监控方法

监控指标：

1 功能性指标

入门理解：
https://www.cnblogs.com/year12/p/14474011.html
功能类指标

可用性 数据库是否可正常对外提供服务
阻塞 当前是否有阻塞的会话（一个事务对一个数据库的资源加了排它锁）
死锁 当前事务是否产生了死锁 （两个事务相互之间锁住了互相锁住的资源）
    MySQL处理死锁的两种方式
        等待，直到超时。（innodb_lock_wait_timeout=50s）
        发起死锁检测，主动回滚一条事务(会选择占用资源较小的事务回滚)，让其他事务继续执行(innodb_deadlock_detect=on)
    死锁检测
        死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。
    回滚
        检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。
慢查询 实时慢查询监控
主从延迟 数据库主从延迟时间
主从状态 数据库主从复制链路是否正常

常用命令：

查询 启动以来分别执行数量。
show global status like 'Com%';
查询 启动以来总共执行数量
show global status like 'Queries%';
 
QPS=(Queries2-Queries1)/时间间隔秒
show global status where variable_name in ('Queries','uptime');
QPS = (2816-2815) / 4372884-4372860
 
 
 
TPS(每秒钟处理的事务数量)
show global status where Variable_name in ('com_insert','com_delete','com_update','uptime');
TC ≈ com_insert + com_delete + com_update
TPS = (Tc2-Tc1)/(time2-time1)
 
数据库并发数
show global status where Variable_name in ('Threads_running','uptime');
 
数据库连接数
show variables 查询系统设置
show global status 查询系统运行实时状态
 
show global status where Variable_name in ('threads_connected','uptime') ;
show variables like 'max_connections';
报警阀值 ： Threads_connected / max_connections > 0.8
 
Innodb缓存命中率
show global status like 'innodb_buffer_pool_read%';
Innodb_buffer_pool_read_requests : 从缓冲池中读取的次数
innodb_buffer_pool_reads : 表示从物理磁盘读取的次数
（Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads ） / Innodb_buffer_pool_read_requests * 100 %
 
数据库可用性
周期性连接数据库服务器并执行select @@version;
Mysqladmin -u xx -p xx -h xx ping
 
阻塞
version < 5.7
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) ) > 1;
 
version >= 5.7
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) ) > 0;
 
死锁

    show engine innodb status 查看最近一次死锁的信息
    pt-deadlock-logger u=dba,p=xxx,h=127.0.0.1 --create-dest-table --dest u=dba,p=xxx,h=127.0.0.1,D=crn,t=deadlock 死锁信息会存在deadlock

    set global innodb_print_all_deadlocks=on; 死锁产生后查看error_log

 
慢查询

    通过慢查询日志监控（周期性）
    通过 information_schema.`PROCESSLIST` 表实时监控
        select * from information_schema.processlist where time > 10 and command <> 'sleep'

 
监控主从延时

    show slave status
        value ： Seconds_Behind_master
        并不靠谱，建议使用 pt-heartbeat 工具 （百度一下）
    pt-heartbeat --user=xx --password=xxx -h master --create-table --database xxx --update --daemonize --interval =1
    pt-heartbeat --user=xx --password=xxx -h slave --databasecrn --monitor --daemonize --log /tmp/slave_lag.log

 
监控主从状态

    show slave status
        value
            Slave_IO_Running : Yes
            Slave_SQL_Running : Yes

2 性能和高可用;