诡象追踪：MySQL开binlog性能更好？！

版本&平台：

版本：MySQL 5.7.38

cpu：112核

内存：512G

磁盘：pcie nvme 6.4T（RW均>5GB/S)

现象：

        同样条件下（同个机器的同个实例，测试场景相同，配置参数也相同），仅修改binlog开关，当关闭binlog时，性能更差，测试场景为sysbench, oltp_update_index, 256并发，64个表，单表100W数据。

双1测试，256并发：

binlog开关	TPS	平均延迟（ms)	最大延迟(ms)	最大cpu占用
开	67983	3.73	1316	20.4核
关	47644	5.37	880	7.4核

        当关闭Binlog后，tps更低，延迟更高，非常诡异。MySQL binlog的生成，即要占cpu又要占io，当sync_binlog=1时，还会阻塞innodb事务提交，延迟理应更高，这个结果非常反直觉。

深入分析：

        实测既然存在关binlog性能表现更好的现象，就一定有其合理性。高并发下锁的热点争抢影响超过binlog的开销，当开了binlog后让整个流程变慢，锁热点下降让整个流程更顺畅，就可能开binlog吞吐量更大，也就是性能更好。关binlog后遇到的并发热点，如trx sys mutex、lock sys mutex、log sys mutex这些锁，因为机器物理核很多，导致实际工作线程并发很大，抢cpu竞争非常激烈，盖过了binlog的开销，反而可能让数据库整理吞吐量下降。并且当前binlog是组提交，要写Binlog时，会将多个并发提交事务合并到一个组写盘，innodb工作线程等写盘完成后被唤醒再继续做innodb 的最后commit，这里会让binlog的开销大大降低。各个用户连接线程抢innodb工作线程是强占cpu自旋方式抢，当争抢的用户线程过多时，额外cpu开销高，就可能会导致性能下降。

        即然各线程争抢资源激烈，那么降低这种资源竞争，应该可以提高吞量，于有了下面验证1。

验证1：降低每个spin lock时间，即减少每次竞争时间，应该能提升性能，测试结果如下：

将spin_lock（innodb_spin_wait_delay ）时间从默认6降到2：

binlog开关	TPS	平均延迟（ms)	最大延迟(ms)	最大cpu占用
开	67983	3.73	1316	20.4核
关	47644	5.37	880	7.4核
关（验证1）	53536	4.78	758	6.6核

        可以看到，TPS提升明显，延迟下降，峰值cpu下降，优化效果明显，减少竞争可以提升性能。

验证2：上面提到当系统热点出现在相关锁时，最直接原因是innodb处理能力不够，那么提升innodb处理能力理论上是能提升性能的，将innodb工作线程从当前默认的16个提升，前面spinlock优化保留为2：

binlog开关	innodb工作线程	TPS	平均延迟（ms)	最大延迟(ms)	最大cpu占用
开	16	67983	3.73	1316	20.4核
关	16	47644	5.37	880	7.4核
关（验证1）	16	53536	4.78	758	6.6核
关（验证2）	32	74121	3.45	490	13.3核
关（验证3）	64	81503	3.14	690	21.3核
关（验证4）	128	75441	3.39	1044	35.8核

        整体来看，当innodb并发处理能力（innodb_thread_concurrency）越高，性能越好，延迟也会降低，但并不是越高越好，设置过多也会降低处理力。最高值为81503，相对于优化前的47644，提升非常多。

        另外，观察到当并发线程小于或等于16时，innodb_thread_sleep_delay值会不停增长（innodb自己动态调整），甚至增到几万，这会严重影响系统吞吐量。

验证5：经过上面的参数调整，关binlog可以远超开Binlog的处理能力，继续组合不同参数值，尝试跑个最优值。

binlog开关	innodb工作线程	TPS	平均延迟（ms)	最大延迟(ms)	最大cpu占用
开	16	67983	3.73	1316	20.4核
关	16	47644	5.37	880	7.4核
关，spinlock 3	32	71219	3.59	543	12.8核
关，spinlock 3	80	91384	2.80	265	28.4核
关，spinlock 3	112	90220	2.83	293	35.9核
关，spinlock 6	32	60773	4.21	1346	14.2核
关，spinlock 6	80	88421	2.89	503	30.6核
关，spinlock 6	112	90812	2.82	317	39.5核

        经过多次搭配，关binlog情况下，spin_lock为3，innodb并发为80时能测到最优值91384，与不调优的47644比，将近提升一倍。

验证6：继续分析，如果用户线程并发低，小于或等于innodb 并发线程时，不存在竞争innodb工作线程资源情况，此时产生了binlog的场景就没有理由比不产生binlog的场景性能好，且有binlog的延迟一定要高，实测如下：

innodb_spin_wait_delay 还原默认值6，innodb_thread_concurrency还原初始值16，压测场景：sysbench, oltp_update_index, 16并发（原来是256并发），64个表，单表100W数据，即其它全不变，用户线程从256减到16个。

binlog开关	TPS	平均延迟（ms)	最大延迟(ms)	最大cpu占用
开binlog	29176	0.55	10.01	5.3核
关binlog	50267	0.32	12.46	7.3核

        这个测试结果完全符合预期，一般场景下，MySQL实例cpu核数较少，所以通常开binlog性能就是要更差的。

结论：

        实测来看，开binlog开销肯定要比不开binlog大，但是存在开binlog吞吐量反而要高的场景，这在cpu核数很多的情况下是存在的。

        对于cpu核数不多的情况，比方说16核，物理上真正并发干活线程最多16个，innodb配置的16个干活线程，真正干活的用户线程估计很难超过10个，他们之间争抢innodb工作线程的竞争不会激烈。如果机器像本次测试机一样112核，真正能并发干活的线程可以是上百个，若innodb同样只提供最多16个干活线程，用户线程与innodb线程比例可能高达10：1，争抢激烈，这个影响可能远超IO影响，导致关binlog性能更差。