innodb_flush_log_at_trx_commit 代表redolog刷新到磁盘的策略：
redolog 在事务进行中会持续写日志信息到redolog buffer中。
- innodb_flush_log_at_trx_commit =0 代表数据库提交事务后，不主动刷新数据到磁盘，只会把日志信息写在buffer中，需要innodb主线程每秒钟把redolog buffer的数据刷新到磁盘，假如此时数据库宕机，则内存的数据就会丢失
- innodb_flush_log_at_trx_commit =1 每次提交事务，都持久化到磁盘，性能最慢
- innodb_flush_log_at_trx_commit =2 每次提交事务，写到文件系统的缓存中，此时，innodb就视为已经持久化了，然后一秒(具体时间存疑)后操作系统把数据从缓存中持久化到磁盘
sync_binlog代表binlog持久化到磁盘的策略：
- sync_binlog=0 每次提交事务，都不持久化。binlog在事务执行时，都会把日志信息写入binlog cache中，当sync_binlog=0，则只会把binlog cache中的数据写入文件系统的cache中，具体持久化到磁盘由文件系统决定
- sync_binlog=1 每次提交事务，都持久化到磁盘
- sync_binlog=N(N>1) 的时候，表示每次提交事务都写文件系统的cahce中，但累积 N 个事务后才 持久化到磁盘
实验
- mysql5.7
- centos虚拟机 内存2G
CREATE TABLE `test_tables` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`username` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
`password` int(11) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;
测试代码
import time
import pymysql
docker_mysql = {'host': '192.168.124.130', 'port': 3320, 'user': 'root8061',
'passwd': 'Root@2020_1', 'db': 'test', "charset": 'utf8'}
conn = pymysql.connect(**docker_mysql)
cursor = conn.cursor()
start = time.clock()
for i in range(1, 200000):
sql_insert = '''
insert into test_tables(id,username,password) values({},md5(uuid()),{})
'''.format(i, i)
print(sql_insert)
cursor.execute(sql_insert)
conn.commit()
end = time.clock()
total_time = end - start
print(total_time)
# innodb_flush_log_at_trx_commit=1 sync_binlog=1 221.3300878603345
# innodb_flush_log_at_trx_commit=2 sync_binlog=1 200.04964449931688
# innodb_flush_log_at_trx_commit=2 sync_binlog=0 201.1382430877705
conn.close()
测试结果如下：
- innodb_flush_log_at_trx_commit=1 sync_binlog=1 时间：221.3300878603345
- innodb_flush_log_at_trx_commit=2 sync_binlog=1 时间：200.04964449931688
- innodb_flush_log_at_trx_commit=2 sync_binlog=0 时间：201.1382430877705
- innodb_flush_log_at_trx_commit=2 sync_binlog=10 时间：197.13044860421326
- innodb_flush_log_at_trx_commit=2 sync_binlog=100 时间：202.02287415459105
- innodb_flush_log_at_trx_commit=2 sync_binlog=1000时间：209.27681759358052
当innodb_flush_log_at_trx_commit=2 sync_binlog=1 时，确实会更快一点，当 innodb_flush_log_at_trx_commit=2 sync_binlog=10时，写入的时间最快，为什么sync_binlog=N ，在N变得更大时，写入速度反而更慢了？
网友评论