优化方向:
1、调整参数innodb_flush_log_at_trx_commit,默认值1
此参数用于控制redo日志写入log file以及落盘时机。
0:log buffer每秒一次写入log file中,并且让数据落盘。
1:每次提交事务都会把log buffer的数据写入log file中,并且让数据落盘。
2:每次提交事务都会把log buffer的数据写入log file中,每秒让数据落盘。
2、关闭binlog,避免生成二进制日志文件
3、在开启binlog的情况下,优化方案
1)调整sync_binlog,默认值1
该参数控制binlog同步磁盘的时机,
0:不会主从将binlog同步磁盘,而是依赖操作系统来刷新binlog。
n(n>0):每写n次二进制日志时,将其同步到磁盘。
2)关闭sql_log_bin
set sql_log_bin=0;
实验思路:
控制单个变量的变化,每种情况测试3次,取平均值。
准备实验数据:
准备数据:准备test库数据进行导入操作
不修改任何参数进行导入:
第一次测试:55s
第二次测试:55s
第三次测试:
平均耗时:55s
实验一:调整redo数据落盘时机为0
set global innodb_flush_log_at_trx_commit = 0;
第一次测试:54s
第二次测试:54s
第三次测试:53s
平均耗时:54s
实验二:调整redo数据落盘时机为2
set global innodb_flush_log_at_trx_commit = 2;
第一次测试:53s
第二次测试:54s
第三次测试:53s
平均耗时:53s
实验三:关闭binlog
Mysql配置文件中增加skip-log-bin参数,重启mysql服务
第一次测试:53s
第二次测试:53s
第三次测试:51s
平均耗时:52 s
实验四:开启binlog情况下设置sql_log_bin参数
set sql_log_bin =0;
第一次测试:50s
第二次测试:50s
第三次测试:50s
平均耗时:50s
实验五:设置sync_binlog参数
set global sync_binlog = 2000;
第一次测试:54s
第二次测试:52s
第三次测试:53s
平均耗时:53s
实验六:设置sql_log_bin参数下确定sync_binlog是否有影响
set sql_log_bin =0;
set global sync_binlog = 2000;
第一次测试:51s
第二次测试:51s
第三次测试:51s
平均耗时:51s
与实验四相比耗时增加1s,没有任何优化,因此判定在关闭binlog情况下, sync_binlog对于性能优化无任何增长,甚至可能会拖慢性能。
结论:
开启binlog情况下两种优化方案:
1)innodb_flush_log_at_trx_commit设置为2 + sync_binlog设置一定数量
2)innodb_flush_log_at_trx_commit设置为2 +sql_log_bin设置为0
未开启binlog情况下优化方案:
innodb_flush_log_at_trx_commit设置为2
最优方案:
开启binlog+innodb_flush_log_at_trx_commit设置为2 +sql_log_bin设置为0