What is the performance test of innodb_flush_log_at_trx_commit parameters?
It is believed that many inexperienced people have no idea about what the innodb_flush_log_at_trx_commit parameter performance testing is like. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.
Mysql > create table test_load (an int,b char (80)) engine=innodb
Query OK, 0 rows affected (0.19 sec)
Mysql > delimiter / /
Mysql > create procedure p_load (count int unsigned)
-> begin
-> declare s int unsigned default 1
-> declare c char (80) default repeat ('astatine 80)
-> while s insert into test_load select null,c
-> commit
-> set s = s + 1
-> end while
-> end
-> / /
Mysql > delimiter
Mysql > call p_load (50000)
Query OK, 0 rows affected (3.02 sec)
Mysql > show variables like'% flush_log%'
+-+ +
| | Variable_name | Value |
+-+ +
| | innodb_flush_log_at_timeout | 1 | |
| | innodb_flush_log_at_trx_commit | 0 | |
+-+ +
Mysql > call p_load (500000)
Query OK, 0 rows affected, 3 warnings (28.43 sec)
Mysql > select * from test_load limit 5
+-+
| | a | b | |
+-+
| | NULL | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| | NULL | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| | NULL | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| | NULL | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| | NULL | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
+-+
5 rows in set (0.12 sec)
Mysql > set global innodb_flush_log_at_trx_commit=1
Query OK, 0 rows affected (0.00 sec)
Mysql > call p_load (500000)
Query OK, 0 rows affected (27.54 sec)
Mysql > show variables like'% flush_log%'
+-+ +
| | Variable_name | Value |
+-+ +
| | innodb_flush_log_at_timeout | 1 | |
| | innodb_flush_log_at_trx_commit | 1 | |
+-+ +
2 rows in set (0.09 sec)
Mysql > truncate table test_load
Query OK, 0 rows affected (0.23 sec)
Mysql > call p_load (500000)
Query OK, 0 rows affected (24.63 sec)
Mysql > set global innodb_flush_log_at_trx_commit=2
Query OK, 0 rows affected (0.30 sec)
Mysql > truncate table test_load
Query OK, 0 rows affected (1.06 sec)
Mysql > call p_load (500000)
Query OK, 0 rows affected (27.69 sec)
After reading the above, have you mastered the method of innodb_flush_log_at_trx_commit parameter performance testing? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!
