Etsy 使用 Vitess 的過程

Etsy 寫了三偏關於使用 Vitess 解決資料庫效能問題的文章:「Scaling Etsy Payments with Vitess: Part 1 – The Data Model」、「Scaling Etsy Payments with Vitess: Part 2 – The “Seamless” Migration」、「Scaling Etsy Payments with Vitess: Part 3 – Reducing Cutover Risk」。

Vitess 是 YouTube 團隊開發出來的東西,試著透過一層 proxy 解決後端 MySQL 資料庫在 sharding 後查詢邏輯的問題。

有一些地方的資訊整理出來:

首先是現代暴力解的能耐,從維基百科可以查到 Etsy 在 2015 年就上市了,但到了 2020 年年底撞到 vertically scaling 的天花板 (這邊是指 GCP 的上限),可以看到現在的暴力法可以撐超久... 如果再多考慮到實體機房的話應該可以找到更大台的機器。

第二個是 Etsy 在 2020 年年底開始從資料庫搬資料,一路到 2022 年五月,算起來差不多搬了一年半,總共轉移了 4 個 database 到 Vitess 的 cluster 上,共 23 張表格與 40B rows。

第三個是利用 Vindexes 這個技術降低 sharding 時所帶來的限制。這個之前沒研究過:

A Vindex provides a way to map a column value to a keyspace ID.

從「Older Version Docs」這邊翻舊版的文件,發現 5.0+ 都有,再往 GitHub 上面的資料翻,看起來從 2016 年的版本就有了,不過當時看起來還一直在擴充:「Vitess v2.0.0-rc.1」。

回來看現在的功能,有 primary vindex 的設計:

The Primary Vindex for a table is analogous to a database primary key. Every sharded table must have one defined. A Primary Vindex must be unique: given an input value, it must produce a single keyspace ID.

然後是 secondary vindex(es) 的設計,指到 keyspace id(s),然後這個資訊會被用在 routing 上:

Secondary Vindexes are additional vindexes against other columns of a table offering optimizations for WHERE clauses that do not use the Primary Vindex. Secondary Vindexes return a single or a limited set of keyspace IDs which will allow VTGate to only target shards where the relevant data is present. In the absence of a Secondary Vindex, VTGate would have to send the query to all shards (called a scatter query).

It is important to note that Secondary Vindexes are only used for making routing decisions. The underlying database shards will most likely need traditional indexes on those same columns, to allow efficient retrieval from the table on the underlying MySQL instances.

然後是 functional vindex 與 lookup vindex,前者用演算法定義 keyspace id,後者讓你查:

A Functional Vindex is a vindex where the column value to keyspace ID mapping is pre-established, typically through an algorithmic function. In contrast, a Lookup Vindex is a vindex that provides the ability to create an association between a value and a keyspace ID, and recall it later when needed. Lookup Vindexes are sometimes also informally referred to as cross-shard indexes.

然後 lookup vindex 還有對 consistent hashing 的支援:

Consistent lookup vindexes use an alternate approach that makes use of careful locking and transaction sequences to guarantee consistency without using 2PC. This gives the best of both worlds, with the benefit of a consistent cross-shard vindex without paying the price of 2PC. To read more about what makes a consistent lookup vindex different from a standard lookup vindex read our consistent lookup vindexes design documentation.

這樣整體看起來,Vitess 把所有常見的 sharding 方式都包進去了,如果以後真的遇到這個量的話,也不需要自己在 application 或是 library 做一堆事情了...

低 Downtime 將 4TB 的 PostgreSQL 9.6 資料庫升級到 13 的故事

前幾天在 Hacker News 首頁上看到的文章,講怎麼把一個 4TB 的 PostgreSQL 從 9.6 升級到 13 的故事:「How we upgraded our 4TB Postgres database (retool.com)」,原文在「How Retool upgraded our 4 TB main application PostgreSQL database」,翻了一下 LinkedIn,這篇文章的作者 Peter Johnston 在 Retool 掛的是 Security Software Engineer,另外他也有在 Hacker News 上的討論出現 (帳號是 mrbabbage),可以搜尋翻翻看他的回覆。

看完文章後發現方法的概念其實不難,主要是要找到對的工具來用。基本的想法是先生出一個 initial dump,然後架構 logical replication,接下來就是處理各種因為在 4TB data 這個 scale 下會遇到的問題。

主要用到的工具是 Citus Data 的 Warp:「Citus warp: Database migrations without the pain」,不過這個工具的限制是表格必須都有 single column primary key,所以他們為了這次轉移也有小改 database schema 配合 Warp 的要求:

We had to do a bit of finagling to coax Warp into processing our database. Warp expects all tables to have a single column primary key, so we had to convert compound primary keys into unique constraints and add scalar primary keys. Otherwise, Warp was very straightforward to use.

另外針對比較大的兩個 append-only 的表格 (分別是 2TB 與 x00 GB) 做處理,在 initial dump 的階段不對這兩個表格做 replication,而是透過自製的 Python script 搬移:

To handle the two massive tables we skipped in Warp, we wrote a Python script to bulk transfer data from the old database server to the new.

然後是 foreign key 相關的關閉與重啟,這算是 RDBMS 在大量資料的 dump & restore 的標準作業了:

As you can see from the runbook above, one of the steps we had to do was to turn off and then re-enable foreign key constraint checks.

最後執行下去,整個 downtime 只有十五分鐘:

We scheduled the maintenance window late on Saturday, October 23rd, at the lowest period of Retool cloud traffic. With the configuration described above, we were able to bring up a new database server at version 13 in around 15 minutes, subscribed to changes at our 9.6 primary with logical decoding.

另外也有提到有計畫要 sharding,之後 main database 就有機會被拆小:

We anticipate we’ll have sharded our database by the end of that support window, and be performing our next substantial version upgrades incrementally.

整個計畫的核心概念不難,主要是要怎麼順出來並且執行...

GitHub 的 MySQL 架構與數字

前幾天 GitHub 有寫一篇文章提到他們的 MySQL 是怎麼 scale 的,另外裡面也有一些數字可以看:「Partitioning GitHub’s relational databases to handle scale」。

他們最主要的 database cluster 叫做 mysql1,裡面有提到 2019 年的時候這個 cluster 是 950K qps,其中 primary 有 50K qps:

In 2019, mysql1 answered 950,000 queries/s on average, 900,000 queries/s on replicas, and 50,000 queries/s on the primary.

在 2021 年的時候變成 1.125M qps,其中 75K qps 在 primary 上:

Today, in 2021, the same database tables are spread across several clusters. In two years, they saw continued growth, accelerating year-over-year. All hosts of these clusters combined answer 1,200,000 queries/s on average (1,125,000 queries/s on replicas, 75,000 queries/s on the primaries). At the same time, the average load on each host halved.

另外這幾年比較成熟的方案都拿出來用了,包括用 ProxySQL 降低連線數的壓力 (connection pool 的概念):

[W]e started using ProxySQL to reduce the number of connections opened against our primary MySQL instances.

ProxySQL is used for multiplexing client connections to MySQL primaries.

另外用 Vitess 協助 sharding 之間的轉移:

Vitess is a scaling layer on top of MySQL that helps with sharding needs. We use its vertical sharding feature to move sets of tables together in production without downtime.

這兩套應該是已經蠻成熟的了... 另外也可以發現老方法還是很好用,就算在 GitHub 這種量還是可以暴力解決很多事情。

PostgreSQL 的 scale 建議

Hacker News Daily 上看到「Postgres scaling advice for 2021」這篇,講 PostgreSQL 要怎麼 scale,在 Hacker News 上也有對應的討論可以看:「Postgres scaling advice (cybertec-postgresql.com)」。

文章前面先提到分散式系統的複雜度會導致 RDBMS 上的一些假設失效,所以如果可以用單台機器暴力解,就儘量用單台機器來解 (scale up 的情境),裡面就提到了一些「暴力可以解決很多問題」的說明,差不多就是前幾天提到的「Let's Encrypt 升級資料庫伺服器 (AMD YES?)」。

後面提到如果真的要放進分散式的 RDBMS (scale out 的情境),怎麼設計資料結構會比較好。

這邊剛好也可以提一下,量夠大的時候要把 OLTPOLAP 的應用分開,現在有很多 OLAP 資料庫可以選擇,同步的工具也很成熟了,通常效能會比在 OLTP 上面硬跑來的好。

最後提一下,文章裡面對於 transaction per second 可以拉很高,有些假設沒有明寫出來。這需要盡可能把 transaction 拆小,避免常常有 giant transaction 卡住整個資料庫,這點對於一般的系統會需要做不少改寫...

不過最後比較疑惑的是,這種文章怎麼會上 Hacker News 的啊...

AWS Cloud 的用法

Hacker News Daily 上看到這則,分享了 AWS (他的前東家,超過八年) 的使用經驗:

除了可以在 Twitter 上看以外,也可以用 Thread reader 直接讀整條 thread,應該也還算清楚:「This is how I use the good parts of @awscloud, while filtering out all the distracting hype.」。

這邊的經驗談主要是在 web 與 app 相關的服務這塊:

有講到 AWS 的業務其實圍繞在 scalability 上發展,但這對 startup 可能反而是扣分,因為暴力法解反而可以大幅簡化架構換得 agile (而讓 startup 存活下來)。

另外從團隊的開發成本來看,這些 scale 的技術增加了開發成本,產生了很多開發上的限制,這些觀點也有點帶到「Premature optimization is the root of all evil」在講的事情:

最後的結論可以看到一些列表:

除了 DynamoDB 的意見不同外 (這邊提到的 DDB),其他的我都可以接受...

RDS 支援 Storage Auto Scaling

Amazon RDS 推出了 Storage Auto Scaling:「Amazon RDS now supports Storage Auto Scaling」。

看起來傳統 RDBMS 類的都支援 (也就是非 Aurora 的這些):

Starting today, Amazon RDS for MariaDB, Amazon RDS for MySQL, Amazon RDS for PostgreSQL, Amazon RDS for SQL Server and Amazon RDS for Oracle support RDS Storage Auto Scaling.

仔細看了一下新聞稿,裡面都只有提到 scale up,沒有提到 scale down,這個功能應該是只會提昇不會下降,所以要注意突然用很多空間,再砍掉後的問題:

RDS Storage Auto Scaling automatically scales storage capacity in response to growing database workloads, with zero downtime.

RDS Storage Auto Scaling continuously monitors actual storage consumption, and scales capacity up automatically when actual utilization approaches provisioned storage capacity.

除了香港外的所有商業區域都提供:

RDS Storage Auto Scaling is available in all commercial AWS regions except in Asia Pacific (Hong Kong) and AWS GovCloud.

DynamoDB Autoscaling 的各種眉眉角角...

AdRollDynamoDB Autoscaling 的踩雷記錄,裡面有些資訊如果不是跳下去玩應該不會注意到 (魔鬼藏在細節裡的感覺):「Managing DynamoDB Autoscaling with Lambda and Cloudwatch」。

第一個提到的問題是 autoscaling 的觀察對象:

Ideally, the table should scale based on the number of requests that we are making , not the number of requests that are successful.

另外一個是 autoscaling 遇到完全不用的情況下不會 scale down,看起來是某種保護機制。但這使得平常只有拿來讀取的表格在跑完 batch job 後得自己處理 write scale down 問題:

Additionally, at the time of implementing this algorithm, the DynamoDB capacity could not be brought down automatically if the consumption was exactly zero, which can happen if you write to your table in batch instead of realtime, for example.

This meant that, when enabling autoscaling, tables that were read in realtime, but written to in batch, still needed manual intervention to bring the write capacity down after our jobs were done writing.

另外一個問題是 scale down 是有次數限制的:

Another interesting point that might bite users is that capacity decreases are an expensive operation for AWS, so they’re limited.

The number of decreases cited in the documentation can be achieved under very special conditions, since you need to have 4 decreases in the first hour of the day plus one for each of the remaining hours, for a total of 4 (first hour) + 23 (1 hourly) = 27.

後面就是自己研究什麼 algorithm 可以調整的更細,然後用 lambda 重寫... 最後省下 30% 的成本:

Here is where we detected our costs for our batch tables dropping to around 30% of the initial cost.

AdRoll 的規模應該是不小,所以為了省 30% 可以花不少力氣在上面...

Aurora Serverless MySQL 進入 GA

AWS 宣佈能 auto-scale 的 Aurora Serverless MySQL 進入 GA:「Aurora Serverless MySQL Generally Available」:

不過目前開放的區域有限:

Aurora Serverless for Aurora MySQL is available now in US East (N. Virginia), US East (Ohio), US West (Oregon), Europe (Ireland).

以秒計費,但低消是 5 分鐘:

You pay a flat rate per second of ACU usage, with a minimum of 5 minutes of usage each time the database is activated.

us-east-1 的價錢來看,每個 ACU 是 USD$0.06/hour,而每個 ACU 大約是 standard instance 的價錢:

1 ACU has approximately 2 GB of memory with corresponding CPU and networking, similar to what is used in Aurora Standard instances.

但這沒看懂,是 db.t2.small 還是 db.t2.medium?另外比較是全速還是 small 的 20% 或 medium 的 40%?這部份也許還要再問看看才知道...

storage 與 I/O 的費用則是相同,倒是不用比較這塊... 再來不知道有沒有推出 Reserved ACU 的計畫,光是一年付清就差蠻多的。

要不要換過去其實還是要看看使用的量,以及可以接受的成本來決定...

Amazon Elasticsearch 支援 I3 instance (i.e. 1.5 PB Disk) 了

Amazon Elasticsearch 支援 I3 instance 了:「Run Petabyte-Scale Clusters on Amazon Elasticsearch Service Using I3 instances」。

Amazon Elasticsearch Service now supports I3 instances, allowing you to store up to 1.5 petabytes of data in a single Elasticsearch cluster for large log analytics workloads.

i3.16xlarge 單台是 15.2 TB 的硬碟空間,100 台就會是 1.5 PB,不知道跑起來會多慢 XDDD

Amazon Elasticsearch Service – Amazon Web Services (AWS) | FAQs 這邊還沒修正 XD:

You can request a service limit increase up to 100 instances per domain by creating a case with the AWS Support Center. With 100 instances, you can allocate about 150 TB of EBS storage to a single domain.

大型 WordPress 站台會用到的 LudicrousDB (以及 HyperDB)

最近收到 HyperDB 的 mailing list 信件 (開頭是「[HyperDB] How can I set up HyperDB with latest version.」這封),有人提到 HyperDB 很久沒更新了... 結果在信理看到有人回了「stuttter/ludicrousdb」這個專案:

LudicrousDB is an advanced database interface for WordPress that supports replication, failover, load balancing, & partitioning

兩個專案都是抽換掉 WordPress 在處理 database 的 library,然後希望自己控制 master/slave 的讀寫分離以及各機房之間的處理 (還有 replication lag),而不要靠 ProxySQL 這類工具來做 (以時間來看,當初他們發展這些工具時,ProxySQL 這類的方案也還不夠成熟,大家都不會想要選這個方向...)。

先記錄下來,如果之後有遇到時可以當作是一個選項...