How to use MySQL to store the stateless data of FriendFeed

We use MySQL to store all the data of FriendFeed. The database increases a lot with the growth of the user base. More than 0.25 billion records and a bunch of other data from comments and "likes" to friends lists have been stored.

With the increase of data, we have also iteratively solved the scalability problems brought about by such rapid growth. Our attempts are representative. For example, we use read-only mysql to increase the read throughput from the node and memcache, and Shard the database to improve the write throughput. However, as the business grows, it is more difficult to add new features than to expand existing features to cater to more traffic.

In particular, the database will be locked for several hours by modifying the schema or adding an index for a database with more than 1000-20 million rows of records. Deleting old indexes also takes so much time, but not deleting them will affect the performance, because the database will continue to read and write these useless blocks on each INSERT, and squeeze out important blocks of memory. To avoid these problems, you need to take some complex measures (for example, setting a new index on the Slave node and then performing the opposite operation between the slave node and the master node ), however, these measures may cause errors and are difficult to implement. They impede the new functions that require schema/index modification. Due to the serious dispersion of databases, MySQL's relational features (such as join) are useless to us, so we decided to leave RDBMS.

Although there are many projects that are used to solve the problem of flexible schema data storage and indexing during runtime (such as CouchDB. However, it is not widely used in large websites to persuade people to use it. In the tests we see and run, these projects are either unstable or lack adequate testing (see this somewhat outdated article about CouchDB ). MySQL is good, it does not damage data; replication is also OK, we have understood its limitations. We like to use MySQL for storage, just non-relational storage.

After thinking about it, we decided to adopt a non-pattern storage system on MySQL, instead of using a storage system that we have never touched on. This article attempts to describe the advanced details of this system. We are curious about how other large websites solve these problems, and hope that some of the designs we have done will be helpful to other developers.

Summary

We store a schema-free attribute set in the database (such as a JSON object or a python dictionary ). Only a 16-byte UUID attribute named id is required for the stored record. Other parts of the object are invisible to the database. We can simply store new attributes to change the schema (it can be simply understood that there are only two fields in the data table: id and data; where data stores the attribute set of the object ).

We retrieve data by storing indexes in different tables. To retrieve three attributes of each object, we need three data tables-each table is used to retrieve a specific attribute. If you do not want to use an index any more, stop the write operation on the table corresponding to the index in the code and delete the table. To add a new index, you only need to create a MySQL table for the index and start a process to add index data to the table asynchronously (this does not affect the running services ).

In the end, although the number of data tables increases, it is easy to add or delete indexes. We have greatly improved the process of adding index data (we call it "cleaners") so that it does not affect the site while adding indexes quickly. We can save and index new attributes within one day, and we do not need to tune the master-slave MySQL database, nor need any other terrible operations.

Details

MySQL uses a table to save our objects. A table is like this:

Create table entities (
Added_id int not null AUTO_INCREMENT primary key,
Id BINARY (16) not null,
Updated timestamp not null,
Body MEDIUMBLOB,
Unique key (id ),
KEY (updated)
) ENGINE = InnoDB;

The added_id field is used because InnoDB stores data in the order of the physical primary key. The auto-increment primary key ensures that the new instance is written to the old entity in order on the disk, which facilitates partition read/write (relatively old entity, new entities often read more frequently, because pages of FriendFeed are arranged in reverse chronological order ). The object itself is serialized in the python dictionary and then compressed and stored using zlib.

An index exists in a separate table. To create an index, we create a new table to store all the attributes of the data shards we want to index. For example, a FriendFeed object looks like this:

{
"Id": "71f0c4d2291844cca2df6f486e96e37c ",
"User_id": "f48b0440ca0c4f66991c4d5f6a078eaf ",
"Feed_id": "f48b0440ca0c4f66991c4d5f6a078eaf ",
"Title": "We just launched a new backend system for FriendFeed! ",
"Link": "http://friendfeed.com/e/71f0c4d2-2918-44cc-a2df-6f486e96e37c ",
"Published": 1235697046,
"Updated": 1235697046,
}

We index the object's property user_id, so that we can render a page that contains all the attributes of a submitted user. Our index table looks like this:

Create table index_user_id (
User_id BINARY (16) not null,
Entity_id BINARY (16) not null unique,
Primary key (user_id, entity_id)
) ENGINE = InnoDB;

Our data storage automatically maintains indexes for you, so if you want to enable an instance in the data storage where we store the above structured entities, you can write a piece of code (using python ):

User_id_index = friendfeed. datastore. Index (
Table = "index_user_id", properties = ["user_id"], shard_on = "user_id ")
Datastore = friendfeed. datastore. DataStore (
Mysql_shards = ["127.0.0.1: 3306", "127.0.0.1: 3307"],
Indexes = [user_id_index])
 
New_entity = {
"Id": binascii. a2b_hex ("71f0c4d2291844cca2df6f486e96e37c "),
"User_id": binascii. a2b_hex ("f48b0440ca0c4f66991c4d5f6a078eaf "),
"Feed_id": binascii. a2b_hex ("f48b0440ca0c4f66991c4d5f6a078eaf "),
"Title": u "We just launched a new backend system for FriendFeed! ",
"Link": u "http://friendfeed.com/e/71f0c4d2-2918-44cc-a2df-6f486e96e37c ",
"Published": 1235697046,
"Updated": 1235697046,
}
Datastore. put (new_entity)
Entity = datastore. get (binascii. a2b_hex ("71f0c4d2291844cca2df6f486e96e37c "))
Entity = user_id_index.get_all (datastore, user_id = binascii. a2b_hex ("f48b0440ca0c4f66991c4d5f6a078eaf "))

The Index class above searches for user_id in all entities and automatically maintains the Index of the index_user_id table. The shard_on parameter is used to determine the Shard where the index is stored (in this case, entity ["user_id"] % num_shards ).

You can use an index instance (see user_id_index.get_all) to query an index. Use the data storage code written in python to merge the index_user_id and entities of the table. First, query the table index_user_id in all database shards to obtain the object ID column, and then propose data in entities.

Create an index. For example, on the property link, we can create a new table:
    
Create table index_link (
Link VARCHAR (735) not null,
Entity_id BINARY (16) not null unique,
Primary key (link, entity_id)
) ENGINE = InnoDB default charset = utf8;

We can modify the data storage initialization code to include our new index:

User_id_index = friendfeed. datastore. Index (
Table = "index_user_id", properties = ["user_id"], shard_on = "user_id ")
Link_index = friendfeed. datastore. Index (
Table = "index_link", properties = ["link"], shard_on = "link ")
Datastore = friendfeed. datastore. DataStore (
Mysql_shards = ["127.0.0.1: 3306", "127.0.0.1: 3307"],
Indexes = [user_id_index, link_index])

I can build indexes asynchronously (especially the real-time transmission service ):

./Rundatastorecleaner. py -- index = index_link

Consistency and Atomicity
 
Because a partitioned database is used, the index of an object may be stored in different partitions of the object, which leads to consistency problems. What if the process crashes before writing data to all index tables?

Many ambitious FriendFeed engineers tend to build a transactional protocol, but we want to keep the system as concise as possible. We decided to relax the restrictions:

The attribute set stored in the primary object table is standardized and complete.

The index does not affect the real object value.

Therefore, we take the following steps to write objects to the database:

Use the ACID attribute of InnoDB to write entities to the entities table.

Write the index to the index table in all partitions.

Remember that the data retrieved from the index table may be inaccurate (for example, if the write operation fails to complete step 2, the old attribute value may be affected ). To ensure that the preceding restrictions can be used to return correct entities, we use an index table to determine which entities to read, but do not trust the integrity of the index, to filter these entities using the query conditions:

1. Obtain entity_id from the index table based on the query conditions

2. Read entities from the entities table based on entity_id

3. Filter out objects that do not meet the query conditions based on the real attributes of objects (using Python).

To ensure the persistence and consistency of indexes, the "cleaners" process mentioned above should continue to run, write lost indexes, and clear invalid old indexes. It first clears objects updated recently, so the index consistency is maintained very quickly (several seconds ).

Performance

We have optimized the primary index of the new system and are very satisfied with the results. The following figure shows the loading latency statistics of the FriendFeed page last month (we launched a new backend a few days ago, and you can find the day based on the significant latency drop ).

Statistical Chart (we started a new backend a few days ago, and you can find the day based on the significant decrease in latency ).

In particular, the system latency is now very stable (even during the midday peak hours ). The following figure shows the loading latency of the FriendFeed page in the past 24 hours.

Compared with the day of last week:

The system is easy to use so far. After deployment, we also changed the Index several times, and we started to apply this pattern to those large tables in MySQL, so that we can easily change their structure in the future.