不奢望岁月静好 只希望点滴积累

0%

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

场景

1
假设有一张市民表、需要查询 城市是 杭州 的所有人的名字、以name排序、返回前1000个
1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

#查询sql
select city,name,age from t where city='杭州' order by name limit 1000  ;

全字段排序.png

image.png

全字段排序

1
2
3
4
5
6
7
8
9
10
11
12
using filesort 表示需要排序、mysql会给每个线程分配一块内存用于排序、sort_buffer

sql执行流程:
1. 初始化sort_buffer、确定放入name、city、age字段
2. 从索引city找到第一个满足 city='杭州' 条件的主键id、也就是图中的ID_X
3. 到主键ID索引取出整行、取name、city、age的值 放入sort_buffer、
4. 从索引city找到下一个满足条件的记录、
5. 重复3、4直到无满足条件的记录、对应的主单id就是图中的ID_Y
6. 对sort_buffer中的数据、按name做快排
7. 按排序结果取前1000行给client

按name排序可以在内存完成、或者靠外部排序，取决于排序所需内存和sort_buffer_size
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算 Innodb_rows_read 差值 */
select @b-@a;

其中number_of_files 表示的是使用的临时文件的个数、需要外部临时文件排序时、mysql会分散到多个文件中进行排序、然后进行归并排序、
examined_rows 表示参与排序的行数

rowid排序

1
2
3
4
5
6
7
8
9
10
单行数据较大时(超过 max_length_for_sort_data)、sort_buffer中存放的字段过多、内存能同时放下的行较少、需要很多临时文件、排序性能会特别差、innodb会采用另外一种排序、只在sort_buffer放入排序字段和id、然后流程变为:
1. 初始化sort_buffer、放入name和id
2. 从索引city找到第一个满足 city='杭州' 条件的主键id、也就是图中的ID_X
3. 回到主键索引取出整行、取id、name这两个字段、放入sort_buffer
4. 从索引city取下一条满足条件的记录、
5. 重复3、4直到无满足条件的记录
6. 对sort_buffer中的记录按照name排序、取前1000行
7. 回id索引取出所有满足条件的记录的name、age、city返回client

SET max_length_for_sort_data = 16;

结论

1
对于innodb表、会优先选择内存排序、内存不足时、会优先选择全字段排序

那么对于内存表呢 ？

1
回表只是简单的根据行的位置得到数据、所以排序行会越小越好、优先选择rowid排序

tmp_table_size 内存临时表的大小、默认16M

Q: 假设表中已有city name的联合索引、sql如下: 

1
mysql> select * from t where city in ('杭州'," 苏州 ") order by name limit 100;

这个sql执行需要排序吗
A: 

1
2
对于不同city、name不是有序的、需要排序
可以将结果拿到业务层去排

总结

1
2
3
4
tmp_table_size 会影响是否使用内存临时表(否则使用磁盘临时表)
sort_buffer_size 使用使用内存完成排序(否则需要文件排序)
max_length_for_sort_data 会影响排序策略(全字段/rowid)
内存排序时、>5.6版本会使用优先队列排序

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

基本概念

脏页: 内存页跟磁盘数据页内容不一致的时候、称这个内存页为 脏页
干净页: 内存数据写入磁盘后、内存和磁盘上的数据页的内容一致、称为 干净页

干净页和脏页都在内存

1
不难想象、平时操作很快的sql、其实就是在写内存和日志、而mysql抖动的那一瞬间、可能就是在刷脏页

什么场景下会引发数据库的flush过程呢 ？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
1. innodb的redo log满了: 此时系统会停止所有更新、把checkpoint推进、redo log留出空间
   此时需要把原write pos和checkpoint之间的所有脏页都flush到磁盘上
2. 系统内存不足: 当需要新的内存页、而内存不够用的时候、就需要淘汰一些数据页、空出内存给别的数据页使用
   若淘汰的是脏页、则需要先将内容flush到磁盘
   为什么不直接淘汰内存页 ？ 
   若刷脏页、一定会写磁盘、就保证了每个数据页有两种状态
   1) 在内存里存在、内存肯定是正确的结果、直接返回
   2) 在内存里无数据、在磁盘数据文件上肯定是正确的结果、直接读入内存后返回、这样的效率最高 
3. mysql系统空闲的时候
4. mysql正常关闭的时候、mysql会把内存的脏页全部flush到磁盘、下次启动的时候、直接从磁盘读取、启动很快

其中：1、2会影响性能
   1. 会停止所有更新、性能会跌为0、应该尽可能的避免
   2. 是内存不够用、需要将脏页写到磁盘、其实是常态、innodb使用buffer pool来管理内存、缓存池中的内存页有3种状态
      1) 暂未使用的 2) 已用&干净页 3)已用&脏页
   innodb的策略是尽量使用内存、对一个长时间运行的db来说、未被使用的页面很少
   当需要读入的数据页不在内存的时候、就必须到缓存池申请一个数据页、只能把最久不使用的数据页从内存淘汰
   若淘汰的是干净页、直接释放出来复用、若淘汰的是脏页、需要先flush到磁盘

innodb刷脏页的控制策略

1
2
3
4
5
6
7
8
9
10
11
12
13
innodb_io_capacity 告诉innodb所在主机的io能力、innodb才知道全力刷脏页时、可以刷多块、
    建议设置为磁盘的iops、测试:  fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 

innodb并不是一直全力脏页、毕竟还要服务用户请求、那么是如何控制的呢 ？
1. 脏页比例(innodb_max_dirty_pages_pct)  2.redo log写盘速度
要合理设置io能力、关注脏页比例、当前脏页比例是通过 .._dirty/.._total 计算的
innodb_buffer_pool_pages_dirty 当前脏页数量
innodb_buffer_pool_pages_total 总的页数量
 
innodb刷页策略: innodb_flush_neighbors 为1: 代表若刷脏页时、临近页也是脏页、则一并刷到磁盘
为0、则只刷本页、
1的策略在机械硬盘时期比较有用、可以减少很多随机io、现在iops都不是问题、设置0即可、减少sql响应时间
mysql8.0 默认为0

Q: 访问某条记录时、mysql是如何知道记录是否在内存中 ？
A:

每个数据页都是有编号的、到内存中查看对应页的编号、若不在内存、去磁盘查找即可

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

  1. 可能在等待锁

    1
    2
    3
    4
    show process list;
    如果看到 State列为: waiting for table metata lock; 则证明在等待MDL表锁

    select blocking_pid from sys.schema_table_lock_waits 可以找到阻塞的pid

  2. 可能在等待flush

    1
    State列为: waiting for table flush

    Mysql对表的flush一般有两种方式

    1
    2
    1. flush tables t with read lock. - 只关闭表t
    2. flush tables with read lock. - 关闭所有表

    正常情况下、这两个操作都很快、除非被阻塞…

  3. 等行锁

    1
    2
    3
    4
    mysql> select * from t where id=1 lock in share mode; 

    >5.7 版本可以通过看到被哪个sql阻塞了
    mysql> select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G

  4. sql慢

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

一主多从基本架构.png
说明: 虚箭头表示主备关系、从库B、C、D指向主库A

那么、当主库A发生故障时、A’会成为新的主库、具体怎么切换的呢 ?

一、基于位点的主备复制
当需要把节点B设置为A’的从库时、需要执行change master命令

1
2
3
4
5
6
7
change master to
master_host=$host_name
master_port=$port
master_user=$user_name
master_password=$password
master_log_file=$master_log_name
master_log_pos=$master_log_pos

其中, master_log_file和master_log_pos表示要从从库的master_log_name文件的master_log_pos位置开始同步, 即: 主库对应文件名和日志偏移量

  1. 如何获取同步位点(偏移量)呢 ?
    方法一：
    1
    2
    3
    4
    5
    6
    1. 等待新主库`A'` 把中转日志 relay log全部同步完成
    2. 在`A'`上执行 show master status命令、得到`A'`上最新的File和Position
    3. 取原主库A故障的时刻T
    4. 用mysqlbinlog工具解析`A'`的File、得到T时刻的位点

    mysqlbinlog File --stop-datatime=T --start-datetime=T， 如下图所示 end_log_pos的值
    image.png
    但这样得到的值并不精准、假设 T时刻、主库A完成insert操作、插入行R、将binlog传给了A’ 和 B、传完的瞬间A故障, 此时系统状态为:
    1
    2
    3
    1. 从库B已经同步了binlog、R已存在
    2. 在新的主库`A'`, R也存在、且是写在123这个位置后的、
    3. 在从库B上执行 change master时、指向`A'`的123位置、R的binlog再次同步到从库B、造成主键冲突.
    通常情况下会主动跳过错误, 有两种方式可以主动跳过:
  2. 主动跳过一个事务
    1
    2
    set global sql_slave_skip_counter=1;
    start slave;
  3. 跳过指定类型的错误
    1
    2
    3
    1062 insert唯一键冲突
    1032 删除数据时、找不到行
    slave_skip_errors='1032,1062'; 会主动跳过这两类的错误

方法二、GTID
通过sql_slave_skip_counter跳过事务和slave_skip_errors忽略错误的方法、虽然最终可以建立新的主备关系、但操作复杂、容易出错、Mysql5.6引入了GTID(Global Transaction Identifier). 格式:GTID=server_uuid:gno
其中:

1
2
server_uuid 是实例第一次启动时生成的, 全局唯一
gno是一个整数、初始值是1, 每次提交事务时分配给这个事务、并+1

官方定义: GTID=source_id:transaction_id

GTID模式的启动很简单、只需要启动是增加参数:
gtid_mode=on 和 enforce_gtid_consistency=on 即可.

GTID模式下、每个事务都会跟一个GTID对应、有两种生成方式, 具体使用哪种取决于 session 变量和 gtid_next 的值

1
2
3
4
5
6
7
1. 若 gtid_next=automatic, 代表使用默认值、Mysql会把server_uuid:gno 分配给它
   a. 记录binlog时、先记录一行set
       @@session.gtid_mext = 'server_uuid.gno'
   b. 把这个gtid加入到本实例的gtid集合 
2. 若gtid是固定的值、eg. 通过set gtid_next='current_gtid'指定、会有两种可能性:
   a. 若current_gtid已存在实例的gtid集合、接下来的事务会被系统忽略
   b. 若不再、就将该值分配给要执行的事务、系统不需要生成、gno也不用+1

这样、MySQL维护了一个GTID集合、用来对应实例执行过的所有事务

当出现主键冲突时、可以通过GTID加入的方式、跳过事务

1
2
3
4
5
set gtid_next='conflict gtid'; 
begin;
commit; // 通过提交一个空事务、将gtid加入到实例X的GTID集合
set gtid_next=automatic; // 恢复GTID自动分配模式
start slave; // 启动从库

二、基于GTID的主备切换

1
2
3
4
5
6
change master to
master_host=$host_name
master_port=$port
master_user=$user_name
master_password=$password
master_auto_position=1 // 表示主备关系使用的是gtid协议

主备切换逻辑:

1
2
3
4
5
6
1. 实例B指向主库`A'`, 基于主备协议建立连接
2. 实例B把 set_b 发送给主库 `A'`
3. 实例`A'`算出 set_a 与 set_b的差集、即: 所有存在于 set_a、但不存在于 set_b 的gtid集合、判断`A'`本地是否包含了这个差集需要的所有binlog事务
   a. 若不包含、表示 `A'` 已经把实例B需要的binlog删除掉、直接返回错误
   b. 若包含、`A'`从自己的binlog找到第一个不在 set_b 的事务、发给B
4. 从该事务开始、往后读文件、按顺序读取binlog发送给B执行.

其实、这里边包含了一个设计思想: 在基于GTID的主备关系里、系统认为只要建立主备关系、就必须要保证主库发给备库的日志是完整的、若实例B需要的日志已经不存在、A'就拒绝发送给B

这跟基于位点的主备协议不同. 基于位点的协议、是由备库决定的、备库指定哪个位点、主库就发送哪个位点、不做日志的完整性判断. GTID是备库自动判断的.

思考:

若在GTID模式下设置主从关系的时候、从库执行start slave命令后、主库发现需要的binlog已经被删除掉了、导致主备创建不成功. 怎么处理呢 ?

1
2
3
4
1. 若业务允许主从不一致、可以在主库上执行 `show global variables like gtid_purged`、得到主库已经删除的gtid集合、假设是 gtid_purged1, 然后先在从库执行 reset master, 再执行 set global gtid_purged=gtid_purged1; 最后执行 start slave, 就会从现存binlog开始同步.
2. 若需要主从数据一致的话、最好还是通过重新搭建从库来做
3. 若其它从库有全量binlog的话、可以把新的从库先接到全量binlog的从库、追上日志以后、若有需要、再切回主库
4. 若binlog有备份、可以先在从库应用缺失的binlog、然后再执行start slave.

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

若备库执行日志的速度低于主库生成日志的速度、备库有可能很久都追不上主库
MySQL5.6版本之前、MySQL只支持单线程复制、主库并发高、TPS较高时、可能会出现主备延迟
5.6版本之后、sql_thread不会直接更新数据、而是交给 coordinator 来中转日志和分发事务、由worker进程来更新数据. worker的线程数由 slave_parallel_works 决定.
一般32核机器设置为 8~16、备库还要提供查询、不能把CPU都吃光了.

思考:

  1. 事务能不能轮询分发给各个worker ?
    不可以. 事务分发给worker以后、不同worker就独立执行了、但由于CPU的调度策略、第二个事务可能会比第一个先执行、此时、若两个事务更新的是同一行数据、就会导致主备不一致.

  2. 同一个事务的多个更新语句、能不能分发给多个worker执行 ?
    不可以. 若Trx1 更新了t1和t2 两个表的各一行数据、若分发到不同的worker执行、虽然备库最终结果是一致的、但 t1更新完成的瞬间、会出现不一致, 破坏了事务的隔离性

所以: 分发器 coordinator 需要满足:

  1. 不能造成覆盖更新、更新同一行数据的两个事务必须分发到同一个worker
  2. 同一个事务必须分发到同一个worker.

主备流程图.png

不同版本的并行复制策略:

  1. 5.5版本
    官方5.5版本不支持并行复制.
    1) 按表分发. 每个worker线程维护一个hash表、用来保存当前正在这个worker上执行的事务所涉及的表. 有事务分配给该worker时、涉及的表会加入到对应hash表、worker执行完成、从hash表移除. 若与多个worker冲突、coordinator进入等待.
    事务分发时、与worker冲突情况:
    a. 跟所有worker都不冲突、coordinator直接分发给最空闲的worker线程
    b. 跟多于一个worker冲突、coordinator进入等待、直到和该事务冲突的worker进程只有1个
    c. 若只跟一个worker冲突、分配给存在冲突关系的worker.
    按表分发、在多表负载均匀的场景下效果不错、但若碰到热点表、比如: 所有的更新事务都涉及同一张表时、所有事务都会分发到同一个worker、就变成单线程了.

    2) 按行分发. 若两个事务没有更新相同行、可以在备库并行执行、要求binlog格式必须是row.
    此时判断冲突规则就是 库名 + 表名 + 唯一键值(还要考虑唯一索引)
    相比于按表分发、按行分发在决定线程分发的时候、需要消耗更多的计算资源.

    按表分发和按行分发都有约束:

    1. binlog格式必须是Row
    2. 表必须要有主键
    3. 不能有外键、有外键时、级联更新的行不会记录binlog、这样冲突检测就不准确.
      按行分发有个问题: 大事务耗费CPU和内存.
      解决: 会设置一个阈值、若单事务超过设置行阈值(eg. 单事务更新行数超过10w)、就退化为单线程模式: 1. coordinator暂时hold住事务、2. 等所有worker变成空队列、coordinator直接执行事务 3. 恢复并行模式

  2. 5.6按库并行
    原理同上、并行效果取决于压力模型. 若主库上有多个DB、并且多个DB的压力均衡、使用库级别分发效果较好. 相对表级别和行级别优势:
    1) 构造hash值很快、只需要库名、且一个实例上DB数量不会过多
    2) 不要求binlog格式为row、statement也可以拿到库名.
    但: 若主库上的所有表都放在同一个DB里、这个策略就没用了、若不同DB热点不同、也起不到并行效果.

  3. MariaDB的并行复制策略
    利用的是组提交的特性.
    1) 能够在同一组里提交的事务、一定不会修改同一行
    2) 主库上可以并行执行的事务、备库上也一定可以并行执行

  4. 在一组提交的事务、有一个相同的commit_id、下一组就是 commit_id + 1

  5. commit_id直接写入binlog

  6. 传到备库应用时、相同commit_id的事务分发到多个worker执行

  7. 一组全部执行完、coordinator再取下一组数据.
    可以模拟主库的并行模式. 但: 并未完全实现模拟主库并发度这个目标, 在主库上、一组事务commit时、下一组事务是同时处于执行中的. 而备库需要第一组事务执行完、第二组才能开始执行、这样系统的吞吐量就不够、另外、容易被大事务拖后腿.
    假设 trx1 / trx2 / trx3为一组事务、trx2为大事务、这样、trx1/trx3执行完后也必须等待trx2执行完成、下一组才能开始执行、这段时间只有一个worker线程在工作、是对资源的浪费.

  8. MySQL5.7的并行复制策略
    MySQL5.7提供了 slave-parallel-type 控制并行复制策略
    slave-parallel-type=database; 表示使用mysql5.6的按库并行复制
    slave-parallel-type=logical_clock; 表示使用类似mariadb的策略、但做了改进

思考: 同时处于执行状态的所有事务、是不是可以并行 ?
不能. 可能有由于锁冲突而处于锁等待状态的事务、若这些事务在备库上分配到不同的worker、会出现主备不一致. 所以、MariaDB的核心是、处于commit状态的事务并行处理.

而实际上: 处于redo log prepare阶段的事务、都已经通过了锁冲突检验、
所以, Mysql5.7的并行复制策略思想是:

1) 同时处于prepare状态的事务、在备库可以并行执行
2) 处于prepare状态的事务、与处于commit状态的事务之间、在备库执行时、也是可以并行的.
binlog_group_commit_sync_delay表示延迟多少微秒后调用fsync
binlog_group_commit_sync_no_Delay_count表示累积多少次后才调用fsync
这两个参数可以用于故意拉长binlog从write到fsync的时间、减少binlog的写盘次数、在5.7的复制策略里、可以用来制造更多处于prepare阶段的事务、增加备库并行度

  1. Mysql5.7.22并行复制策略
  2. 4月发布的5.7.22版本、增加一个机遇writeset的并行复制
    binlog-transaction-dependency-tracking控制是否使用新的策略
    1) commit_order、根据同时进入prepare和commit来判断是否可以并行的策略
    2) writeset、表示对于事务涉及更新的每一行计算hash值、组成writeset、若两个事务没有操作相同行、就可以并行复制
    3) writeset_session、在主库上同一个线程先后执行的两个事务、在备库执行时、要保证相同的先后顺序、hash值是通过 db+table+index+value计算的、类似5.5按行复制
    但: 1. writeset是在主库生成后直接写入binlog的、在备库执行时、不需要解析binlog、节省计算量
  3. 不需要把整个事务的binlog都扫一遍、才决定分发到哪个worker、更省内存
  4. 备库的分发策略不依赖binlog内容、binlog格式可以不局限于row
    通用性更有保证.

思考: 假设5.7.22版本的主库、单线程插入很多数据、过了3个小时、想给这个主库搭一个相同版本的备库. 为了更快的追上主库、需要怎么选择并行复制策略呢 ?

  1. commit_order 由于是单线程执行、每个事务的commit_id不同、从库也只能是单线程执行
    2、 writeset_order 要求同一个线程的日志必须要与主库上的先后顺序相同、也会导致退化为单线程
    所以应该选择 writeset 并行复制策略.

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

主备切换有两种场景: 主动切换 和 被动切换. 被动切换大多是主库出了问题、由HA系统引发的. 那么: 如何判定主库出了问题呢 ?

一、 select 1 判定
实际上, select 1 成功返回、只能说明这个库的进程还在、并不能说明主库没问题. eg. 以下场景:

1
2
3
4
5
6
7
8
set global innodb_thread_concurrency=3; // 控制innodb并发线程上限. 超过、进入等待. 来避免线程数过多、上下文切换的成本过高.

create table `t`(
 `id` int(11) not null,
`c` int(11) default null
) engine=innodb;

insert into t values(1,1);

image.png
在session D里、select 1是可执行成功的, 但查询表t会被堵住.
注意:

并发连接和并发查询不是一个概念. show processlist看到的几千个连接、指的就是并发连接.
当前正在执行的语句、才是并发查询.
连接多顶多占一些内存、而并发查询多、才是造成CPU压力的根本. 在线程进入锁等待之后、并发线程的计数会-1, 行锁(包括间隙锁)不占用CPU资源.

思考:

Mysql会什么设计锁等待不计入并发线程数呢 ?

1
2
3
4
5
6
7
8
9
10
11
假设以下场景:
1. 线程1 执行begin; update t set c=c+1 where id=1; 启动事务trx1, 然后保持、此时线程处于空闲
状态、不计入并发线程
2.  线程2~129执行 update t set c=c+1 where id=1; 由于等行锁、进入等待、这样就有128个
线程处于等待状态.
3. 若处于等待状态线程计数不-1, innodb会认为线程用满、阻止其他查询语句进入引擎执行、线程1不能
提交、而另外的128个线程又处于锁等待状态、整个系统就阻塞了. 如下图:
此时innodb不能响应任何请求、且所有线程都处于等待状态、此时占用CPU为0, 很不合理、所以 
设计进入锁等待、将并发线程的计数器-1, 是合理的.

但: 若真的执行查询、还是计入并发线程的. eg. select sleep(100) from t;

等待并发线程不减1, 系统锁死.png

二、查表判断
在系统库(mysql)新建一个表, eg. 命名为 health_check, 里边只放一行数据、定期检测, 可以发现由于并发线程数过多导致的db不可用.

1
select * from mysql.health_check;

但: 若空间满了、这种方法又不好使.
更新事务要写binlog、而一旦binlog所在磁盘空间占用率达到100%, 那所有的更新和事务提交的commit语句都会被堵住, 但可以正常读取.

三、更新判断

1
update mysql.headlth_check set t_modified=now();

节点可用性的检测都应该包含主库和备库、若用来检测主库的话、备库也要进行更新检测. 但: 备库的检测也是要写binlog的、一般会把A和B的主备关系、设计为双M架构、所以在备库B上执行的检测命令、也会发回给主库A.
但若A、B更新同一条数据、就可能发生行冲突、导致主备同步停止. 可以插入多条数据、使用A、B的server_id做主键.

1
2
3
4
5
6
7
create table `headlth_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

这是一个比较常见的方案、但还存在一些问题, 判定慢.
eg. 所有的检测逻辑都需要一个超时时间N、执行update、超过Ns不返回、认为系统不可用.
但: 假设日志盘IO利用率已经100%, 这个时候系统响应很慢、需要主备切换. 但检测使用的update需要的资源很少、拿到io就可以提交成功、超时之前就返回了、于是得到了系统正常的结论, 显然这是不合理的判定.

四、内部统计
针对磁盘利用率、若Mysql 可以告知每次请求的io时间、就靠谱多了.
5.6版本以后的performance_schema库、file_summay_by_event_name表里就统计了每次IO请求时间.
event_name=’wait/io/file/innodb/innodb_log_file’ 统计的是redo log的写入时间. 第一列 event_name表示统计的类型.
接下来3组、统计的是redo log操作时间

第一组5列、是所有IO类型的统计:
count_star 是所有IO总次数
sum、min、avg、max是统计的总和、最小、平均和最大值.(单位ps)

第二组6列、是读操作的统计
最后一列 sum_number_of_bytes_read统计的是、总共从redo log读多少个字节

第三组6列, 是写操作统计

最后四组时间、是对其它类型数据的统计、在redo log里、可以认为是对 fsync的统计.

binlog对应的event_name是: wait/io/file/sql/binlog 这行、统计逻辑同 redo log. 额外的统计这些信息、是有性能耗损的、大概在10%左右. 建议只开启需要的项.

打开统计项之后、可以通过判断MAX_TIMER的值来判断数据库是否有问题.
eg. 设定阈值、单次IO超过200ms属于异常、出现异常把之前的统计值清空、再次出现这个异常就可以加入监控累计值了.

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

Innodb默认事务隔离级别是可重复读. 接下来的场景设定在可重复读隔离级别(特殊说明除外)

一、幻读是什么 ?
假设只在id=5这行加锁.png

  1. 在Q1只返回id=5这一行
  2. 在T2时刻、session B把id=0 这行的d值改为5、T3时刻Q2查询出来 id=5 和 id=0 这两行
  3. T4时刻、session C 又插入一行 (1,1,5), 因此T5时刻、查出 id=0, 1, 5三行.
    Q3读到id=1 这一行的现象被称为幻读.

幻读: 一个事务在前后两次查询同一个范围的时候、后一次查询看到了前一次查询未看到的行. 两点说明:

  1. 在可重复读隔离级别下、普通查询是快照读、不会看到别的事务插入数据, 所以、只会在当前读下才会出现.
  2. 幻读 仅指新插入的行被读到、修改后满足条件、而被读取到、不能称为幻读.

二、幻读有什么问题 ?

  1. 首先是语义上的. 前一次查询已经声明要把d=5的行加锁、不许别的事务进行读写操作, 而幻读却破坏了这个语义.
  2. 是数据一致性的问题. 锁的设计是为了保证数据的一致性. 这个一致性、不止是数据库内部数据状态此刻的一致性、还包含了数据和日志在逻辑上的一致性.

三、如何解决幻读?
产生幻读的原因是: 行锁只能锁住行、但是新插入记录这个动作、要更新的是记录之间的间隙、为了解决幻读、就引入了间隙锁(Gap Lock).
跟行锁产生冲突的是、另外一个行锁. 跟间隙锁存在冲突关系的是: 往间隙中插入一个记录这个操作. 间隙锁之间不存在冲突关系.
两种行锁之间的冲突关系.png

间隙锁和行锁合称 next-key lock, 每个next-key lock是前开后闭的区间. 即: 若表t初始化后、若用select * from t for update. 将整个记录锁起来、就会形成7个next-key lock, 分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum].
表t主键索引上的行锁和间隙锁.png

但: 间隙锁的引入会降低db的并发度.

如果事务隔离级别是: RC(Read Committed)就不会有间隙锁了.
那线上业务该如何选择隔离级别呢 ?

  1. 若读提交隔离级别够用、业务不需要可重复读的保证、这样考虑读提交下操作数据的锁范围更小(没有间隙锁), 这个选择是合理的.
  2. 业务逻辑使用读提交、逻辑备份时、mysqldump为什么把备份线程设置为 可重复读呢 ?

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

innodb基于系统参数开启监控

1
2
3
1. set global  innodb_status_output=on; #开启innodb标准监控
2. set global innodb_status_output_locks=on; # 开启innodb锁监控
3. set global innodb_print_all_deadlocks=on; # 将死锁日志记录到错误日志文件

死锁分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
2019-05-21 21:46:44 7f9ac2fff700
*** (1) TRANSACTION:
TRANSACTION 14234356386, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 101287388, OS thread handle 0x7f9ac472c700, query id 15913830552 10.20.00.217 zop Searching rows for update
update driver_statistics set statistics_status=0 , statistics_time=NOW() where statistics_status=1 and statistics_time <= DATE_SUB(NOW(),INTERVA
L 120 SECOND)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 65 page no 203 n bits 208 index `PRIMARY` of table `platform`.`driver_statistics` trx id 14234356386 lock_mode X loc
ks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 14234356375, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 0
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 101570708, OS thread handle 0x7f9ac2fff700, query id 15913830539 10.20.00.217 zop updating
update driver_statistics
     SET status = 2,time = '2019-05-21 21:46:44.96',d_id = 44201,
        d_phone = '15601237231', d_name = 'k'
    where id = 54145
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 65 page no 203 n bits 208 index `PRIMARY` of table `platform`.`driver_statistics` trx id 14234356375 lock_mode X loc
ks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 65 page no 7749 n bits 1616 index `idx_statistics_status` of table `platform`.`driver_statistics` trx id 14234356375
 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)
------------

先看事务一的信息

***(1) TRANSACTION:
TRANSACTION 14234356386, ACTIVE 0 sec fetching rows

active 0 sec 表示事务活跃时间
fetching rows 表示事务正在做的事儿

1
可能的事务有 inserting、updating、deleting、fetching rows等

mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)

mysql tables in use 1 表示mysql有一个表被使用
locked 1 表示有一个表锁
LOCK WAIT 表示事务正在等待锁
4 lock struct(s) 表示该事务的锁链表长度为 4, 表示该事务的锁链表的长度为 11，每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及 autoinc 锁等。
heap size 1184 为事务分配的锁堆内存大小
2 row lock(s) 表示当前事务持有的行锁个数, 通过遍历上边的4个锁结构、找到其中类型为LOCK_REC的记录数、

MySQL thread id 101287388, OS thread handle 0x7f9ac472c700, query id 15913830552 10.20.00.217 zop Searching rows for update

MySQL thread id .. OS... 当前事务线程信息
10.20.00.217 zop 数据库的ip和dbname
Searching rows for update 当前事务的状态

update d_statistics set statistics_status=0, statistics_time=now() where statistics_status=1 and statistics_time <=date_sub(now(),interval 120 seconds)

当前正在执行的sql语句

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 65 page no 203 n bits 208 index PRIMARY of table platform.driver_statistics trx id 14234356386 lock_mode X loc
ks rec but not gap waiting

lock_mode X 表示改记录锁为排他锁
RECORD LOCKS 表示正在等待的是记录锁
index PRIMARY 表示要加锁的索引为主键索引
n bits 208 表示这个记录锁结构上保留有208个bit位(改page上的记录数+64)、
lock_mode X表示该记录锁为排他锁、
locks rec but not gap waiting表示要加的锁为记录锁、并且处于锁等待状态

再看事务二的分析

undo log entries 1表示当前事务有1个undo log记录、因为二级索引不记undo log、表示该事务更新了1个聚簇索引

1
2
3
 (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 65 page no 203 n bits 208 index `PRIMARY` of table `platform`.`driver_statistics` trx id 14234356375 lock_mode X loc
ks rec but not gap

表示事务2 持有主键索引的X记录锁、

1
2
3
(2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 65 page no 7749 n bits 1616 index `idx_statistics_status` of table `platform`.`driver_statistics` trx id 14234356375
 lock_mode X locks rec but not gap waiting

表示事务2正在等待二级索引上的X记录锁

mysql 加锁方式

https://www.aneasystone.com/archives/2017/11/solving-dead-locks-two.html

1
2
3
4
批量update时、mysql server会根据where条件读取第一条满足条件的记录、innodb引擎返回第一条记录、并加锁(current read)、待mysql server收到这条加锁的记录后、再发起update请求、更新记录
server和innodb的交互是一条一条进行的、加锁也是一条一条进行的、
在给二级索引加X锁的同时、会给主键索引也加上X锁、
记录锁是加在索引上的、若表未建索引、db也会隐式的创建一个索引

注意

1
2
3
若sql无法使用索引时会走主索引实现全表扫描、mysql会给全表所有的记录行加记录锁、若一个where条件无法索引快速过滤、存储引擎就会将所有的记录加锁后返回、mysql server在进行过滤时、若发现不满足、会调用unlock_row把不满足的记录释放、但是每条记录的加锁操作还是不能省略的、so在没有索引时、会消耗大量的锁资源、增加db开销、降低db的并发性

RC级别不会加间隙锁

加锁情况分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
1. 主键索引命中:
    RR和RC 一样、都只加记录锁

2. 主键索引非命中
    RC不加锁、RR加 gap 锁

3. 唯一二级索引、查询命中
   RC和RR都只加记录锁、加二级索引锁的同时会加主键索引锁

4. 唯一二级索引、查询非命中
   RC不加锁、RR会加GAP锁、但只加二级索引、不加主键索引

5.二级非唯一索引、查询命中
  RC会加记录锁、RR还会加GAP锁、

6.二级非唯一索引、查询未命中
  RC不加锁、RR会加GAP锁、

7. 无索引
   若where条件不能走索引、只能走全表扫描
   RC会给所有记录加行锁、RR会给所有记录加行锁 + 所有聚簇索引之间加GAP锁

8.聚簇索引范围查询
   包含等于时、会给下一条记录也加上next-key lock

9.二级索引范围查询

参考

https://www.aneasystone.com/archives/2018/04/solving-dead-locks-four.html
https://www.aneasystone.com/archives/2017/12/solving-dead-locks-three.html
https://blog.csdn.net/enweitech/article/details/52447006
https://blog.csdn.net/miyatang/article/details/78227344
https://time.geekbang.org/column/article/75659
https://my.oschina.net/u/2408025/blog/535941
https://dbarobin.com/2015/01/27/innodb-lock-wait-under-mysql-5.5/
https://www.aneasystone.com/archives/2018/04/solving-dead-locks-four.html
https://www.cnblogs.com/LBSer/p/5183300.html

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

innodb表包含两部分: 表定义和表数据、在mysql8.0之前、表定义单独存放、.frm 文件、8.0之后允许把表结构定义放在系统数据表中了(表结构定义占用的空间很小)

innodb_file_per_table

1
2
off: 表数据放在系统共享表空间、跟数据字典放在一起
on: 每个innodb表数据存储在一个.ibd结尾的文件中(drop table时直接删除文件)

数据删除流程

索引树示意图

1
2
3
4
5
6
7
8
9
10
11
1. 假设删掉R4这个记录、innodb引擎只会把这个记录标记为已删除、之后再插入一个300-600之间的记录时、可能会复用这个位置、但是空间不会释放
2. innodb的数据是按页存放的、如果删掉了一个数据页上的所有内容呢 ？
    整个页可以复用
3. 数据页的复用: 可以复用到任意位置
    记录复用: 插入数据若不在400-600之间、无法复用
4. 若相邻两个数据页的利用率都很小、系统会把数据合并到一个页】另一个标记为可复用
5. delete删除整个表数据呢？所有数据页都被标记为可复用
所以delete操作只是标记页面可复用、这些可复用但未被复用的空间、就像是空洞、空间不会回收

6. 插入数据也会造成空洞
    数据若不是顺序插入、如果需要在某个页的末尾插入一条记录、就会申请新的页、把要插入位置之后的数据转移到新的页去: 如下图

image.png

如何去除空洞

1
2
3
4
5
6
7
8
1. 重建表 alter table A engine=Innodb
2. 5.6之后的版本、引入Online DDL、重建流程变为: 
    1. 建立一个临时文件、扫描表A主键的所有数据页、
    2. 用数据页中表A的记录生成B+树、存储到临时文件中、
    3. 生成临时文件过程中、所有表A的操作记录在日志文件(row log) 中、
    4. 临时文件生成后、将日志文件应用于临时文件、得到与表A逻辑相同的文件
    5. 用临时文件替换表A的数据文件
    与以前不同之处在于: 在重建表的过程中允许对表A的操作

重建表流程.png

Online DDL.png

完全Online ？

1
2
3
不是, DDL之前是要拿MDL锁的、但这个写锁会在copy数据之前退化成读锁、就不会阻塞CURD操作了
可以直接解锁？不行、为了保证自己在copy的过程中表结构不会再次被修改、为了保护自己
对大表来说、最耗时的是copy文件、可以认为MDL读锁的时间可以忽略、但操作会比较消耗IO和CPU、安全操作可以使用 gh-ost来做

online和inplace

1
2
3
4
5
6
7
8
9
图三中、表A导出数据存放位置叫tmp_table、是一个临时表、在server层创建
图四种、表A重建的数据放在tmp_file、是innodb内部创建出来的、整个DDL过程在innodb内部完成、对server来说、没有挪到数据到临时表、是原地操作、inplace

假如磁盘空间是1.2T、现有一个1T的表、可否完成一个inpalce的DDL？
不能、inplace也是需要临时空间的、alter table t engine=innodb,ALGORITHM=inplace;
copy表其实是: alter table t engine=innodb,ALGORITHM=copy; 对应图三

inplace和online并无直接关系
eg. 添加索引、是inplace操作、但不是online的

alter table、analyze table 、 optimize table

1
2
3
1. 5.6之后、默认上图四操作、就是recreate
2. analyze table不是重建表、是重新统计索引信息、过程会加MDL读锁
3. optimize = recreate+analyze

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

读写分离基本架构.png

读写分离的主要目标是: 分摊主库的压力. 一般有两种架构. 客户端主动做负载 和 proxy做负载.

两种负载方式的差异:

1
2
3
1. Client负载、少了一层proxy转发、查询性能稍微好一些、且整体架构简单、排查问题方便、
但与DB层耦合性高、若出现主备切换或者库迁移、Client需要调整连接信息
2. proxy负载. 对Client友好、但proxy本身也需要高可用架构、整体架构设计较复杂.

不管采用哪种负载方式, 都会存在从库读到系统过期状态的现象, 称为过期读.
那么、如何解决过期读呢 ?
一、强制走主库
其实就是将查询分类、对于必须拿到最新结果的请求、强制转发到主库; 对于可以接受稍微延迟的请求、转发给从库. 但: 极端情况下、可能所有的请求都不能接受延迟、这样所有请求都打到主库、就失去了扩展性.

二、sleep一段时间
eg. 查询数据之前、先强制等待一段时间、但会失去用户体验度. 不过可以折中、用Ajax将客户端输入内容直接展示在页面上、而不去查询DB.
存在的问题是:

  1. 若查询本可以0.5s就从从库拿到结果、也会等1s
  2. 若延迟超过1s、还是会出现过期读.

三、判断主备无延迟

  1. 每次从库查询前、先判断seconds_behind_master值、直到=0、才执行查询请求.

  2. 对比位点、确保主备无延迟
    master_log_file 和 read_master_log_pos, 表示读到的主库的最新位点
    relay_master_log_file 和 exec_master_log_pos表示备库执行的最新位点
    这两组值完全相同时、表示接收的日志已完成同步.

  3. 对比gtid
    auto_position=1, 表示主备关系使用了gtid协议
    retrieved_gtid_set, 表示的是备库收到的所有日志gtid集合
    executed_gtid_set, 是备库已经执行完成的gtid集合
    这两个集合相同、表示接收到的日志已同步完成.

    2、3两个方案都是基于备库接收到的日志执行完成了、但还有一部分日志是: Client已经收到提交确认、但备库还未收到日志的状态、所以, 比1要好些、但未达到精准的程度.

四、配合semi-sync方案
对于上边的问题、可以解决不? 利用版同步复制、semi-sync replication可以.
semi-sync设计:

  1. 事务提交时、将binlog发给从库
  2. 从库收到binlog、给主库发送一个ack、代表收到了
  3. 主库收到ack以后、才返回给客户端事务完成的确认.
    这样, 使用semi-sync配合前边的位点判断、就可以确定从库上执行的查询请求、避免过期读

但: 一主多从的场景下、主库只要等到一个从库的ack、就开始对Client返回确认、这时可能会存在过期读(查询请求落到了非确认的从库上)

判断同步位点还有一个潜在问题: 若业务更新的高峰期、可能出现主库的位点或者GTID集合更新很快、导致位点等着判断一直不成立、从库迟迟无法响应的情况. 其实、我们并不需要完全同步、只希望要查询的数据已经同步即可.

五、等主库位点
可以解决上边两个问题:

  1. 一主多从时、部分从库过期读
  2. 过度等待
    1
    select master_pos_wait(file, pos[, timeout]);
    执行逻辑:
  3. 从库上执行
  4. file 和 pos 指主库上的文件名 和 位置
  5. timeout 可选、设为正整数N表示这个函数最多等待N秒
    正常返回一个正整数M、表示从命令开始执行、到应用完file和pos代表的binlog位置、执行了多少事务.
    其它返回值:
  6. null、表示执行期间、备库同步线程发生异常
  7. -1、超过等待时间Ns
  8. 0、刚开始执行时、发现该位置已经执行过了.
    则、查询逻辑变成了:
    1
    2
    3
    4
    5
    1. trx1更新完成后、马上执行 show master status 得到当前主库执行到的File 和 Pos
    2. 选择一个从库执行查询语句
    3. 在从库上执行 select master_pos_wait(File, Pos, 1);
    4. 若返回值是>=0的正整数、则在该从库查询、否则到主库查询
    同样存在的问题是: 若所有从库都延迟了、查询压力会打到主库
    一般对于不允许过期读的要求、有两种方案: 超时放弃 和 转到主库, 要根据业务选择.

六、等GTID
若开启GTID模式、同样有等待GTID的方案

1
select wait_for_executed_gtid_set(gtid_set, 1);

执行逻辑:

  1. 等待、直到该库执行的事务中包含传入的gtid_set、返回0
  2. 超时返回1
  3. 7.6版本开始、可以把事务的gtid返回给客户端
    此时、等GTID的执行流程就变成:
    1
    2
    3
    4
    1. trx1事务更新完后、从返回包之间获取事务的gtid、记为gtid1
    2. 选定一个从库执行查询语句
    3. 在从库上执行 select wait_for_Executed_gtid_set(gtid1, 1);
    4. 若返回0、则在该从库执行查询、否则、到主库执行查询.
    问题跟等位点的一样、选择超时放弃还是转到主库查询、要根据业务场景选择.

思考:

若系统采用的是等待GTID的方案、此时要对一个大表做DDL、可能会出现什么情况呢? 为避免这种情况、改怎么做呢 ?

1
2
3
4
    这是一个典型的大事务的场景、若该DDL语句在主库执行了10min、提交到备库、也需要执行10min、
那么在主库DDL之后再提交的事务的GTID、去备库查的时候、就会等待10min才出现. 这样这个读写分离机制
至少10min内都会超时、既然是预期内的操作、应该在业务低峰期进行、确保主库可以支撑所有的业务查询、
然后把请求都切到主库、再在主库上做DDL.