innodb_flush_log_at_trx_commit参数对插入性能的影响测试方法

//

innodb_flush_log_at_trx_commit参数对插入性能的影响测试方法

//

最近工作上的事情比较繁琐，回到家就想休息，今天介绍一个简单的测试innodb_flush_log_at_trx_commit参数对插入性能影响的方法吧。

其实这个很好测试，现在的mysql服务器，大多数都是SSD了，早期的时候，机械的磁盘比较多，我们在上MySQL服务的时候，总是习惯性的测试一下innodb_flush_log_at_trx_commit参数对性能的影响，这个参数的意义大家可能也都知道，它和sync_binlog这2个参数是MySQL DBA必须知道的参数，之前针对这两个参数也做过介绍：

MySQL数据安全的双1模式简介

测试方法如下，利用一个存储过程来进行测试：

CREATE TABLE `flush_1` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

创建一个存储过程
drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000) do
    insert into flush_1 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;

调用这个存储过程
call idata();

其中，flush_1就是指参数innodb_flush_log_at_trx_commit的值变成1，这里我简单在docker环境中测试了一下：

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

---------------默认值是1，开始测试（8.05s）---------

mysql> CREATE TABLE `flush_1` (
    ->   `id` int(11) NOT NULL,
    ->   `a` int(11) DEFAULT NULL,
    ->   `b` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `a` (`a`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 3 warnings (0.22 sec)

mysql> 
mysql> drop procedure idata;
Query OK, 0 rows affected (0.07 sec)

mysql> delimiter ;;
mysql> create procedure idata()
    -> begin
    ->   declare i int;
    ->   set i=1;
    ->   while(i<=1000) do
    ->     insert into flush_1 values(i, i, i);
    ->     set i=i+1;
    ->   end while;
    -> end;;
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call idata();
Query OK, 1 row affected (8.05 sec)


---------------第一次修改，改为2，开始测试（5.76s）-----------
mysql> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `flush_2` (
    ->   `id` int(11) NOT NULL,
    ->   `a` int(11) DEFAULT NULL,
    ->   `b` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `a` (`a`)
    -> ) ENGINE=InnoDB;

drop procedure idata;
Query OK, 0 rows affected, 3 warnings (0.04 sec)

mysql> drop procedure idata;
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;;
mysql> create procedure idata()
    -> begin
    ->   declare i int;
    ->   set i=1;
    ->   while(i<=1000) do
    ->     insert into flush_2 values(i, i, i);
    ->     set i=i+1;
    ->   end while;
    -> end;;
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call idata();
Query OK, 1 row affected (5.76 sec)

---------------第二次修改，改为0，开始测试（3.88 s）---------
mysql> set global innodb_flush_log_at_trx_commit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `flush_0` (
    ->   `id` int(11) NOT NULL,
    ->   `a` int(11) DEFAULT NULL,
    ->   `b` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `a` (`a`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 3 warnings (0.04 sec)

mysql> drop procedure idata;
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;;
mysql> create procedure idata()
    -> begin
    ->   declare i int;
    ->   set i=1;
    ->   while(i<=1000) do
    ->     insert into flush_0 values(i, i, i);
    ->     set i=i+1;
    ->   end while;
    -> end;;
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call idata();
Query OK, 1 row affected (3.88 sec)

可以看到：

该参数设置为1，最安全，需要8.05s才能将1000条数据写到磁盘；

设置为2，总计需要5.76 s

设置为0，只需要3.88s

如果是SSD的话，这个差距可能会缩小，具体的情况，大家可以将插入的数据条数增多之后（例如50000条），自行测试，这样的结果会更加明显。