暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 聊聊数据库：MySQL监控 ~ 性能指标

聊聊数据库：MySQL监控 ~ 性能指标

逸鹏说道 2019-08-29
2068

上篇回顾：SQLServer日记监控 | SQL运维 ~ binlog专栏


4.6.6.MySQL监控指标

  • 4.6.6.1.DB可用性监控

    • 1.是否可连接

    • 2.是否可读写

    • 3.监控连接数

  • 4.6.6.2.DB性能的监控（重要）

    • 1.TPS and QPS

    • 2.计算

    • 2.并发请求数

    • 3.缓存命中率（innodb）

  • 4.6.6.3.DB异常的监控

    • 1.检查innodb的阻塞

    • 2.检查死锁

    • 3.慢查询监控

  • 4.6.6.4.主从相关监控

    • 1.主从状态

    • 2.主从延迟

    • 附录：看看innodblockwaits的本质

4.6.6.MySQL监控指标

这边我们说说 MySQL
的监控：MySQL社区版不提供监控工具，MariaDB有 Monyog
（收费）

系统规模大了基本上都是使用企业级监控，小了都是写个脚本定期查看下各项监控指标

公司以前基本上都是使用 Zabbix
和 Nagios

PS：现在中小企更欢喜小米的 Open-Falcon
（潜力股）

软件使用这块后面有机会再聊，我们今天主要说说MySQL中那些需要知道的监控指标

PS：软件也是根据这些指标来监控的，你完全可以根据这些自己写个轻量级监控程序

数据库监控基本上都是这几方面来进行的：

  1. DB可用性监控

    • 进程存在，能对外服务（能执行SQL）

  2. DB性能的监控

    • QPS、TPS、并发线程数（小于数据库连接数）、缓存命中率

  3. DB异常的监控

    • innodb阻塞和死锁、慢查询

  4. 主从相关监控

    • 链路状态、主从延迟、数据一致性（定期检查）

  5. 服务器的监控

    • CPU、内存、swap分区（内存不够的时候它来顶包）、网络IO、磁盘空间（数据目录和日志目录）

4.6.6.1.DB可用性监控

这个其实只要判断DB能对外服务就说明DB是可用的（三步走，1可连接，2可读写，3连接数）

1.是否可连接

查看数据库是否可以连接自带的工具就可以实现了：（也可以使用 telnet ip port
来测试）

在另一台服务器上运行：mysqladmin-u用户名-p-h服务器ip地址ping
（显示 mysqldisalive
）

如果Win没安装可以安装一下：

其实最好就是通过程序看看能不能connect数据库，能就没问题，不能就over了（你通过IDE也一样，程序的优点在于可自动化）

PS：可以周期性的通过程序建立连接，然后执行一个简单查询 select@@version;
，能得到返回结果就行了

2.是否可读写

一般看能否读写都是简单的查询或者写入（太复杂的容易耗费服务器资源）

  1. 检查是否可读：执行一个简单的查询即可

    • eg: select@@version;

  2. 检查是否可写：创建一个监控表，对表中数据进行update

    • eg：更新最近监控时间

扩：如果启用了主从服务，那么需要额外检查下 read_only
的配置：

PS：检查主从服务器的主数据库 read_only
是否为off（从库一般都是设置只读，如果切换了数据库每改过来就不能写了）

3.监控连接数

主要是两种情况：

  1. 当前连接数一直逼近最大连接数：配置优化 or 提升硬件

  2. 当前连接数短时间爆增：发警告，防止CC之类的安全攻击

    • eg： 当前连接数/最大连接数>80%
       预警一下，DBA可以查看是因为啥

最大连接数：show variables like'max_connections';

当前连接数：showglobalstatus like'Threads_connected';

4.6.6.2.DB性能的监控（重要）

记录性能监控过程中采集到DB的status信息（以后分析趋势会用到）

1.TPS and QPS

QPS
（Queries Per Second）:每秒钟处理的请求数（一般都是查询，但DML、DDL也包括）

PS：贴一下专业的计算公式：QPS=(Queries2-Queries1)/时间间隔

TPS
（Transactions Per Second）:每秒钟处理事务的数量（insert、update、delete）

PS：贴一下专业的计算公式： TPS=(TC2-TC1)/时间间隔
（ TC=Com_insert+Com_delete+Com_update
）

PS：你可以理解为 TPS
是 QPS
的子集，我画个草图表示一下:

2.计算

大体思路：通过前后两次采集系统状态变量，然后套公式计算

PS：QPS一般是测一下查频繁程度，TPS是测一下写的频繁程度

QPS=(Queries2-Queries1)/时间间隔

PS：Queries = showglobalstatus like'Queries';

查询命令： showglobalstatuswherevariable_namein('Queries','uptime');

PS：其实 Queries
是MySQL为我们提供的简便方法，本质还是使用了计数器的求和 Sum(Com_xx)

计数器相关： showglobalstatus like'Com%';

eg：Comselect、Cominsert、Comdelete、Comupdate、Comcreateindex、Comaltertable...

TPS=(TC2-TC1)/时间间隔

PS： TC=Com_insert+Com_delete+Com_update

查询命令： showglobalstatuswherevariable_namein('com_insert','com_delete','com_update','uptime');

带入公式计算就知道TPS了

2.并发请求数

这个大家都知道，并发数越大系统性能越弱（结合CPU*、内存、网络IO来看）

数据库当前并发数量：showglobalstatus like'Threads_running';

PS：当前连接数：showglobalstatus like'Threads_connected';

线上项目的并发数一般远小于同时间数据库线程数量（如果不是，那可能出现了大量阻塞）

PS：当前并发数量（ Threads_running
） << 当前连接数（ Threads_connected
）

区分：并发数：同时执行的会话数量，当前连接数：会话总数（包括sleep的）

3.缓存命中率（innodb）

这个主要是指innodb查询的 缓存命中率
（一般都 >=95%
）

公式：(innodb_buffer_pool_read_requests-innodb_buffer_pool_reads)/innodb_buffer_pool_read_requests

简单解释一下：

  1. innodb_buffer_pool_read_requests
    ：请求读取总次数    （从缓冲池中读取的次数 + 物理磁盘读取次数）

    • PS：这个参数已经包含了 从磁盘读取次数
      （两者差值就说明缓存命中的次数）

  2. innodb_buffer_pool_reads
    ：从物理磁盘读取的次数

PS：查询：showglobalstatus like'innodb_buffer_pool_read%';

4.6.6.3.DB异常的监控

1.检查innodb的阻塞

MySQL5.7以前使用了2张表：information_schema.innodb_lock_waits
and information_schema.innodb_trx

PS：MySQL5.7以后用一张表就行了： sys.innodb_lock_waits
（本质就是上面封装的一个视图）

看个案例：摸拟两个会话同时对一条记录进行修改

查询阻塞时间>30s的相关信息

PS：可以捕获被阻塞的语句，缺没法捕获导致阻塞的语句（已经执行完了）

  1.  -- 查询阻塞时间大于30s的信息
  2. select waiting_pid             as blocked_pid,
  3.        waiting_query           as blocked_sql,
  4.        blocking_pid            as running_pid,
  5.        blocking_query          as running_sql,
  6.        wait_age                as blocked_time,
  7.        sql_kill_blocking_query as info
  8. from sys.innodb_lock_waits
  9. where (unix_timestamp() - unix_timestamp(wait_started)) > 30;

PS：之所以阻塞是因为都对同一条资源占用排它锁

可以这么理解：大家都要排他锁，第一个占了lock，第二个就阻塞了

扩展：MariaDB 5.5.60 或者小于MySQL5.7使用这句SQL：

  1. select b.trx_mysql_thread_id                              as blocked_pid,
  2.        b.trx_query                                        as blocked_sql,
  3.        c.trx_mysql_thread_id                              as running_pid,
  4.        c.trx_query                                        as running_sql,
  5.        (unix_timestamp() - unix_timestamp(c.trx_started)) as blocked_time
  6. from information_schema.innodb_lock_waits a
  7.          join information_schema.innodb_trx b on a.requesting_trx_id = b.trx_id
  8.          join information_schema.innodb_trx c on a.blocking_trx_id = c.trx_id
  9. where (unix_timestamp() - unix_timestamp(c.trx_started)) > 30;

简单总结一下：

查询当前会话的连接号： selectconnection_id();

PS：修改innodb事务锁的超时时间：setglobalinnodb_lock_wait_timeout=60;
（MySQL8默认是50s）

MySQL命令下 kill线程号
可以杀死阻塞线程

可以抓到阻塞线程的线程号，但没法准确的抓到被哪条SQL阻塞的，只能抓取正在执行中的SQL语句

PS：MyISAM的表不能用这种方式检查（现在基本都是innodb，如果历史版本的Table记得改下引擎）

2.检查死锁

当前事物是否产生了死锁（MySQL会主动回滚占用资源比较小的那个事物）

PS：如果回滚资源占用多了，那计算运行半天不就白费了？（时间和资源的浪费）

微端解决：把死锁信息记录到error.log中

PS：因为MySQL会自动处理死锁，所以简单记录一下就行了，然后自己再场景欢迎来排除死锁

setglobalinnodb_print_all_deadlocks=on;
（MySQL8可以使用 persist
持久化配置文件）

PS：查看最近一次死锁相关信息： show engine innodb status

企业级方案：

pt-deadlock-logger u=用户名,p=密码,h=ip地址--create-dest-table--dest u=用户名,p=密码,h=ip地址,D=数据库,t=记录死锁信息的表名

PS：--dest u=用户名,p=密码,h=ip地址,D=数据库,t=表名
~存储死锁信息到哪个数据库

PS：有一个坑点，pt连接的用户必须要有root权限，不然只能建表，但没有死锁记录，建议单独创建一个dba账号用于监控

死锁演示：

死锁捕获：

画个流水线的表格把，不然新手不懂：

看完流水线再看gif演示就简单了（之前讲Python的死锁画了很多图，可以看看）

会话1会话2
begin;x
update workdb.userssetemail='dnt@188.com'whereid=1;
x
xbegin;
xupdate workdb.userssetemail='dnt@taobao.com'whereid=2;
update workdb.userssetemail='dnt@188.com'whereid=2;
x
xupdate workdb.userssetemail='dnt@taobao.com'whereid=1;
3.慢查询监控

两种处理方式：

  1. 定期对慢查询日志分析（上节讲过）

  2. 实时监控 information_schema.processlist
    表

    • eg： select*frominformation_schema.processlistwheretime>60andcommand<>'sleep';

PS：这个 event_scheduler
是系统的后台线程，忽略即可

4.6.6.4.主从相关监控

1.主从状态

数据库主从复制链路是否正常

show slave status;
查看 Slave_IO_Running
和 Slave_SQL_Running

PS：正常都是yes，如果是no可以在 Last_Errno
和 Last_Error
查看错误信息

2.主从延迟

数据库主从延迟时间：

微端方案：show slave status;
查看 Seconds_Behind_Master
（主库binlog时间和从库重新执行过的binlog的时间差）

PS：很多开源工具都是监控这个值，但这个监控不是很准，eg：网络延迟 or 主库执行了一个耗时的事物，binlog还没同步到从库上，这时就不准了

企业级方案：

先说原理：主要就是在主数据库中创建一个表，然后周期性的在主库中插入数据，然后读取从库中的这条数据，并统计下同步完成所耗的时间

主库： pt-heartbeat--user=用户名--password=密码-h master_ip--create-table--database数据库名--update--daemonize--interval=1

从库： pt-heartbeat--user=用户名--password=密码-h slave_ip--database数据库名--monitor--daemonize--log/tmp/salve_tmp.log

参考文章：

  1. 数据库架构--数据库监控
  2. https://blog.csdn.net/xiaochen1999/article/details/80947183
  4. mysql 查询正在执行的事务以及等待锁 常用的sql语句
  5. https://www.cnblogs.com/xiaoleiel/p/8316527.html
  7. 使用pt-heartbeat检测主从复制延迟
  8. https://www.cnblogs.com/xiaoboluo768/p/5147425.html
附录：看看innodblockwaits的本质

验证：show create view sys.innodb_lock_waits;

  1. show create view sys.innodb_lock_waits;
  3. -- MySQL5.7.27
  4. CREATE ALGORITHM = TEMPTABLE DEFINER =`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `sys`.`innodb_lock_waits` AS
  5. select `r`.`trx_wait_started`                               AS `wait_started`,
  6.        timediff(now(), `r`.`trx_wait_started`)              AS `wait_age`,
  7.        timestampdiff(SECOND, `r`.`trx_wait_started`, now()) AS `wait_age_secs`,
  8.        `rl`.`lock_table`                                    AS `locked_table`,
  9.        `rl`.`lock_index`                                    AS `locked_index`,
  10.        `rl`.`lock_type`                                     AS `locked_type`,
  11.        `r`.`trx_id`                                         AS `waiting_trx_id`,
  12.        `r`.`trx_started`                                    AS `waiting_trx_started`,
  13.        timediff(now(), `r`.`trx_started`)                   AS `waiting_trx_age`,
  14.        `r`.`trx_rows_locked`                                AS `waiting_trx_rows_locked`,
  15.        `r`.`trx_rows_modified`                              AS `waiting_trx_rows_modified`,
  16.        `r`.`trx_mysql_thread_id`                            AS `waiting_pid`,
  17.        `sys`.`format_statement`(`r`.`trx_query`)            AS `waiting_query`,
  18.        `rl`.`lock_id`                                       AS `waiting_lock_id`,
  19.        `rl`.`lock_mode`                                     AS `waiting_lock_mode`,
  20.        `b`.`trx_id`                                         AS `blocking_trx_id`,
  21.        `b`.`trx_mysql_thread_id`                            AS `blocking_pid`,
  22.        `sys`.`format_statement`(`b`.`trx_query`)            AS `blocking_query`,
  23.        `bl`.`lock_id`                                       AS `blocking_lock_id`,
  24.        `bl`.`lock_mode`                                     AS `blocking_lock_mode`,
  25.        `b`.`trx_started`                                    AS `blocking_trx_started`,
  26.        timediff(now(), `b`.`trx_started`)                   AS `blocking_trx_age`,
  27.        `b`.`trx_rows_locked`                                AS `blocking_trx_rows_locked`,
  28.        `b`.`trx_rows_modified`                              AS `blocking_trx_rows_modified`,
  29.        concat('KILL QUERY ', `b`.`trx_mysql_thread_id`)     AS `sql_kill_blocking_query`,
  30.        concat('KILL ', `b`.`trx_mysql_thread_id`)           AS `sql_kill_blocking_connection`
  31. from ((((`information_schema`.`innodb_lock_waits` `w` join `information_schema`.`innodb_trx` `b` on ((`b`.`trx_id` = `w`.`blocking_trx_id`))) join `information_schema`.`innodb_trx` `r` on ((`r`.`trx_id` = `w`.`requesting_trx_id`))) join `information_schema`.`innodb_locks` `bl` on ((`bl`.`lock_id` = `w`.`blocking_lock_id`)))
  32.          join `information_schema`.`innodb_locks` `rl` on ((`rl`.`lock_id` = `w`.`requested_lock_id`)))
  33. order by `r`.`trx_wait_started`;
  35. -- MySQL8.0.16（information_schema
  36. CREATE ALGORITHM = TEMPTABLE DEFINER =`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `sys`.`innodb_lock_waits`
  37.     (`wait_started`, `wait_age`, `wait_age_secs`, `locked_table`, `locked_table_schema`, `locked_table_name`,
  38.      `locked_table_partition`, `locked_table_subpartition`, `locked_index`, `locked_type`, `waiting_trx_id`,
  39.      `waiting_trx_started`, `waiting_trx_age`, `waiting_trx_rows_locked`, `waiting_trx_rows_modified`,
  40.      `waiting_pid`, `waiting_query`, `waiting_lock_id`, `waiting_lock_mode`, `blocking_trx_id`, `blocking_pid`,
  41.      `blocking_query`, `blocking_lock_id`, `blocking_lock_mode`, `blocking_trx_started`, `blocking_trx_age`,
  42.      `blocking_trx_rows_locked`, `blocking_trx_rows_modified`, `sql_kill_blocking_query`,
  43.      `sql_kill_blocking_connection`) AS
  44. select `r`.`trx_wait_started`                               AS `wait_started`,
  45.        timediff(now(), `r`.`trx_wait_started`)              AS `wait_age`,
  46.        timestampdiff(SECOND, `r`.`trx_wait_started`, now()) AS `wait_age_secs`,
  47.        concat(`sys`.`quote_identifier`(`rl`.`OBJECT_SCHEMA`), '.',
  48.               `sys`.`quote_identifier`(`rl`.`OBJECT_NAME`)) AS `locked_table`,
  49.        `rl`.`OBJECT_SCHEMA`                                 AS `locked_table_schema`,
  50.        `rl`.`OBJECT_NAME`                                   AS `locked_table_name`,
  51.        `rl`.`PARTITION_NAME`                                AS `locked_table_partition`,
  52.        `rl`.`SUBPARTITION_NAME`                             AS `locked_table_subpartition`,
  53.        `rl`.`INDEX_NAME`                                    AS `locked_index`,
  54.        `rl`.`LOCK_TYPE`                                     AS `locked_type`,
  55.        `r`.`trx_id`                                         AS `waiting_trx_id`,
  56.        `r`.`trx_started`                                    AS `waiting_trx_started`,
  57.        timediff(now(), `r`.`trx_started`)                   AS `waiting_trx_age`,
  58.        `r`.`trx_rows_locked`                                AS `waiting_trx_rows_locked`,
  59.        `r`.`trx_rows_modified`                              AS `waiting_trx_rows_modified`,
  60.        `r`.`trx_mysql_thread_id`                            AS `waiting_pid`,
  61.        `sys`.`format_statement`(`r`.`trx_query`)            AS `waiting_query`,
  62.        `rl`.`ENGINE_LOCK_ID`                                AS `waiting_lock_id`,
  63.        `rl`.`LOCK_MODE`                                     AS `waiting_lock_mode`,
  64.        `b`.`trx_id`                                         AS `blocking_trx_id`,
  65.        `b`.`trx_mysql_thread_id`                            AS `blocking_pid`,
  66.        `sys`.`format_statement`(`b`.`trx_query`)            AS `blocking_query`,
  67.        `bl`.`ENGINE_LOCK_ID`                                AS `blocking_lock_id`,
  68.        `bl`.`LOCK_MODE`                                     AS `blocking_lock_mode`,
  69.        `b`.`trx_started`                                    AS `blocking_trx_started`,
  70.        timediff(now(), `b`.`trx_started`)                   AS `blocking_trx_age`,
  71.        `b`.`trx_rows_locked`                                AS `blocking_trx_rows_locked`,
  72.        `b`.`trx_rows_modified`                              AS `blocking_trx_rows_modified`,
  73.        concat('KILL QUERY ', `b`.`trx_mysql_thread_id`)     AS `sql_kill_blocking_query`,
  74.        concat('KILL ', `b`.`trx_mysql_thread_id`)           AS `sql_kill_blocking_connection`
  75. from ((((`performance_schema`.`data_lock_waits` `w` join `information_schema`.`INNODB_TRX` `b` on ((
  76.         convert(`b`.`trx_id` using utf8mb4) =
  77.         cast(`w`.`BLOCKING_ENGINE_TRANSACTION_ID` as char charset utf8mb4)))) join `information_schema`.`INNODB_TRX` `r` on ((
  78.         convert(`r`.`trx_id` using utf8mb4) =
  79.         cast(`w`.`REQUESTING_ENGINE_TRANSACTION_ID` as char charset utf8mb4)))) join `performance_schema`.`data_locks` `bl` on ((`bl`.`ENGINE_LOCK_ID` = `w`.`BLOCKING_ENGINE_LOCK_ID`)))
  80.          join `performance_schema`.`data_locks` `rl` on ((`rl`.`ENGINE_LOCK_ID` = `w`.`REQUESTING_ENGINE_LOCK_ID`)))
  81. order by `r`.`trx_wait_started`;

(over)

mysql
文章转载自逸鹏说道，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论