MySQL -- 故障诊断

栏目: 数据库 · 发布时间: 5年前

内容简介：并发线程达到如果打开所有的

SELECT 1 只能说明数据库 进程还在 ，但不能说明数据库没有问题 

-- innodb_thread_concurrency表示并发线程数量
mysql> SHOW VARIABLES LIKE '%innodb_thread_concurrency%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 16    |
+---------------------------+-------+

表初始化 

-- innodb_thread_concurrency默认为0，表示不限制并发线程数量，建议设置范围64~128
SET GLOBAL innodb_thread_concurrency=3;

CREATE TABLE `t` (
    `id` INT(11) NOT NULL,
    `c` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO t VALUES (1,1);

操作序列

session A session B session C session D
SELECT SLEEP(100) FROM t; SELECT SLEEP(100) FROM t; SELECT SLEEP(100) FROM t;
SELECT 1;
(Query OK)
SELECT * FROM t;
(Blocked)

并发线程达到 上限 3后，InnoDB在接收新请求时，会进入 等待 状态

并发连接 VS 并发查询

  1. SHOW PROCESSLIST 可能会看到几千个连接，指的是 并发连接 ；而 当前正在执行 的语句，才是 并发查询
  2. 并发连接达到几千影响并不大，无非就是多占用一些内存，
    • 并发查询 太大才是 CPU杀手 ，因此才需要设置 innodb_thread_concurrency
  3. 在线程进入锁等待后，并发线程的计数将会减一
    • 等待 行锁 或 间隙锁 的线程 不属于并发线程 ，因为这些线程 不会再消耗CPU
  4. SELECT SLEEP(100) FROM t 是在真正地执行查询，所以还是要算并发线程

查询判断

  1. 在系统库（ mysql ）里建一个表，命名为 health_check ，里面只放一行数据，然后 定期查询
    • SELECT * FROM mysql.health_check
    • 可以检测出由于 并发线程过多 而导致数据库不可用的情况
  2. 但该方法无法检测 磁盘空间满 的情况
    • 更新事务需要写 binlog ，而一旦 binlog 所在 磁盘的空间占用率 达到率100%
    • 所有的 更新语句 和 事务的commit语句 都会被 阻塞
    • 但此时系统还是可以 正常地读取数据 的

更新判断

  1. UPDATE mysql.health_check SET t_modified=now()
  2. 主库和从库都需要进行 节点的可用性检测 ，从库的可用性检测也是需要写 binlog 的
  3. 一般会把A和B的主从关系设计为 Master-Master 结构，在从库B上执行的检测命令，也会发回主库A
  4. 如果主库A和从库B都使用 相同的更新命令 ，可能会出现 行冲突 （无法区分谁更新的），导致 主从同步停止
    • 为了主从之间的更新不产生冲突，在 mysql.health_check 上存入 多行 数据， server_id 为主键
    • MySQL规定主库和从库的 server_id 必须 不同 ，从而保证主从各自的检测命令不会发生冲突 
CREATE TABLE `health_check` (
    `id` INT(11) NOT NULL,
    `t_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 检测命令
INSERT INTO mysql.health_check (id, t_modified) VALUES (@@server_id, now()) ON DUPLICATE KEY UPDATE t_modified=now();

判定慢

  1. 所有的检测都需要一个 超时时间N ，执行一个 UPDATE 语句，如果超过N秒后不返回，会认为系统不可用
  2. 假设一个日志盘的 IO利用率 已经是100%，整个系统响应非常慢，已经准备做主从切换了
    • IO利用率为100%，表示系统的IO在正常工作，每个请求都是 有机会 得到IO资源的
    • 而检测使用的 UPDATE 命令，需要的资源是很少的
      • 可能在N秒内返回给检测系统，检测系统 误认为 系统是正常的
  3. 表现：业务系统上正常的 SQL 执行很慢，但DBA在HA系统上看到的却是系统处于可用状态
  4. 根本原因：都是基于 外部检测 （定时轮询），天然存在 随机性 的问题

内部统计

redolog 

mysql> SELECT * FROM performance_schema.file_summary_by_event_name WHERE EVENT_NAME='wait/io/file/innodb/innodb_log_file'\G;
*************************** 1. row ***************************
               EVENT_NAME: wait/io/file/innodb/innodb_log_file
               COUNT_STAR: 233
           SUM_TIMER_WAIT: 132552328013
           MIN_TIMER_WAIT: 1665048
           AVG_TIMER_WAIT: 568893997
           MAX_TIMER_WAIT: 86702766780
               COUNT_READ: 8
           SUM_TIMER_READ: 87079515796
           MIN_TIMER_READ: 2300200
           AVG_TIMER_READ: 10884939289
           MAX_TIMER_READ: 86702766780
 SUM_NUMBER_OF_BYTES_READ: 70656
              COUNT_WRITE: 114
          SUM_TIMER_WRITE: 8780811305
          MIN_TIMER_WRITE: 10705576
          AVG_TIMER_WRITE: 77024423
          MAX_TIMER_WRITE: 679922054
SUM_NUMBER_OF_BYTES_WRITE: 92160
               COUNT_MISC: 111
           SUM_TIMER_MISC: 36692000912
           MIN_TIMER_MISC: 1665048
           AVG_TIMER_MISC: 330558403
           MAX_TIMER_MISC: 18323439204
  1. EVENT_NAME ：统计的类型，这里为 redolog
  2. 第1组： COUNT_STAR ~ MAX_TIMER_WAIT ，所有IO类型的统计，单位为皮秒， 1 PS = 10^-12 S
  3. 第2组： COUNT_READ ~ SUM_NUMBER_OF_BYTES_READ ，读操作的统计
    • SUM_NUMBER_OF_BYTES_READ ：总共从 redolog 读取了多少 字节
  4. 第3组： COUNT_WRITE ~ SUM_NUMBER_OF_BYTES_WRITE ，写操作的统计
  5. 第4组： COUNT_MISC ~ MAX_TIMER_MISC ，其它类型数据的统计
    • 在 redolog 里，可以理解为对 fsync 的统计

binlog 

mysql> SELECT * FROM performance_schema.file_summary_by_event_name WHERE EVENT_NAME='wait/io/file/sql/binlog'\G;
*************************** 1. row ***************************
               EVENT_NAME: wait/io/file/sql/binlog
               COUNT_STAR: 27
           SUM_TIMER_WAIT: 3003083244
           MIN_TIMER_WAIT: 0
           AVG_TIMER_WAIT: 111225058
           MAX_TIMER_WAIT: 1100158206
               COUNT_READ: 4
           SUM_TIMER_READ: 1283933427
           MIN_TIMER_READ: 3404667
           AVG_TIMER_READ: 320983264
           MAX_TIMER_READ: 1100158206
 SUM_NUMBER_OF_BYTES_READ: 10248
              COUNT_WRITE: 5
          SUM_TIMER_WRITE: 597349326
          MIN_TIMER_WRITE: 18148578
          AVG_TIMER_WRITE: 119469791
          MAX_TIMER_WRITE: 421662276
SUM_NUMBER_OF_BYTES_WRITE: 896
               COUNT_MISC: 18
           SUM_TIMER_MISC: 1121800491
           MIN_TIMER_MISC: 0
           AVG_TIMER_MISC: 62322064
           MAX_TIMER_MISC: 188882778

性能损耗

如果打开所有的 performance_schema ，性能大概会下降 10% 左右，建议只打开所需要的项 

UPDATE setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME LIKE '%wait/io/file/innodb/innodb_log_file%';

故障诊断

假设已经开启了 redolog 和 binlog 的统计信息功能，可以通过 MAX_TIMER 来判断数据库是否有问题 

-- 单次IO超过200ms
SELECT EVENT_NAME,MAX_TIMER_WAIT FROM performance_schema.file_summary_by_event_name WHERE EVENT_NAME IN ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') AND MAX_TIMER_WAIT>200*1000000000;

参考资料

《MySQL实战45讲》

转载请注明出处：http://zhongmingmao.me/2019/03/03/mysql-trouble-shooting/

访问原文「MySQL -- 故障诊断」获取最佳阅读体验并参与讨论


以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍
高性能MySQL

高性能MySQL

施瓦茨 (Baron Schwartz)、扎伊采夫 (Peter Zaitsev)、特卡琴科 (Vadim Tkachenko) / 宁海元、周振兴、彭立勋、翟卫祥,刘辉 / 电子工业出版社 / 2013-5-1 / 128.00元

《高性能mysql(第3版)》是mysql 领域的经典之作，拥有广泛的影响力。第3 版更新了大量的内容，不但涵盖了最新mysql 5.5版本的新特性，也讲述了关于固态盘、高可扩展性设计和云计算环境下的数据库相关的新内容，原有的基准测试和性能优化部分也做了大量的扩展和补充。全书共分为16 章和6 个附录，内容涵盖mysql 架构和历史，基准测试和性能剖析，数据库软硬件性能优化，复制、备份和恢复，高可......一起来看看 《高性能MySQL》 这本书的介绍吧!

码农工具
JSON 在线解析
JSON 在线解析

在线 JSON 格式化工具

XML、JSON 在线转换
XML、JSON 在线转换

在线XML、JSON转换工具

UNIX 时间戳转换
UNIX 时间戳转换

UNIX 时间戳转换

  • New
  • 文章
  • 话题
  • 教程
    • 关注 码农网 公众号
    版权所有，保留一切权利！© 2018-2024 码农网 粤ICP备17054400号-3