How Etsy Scaled Their Database

Plus MIT's latest Intro to Deep Learning course, Logical Clocks in Distributed Systems, Twitter's Rec Algorithm and more.

Author

Arpan KG
April 04, 2023

Hey Everyone!

Today we’ll be talking about

  • Sharding MySQL at Etsy

    • In 2021, Etsy migrated 23 tables (over 40 billion rows) from MySQL to Vitess (an open source, managed sharding solution for MySQL)

    • Changing the Data Model to make Sharding Easier

    • Building Confidence in the Data Migration Process

    • Dealing with Potential Issues in the Migration

  • Tech Snippets

    • MIT’s Introduction to Deep Learning course - Recurrent Neural Networks, Transformers and Attention

    • How to be a -10x Engineer

    • Logical Clocks in Distributed Systems

    • Dealing with Collisions in Hash Tables

    • Twitter’s Recommendation Algorithm

How Modern Time Series Databases Work

Companies are collecting massive amounts of time series data and we’re currently seeing a huge amount of research in the field.

InfluxData recently released InfluxDB IOx, a completely new storage engine to power InfluxDB (the most popular Time Series DBMS according to DB-Engines).

IOx is written in Rust and is column-oriented, so it’s optimized for fast analytical queries and extremely high data ingestion. It’s also SQL-native, so it has a SQL parser, planner, execution engine, etc. to make it as accessible as possible.

The Apache Arrow ecosystem has been one of the coolest developments in data engineering and IOx was designed to integrate seamlessly with support for Arrow Flight SQL.

Paul Dix, InfluxData’s CTO, recently gave a talk about trends in the data engineering ecosystem and how that affected the design and development of InfluxDB IOx.

sponsored

Database Sharding at Etsy

Etsy is a popular online marketplace for handmade, vintage items, and craft supplies with millions of active buyers and sellers. In 2020, they experienced massive growth, and by the end of the year, their payments databases urgently needed horizontal scaling as two of their databases were no longer vertically scalable (they were already on the highest resource tier on GCP). Additional spikes in traffic could lead to performance issues or loss of transactions so Etsy needed a long term solution to fix this.

To tackle this issue, Etsy spent a year migrating 23 tables (with over 40 billion rows) from four payments databases into a single sharded environment managed by Vitess. Vitess is an open-source sharding system for MySQL, originally developed by YouTube.

Etsy software engineers published a series of blog posts discussing the changes they made to the data model, the risks they faced, and the process of transitioning to Vitess.

Sharding

Ideal Model

When sharding, the data model plays a crucial role in how easy implementation will be. The ideal data model is shallow, with a single root entity that all other entities reference via foreign key. By sharding based on this root entity, all related records can be placed on the same shard, minimizing the number of cross-shard operations. Cross-shard operations are inefficient and can make the system difficult to reason about.

For one of the databases, Etsy’s payments ledger, they used a shallow data model. The Etsy seller’s shop was the root entity and the team used the shop_id as the sharding key. By doing this, they were able to put related records together on the same shards.

Non-Ideal Model

However, Etsy's primary payments database had a complex data model that had grown/evolved over a decade to accommodate changing requirements, new features, and tight deadlines.

Each purchase could be related to multiple different shops/customers and payments were linked to various transaction types (Credit Card, PayPal, etc,). This made it challenging to shard.

The use of shop_id as the sharding key would have dispersed the data around a single payment across many different shards.

Etsy had two options to deal with this

  • Option 1 - Modify the structure of the data model to make it easier to shard. They considered creating a new entity called Payment that would group together all entities related to a specific payment. Then, they would shard everything by the payment_id to enable colocation of related data.

  • Option 2 - The second approach was to create sub-hierarchies in the data and then shard these smaller groups. They would use a transaction’s reference_id to shard the Credit Card and PayPal Transaction data. For payment data, they would use payment_id. After, the team would identify transaction shards and payment data shards that were related and collocate them.

The team found Option 2 to be faster to implement so they went with that. Using the already established primary keys to shard was much easier than changing the data model.

Additionally, Vitess has re-sharding features that make it easy to change your shard decisions in the future. Sharding based on the legacy payments data model was not a once-and-forever decision.

The Data Migration Process

After choosing the sharding method, Etsy had to migrate the data over to Vitess. They needed to have extreme confidence in the migration process and ensure that the system would function effectively after the switch.

Therefore, the team spun up a staging environment so they could test their migration process and run through it several times to find any potential issues/unknowns.

The engineers created 40 Vitess shards and used a clone of the production dataset to run through mock migrations. They documented the process and built confidence that they could safely wrangle the running production infrastructure.

They also ran test queries on the Vitess system to check behavior and estimate workload and then used VDiff to confirm data consistency during the mock migrations. VDiff lets you compare the contents of your MySQL tables between your source database and Vitess. It will report counts of missing/extra/unmatched rows.

To migrate the data from the source MySQL databases to sharded Vitess, the team relied on VReplication. This sets up streams that replicate all writes. Any writes to the source side would be replicated into the sharded destination hosts.

Additionally, any writes on the sharded replication side could be replicated to the source database. This helped the Etsy team have confidence that they could switch back to the original MySQL databases if the switchover wasn’t perfect. Both sides would stay in sync.

Potential Issues

During the migration mocks, the Etsy team found several challenges. They talked about these potential pitfalls and how they mitigated them.

  • Reverse VReplication Breaking - As mentioned previously, reverse VReplication meant that any changes on sharded MySQL would be written back to the original MySQL databases. This gave the Etsy team confidence that they could switch back if there were issues. However, this broke several times due to enforcement of MySQL unique keys. In the sharded database, unique keys were only enforcing per-shard uniqueness. This created a problem when VReplication attempted to write those rows back to the unsharded database and the unique keys would collide causing one of the writes to fail. They solved this problem by using Vitess’ solution for enforcing global uniqueness.

  • Scatter Queries - If you don’t include the sharding key in the query, Vitess will default to sending the query to all shards (a scatter query), This can be quite expensive. If you have a very large codebase with many types of queries, it can be easy to overlook adding the shard key to some and have a couple of scatter queries slip through. Etsy was able to solve this by configuring Vitess to prevent all scatter queries. A scatter query will only be allowed if it includes a specific comment in the query, so that scatter queries are only done intentionally.

In the end, the team was able to migrate over 40 billion rows of data to Vitess. They were able to reduce the load on individual machines massively and gave themselves room to scale for many years.

For more details, you can check out the full posts here.

How did you like this summary?

Your feedback really helps me improve curation for future emails.

Login or Subscribe to participate in polls.

It's About Time. Build on InfluxDB.

Working with large sets of time-stamped data has its challenges.

Fortunately, InfluxDB is a time series platform purpose-built to handle the unique workloads of time series data.

Using InfluxDB, developers can ingest billions of data points in real-time with unbounded cardinality, and store, analyze, and act on that data – all in a single database.

No matter what kind of time series data you’re working with – metrics, events, traces, or logs – InfluxDB Cloud provides a performant, elastic, serverless time series platform with the tools and features developers need. Native SQL compatibility makes it easy to get started with InfluxDB and to scale your solutions.

Companies like IBM, Cisco, and Robinhood all rely heavily on InfluxDB to build and manage responsive backend applications, to power predictive intelligence, and to monitor their systems for insights that they would otherwise miss.

See for yourself by quickly spinning up the platform and testing it out InfluxDB Cloud for free.

sponsored

Tech Snippets

Recurrent Neural Networks, Transformers and Attention

This is lecture 2 from MIT’s 2023 Introduction to Deep Learning course. The lecture covers sequential modeling and talks about RNNs, LSTMs and real world applications like music generation, sentiment classification and more.

https://youtu.be/ySEx_Bqxvvo

How to be a -10x Engineer

Inversion thinking is a mental model that involves looking at a problem from the opposite angle to gain new insights and solutions.

Instead of thinking about how to increase the productivity of your dev team, you could think about how to -10x it. Then, make sure your org isn’t doing any of that.

Great strategies include holding 10 engineers hostage in a technical discussion, adding 100 hours a week of busywork, encouraging context-switching and more.

taylor.town/-10x

Clocks and Causality - Ordering Events in Distributed Systems

In distributed systems, you need some way to synchronize events across your multiple machines. Logical clocks are a way of doing this. This is a fantastic article that delves into several types of logical clocks that are commonly used. It discusses Lamport clocks, Vector clocks and some variants.

www.exhypothesi.com/clocks-and-causality

Optimizing Open Addressing in Hash Tables

With hash tables, a common problem is dealing with collisions, where two items are hashed to the same value. Open addressing and separate chaining are two ways of dealing with collisions. This article delves into dealing with collisions, how to think about the tradeoffs and strategies you can use.

thenumb.at/Hashtables

Twitter's Recommendation Algorithm

Twitter recently open sourced a significant part of their recommendation algorithm on Twitter. This is a post from their engineering blog that gives a high level overview of how it works. It’s quite similar in structure to other recommendation algorithms (Instagram, Facebook, etc.) where there’s a sourcing step, a ranking step and then a final layer that checks filters and heuristics.

blog.twitter.com/engineering/en_us/topics/open-source/2023/twitter-recommendation-algorithm