Dec
11
Continuing the series about Database Sharding, I’m going to to talk about the software/hardware architecture. This post started from an excellent read, MySQL Database Scale-out and Replication for High Growth Businesses.
The first order of business is MySQL replication. Replication is needed to offer redundancy and to distribute even further the load on the system. In a typicall shard environment, the database is split among multiple servers, with data being unique to each server. If one of the servers goes down, all that data will become unavailable, and even though the system will continue working, parts of some scenarios will fail. This is where replication comes to stage.
It has two main uses: one is to provide a fail-proof user experience and the other is for geo-databases. Replication is usually asynchrounous and from now on, when saying replication we’ll automatically reffer to async ops.
The definition is simple: data duplication to more locations. From the definition the pros and cons become apparent. Pros: load splitting and fail over user experience. Cons: the entire data exists on each machine. This is ok until the data becomes to large. The whole point is to use cheap, commodity hardware which would be impossible on huge information sets. In an async environment, the reads are distributed to all members of the system, while the writes go only to the master. The master then dispatches them further down to the slaves.
In MySQL 5.1 a row based replication has been introduced (apart from the already existing statement based replication). The basic difference beteween the two is that statement means deal with structured query language emits, while row deals with raw binary data. Statement based replication is ok when there is not a very heavy use of non-deterministic function calls (rand(), now(), etc.), or system calls like select users(). Small binary log files are produced and they will become a basis for later auditing. Using row-based replication, everything can be replicated. Also, fewer locks are used for Data Manipulation Language (DML) statements - select, update, delete, merge, etc., on both master and slave servers. Usually, this means faster data replication on slaves, especially for rows that contain primary keys.
The process of replication in a master-slave implementation is fairly simple to visualize. The mysqld daemon on the master serves out reads and selects, while updates are written to the disk. At the same time, all updates are written to a binary log, or binlog. The binlog is indexed and becomes the basis for replicating the updates on the slaves. After writing it, the masters sends the binlog to the mysqld daemon on the slave. MySQL is smart enough to send the replication binlog to a special process called I/O thread which actually emulates a write event to the slave server’s temporary log (relay log). The relay log is then sent as a normal SQL thread to the DB on the slave server which in turn takes two actions: writes the data to the disk and also to the binlog of the slave itself. As you can probably guess, the slave’s binlog can act as a master log for a child slave (slave cascading). The best part is that the slave controls the replication process, so the whole system supports hot plug and play slaves (also each slave remembers the position it reached in the binary log, so it can catch-up at any time).
MySQL supports almost all types of replication topologies, except multiple masters updating a single slave.
The most common replication scenario is multiple reads, low-moderate writes. The solution is to use one master for writing and multiple slaves for reading. Because the read ops are handled by a variable number of slave servers and also because of the asynchronous replication process, the data the user receives might not be the freshest, but it’s still ACID ok. This effect can be seen on large social media sites where the user attempting an update has immediate feedback, while his friends who view his profile, for instance, might get the update in a couple of seconds/minutes.
The biggest problem when using replication is load balancing, precisely because the multitude of hardware platforms that comprise the farm. The idea is to be able to separate reads from writes before they hit the server cloud. There are two types of load balancers, hardware and software ones (the latter might require existing app modifications, to make the app aware of the replication topologies in place).
It’s now time to see where sharding comes into play. Well, as discussed in the previously three parts, the data is functionally split across multiple databases. All those databases can sit on a different type of replication topology, depending on the ops required. The beauty of sharding actually comes from being able to process more writes than normal, because of the multiple gateways available for writing.
NOTE. Even for the smallest application, database disaster recovery is needed, so be very careful when designing the cloud. Also, geographic replication doesn’t have automatic failover or automatic resynchronization once the site has come back online.
Comments
Leave a Reply