ChapTEr 8 ■ DEmonsTraTion of high availabiliTy TEChniquEs
Now that you understand what read scaling is and how it is used to improve performance, let’s see a
simple server selection code example you can use to spread reads across several servers. Let’s begin with
the design.
Overview of the Design
Rather than design a code solution, I present a solution that you can use to implement the persistence
component in MySQL. That is, we can use the database server as a gatekeeper to help ensure two clients
do not choose the same slave. It may not be the most efficient mechanism available and falls short of
being a general-purpose load balancer (but it mimics a rudimentary one), it presents a mechanism that
permits you to use it without modifying your code every time you add a new server. In fact, it will select
a server automatically from the list of available servers, and you don’t even have to create and manage a
configuration file.^5
The key concept is a feature of replication itself. We will use the SHOW SLAVE HOSTS statement on the
master to get a list of the slaves. Recall we require the use of the --report-host and --report-port options
for all slaves to get the data to appear in the list. We then use a round-robin mechanism to select the next
server in the list.
Another key concept is the use of the database server to store the last server ID used. We create a simple
database and table with a single row as follows. Notice we store the server ID, hostname, and port.
CREATE DATABASE read_scaling;
CREATE TABLE read_scaling.current_slave(server_id INT, host CHAR(30), port INT);
INSERT INTO read_scaling.current_slave VALUES (0,NULL,0);
Notice the INSERT statement. You will need to insert a starting row because the code is designed to
update only the single row, making it small and easy to use. However, you may be wondering about that
single row in the table. Savvy readers who have worked with applications that have multiple clients may be
wondering how to keep two or more clients from colliding and updating the row at the same time (or out of
sequence). Here is where the power of the database helps us.
In computer programming jargon, we call the portion of a program that must be executed such that
it must complete all at once a critical section. In this case, it is similar to a transaction in that vein. MySQL
provides a SQL statement named LOCK TABLES that we can use to lock the table, update it, and then release
the lock. In this way, we ensure one and only one client can update the table at a time because the WRITE lock
blocks all other connections from reading or writing to the table. The following shows the SQL statements we
will use to do this in our code:
LOCK TABLES read_scaling.current_slave WRITE;
UPDATE read_scaling.current_slave SET server_id=2,host='test123',port=13001;
UNLOCK TABLES;
But wait, what about the data—won’t it get replicated to the slaves? As a matter of fact, it will. While it
really won’t matter since we won’t be reading the data from the slaves, there is a good reason to allow this
data to replicate. Consider what would happen if the master were to go down. If you had not replicated the
data, you would lose it when the new master is configured; you would have to set it up again. But that’s not
really a problem given its simplicity. So, it is fine to allow it to replicate.
(^5) Not that there is anything wrong with configuration files, but if you’ve ever encountered an installation where there are
multiple configuration files scattered across several parts of the solution, you’ll grow to appreciate not requiring them.