MySQL master-slave replication delay

For high-concurrency Web sites, a single point of the database is often difficult to support, generally using master-slave replication, plus MySQL proxy to achieve replication equalization, read-write separation and other functions. But there are delays in master-slave replication, how do big websites solve these problems? Reproduced from PHP Lao Yang article.

1. Youku's Experience

The database uses the horizontal expansion, the master-slave replication, with the increase from the library, replication delays more and more severe, ultimately unbearable. In the end, a parallel database is used, the equivalent of a cluster bar, and a set of user related data and tables are placed on a set of databases. Using SSD to optimize MySQL I/O, performance improved, using Database sharding technology, discard the original master-slave delay problem, according to UserID to Shard, so that there must be a global table to manage the relationship between users and Shard, according to UserID can get Shardid , and then according to the Shardid to develop the Shard query data, but if the site too many users, this table may also become a bottleneck, because the query will be very frequent, you can consider the use of memcache, and other scenarios.

The specific user shard scheme is userid is usually the user registration automatically generated, and then see a few Web servers, if there is a M, with a userid% M can be assigned a DB server, and then continue to the corresponding database table, and so on, to find the remainder of the scheme.

2.facebook of Experience

Use a large number of MySQL servers plus memcache servers.

User initiated the update operation, renamed ' A ', ' B '----> Primary database to write B, remove the primary MEMCAHCE name value--Remote Memcache does not delete, master-slave copy updates from the library, and then update Memcache, Such a solution will still have the problem of data latency, I think so, when the primary server has data updates, immediately update the Memcache data from the server, so there is very little delay.

For the more important and write must be real-time data, such as user replacement password, update operation to write to the main library, and then log in with the new password (read from the library), resulting in inconsistent passwords, resulting in short-term login errors, so this need to read real-time data from the main library directly read, to avoid lag from the library data Fortunately, there are not many situations where real-time reading is required.

MySQL master-slave replication delay