编程

关系型数据库性能优化- 提升慢插入速度

1147 2021-12-25 11:28:43

如果你使用MySQL数据库运行一个写频繁的应用,你可能会发现写操作增加的时候,服务器运行变得异常的慢。在本例中,我们基于Laravel 事件溯源服务(event sourced service), 通过读取数据表来写入事件。当进行大量写操作的时候,仅仅是少量数据的简单插入和更新都会有250ms. 如何加速这些慢速写操作?

使用支持高性能SSD存储的服务器

MySQL(默认设置下)在每次交易后将日志缓冲(log bufffer)写入磁盘。在高负载下,这会导致大量的小数据写操作,在传统的机械硬盘中会特别慢。

以我们为例,我们使用了普通尺寸的亚马逊RDS实例硬盘,他对写性能上有直接影响(更高的容量意味着更多的IOPS/写带宽)。增加磁盘容量将写操作的平均速度下降到50ms。

TL;DR: 确保服务器磁盘尽可能快

增加日志文件容量限制

默认innodb_log_file_size 被设为128M, 对于重插入环境不太友好。将其增加到比如500M会减少日志刷新(当写入磁盘的时候,尤其慢)。如果你插入大量数据,这点尤为重要。

在我们的案例中,修改日志容量实际帮助不太大,因为我们有很多小量的读操作(不足以频繁写满日志文件)

通过减少日志刷新频率延迟磁盘写操作

默认情况下,MySQL 会在每个单独的事务中将日志写入到磁盘。以我们为例,不可能将事务中的成批的插入进行打包,因此每一次单独的查询会导致一次磁盘写操作。

你可以通过将innodb_flush_log_at_trx_commit设置为 2 修改日志写入间隔。与默认情况下设置为1的相比,这个设置主要的问题是数据可能会丢失。 不过你也因此避免因为MySQL崩溃导致数据丢失,整个服务器掉电可能导致数据丢失。比如你设置刷新间隔为默认的1,你可能会丢失一秒钟的写操作因为服务器错误。

设置innodb_flush_log_at_trx_commit为2,我们可以将平均的写入时间从超过200ms降到50ms以下。这是一个较大的提升,如果你愿意多冒几秒钟数据丢失的风险或许还能挤出更多的速度。你可以通过将innodb_flush_log_at_timeout设置为你想要的间隔(以秒计算)来增加刷新间隔。

以我们为例,我们设置成5秒,因此插入平均时间下降到了5ms。相比原来的250ms, 是极大的提升,这5秒钟潜在的数据丢失风险是值得的。