How to carry on the sync_binlog parameter experiment of mysql
Today, I will talk to you about how to carry out the sync_binlog parameter experiment of mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.
Mysql sync_binlog parameter experiment
1. The default parameter is 1, that is, each time a MySQL is submitted, a disk synchronization instruction such as fsync will be issued to force the data in the binlog_cache to be written to disk.
Mysql > show variables like'% sync_binlog%'
+-+ +
| | Variable_name | Value |
+-+ +
| | sync_binlog | 1 | |
+-+ +
1 row in set (0.01 sec)
Mysql >
Mysql >
2. Set up the experimental environment.
Clear test table t
Mysql > truncate t
Query OK, 0 rows affected (0.02 sec)
Mysql >
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)
Mysql >
Mysql >
3, when sync_binlog=1, p1 executes 14.04 seconds
Mysql >
Mysql > call p1
Query OK, 0 rows affected (14.04 sec)
Mysql >
Mysql >
4, when sync_binlog=0, p1 executes 6.94 seconds
Mysql > set global sync_binlog=0
Query OK, 0 rows affected (0.00 sec)
Mysql >
Mysql > truncate t
Query OK, 0 rows affected (0.02 sec)
Mysql > call p1
Query OK, 0 rows affected (6.94 sec)
5, when sync_binlog=5, p1 executes 8.28 seconds
Mysql > set global sync_binlog=5
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 (8.28 sec)
Mysql >
After reading the above, do you have any further understanding of how to carry out the sync_binlog parameter experiment of mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.
