mysql flush hosts命令有什么影响 mysql innodb_flush_log

转载

mob64ca14005461 2024-02-06 22:24:27

文章标签 mysql 事务日志缓冲 mysql 存储过程提交事务 文章分类 MySQL 数据库

事务日志刷写参数 innodb_flush_log_at_trx_commit=0｜1｜2三个值

其原理如下图

image

1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。这是完全遵守ACID特性

0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务

2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失

高并发业务行业最佳实践，是使用第三种折衷配置(=2)：

1.配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快

2.配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置为2，只要操作系统不奔溃，也绝对不会丢数据

那么我们了解了参数innodb_flush_log_at_trx_commit=0｜1｜2

无非是1是最安全的，每条事务commit后都需要立即从事务日志缓冲区log buffer 执行落盘

0是每条事务commit后会继续放到事务日志缓冲区log buffer中，但是每隔一秒就会落盘log buffer中已提交的事务。假如log buffer中存了上千上万个已提交事务，这一秒也要落盘完。

2和0一样，每条事务commit后不会立即落盘，而是放到os buffer缓冲区中，它和0的区别就是：

0将已提交的事务放在log buffer即mysql的在内存的日志缓冲区，而2是将已提交的事务放到os buffer 系统的内存缓冲区，从用户态转向内核态仅此而已。

准备阶段

库下创建数据表testlog 查看初始大小

use helldb;
CREATE TABLE `testlog` (
`id` int NOT NULL AUTO_INCREMENT,
`name` char(10) DEFAULT NULL,
`age` int DEFAULT '20',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
root@17 hellodb]# ll -h

总用量 992K

-rw-r----- 1 mysql mysql 112K 1月 12 04:59 classes.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 coc.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 courses.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 scores.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 students.ibd
-rw-r----- 1 mysql mysql 112K 1月 14 14:33 t1.ibd
-rw-r----- 1 mysql mysql 112K 1月 14 16:10 t2.ibd
-rw-r----- 1 mysql mysql 112K 1月 13 18:22 t3.ibd
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 teachers.ibd
-rw-r----- 1 mysql mysql 112K 1月 14 16:57 testlog.ibd #看到只有112k
-rw-r----- 1 mysql mysql 112K 1月 12 04:59 toc.ibd
-rw-r----- 1 mysql mysql 128K 1月 14 10:41 t_user.ibd

定义存储过程向testlog表中插入十万行数据

delimiter //
create procedure sp_testlog()
begin
declare i int;
set i=1;
while i <= 100000
do insert into testlog(name,age) values(concat("wang",i),i);
set i=i+1;
end while;
end //
delimiter;
(root@localhost) [hellodb]> show create procedure sp_testlog\G;
*************************** 1. row ***************************
Procedure: sp_testlog
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_testlog`()
begin
declare i int;
set i=1;
while i <= 100000
do insert into testlog(name,age) values(concat("wang",i),i);
set i=i+1;
end while;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)

验证

innodb_flush_log_at_trx_commit=1级别下十万次事务刷写时长

当我们直接call调用定义好的存储过程(procredure)sp_testlog时，由于自动提交autocommit=1开启，所以mysql认为存储过程中的每条DML语句都是一条事务。这里有十万条事务提交。

在innodb_flush_log_at_trx_commit=1时，每条事务commit都必须立即刷盘，所以10万条事务，会落盘10万次 IO操作。相当慢。

#innodb_flush_log_at_trx_commit=1 每次提交事务都立即将日志从log buffer中刷写到磁盘
(root@localhost) [hellodb]> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)
#直接call sp_testlog
(root@localhost) [hellodb]> call sp_testlog;
Query OK, 1 row affected (45.36 sec) #用时45秒
#因为我们定义的存储过程 如下
delimiter //
create procedure sp_testlog()
begin
declare i int;
set i=1;
while i <= 100000
do insert into testlog(name,age) values(concat("wang",i),i);
set i=i+1;
end while;
end //
delimiter;
#每条insert在autocommit=1自动提交下都是单独的一个事务。
#所以该存储过程在执行了10万次事务。在innodb_flush_log_at_trx_commit=1级别刷写时长为45秒
#查看ibd文件变大为12M

root@17 hellodb]# ll -h

总用量 13M

-rw-r----- 1 mysql mysql 112K 1月 12 04:59 classes.ibd

-rw-r----- 1 mysql mysql 112K 1月 12 04:59 coc.ibd

-rw-r----- 1 mysql mysql 112K 1月 12 04:59 courses.ibd

-rw-r----- 1 mysql mysql 112K 1月 12 04:59 scores.ibd

-rw-r----- 1 mysql mysql 112K 1月 12 04:59 students.ibd

-rw-r----- 1 mysql mysql 112K 1月 14 14:33 t1.ibd

-rw-r----- 1 mysql mysql 112K 1月 14 16:10 t2.ibd

-rw-r----- 1 mysql mysql 112K 1月 13 18:22 t3.ibd

-rw-r----- 1 mysql mysql 112K 1月 12 04:59 teachers.ibd

-rw-r----- 1 mysql mysql 12M 1月 14 19:03 testlog.ibd

-rw-r----- 1 mysql mysql 112K 1月 12 04:59 toc.ibd

-rw-r----- 1 mysql mysql 128K 1月 14 10:41 t_user.ibd

innodb_flush_log_at_trx_commit=1级别一次事务执行时长

autocommit=1,但是begin显式开启事务调用存储过程(procredure)sp_testlog时，mysql认为整个存储过程是一条事务，即使里面执行了10万次insert也是一条事务。所以只有一次事务提交。

在innodb_flush_log_at_trx_commit=1时，每条事务commit都必须立即刷盘，所以1条事务，flush一次打包把这十万个一次性落盘不用每次一条一条落盘了很快。

#innodb_flush_log_at_trx_commit=1 每次提交事务都立即将日志从log buffer中刷写到磁盘
(root@localhost) [hellodb]> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)
(root@localhost) [hellodb]> begin;call sp_testlog;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (4.01 sec)
Query OK, 0 rows affected (0.49 sec)
#我们看到将存储过程call sp_testlog作为一个事务来说，其内部的十万条insert合起来一个事务的所有操作。
#总共执行时间为4秒 大大小于10万次事务操作
innodb_flush_log_at_trx_commit=0级别 一次事务执行时长
#innodb_flush_log_at_trx_commit=0 事务提交时不会立即刷新到磁盘 每秒从log buffer中刷新已提交的事务到磁盘。如果故障 丢失一秒钟的数据 但是性能因为从log buffer直接刷磁盘 很快
(root@localhost) [hellodb]> begin;call sp_testlog;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (3.01 sec)
Query OK, 0 rows affected (0.03 sec)
#我们看到将存储过程call sp_testlog作为一个事务来说，其内部的十万条insert合起来一个事务的所有操作。
#总共执行时间为3秒 大大小于10万次事务操作
innodb_flush_log_at_trx_commit=2级别下一次事务执行时长
#innodb_flush_log_at_trx_commit=2 事务提交时会写入os buffer缓冲区，每秒执行一次从os buffer刷新日志记录到到磁盘文件中。 性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失
(root@localhost) [hellodb]> begin;call sp_testlog;commit
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (2.97 sec)
Query OK, 0 rows affected (0.05 sec)

通过上面例子，我们明显看到了有事务日志与没有事务日志的区别。

有事务十万条insert打包统一从内存写入到磁盘。

有事务级别不一样速度差不多安全不一样。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。