innodb_flush_log_at_trx_commit不同参数值下的性能测试

一.基本信息

10.0.20-MariaDB-log
sync_binlog=0
tx_isolation=READ-COMMITTED
binlog_format=ROW
innodb_io_capacity=1200
innodb_buffer_pool_size=21G
os:redhat 6.3 64位
vmware虚拟机
cpu:8个
内存：64G

测试innodb_flush_log_at_trx_commit参数分别在2、0、1下对一个表插入50W行数据所花时间。

二.场景一：innodb_flush_log_at_trx_commit=2下的性能
1.建立测试表和测试procedure
use test;
create table test_load (
a INT,
b CHAR(80)
) ENGINE=INNODB;

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;

2.测试参数：innodb_flush_log_at_trx_commit=2
(product)root@localhost [(none)]> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set (0.01 sec)

(product)root@localhost [(none)]> use test;
Database changed
(product)root@localhost [test]> CALL p_load(500000);
Query OK, 0 rows affected (1 min 0.35 sec)
上面执行时间为1分0.35秒.

每轮测试完成后，需要作如下处理：

1.drop table test_load和drop PROCEDURE p_load
2.重启mysqld实例，并且用下面的方法删除系统cache，释放swap（如果用到了swap的话），甚至可以重启整个OS。
3.sync  -- 将脏数据刷新到磁盘
4.echo 3 > /proc/sys/vm/drop_caches  -- 清除OS Cache
5.swapoff -a && swapon -a

三.场景二：innodb_flush_log_at_trx_commit=0下的性能
1.建立测试表和测试procedure
use test;
create table test_load (
a INT,
b CHAR(80)
) ENGINE=INNODB;

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;
//

2.测试参数：innodb_flush_log_at_trx_commit=0

(product)root@localhost [test]> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 0     |
+--------------------------------+-------+
1 row in set (0.00 sec)

(product)root@localhost [test]>  CALL p_load(500000);
Query OK, 0 rows affected (25.08 sec)
上面执行时间为25.08秒.

每轮测试完成后，需要作如下处理：

1.drop table test_load和drop PROCEDURE p_load
2.重启mysqld实例，并且用下面的方法删除系统cache，释放swap（如果用到了swap的话），甚至可以重启整个OS。
3.sync  -- 将脏数据刷新到磁盘
4.echo 3 > /proc/sys/vm/drop_caches  -- 清除OS Cache
5.swapoff -a && swapon -a

四.场景三：innodb_flush_log_at_trx_commit=1下的性能
1.建立测试表和测试procedure
use test;
create table test_load (
a INT,
b CHAR(80)
) ENGINE=INNODB;

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;
//

2.测试参数：innodb_flush_log_at_trx_commit=1

(product)root@localhost [test]> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

(product)root@localhost [test]> CALL p_load(500000);
Query OK, 0 rows affected (17 min 21.91 sec)
上面执行时间为17分21.91秒.

每轮测试完成后，需要作如下处理：

1.drop table test_load和drop PROCEDURE p_load
2.重启mysqld实例，并且用下面的方法删除系统cache，释放swap（如果用到了swap的话），甚至可以重启整个OS。
3.sync  -- 将脏数据刷新到磁盘
4.echo 3 > /proc/sys/vm/drop_caches  -- 清除OS Cache
5.swapoff -a && swapon -a

再总结一下：
  1.相同条件下：innodb_flush_log_at_trx_commit=0，插入50W行数据所花时间25.08秒;
  2.相同条件下：innodb_flush_log_at_trx_commit=1，插入50W行数据所花时间17分21.91秒;
  3.相同条件下：innodb_flush_log_at_trx_commit=2，插入50W行数据所花时间1分0.35秒。
上面参数值为1时，所花时间太长了，与虚拟机磁盘IO性能较差有关，若是换成SSD或PCIE，差别没那么大。
参考各参数值具体含义：
0：每秒将log buffer的内容写事务日志并且刷新到磁盘；
1：每个事务提交后，将log_buffer的内容写事务日志并刷新数据到磁盘；
2：每个事务提交，将log_buffer内容写事务日志，但不进行数据刷盘。