How Quora scaled MySQL

How Quora scaled their MySQL database to support 100k+ queries per second.

Hi Friends,

Welcome to the 83rd issue of the Polymathic Engineers newsletter.

This week, we discuss an interesting case study for system design: how Quora scaled their MySQL database to support 100k+ queries per second.

Quora is a popular question-and-answer platform that connects people seeking knowledge with those with the expertise to share.

Since its launch in 2009, the platform has grown exponentially, with millions of people adding questions and answers on various subjects.

Quora initially relied on a straightforward MySQL setup, which worked well during its early stages. However, as the number of users grew, it became clear that a single MySQL database instance had too many limitations.

The database needed to handle high read and write volumes, maintain data integrity, and ensure quick response times. To deal with these problems, Quora used several strategies:

• Sharding

• Optimize read operations

• Optimize space used by tables

Sharding

Sharding is a technique to split a database horizontally by distributing the data across multiple servers. Each server contains a subset of the data (or shard), allowing queries and transactions to be processed in parallel.

Initially, Quora split the data by tables. Whenever a table became very large or had too much traffic, a new partition was created for that table.

Each partition consisted of a master node and multiple replicas. The mapping from a partition to the list of tables in each partition was stored using ZooKeeper.

The process for creating a new partition was pretty simple:

1. Dump the table in a single transaction along with the current binary log position

2. Restore the dump on the new partition

3. Replay the binary log from the position in the dump to the present to restore any writes that happened after the dump

4. Set the table's location to the new partition in ZooKeeper and direct queries to it.

This approach worked well because it was straightforward to undo mistakes, but it also had disadvantages. Joins were strongly discouraged because two tables could be joined only in the same partition. In addition, working with very large tables was still very challenging.

This is why Quora engineers looked into solutions to split individual tables. They decided to build their own sharding solution without using an out-of-the-box solution like Vitess. They had only a few tables to split and felt implementing their solution would be faster than developing expertise in the third-party solution.

Different partitioning criteria can be used to split up the rows in a table. The most popular ones are range-based, which splits up the table rows based on whether the partition key is in a particular range, and hash-based, which applies a hash function to the key.

Quora decided to use range-based sharding since they frequently used range queries. The steps they followed for splitting a table were similar to those used to create a partition, with the addition of a testing phase.

The purpose of the testing phase was to read/write from/to both the sharded and original tables to check that the results were the same.

Optimize read operations

Since Quora has a read-heavy workload, optimizing read operations was a priority.

The approach for complex queries like joins and aggregations was to rewrite them to use less CPU and memory. 

However, this was not enough to deal with many queries per second. In most cases, a high percentage of cache misses caused unnecessary database load, and the solution was to implement a more efficient caching strategy.

As an example of optimization, consider that Quora supports many languages and needs to check which languages each user uses. Initially, they used pairs (user_id, language_id) as a key and a Yes/No as a value. 

However, this choice led to a large key space (all user_ids multiplied by all language_ids) and resulted in a huge number of cache misses. 

A more efficient way to use the cache was to use just the user ID as a key and change the value to a list of languages the user uses. This increased the size of the value a bit but allowed a significantly higher cache hit rate. 

Optimize space used by tables

It is crucial to have optimal data retention policies and optimize the way data is stored on the disk to deal with large tables. 

Since MySQL allows you to swap out the storage engine, Quora engineers decided to replace the default engine, InnoDB, with Rocks DB. 

The main problem with InnoDB was that the page size was fixed, so the space was wasted whenever the data didn’t fit the page size. TocksDb instead supports variable page size, optimizing the space usage and allowing for efficient data compression.

References

• MySQL sharding at Quora

• Optimizing the databases at Quora

Food for thoughts

I haven't. seen such a thing happening. However, I think a pair programming or code review session on a video call would be a good way to see if this is true for a software developer position.

People who think testing impacts development time assume that testing is something different from development. But it's not. Testing is an integral part of development.

When I hear about such stories, I'm skeptical as well. Using LLMs for snippets or boilerplate code is fine, but every time I need to do something of medium difficulty I still have to do the job. I can't imagine building an entire application only with them without having any knowledge. Link

Interesting Readings

These are some interesting articles I read this week:

• A good System Design is not about getting more instances running by

  Raul Junco

• 7 Techniques for Database Performance & Scaling by

  Saurabh Dashora

• The temple is not a cigarette by

  Alvin

• Common mistakes by doing coding challenges by

  John Crickett