MySQL at Facebook

01/19/2022

MySQL for… queues!

FOQS has evolved from a regional to a global deployment for highly available data, even through large-scale disaster scenarios.

09/02/2021

Many efforts of fine-tuning caches (write-through or look-aside) ended up in this now open-source library. We don't use it inside MySQL, but quite a few of clients above us do.

CacheLib is a pluggable in-process caching engine to build and scale high-performance services collaboratively.

07/22/2021

Our major MySQL version upgrades are significant undertakings and this post from Herman and Pradeep tells why

We are sharing how we tackled the migration to MySQL 8.0 — and some of the surprises we had in the process.

09/01/2020

MyRocks paper at VLDB

We published a paper "MyRocks: LSM-Tree Database Storage Engine Serving Facebook's Social Graph" at VLDB this year. You can download the paper fromhttp://www.vldb.org/pvldb/vol13/p3217-matsunobu.pdf . Our MyRocks paper received Honorable Mention Award at the VLDB Industry Track.

The paper covers collections of our development, deployment and improvement of MyRocks in our social database (UDB). The following topics are covered.

- UDB overview, challenges with B-Tree indexes and why we thought LSM-tree database optimized for flash storage was suitable (Section 2).
- How we optimized MyRocks for various read workloads and compactions (Section 3).
- How we migrated to MyRocks in production (Section 4).
- Then we show migration results in Section 5, followed by lessons learned in Section 6. Finally, we show related work in Section 7, and concluding remarks in Section 8.

I'm confident that it is a good paper, and is worth reading for MySQL and Database communities.

07/06/2020

In our quest for service quality we're great fans of new inspection techniques in Linux - like BPF, and all the work Brendan Gregg did popularize them.
One neat trick is to figure out longest stalls per each stack trace in the system and see if they correlate with system outages.
Some gnarly stuff was uncovered in various components just by reviewing information provided by the top-waits inspection.

Modern systems are complicated beasts with lots of interdependent activities between threads, programs and kernels. Figuring out some problems is nearly impossible without building some time machin…

01/22/2020

We've started experimenting with Multi-Range-Reads in MyRocks.
MySQL optimizer can pass multiple ranges to storage engines, but default implementation in various storage engines just makes a nested loop of single range gets - this was initially a big deal for NBD (MySQL Cluster).
If we pass multiple ranges, we can indicate a larger batch of operations we are going to do, and for higher index dive setup costs in MyRocks (it possibly has to read index blocks and bloom filter blocks of multiple levels of data, instead of single B-Tree dive) that kind of batching can be very useful.
We added multi-get interface to RocksDB (and Jens Axboe already contributed io_uring support for that) - so by plugging through we can have a single query issue multiple I/Os in parallel for different ranges.
There're more wins that can be done with this kind of batching - there're cache efficiencies from testing index block or bloom filter with multiple keys, block cache LRU management gets cheaper as some of these blocks are checked out for the whole batch and not per individual key dive, etc.
In production we see that multiple concurrent queries hitting same range (via secondary index lookup or a join). That kind of workload ends up thrashing on index blocks's LRU shard mutex. Checking out the block less frequently by holding onto it during MRR helps us there.
We have to be careful not to do too much of prefetching/reading for queries that have LIMIT clause on them - but other than that there're wins all over the place.
Initially we had a bit of miscalculation with our limits and there was a possibility to pin your whole buffer pool to single query. Adding proper limits, checks for kill flag and tuning the optimizer choices is part of effort to roll features like that live.
This work is a collaboration with Sergey Petrunia from MariaDB.

Summary: First implementation. Supports scans on full primary key Cost-based choice whether to use MRR/BKA is not supported (set mrr=on,mrr_cost_based=off) Pull Request resolved: https://github.com...

01/15/2020

One of most exciting features that we'll have a trouble to upstream (will never happen) is dependency replication - we are able to have intra-shard parallel replication. That requires row based replication and can be done in two modes - one is table-level, where we can apply changes to different tables in parallel.
Another one, much more fun, is statement-based, where replication threads analyze lock acquisitions and can order transactions in a right way and apply them concurrently.
This can speed up replication significantly for pretty much any workload.
This work has been collaboration between Abhinav and Jose M. Faleiro who was a post-doc at UC Berkeley at the time.

Update: slides from 2018 conference talk - https://www.percona.com/live/e18/sessions/faster-mysql-replication-using-row-dependencies

08/01/2018

Percona wrote a great blog post about how MyRocks can save cost in the Cloud environments, where cost is calculated based on IOPS. In general, MyRocks can reach same throughput as InnoDB with much fewer IOPS and Percona has shown benchmarks about it.

The main focus of a previous blog post was the performance of MyRocks when using fast SSD devices. However, I figured that MyRocks would be beneficial for use in cloud workloads, where storage is e…

06/26/2018

Messenger storage migrated from HBase to MySQL/MyRocks. Do note, Iris buffer is backed by MySQL/InnoDB - so we have a hybrid MySQL system behind Messenger now.

To improve Messenger performance and add new features, we migrated more than 1 billion accounts to MyRocks, using two migration flows to ensure that ever single user was successfully moved over.

05/22/2018

MySQL 8.0 introduces new protobufs based Protocol X. No wonder one thought we need a new protocol - e.g. functions that were putting integers on the wire did not get much makeover for past 16 years and are somewhat... naive.
There're various ways to improve C++ code, one could use new CPU instructions, but some optimizations techniques are more straightforward, like ones in this five year old article:
https://code.facebook.com/posts/206591859504673/three-optimization-tips-for-c-/

mysql-server - MySQL Server, the world's most popular open source database, and MySQL Cluster, a real-time, open source transactional database.

12/12/2017

Making MySQL work with bigger instance size (~100TB) is an interesting technical challenge. With bigger instance support, you can continue to use important RDBMS features like transactions, joins, and secondary keys, without worrying about sharding (at least for small-mid size applications). To make single 100TB MySQL instance really work in production, lots of features are needed. I'm looking forward at least some of them will be supported in the near future. --
https://yoshinorimatsunobu.blogspot.com/2017/11/towards-bigger-small-data.html

In MySQL ecosystems, it has been a very common practice to shard MySQL databases by application keys, and to manage multiple small sized ...

10/26/2017

Percona fixed one of biggest InnoDB flaws (bug #84958) with this elegant patch. Thanks!

mysql-5.6 - Facebook's branch of the Oracle MySQL v5.6 database. This includes MyRocks.