优化 AWS Aurora 实例的写入性能 [英] Optimize write performance for AWS Aurora instance

问题描述

我有一个 AWS Aurora 数据库集群正在运行，它 99.9% 专注于写入.在它的高峰期，它将运行 2-3k 写入/秒.

I've got an AWS Aurora DB cluster running that is 99.9% focused on writes. At it's peak, it will be running 2-3k writes/sec.

我知道 Aurora 在默认情况下针对写入进行了一些优化，但作为 AWS 的新手，我想问一下 - Aurora 的写入性能有哪些最佳实践/技巧?

I know Aurora is somewhat optimized by default for writes, but I wanted to ask as a relative newcomer to AWS - what are some best practices/tips for write performance with Aurora?

推荐答案

根据我的经验，Amazon Aurora 不适合运行具有大量写入流量的数据库.至少在 2017 年左右实施.也许它会随着时间的推移而改进.

From my experience, Amazon Aurora is unsuited to running a database with heavy write traffic. At least in its implementation circa 2017. Maybe it'll improve over time.

我在 2017 年早些时候为一个写入繁重的应用程序进行了一些基准测试，我们发现 RDS(非 Aurora)在写入性能上远远优于 Aurora，考虑到我们的应用程序和数据库.基本上，Aurora 比 RDS 慢两个数量级.亚马逊声称 Aurora 的高性能显然完全是营销驱动的胡说八道.

I worked on some benchmarks for a write-heavy application earlier in 2017, and we found that RDS (non-Aurora) was far superior to Aurora on write performance, given our application and database. Basically, Aurora was two orders of magnitude slower than RDS. Amazon's claims of high performance for Aurora are apparently completely marketing-driven bullshit.

2016 年 11 月，我参加了在拉斯维加斯举行的 Amazon re:Invent 大会.我试图找到一位知识渊博的 Aurora 工程师来回答我关于性能的问题.我能找到的只有初级工程师，他们被命令重复声称 Aurora 比 MySQL 快 5-10 倍.

In November 2016, I attended the Amazon re:Invent conference in Las Vegas. I tried to find a knowledgeable Aurora engineer to answer my questions about performance. All I could find were junior engineers who had been ordered to repeat the claim that Aurora is magically 5-10x faster than MySQL.

2017 年 4 月，我参加了 Percona Live 会议，看到了一个关于如何使用标准 MySQL 和 CEPH 开发类似 Aurora 的分布式存储架构的演示，用于开源分布式存储层.这里有一个关于同一主题的网络研讨会:https://www.percona.com/resources/webinars/mysql-and-ceph，由我在会议上看到的工程师 Yves Trudeau 共同主持.

In April 2017, I attended the Percona Live conference and saw a presentation about how to develop an Aurora-like distributed storage architecture using standard MySQL with CEPH for an open-source distributed storage layer. There's a webinar on the same topic here: https://www.percona.com/resources/webinars/mysql-and-ceph, co-presented by Yves Trudeau, the engineer I saw speak at the conference.

关于将 MySQL 与 CEPH 一起使用变得清晰的是，工程师必须禁用 MySQL 更改缓冲区 因为没有办法缓存对二级索引的更改，同时也有分布式存储.这导致写入具有二级(非唯一)索引的表的巨大性能问题.

What became clear about using MySQL with CEPH is that the engineers had to disable the MySQL change buffer because there's no way to cache changes to secondary indexes, while also have the storage distributed. This caused huge performance problems for writes to tables that have secondary (non-unique) indexes.

这与我们在使用 Aurora 对我们的应用程序进行基准测试时看到的性能问题一致.我们的数据库有很多二级索引.

This was consistent with the performance problems we saw in benchmarking our application with Aurora. Our database had a lot of secondary indexes.

因此，如果您绝对必须将 Aurora 用于具有高写入流量的数据库，我建议您首先要做的是删除所有二级索引.

So if you absolutely have to use Aurora for a database that has high write traffic, I recommend the first thing you must do is drop all your secondary indexes.

显然，如果需要索引来优化您的某些查询，这是一个问题.当然 SELECT 查询，但也有一些 UPDATE 和 DELETE 查询可能使用二级索引.

Obviously, this is a problem if the indexes are needed to optimize some of your queries. Both SELECT queries of course, but also some UPDATE and DELETE queries may use secondary indexes.

一种策略可能是制作 Aurora 集群的非 Aurora 只读副本，并仅在只读副本中创建二级索引以支持您的 SELECT 查询.我从未这样做过，但根据 https://aws.amazon.com/premiumsupport/knowledge-center/enable-binary-logging-aurora/

One strategy might be to make a non-Aurora read replica of your Aurora cluster, and create the secondary indexes only in the read replica to support your SELECT queries. I've never done this, but apparently it's possible, according to https://aws.amazon.com/premiumsupport/knowledge-center/enable-binary-logging-aurora/

但这仍然无助于您的 UPDATE/DELETE 语句需要二级索引的情况.我对这种情况没有任何建议.你可能不走运.

But this still doesn't help cases where your UPDATE/DELETE statements need secondary indexes. I don't have any suggestion for that scenario. You might be out of luck.

我的结论是，我不会选择将 Aurora 用于需要大量写入的应用程序.也许这会在未来改变.

My conclusion is that I wouldn't choose to use Aurora for a write-heavy application. Maybe that will change in the future.

2021 年 4 月更新:

Update April 2021:

自从撰写上述内容以来，我已经针对 Aurora 版本 2 运行了 sysbench 基准测试.我无法分享具体数字，但我得出的结论是，当前的 Aurora 改进更适合写入繁重的工作负载.我确实运行了许多二级索引的测试以确保.但我鼓励任何认真采用 Aurora 的人来运行他们自己的基准测试.

Since writing the above, I have run sysbench benchmarks against Aurora version 2. I can't share the specific numbers, but I conclude that current Aurora improvements are better for write-heavy workload. I did run tests with lots of secondary indexes to make sure. But I encourage anyone serious about adopting Aurora to run their own benchmarks.

至少，Aurora 比使用 EBS 存储的传统 Amazon RDS for MySQL 要好得多.这可能是他们声称 Aurora 比 MySQL 快 5 倍的地方.但是 Aurora 并不比我测试的其他一些替代方案快，而且实际上无法匹敌:

At least, Aurora is much better than conventional Amazon RDS for MySQL using EBS storage. That's probably where they claim Aurora is 5x faster than MySQL. But Aurora is no faster than some other alternatives I tested, and in fact cannot match:

• MySQL Server 将自己安装在使用本地存储的 EC2 实例上，尤其是具有本地附加 NVMe 的 i3 实例.我知道实例存储不可靠，因此需要运行冗余节点.

• MySQL Server installed myself on EC2 instances using local storage, especially i3 instances with locally-attached NVMe. I understand instance storage is not dependable, so one would need to run redundant nodes.

MySQL Server 使用直连 SSD 存储将自己安装在我们数据中心的物理主机上.

MySQL Server installed myself on physical hosts in our data center, using direct-attached SSD storage.

使用 Aurora 作为托管云数据库的价值不仅仅在于性能.它还具有自动监控、备份、故障转移、升级等功能.

The value of using Aurora as a managed cloud database is not just about performance. It also has automated monitoring, backups, failover, upgrades, etc.

这篇关于优化 AWS Aurora 实例的写入性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！