How to analyze the innodb_flush_log_at_trx_commit parameters of mysql
In this issue, the editor will bring you the innodb_flush_log_at_trx_commit parameter analysis of mysql. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.
Innodb_flush_log_at_trx_commit parameter experiment
1, first set sync_binlog to 0
Mysql > set global sync_binlog=0
Query OK, 0 rows affected (0.00 sec)
Create a stored procedure p1, insert 5,000 times into the t table, and commit each insert
Mysql > DELIMITER / /
Mysql > CREATE PROCEDURE p1 ()
-> begin
-> declare i int
-> set iTunes 0
-> while i insert into t values (I)
-> set i=i+1
-> commit
-> end while
-> end
-> / /
Query OK, 0 rows affected (0.00 sec)
2, now the parameter is 2, that is, the log cache is written to the file, but will not be brushed to disk
Insert 5000 pieces of data for 3.56 seconds
Mysql > show variables like'% innodb_flush_log_at_trx%'
+-+ +
| | Variable_name | Value |
+-+ +
| | innodb_flush_log_at_trx_commit | 2 | |
+-+ +
1 row in set (0.01 sec)
Mysql > truncate t
Query OK, 0 rows affected (0.02 sec)
Mysql > call p1
Query OK, 0 rows affected (3.56 sec)
3, now this parameter is set to 1, that is, each time it is submitted, the log cache is written to the log file and brushed to disk
Insert 5000 pieces of data, the time is 6.21 seconds
Mysql > set global innodb_flush_log_at_trx_commit=1
Query OK, 0 rows affected (0.00 sec)
Mysql > truncate t
Query OK, 0 rows affected (0.02 sec)
Mysql > call p1
Query OK, 0 rows affected (6.21 sec)
4, now the parameter is set to 0, that is, the log cache is written to the log file once a second and brushed to disk
But when the transaction commits, nothing is done. Insert 5000 pieces of data for 3.18 seconds
Mysql > set global innodb_flush_log_at_trx_commit=0
Query OK, 0 rows affected (0.00 sec)
Mysql > truncate t
Query OK, 0 rows affected (0.02 sec)
Mysql > call p1
Query OK, 0 rows affected (3.18 sec)
The above is the editor for you to share how to analyze the innodb_flush_log_at_trx_commit parameters of mysql, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.
