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

问题描述

我有一个运行中的99.9％的AWS Aurora数据库集群专注于写入.在高峰时，它将以每秒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/资源/网络研讨会/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.

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