MySQL~Redo刷盘策略、LogBuffer缓存池和LogFile日志文件理解_每次commit log buffer日志

作者：Cpp五条 | 2024-05-16 00:55:35

踩

每次commit log buffer日志

文章目录

刷盘策略

Innodb在写Redo日志的时候，是先写入redo log buffer 中，然后再按照一定频率刷新到redo log file中。
这里的一定频率有多个选择，这些选择对应的就是不同的刷盘策略.

这里的刷盘不是指将内存中的数据刷新到磁盘，而是指从Redo日志位于内存中的缓冲区（redo log buffer）刷新到位于磁盘中的文件区（redo log file）.

redo log buffer 刷盘到redo log file的过程也不是真正刷到磁盘中去，只是刷入到文件缓存系统（page cache）中去。真正的刷入磁盘会交给操作系统来决定

关键参数
数据库中innodb_flush_log_at_trx_commit参数就控制了在事务提交时，如何将buffer中的日志数据刷新到file中.

该参数支持三种策略：

参数值为0：提交事务也不进行刷盘操作
参数值为1：提交事务一次就刷盘一次（ 默认刷盘策略）
参数值为2：每次提交事务时，只会将buffer中的内容写入页面缓存中，不会在将页面缓存中的数据刷盘到file中。

查看默认的刷盘参数值：

mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set, 1 warning (0.03 sec)

可以看到参数值为1，即默认提交一次就刷盘一次
1
2
3
4
5
6
7
8
9

这里必须提到的一个情况是，即使我们将参数设为0，什么也不做，在数据库中也有一个后台线程，默认每1秒就帮我们自动刷盘一次：
在这里插入图片描述
没有提交的事务在执行过程中写下的redo log记录，也可能会被后台线程刷盘，如下图：

除了后台线程自动刷盘外，还有一种情况也会刷盘，就是当buffer中的记录数据大小达到最大值（16M）的一半的时候，也会自动刷盘。

总结：

事务提交时会刷盘
后台线程每隔1秒自动刷盘
记录达到buffer空间一半时刷盘

不同刷盘策略演示

参数值为1时，提交一次刷盘一次

在这里插入图片描述
参数为2时，将buffer中的数据刷新到page cache中间缓存中，不会进一步更新到file中

我们分别对参数为1和参数为2时进行操作，看相同操作下哪个用掉的时间更短。
参数1演示


创建数据库
CREATE DATABASE atguigudb3;

使用数据库
USE atguigudb3;

创建test_load表作为初始数据
CREATE TABLE test_load(
a INT,
b CHAR(80)
)ENGINE=INNODB;

使用存储过程添加数据
这里是每insert一次就提交一次事务，过会调用储存过程是可以看看多长时间 
DELIMITER//
CREATE PROCEDURE p_load(COUNT INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80)DEFAULT REPEAT('a',80);
WHILE s<=COUNT DO
INSERT INTO test_load SELECT NULL,c;
COMMIT;
SET s=s+1;
END WHILE;
END //
DELIMITER;

先查看一下当前刷盘参数是多少
mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set, 1 warning (0.03 sec)

当前是1，则看1的过程
调用存储过程生成数据  3万次操作就是3万个事务提交

mysql> CALL p_load(30000);
Query OK, 0 rows affected (27.41 sec) 

可以看到，参数为1时用掉了27秒
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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

参数2演示

先清空一下数据
mysql> truncate table test_load;
Query OK, 0 rows affected (0.03 sec)
查看是否清空 为0 清空
mysql> select count(*) from test_load;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
修改为2
mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 2;
Query OK, 0 rows affected (0.00 sec)

查看是否修改为2
mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set, 1 warning (0.00 sec)
开始操作
mysql> CALL p_load(30000);
Query OK, 0 rows affected (19.52 sec)

参数为2时，用掉了19.5秒
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

参数0演示

直接设置为0
mysql> set global innodb_flush_log_at_trx_commit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 0     |
+--------------------------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> call p_load(30000);
Query OK, 0 rows affected (14.81 sec)
相同操作 参数为0 用掉了14.8秒
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15