Brian "Krow" Aker's Idle Thoughts - Genesis: Read Replication Cluster

If there is a common method of scaling with MySQL databases it is the
Read Replication Cluster solution.

Most websites start out with a single database and grow from there.
If the site's content is being generated from their database then
they will eventually hit a wall with reads from the database. Tuning
and hardware will buy you some growth but in the end disks spin only
so quickly. Luckily most websites are predominantly read intensive
and for this reason replication will solve scaling problems for many
people. Replication is a means by which MySQL sends updates of one
database to one or more databases which will act as a slave. These
changes are atomic, which means the changes are applied in full. No
row will ever be partially updated, and no transaction will be seen
on the slave that did not commit on the master

Make a change in the master, and the slave will see the change,
though with some delay. I refer to the master as the "writer" and the
slaves as "readers" frequently when I explain this setup.





By its nature replication is not synchronous, which means that
changes will arrive to the slaves as they can be applied. This is an
advantage for the master database since it will not wait for the
slaves to apply a set of changes. MySQL tries to minimize this delay
on the slave by having a read ahead replication. Read ahead means
that a slave uses one thread to retrieve change sets and a second
thread to apply change sets. For application designers this means
that they will need to consider the delay in the user experience.
Fortunately MySQL replication is fast, so the delay the user
experiences is often quite small. One trick to getting around this
delay involves using session cookies to determine if the user has
recently performed an action that would require them to have up-to-
date data. You can use this cookie for future visits to point the
user either at the master or the slave database.

Lets look at a couple of examples. In the first case the user changes
a personal preference for their account to no longer see posts from a
particular person. This user will expect this change to be
immediately reflected in their environment. The selection of posts
to be displayed can come from a slave, after the user information is
retrieved from the master database. "Hot" information is expensive,
but if the majority of reads come from the slave then the master
database load is kept low.

In the second case a user creates a comment on a page. If the user
then looks at a page, they will expect to see their comment. To make
sure the user receives the experience that they expect you can tag
their cookie so that for their next request, the user is connected to
a database that will have their comment. This way they always get the
most "up to date" data.

The key to making this solution work is monitoring. You will want to
know how far behind the slaves are from the master at any point, so
that you can disable a slave if some problem arises. You will also
find it handy to be able to disable your master database to do
maintenance on it. Once you know the state of your databases you can
tell clients where to retrieve data.

The simple way to handle this is to use another database to store
monitoring information and have clients do a single read from this
database to determine the state of your cluster. If you want to get a
little fancier you can skip using a database for the monitor and
write your own monitoring server.

I have seen setups that used DNS to solve the "who is your reader"
but I dislike this approach since I find that the DNS setup is a bit
more Rube Goldberg like.

The Read Replication Cluster solution has its weak points. You still
have only a single writer in this setup, so down time on your writer
means that your environment will need to be able to fall back to a
"read-only" mode. The lack of synchronous commits on both the master
and the slave also means that if your user's need to frequent the
writer for up to date data too often that you won't be able to make
good use of a slave. Also take note, if you don't create redundancy
in the monitoring service then you will either need to have your
clients be able to "find a reader" without the monitor or extend the
monitoring solution for fail over of the monitor itself.

In future articles I am going to demonstrate additional environments
that solve these problems.

One final note about this setup. One additional advantage of this
environment is that it makes backups easy to accomplish. Just
shutdown one of the slaves and let your system administrator back up
the database like any other file. No need to take the site offline
and no need to take any IO hit from a backup application reading the
entire contents of your database.