gridcontrol值为0时设置为空_mysql实验--innodb_flush_log_at_trx_commit分别为0、1、2的性能...

概述

今天主要探讨下innodb_flush_log_at_trx_commit这个参数，然后用实验来测试这个参数不同值的影响。

---

一、参数解释

0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。

1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。

2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

---

二、参数修改

找到mysql配置文件mysql.ini，修改成合适的值，然后重启mysql。

---

三、注意事项

当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。。

当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

---

四、实验

下面主要测试innodb_flush_log_at_trx_commit 分别为0、1、2时的差距。

1、环境模拟

#创建测试表drop table if exists test_flush_log;create table test_flush_log(id int,name char(50))engine=innodb;#创建插入指定行数的记录到测试表中的存储过程drop procedure if exists proc;delimiter $$create procedure proc(i int)begin declare s int default 1; declare c char(50) default repeat('a',50); while s<=i do start transaction; insert into test_flush_log values(null,c); commit; set s=s+1; end while;end$$delimiter ;

2、innodb_flush_log_at_trx_commit为1

当前环境下， innodb_flush_log_at_trx_commit 的值为1，即每次提交都刷日志到磁盘。测试此时插入10W条记录的时间结果是8分43秒。

mysql> call proc(100000);

3、innodb_flush_log_at_trx_commit为2

测试值为2的时候，即每次提交都刷新到os buffer，但每秒才刷入磁盘中。

mysql> set @@global.innodb_flush_log_at_trx_commit=2; mysql> truncate test_flush_log;mysql> call proc(100000);

结果插入时间大减，只需4分23秒。

4、innodb_flush_log_at_trx_commit为0

测试值为0的时候，即每秒才刷到os buffer和磁盘。

mysql> set @@global.innodb_flush_log_at_trx_commit=0;mysql> truncate test_flush_log;mysql> call proc(100000);

结果只有4分9秒。

最后可以发现，其实值为2和0的时候，它们的差距并不太大，但2却比0要安全的多。它们都是每秒从os buffer刷到磁盘，它们之间的时间差体现在log buffer刷到os buffer上。因为将log buffer中的日志刷新到os buffer只是内存数据的转移，并没有太大的开销，所以每次提交和每秒刷入差距并不大。可以测试插入更多的数据来比较，以下是插入100W行数据的情况。从结果可见，值为2和0的时候差距并不大，但值为1的性能却差太多。

5、优化存储过程

尽管设置为0和2可以大幅度提升插入性能，但是在故障的时候可能会丢失1秒钟数据，这1秒钟很可能有大量的数据，尽管上述插入的数据简单，但却说明了数据丢失的大量性。更好的插入数据的做法是将值设置为1，然后修改存储过程，将每次循环都提交修改为只提交一次，这样既能保证数据的一致性，也能提升性能，修改如下：

drop procedure if exists proc;delimiter $$create procedure proc(i int)begin declare s int default 1; declare c char(50) default repeat('a',50); start transaction; while s<=i DO insert into test_flush_log values(null,c); set s=s+1; end while; commit;end$$delimiter ;

测试值为1时的情况：

mysql> set @@global.innodb_flush_log_at_trx_commit=1;mysql> truncate test_flush_log;mysql> call proc(1000000);Query OK, 0 rows affected (11.26 sec)

---

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下！