【mySQL】WAL和MVCC ----待消化
WAL(Write-Ahead Logging)机制
MySQL 里经常说到的 WAL技术,也就是先写日志,再写磁盘。
当内存数据页跟磁盘数据页内容不一致的时候,我们成这个内存页为“脏页”。内存数据写入磁盘后,内存和磁盘上的数据页内容就一致了,称为“干净页”。
MySQL 从 内存更新到磁盘的过程,称为刷脏页的过程(flush)。
InnoDB 刷脏页的时机:
-
内存中的redo log 写满了,这时系统就会停止所有更新操作,把checkoutpoint 往前推,redo log留出空间可以继续写。
往前推进之后,就要把两个点之间的日志对应的所有脏页都 flush 到磁盘上。
这种情况是 InnoDB 要尽量避免的。因为出现这种情况,整个系统都不能接受更新。更新数会跌为0。
- 系统中内存不足时,当这个时候需要新的数据页到内存中,就要淘汰掉一些数据页,如果淘汰的是“脏页”,就要先将“脏页”写到磁盘。
那么为什么不能直接淘汰所有的内存,下次请求的时候,再从磁盘读入数据页,然后 拿 redo log 出来应用?这其实也是从性能的角度来考虑的,刷脏页一定写盘,就保证了每个数据页只有两种情况:
- 数据页直接在内存里,内存里的肯定是正确的,直接返回
- 内存里没有数据,就可以肯定数据文件上是正确的结果,读入内存后返回。 这样的效率最高。
这种情况在日常应用中其实是常态。在InnoDB 中,使用缓冲池 (buffer pool)管理内存,缓冲池中的内存页有三种状态:
- 还没有使用的;
- 使用了并且是干净页
- 使用了并且是脏页
- 数据库空闲的时候刷脏页。
- 数据库正常关闭的时候,也要把内存中所有的脏页全都flush 到磁盘上。
对性能的影响
刷脏页是常态,所以如果出现以下的情况,都会明明显影响性能:
- 一个查询要淘汰的脏页太多,会导致查询的响应时间明显变长;
- 日志写满,更新全部堵住,写性能跌为0,这种情况对于敏感业务来说是不能接受的。
InnoDB 刷脏页的控制策略
首先,需要让 InnoDB 正确指导系统的 IO 能力,来控制刷脏页的快慢。
innodb_io_capacity 这个参数,它会告诉 InnoDB 你的磁盘能力,所以尽量设置成磁盘的 IOPS。可以使用 fio 工具来获取。
fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
然后,如果你来设计策略控制刷脏页的速度,会参考哪些因素呢?
这个问题可以这么想,如果刷太慢,会出现什么情况?首先是内存脏页太多,其次是 redo log 写满。
所以,InnoDB 的刷盘速度就是要参考这两个因素:一个是脏页比例,一个是 redo log 写盘速度。
参数 innodb_max_dirty_pages_pct 是脏页比例上限,默认是 75%。InnoDB 会根据当前的脏页比例,计算出一个数字 F1。
F1(M)
{
if M>=innodb_max_dirty_pages_pct then
return 100;
return 100*M/innodb_max_dirty_pages_pct;
}
InnoDB 写入日志都会有一个序号,当前写入序号跟 checkpoint 对应的序号之间的差值,假设为N。InnoDB 会根据N 计算出 F2.
根据 F1和F2 取其中较大的值为 R,之后引擎就可以按照 Innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。
MySQL 中有一个机制,刷脏页的时候如果数据页旁边的数据页也是脏页,那么就会一起刷掉,而且这个逻辑是可以蔓延的,所以对于每个相邻的数据页,都会被一起刷。
在 InnoDB 中,innodb_flush_neighbors 参数就是用来控制这个行为的,值为 1 的时候会有上述的“连坐”机制,值为 0 时表示不找邻居,自己刷自己的。
在使用机械硬盘时,这个优化很有意义,可以减少很多随机 IO。如果使用的是 SSD 这种IOPS 比较高的设备,可以设置innodb_flush_neighbors 为0,只刷自己,这个时候 IOPS 往往就不是性能瓶颈了。只刷自己就可以提高刷脏页的速度,减少 SQL 语句的响应时间。
binlog 的写入机制
binlog 的写入机制比较简单:事务执行的过程中,先把日志写到 binlog cache,事务提交的时候,再把 binlog cache 写到binlog 文件中。
系统给 binlog cache 分配了一片内存,每个线程一个,参数 binglog_cache_size 用于控制单个线程内 binlog cache 的内存大小,超过就要暂存在磁盘。
事务提交的时候,执行器把 binlog cache 里完整事务写入到 binlog 中,并清空 binlog cache。
binlog 写盘状态
- write 指的是把日志写入到文件系统的 page cache,并没有吧数据持久化到磁盘,所以速度比较快。
- fsync 是持久化到磁盘的操作,一般情况下, fsync 才会占磁盘的 IOPS。
write 和 fsync 的时机,是由参数 sync_binlog 控制的:
- sync_binlog=0 的时候,表示每次提交事务都只 write,不 fsync;
- sync_binlog=1 的时候,表示每次提交事务都会执行 fsync;
- sync_binlog=N(N>1) 的时候,表示每次提交事务都 write,但累积 N 个事务后才 fsync。
因此,在出现 IO 瓶颈的场景里,将 sync_binlog 设置成一个比较大的值,可以提升性能。在实际的业务场景中,考虑到丢失日志量的可控性,一般不建议将这个参数设成 0,比较常见的是将其设置为 100~1000 中的某个数值。但是,将 sync_binlog 设置为 N,对应的风险是:如果主机发生异常重启,会丢失最近 N 个事务的 binlog 日志。
redo log 的写入机制
事务的执行过程中,生成的 redo log 是要先写到 redo log buffer 的。
redo log 三种状态:
- 存在 redo log buffer 中,物理上是在 MySQL 进程内存中
- 写到磁盘(write),但是没有持久化(fsync),物理上是在文件系统的 page cache 里
- 持久化磁盘,对应的是 hard disk
日志写到 redo log buffer 是很快的,write 到 page cache 也差不多,但是持久化到磁盘的速度就慢多了。
InnoDB 提供了 innodb_flush_log_at_trx_commit 参数,取值如下:
- 设置为 0 时,表示每次事务提交时都只是把 redo log 留在 redo log buffer 中;
- 设置为 1 时,表示每次事务提交时都将 redo log 直接持久化到磁盘;
- 设置为 2 时,表示每次事务提交时都只是把 redo log 写到 page cache。
InnoDB 有一个后台线程,每隔 1 秒,就会把 redo log buffer 中的日志,调用 write 写到文件系统的 page cache,然后调用 fsync 持久化到磁盘。
组提交机制
日志逻辑序列号(log sequence number,LSN)是一个单调递增的值,对应 redo log 的一个个写入点。每次写入的长度为 lenght 的 redo log,LSN的值就会加上 length。
LSN 也会写到 InnoDB 的数据页中,来确保数据也不会被多次执行重复的 redo log。
在一组提交里面,组员越多,节约磁盘 IOPS 的效果越好。在并发更新的场景下,第一个事务写完 redo log buffer 以后,接下来这个 fsync 越晚调用,组员可能越多,节约 IOPS 的效果就越好。
- binlog_group_commit_sync_delay 参数,表示延迟多少微秒后才调用 fsync;
- binlog_group_commit_sync_no_delay_count 参数,表示累积多少次以后才调用 fsync。
WAL机制主要得益于两个方面:
- redo log 和binlog 都是顺序写,磁盘的顺序写比随机写速度要快;
- 组提交机制,可以大幅度降低磁盘的 IOPS 消耗。
如果你的 MySQL 现在出现了性能瓶颈,而且瓶颈在 IO 上,可以通过哪些方法来提升性能呢?
针对这个问题,可以考虑以下三种方法:
- 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数,减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的,因此可能会增加语句的响应时间,但没有丢失数据的风险。
- 将 sync_binlog 设置为大于 1 的值(比较常见是 100~1000)。这样做的风险是,主机掉电时会丢 binlog 日志。
- 将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是,主机掉电的时候会丢数据。
作者:如梦又似幻
链接:https://www.jianshu.com/p/f242bc1e95ff
MVCC(Multiversion concurrency control)机制
MVCC
Mysql的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑,他们一般都同时实现了MVCC.实现了非阻塞的读操作,写操作也只锁定必要的行。
MVCC的实现,是通过保存数据在某个时间点的快照来实现的。即为:不管需要执行多长时间,每个事务看到的数据都是一致的。
不同的存储引擎的MVCC实现不同,典型的有乐观并发控制和悲观并发控制。
innodb的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的。这两个列,一个是行的创建时间,一个保存行的过期时间。存储的是系统版本号,不是真实的时间。每开始一个新的事务,系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号,用来和查询到的每行记录的版本号进行比较。
在REPEATABLE READ隔离级别下,MVCC具体操作:
SELECT
innodb会根据以下两个条件检查每行记录:
a.innodb只查找版本号早于当前事务版本的数据行,<=当前事务版本号,这样可以确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的
b.行的删除版本要么未定义,要么大于当前的事务版本号。这可以确保事务读取到的行,在事务开始之前未被删除。
INSERT
INNODB为新插入的每一行保存当前系统版本号作为行版本号
DELETE
innodb为删除的每一行保存当前系统版本号作为行删除标识
UPDATE
innodb为插入一行新纪录,保存当前系统版本号为行版本号,同时保存当前系统版本号到原来的行作为行删除标识
MVCC只在repeatable read和read committed两个隔离级别下工作。其他两个隔离级别和MVCC不兼容。因为READ UNCOMMITTED 总是读取最新的数据行,而不是符合当前事务版本的数据行。而SERIALIZABLE 则会对所有读取的行都加锁。
https://www.cnblogs.com/maggie94/p/6746549.html
MVCC 全称是 multiversion concurrency control,即多版本并发控制,是 innodb 实现事务并发与回滚的重要功能。
具体的实现是,在数据库的每一行中,添加额外的三个字段:
DB_TRX_ID – 记录插入或更新该行的最后一个事务的事务 ID
DB_ROLL_PTR – 指向改行对应的 undolog 的指针
DB_ROW_ID – 单调递增的行 ID,他就是 AUTO_INCREMENT 的主键 ID