Databases Reference
In-Depth Information
or not its data is too stale to read. Many reporting applications can use this strategy:
as long as the nightly data load has finished replicating to the replica, they don't
care whether it is 100% caught up with the master.
Session-based split
A slightly more sophisticated way to decide whether a read can go to a replica is
to note whether the user has changed any data. The user doesn't have to see the
most up-to-date data from other users but should see her own changes. You can
implement this at the session level by flagging the session as having made a change
and directing the user's read queries to the master for a certain period of time after
that. This is the strategy we usually suggest to clients, because it's a good com-
promise between simplicity and effectiveness.
If you want to get fancy, you can combine session-based splitting with replication
lag monitoring. If the user changed some data 10 seconds ago and no replica is
more than 5 seconds behind, it's safe to read from a replica. It's a very good idea
to select one of the replicas and use it for the whole session, though, or the user
might see strange effects caused by some of the replicas being farther behind than
others.
Version-based split
This is similar to session-based splitting: you can track version numbers and/or
timestamps for objects, and read the object's version or timestamp from the replica
to determine whether its data is fresh enough to use. If the replica's data is too old,
you can read the fresh data from the master. You can also increment the top-level
item's version number even when the object itself doesn't change, which simplifies
staleness checks (you need to look in only one place—at the top-level item). For
example, you can update the user's version if he posts a new blog entry. This will
cause reads to go to the master.
Reading the object's version from the replica adds overhead, which you can reduce
with caching. We discuss caching and object versioning further in later chapters.
Global version/session split
This is a variation on version- and session-based splitting. When the application
performs a write, it runs
SHOW MASTER STATUS
after the transaction commits. It stores
the master's log coordinates in the cache as the modified object's and/or session's
version number. Then, when the application connects to the replica, it runs
SHOW
SLAVE STATUS
and compares the replica's coordinates to the stored version. If the
replica has advanced to at least the point at which the master committed the trans-
action, the replica is safe to use for the read.
Most read/write splitting solutions require you to monitor replication lag and use it to
decide where to direct reads, either in the application or through a load balancer or
another man-in-the-middle system. If you do this, be aware that the
Seconds_behind
_master
column from
SHOW SLAVE STATUS
is not a reliable way to monitor lag. (See
Chapter 10
for details.) The
pt-heartbeat
tool from Percona Toolkit can help you
