jivid

Joined 13 years ago

Karma

114

Submissions

Member since

Feb 23

Production Engineer @ Facebook - MySQL Infrastructure Facebook: facebook.com/dr

View on Hacker News

Recent submissions

Comment · 9 years ago

Super interesting post. Would love to read more detail about their backup and restore infrastructure.

If Tom and/or Shlomi are reading this: you mention taking multiple logical backups per day. What benefit does this bring versus just having one per day and doing a point-in-time restore using binlogs? Is this just a tradeoff between time taken for a restore and storage you're willing to dedicate to backups?

Disclaimer: I work on Facebook's MySQL backup and restore system (https://code.facebook.com/posts/1007323976059780/continuous-...)

Comment · 9 years ago

Author here.

We use it because it works well for us. We've put a lot of work into making MySQL scale for us to the point where it's a very well supported system and one of the main choices for a lot of storage decisions.

We even use MySQL as a queue for Facebook Messenger. More details about this:

https://www.youtube.com/watch?v=eADBCKKf8PA

https://code.facebook.com/posts/820258981365363/building-mob...

Comment · 10 years ago

In addition to what evanelias said, a logical dump also means we can load it into a MySQL instance running a different storage engine as well. In our case, it allows us to take a mysqldump from an InnoDB instance and load it into a MyRocks instance if we wish.

Comment · 10 years ago

Instance size varies a lot because we've got a lot of different MySQL workloads, some with very different configurations.

Remember we're backing up each database separately though, not the entire MySQL instance at once. Each database's backup is in a separate file.

Comment · 10 years ago

We run a single mysqldump with --single-transaction for each database on the server, nothing special.

Comment · 10 years ago

We take logical backups, not physical, and mysqldump is the best option for that. Having logical backups means we can do logical diffs as well (see https://youtu.be/Fe2oLZ4CWD4?t=951), and we've added table checksum support to mysqldump in our branch of MySQL (https://github.com/facebook/mysql-5.6/commit/54acbbf915935a0...)

Comment · 10 years ago

There's a few different ways to verify, a few of them involving stopping replication, like you pointed out. These can also sometimes be quite expensive, so depending on the type of verification required, the verification method can be tuned.

We also implemented table checksums inside of mysqldump, allowing us to dump out the restored data and compare checksums as well, if required. https://github.com/facebook/mysql-5.6/commit/54acbbf915935a0...

Comment · 10 years ago

> Do you diff against the same base, or create an incremental chain? How many diffs do you take in between recapturing a full image? At $DAYJOB we always take full backups into a fast in-house deduplicating store.

We always diff against the same base and have 5 days in between subsequent full dumps. The number of days just comes from a trade off between space occupied by the backups and time it takes to generate them.

> Is there no better way to handling this than polling?

There's definitely different ways to approach this, we find polling works well for us. We also use the same database for crash recovery, so doing the assignments through it serves both purposes.

> Presumably you can only get this parallelism by disabling FK integrity. Is it re-enabled in the following VERIFY stage?

I'm not sure what you mean by parallelism through disabling FK integrity. Splitting the backup into its tables means we can restore a subset of tables instead of the entire backup. This allows us to load individual tables concurrently, but also not have to wait to load a massive database if all we need is a few small tables.