MySQL at Facebook

MySQL for… queues!

FOQS: Making a distributed priority queue disaster-ready FOQS has evolved from a regional to a global deployment for highly available data, even through large-scale disaster scenarios.

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, Facebook’s open source caching engine for web-scale services CacheLib is a pluggable in-process caching engine to build and scale high-performance services collaboratively.

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

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

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.

vldb.org

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.

catching top waits 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…

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.

Issue790: MultiGet-MRR implementation (#1041) · facebook/mysql-5.6@45110dc 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...

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

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.

Saving With MyRocks in The Cloud 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…

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.

Migrating Messenger storage to optimize performance 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.

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/mysql-server mysql-server - MySQL Server, the world's most popular open source database, and MySQL Cluster, a real-time, open source transactional database.

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

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

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

InnoDB: MVCC has O(N^2) behaviors fix [84958] by midenok · Pull Request #719 · facebook/mysql-5.6 mysql-5.6 - Facebook's branch of the Oracle MySQL v5.6 database. This includes MyRocks.