//
//
最近工作上的事情比较繁琐,回到家就想休息,今天介绍一个简单的测试innodb_flush_log_at_trx_commit参数对插入性能影响的方法吧。
其实这个很好测试,现在的MySQL服务器,大多数都是SSD了,早期的时候,机械的磁盘比较多,我们在上MySQL服务的时候,总是习惯性的测试一下innodb_flush_log_at_trx_commit参数对性能的影响,这个参数的意义大家可能也都知道,它和sync_binlog这2个参数是MySQL DBA必须知道的参数,之前针对这两个参数也做过介绍:
测试方法如下,利用一个存储过程来进行测试:
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条),自行测试,这样的结果会更加明显。