Tag: mysql

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 做一堆事情了...