mysql的innodb_flush_log_at_trx_commit和sync_binlog

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变得更大时，写入速度反而更慢了？