特别正经的面试题：系统负载较高，事务提交的性能下降。有哪些可能的瓶颈？如何优化事务提交的性能？

”当系统负载较高时，事务提交的性能可能会下降。你认为有哪些可能的瓶颈？如何优化事务提交的性能？“

这算是一道很正常的面试问题了，今天就来讨论一下这个问题。

核心要点

1. 磁盘IO瓶颈

2. LogBuffer大小不足

3. 锁争用

4. CPU瓶颈

5. 内存瓶颈

6. 网络延迟

磁盘IO瓶颈

事务提交时，MySQL需要将 redo log 写入磁盘以保证数据的持久性。

如果事务频繁提交且 

innodb_flush_log_at_trx_commit 参数设置为 1；

每次提交都需要执行磁盘同步操作（fsync），这会导致磁盘IO成为性能瓶颈。

配置参数：

innodb_flush_log_at_trx_commit ：

事务提交时，刷redo log的策略

最佳性能：=0

每隔1秒，将log buffer中的数据，批量写到os cache中，同时主动fsync到磁盘。如果数据库崩溃，会有1秒的数据丢失。

强一致：=1

每次事务提交，将log buffer中的数据，批量写到os cache中，同时主动fsync到磁盘。MySQL的默认配置，保证数据的ACID但性能最差。

折衷方案：=2

每次事务提交，将log buffer中的数据，批量写到os cache中，每隔1秒将os cache中的数据批量fsync到磁盘。

如果操作系统崩溃，最多会损失1秒的数据，这个方案兼具了性能和保持数据完整性。因为操作系统的崩溃几率较数据库而言大大降低了很多。

对于没有强一致性要求且高并发的业务来说，用=2的策略是比较合适的。从性能上来说从log buffer写到os cache 每隔1秒写一次和每次写性能差异不大，因为都是从内存到内存的写入。  

优化方法:

• innodb_flush_log_at_trx_commit: 将其从默认的 1 改为 2 ，减少每次提交后都必须立即同步磁盘的需求，以换取一定的性能提升。

• 升级存储设备: 使用SSD等高速存储设备以提高磁盘IO能力。

Log Buffer大小不足

如果 log buffer 的大小不足，MySQL会频繁将未提交的日志刷新到磁盘，从而增加IO操作，影响事务提交性能。

1

什么是Log Buffer？

首先，看一下MySQL官方关于 Log Buffer的描述

总结以下几点

1. log buffer 是保存要写入到磁盘上的日志文件的数据的一块内存区域
2. log buffer 会定期刷新到磁盘上
3. 大事务需要大的log buffer size,这样才能保证在事务提交之前不需要将日志数据写入磁盘

再来看看MySQL的 InnoDB存储引擎的架构图

从上图中可以看出 Log Buffer 到 Redo Log的数据流向是

2

Log Buffer的作用

log buffer 的主要作用是：

优化redo log 性能

提供高并发与强一致性的折衷方案

事务提交后，必须将事务修改的数据页刷到磁盘上，才能保证事务的ACID特性。

这个刷盘的操作是一个随机写，随机写性能低，每次事务都刷盘会大大影响数据库的性能。

优化随机写的两种方法：

• 先写日志，优化随机写为顺序写
• 采用缓存，将每次写改为批量写

WAL机制：

MySQL 采用 WAL (write-ahead logging)机制来优化数据持久化到磁盘的操作

1. 事务修改数据页之后将更新内容通过WAL机制将随机的脏页写入变成顺序的日志写入，这就是redo log。在数据刷盘之前出现数据库崩溃的话，可以从redo log中恢复数据。

2. 在redo log 刷盘的过程中，通过buffer将每次写入改为批量写入。

redo log 的落盘过程：

1. 事务提交，事务修改的数据页会先写入log buffer
2. MySQL将log buffer 中的数据写入到os cache中
3. 按照log buffer的刷盘设置，操作系统决定何时将数据写入到磁盘的redo log

这个过程有可能丢失数据：事务提交时，将数据写入log buffer, MySQL认为事务提交成功

• 如果写入log buffer的数据，在写入os cache之前，数据库崩溃，会丢失数据
• 如果写入os cache的数据，在刷到磁盘之前，操作系统崩溃，会丢失数据

3

优化方法：

• 增加 log buffer 大小:

  调整 innodb_log_buffer_size 参数，增大 log buffer 的大小，减少日志刷新的频率，尤其是在高并发的情况下可以减少磁盘写操作。

• 避免大事务，事务越大需要的占用的内存就越多，刷盘过程也会越长，同时还是长时间占用锁，因此要在满足业务的基础上改大事务为多个小事务。

锁争用

1. 当多个事务同时修改相同的数据或表时，可能会产生锁争用，导致事务提交延迟。

2. 当一个大事务提交时，由于长时间占用锁，也会导致其他事务修改相同表时出现延迟

 优化方法:

• 减少事务持锁时间: 优化事务逻辑，确保事务尽可能快地提交，减少锁持有时间。

• 采用合适的隔离级别: 根据业务需求，选择合适的隔离级别（如从可重复读调整为读已提交），减少锁冲突。

关于MySQL锁我之前有一篇专门讲锁的文章

CPU瓶颈

在高并发场景下，CPU负载可能会因为处理大量事务的提交而过载，导致事务提交速度变慢。

优化方法:

• 增加CPU核心数量: 提升系统的硬件资源，使用更多CPU核心来处理并发事务。

• 优化SQL语句: 通过优化SQL语句，减少不必要的CPU消耗，减轻系统负载。

内存瓶颈

内存不足会导致数据频繁交换到磁盘（swap），进而影响数据库性能。尤其在大事务或高并发场景下，内存的有效利用至关重要。

优化方法:

• 增大内存: 提供足够的内存，以确保MySQL的缓冲池（InnoDB Buffer Pool）和日志缓冲区能够高效工作。

• 调整 innodb_buffer_pool_size: 合理设置InnoDB的缓冲池大小，以确保足够的内存用于缓存数据页和索引，从而减少磁盘访问。

网络延迟

如果事务提交需要与远程服务器或备库同步（如主从复制架构的半同步和增强半同步模式），网络延迟可能会影响事务的最终提交速度。

优化方法:

• 优化网络配置: 减少网络延迟，特别是在主从复制或分布式数据库场景下。

• 异步复制: 考虑使用异步复制方式来避免网络延迟对主库事务提交的影响。（需要结合具体的业务来考虑）

【以上仅为个人观点，如有不同意见，欢迎留言讨论！】

点击蓝字 关注我们