HarrisonFisk

The primary settings are:

sync_binlog=1 innodb_flush_log_at_trx_commit=1 (which is default, but we add it to our configs since we run with =2 on the slaves since they don't need full durability)

We do use some performance related settings related to this as well:

innodb_flush_method = O_DIRECT innodb_prepare_commit_mutex=0 (enables group commit in the Facebook patch, other branches have a different setting)

For settings like innodb_doublewrite we leave the default of enabled.

Regarding #2, we do normally run MySQL with full transactional durability at Facebook. We have for a very long time (several years at least). For example, here is a FB note regarding our enhancing performance under full durability in MySQL from Oct 2010:

https://www.facebook.com/note.php?note_id=438641125932

Any chance you could publish full info on the benchmarks?

For example, where can we find your configurations for the MySQL vs. MemSQL benchmark you show in your video? Or how big the dataset was, etc...?

The MySQL optimizer will actually notice and remove the outer join aspect automatically. So I have often done this out of pure laziness if I start with an outer join, but really end up needing an inner join.

HPHP has a built in multi-threaded webserver.

Quora question about that:

http://www.quora.com/How-does-Facebook-implement-feature-swi...

It's a bit funny that you use Facebook as an example since they use sharded MySQL as their primary data store.

It is more suited for realtime transaction processing type activity, not for big data type analytics.

NDB was originally created by and for telecoms. So very high write/read rates with very fast response times and very high availability required. Generally not extremely large datasets.

If you know what you are doing, NDB can work extremely well. It supports all of the highend cluster goodies including online software upgrade, online node addition, automatic handling of node failures, geographic async replication, etc...

However, it certainly has a pretty steep learning curve from the admin point of view and it is a bit easy to mess things up. It is a bit brittle due to this, but once it is setup properly and running, it can deliver on the promises.

NDB originally only had a NoSQL interface called NDB API. NDB API is a bit complex and when MySQL acquired them, they added the SQL layer on top of it to make it easier for developers to use it.

This release adds an additional memcache api layer which sits on top of NDB API, so that you can more easily program against it.

For point #3, NDB has supported on disk non-indexed attributes for a while now (2-3 years?). So you just need to be able to fit indexes in memory which is a much smaller dataset, but still limiting.

I'm sure for the benchmark it was all in memory though.

There is a big difference with regards to PostgreSQL and InnoDB when you have an indexed COUNT(*) clause.

MySQL can do index only queries, whereas PG currently can not (it is part of the next release iirc). This can often result in a fraction of the disk I/O being done for MySQL.

You need to make sure all of your DB changes are backwards compatible. For example, adding new tables, adding columns (with defaults), and adding indexes can all be done without breaking existing code. The code does need to do the proper thing to make this work, such as INSERTs with the column list.

We normally disallow non-backwards compatible changes, such as renaming columns. We only drop tables after renaming and waiting a while (so we can quickly rename back).

When you have a lot of database servers this is pretty important since trying to keep them all exactly in-sync with the same schema at all times in the process is pretty much impossible. While doing the change, you are always going to end up with some finishing earlier than others.

PostgreSQL schemas are very similar to MySQL databases in functionality. In fact, in MySQL you can use SCHEMA in all places you can use the term database, ie. CREATE SCHEMA foo; instead of CREATE DATABASE foo;

There are a number of benefits:

1. InnoDB has certain optimizations that PG lacks which can make a big performance difference at the high end: index-only queries, insert buffer (or change buffer in MySQL 5.5+), clustered index

2. Lightweight connection creation: MySQL can handle many more concurrent connections and also can create new connections much faster due to threading vs. process model

3. More flexible replication: PG is catching up, but MySQL still has the edge here imo.

All content on timeline follows the same privacy settings as the object has. So if you have been posting things as Friends only, all of the content will also be Friends only. If you have been posting Public updates, then it will also be Public.

You can choose what is shown or hidden. There are initial recommendations, but you have 100% control over what is shown, highlighted or hidden.

I don't think the latter SQL would be significantly faster assuming the appropriate index on created_at.

The database can read the last 10 via the index directly and they would all most likely be on the same index page. Assuming any sort of normal caching, this would be at most 1-3 random reads and most likely none since I presume that the created_at index is generally be written in ascending order.

Once that step is done, it is essentially identical to your IN statement you did.

Facebook uses MySQL as it's primary data store, with some hbase, cassandra, and other more minor usage storage solutions in various places.

MongoDB is being used for historical archiving, not for the live site itself. The big reason being that changing table schemas for very large sets of old data is painful with MySQL. So the 2 billion number would be any ad/listing older than a set amount of time.

The live data is < 1 TB and is still stored in MySQL.

The problem with the solutions you mentioned is that it requires double provisioning hardware. When you have just a few MySQL servers, buying a few extra isn't a big deal. When you have X,000 MySQL servers, buying X,000 * 2 is a huge deal and not a nice scalable way to do backups.

You can use DRBD on linux to do synchronous replication of any file system since it works on the block level.

You can actually use LVM on a live InnoDB instance as long as both the logs and tablespace reside on the same volume. There is no locking required.

We actually used LVM at Facebook for MySQL backups for a while, however as you stated it requires at least double local space. In addition it is also a real nightmare on performance when it is running since it is double writing data locally to disk. So basically you need to run at < 50% utilization for some extended period of time to be able to take a snapshot successfully. We run much higher than that 24/7.

When you aren't disk performance or space constrained then LVM snapshots can be a very good option.