Databases Reference
In-Depth Information
Figure 10-12. A pyramid replication topology
Custom Replication Solutions
MySQL replication is flexible enough that you can often design a custom solution for
your application's needs. You'll typically use some combination of filtering, distribu-
tion, and replicating to different storage engines. You can also use “hacks,” such as
replicating to and from servers that use the Blackhole storage engine (as discussed
earlier in this chapter). Your design can be as elaborate as you want. The biggest limi-
tations are what you can monitor and administer reasonably and what resource con-
straints you have (network bandwidth, CPU power, etc.).
Selective replication
To take advantage of locality of reference and keep your working set in memory for
reads, you can replicate a small amount of data to each of many replicas. If each replica
has a fraction of the master's data and you direct reads to the replicas, you can make
much better use of the memory on each replica. Each replica will also have only a
fraction of the master's write load, so the master can become more powerful without
making the replicas fall behind.
This scenario is similar in some respects to the horizontal data partitioning we'll talk
more about in the next chapter, but it has the advantage that one server still hosts
all
the data—the master. This means you never have to look on more than one server for
the data needed for a write query, and if you have read queries that need data that
doesn't all exist on any single replica server, you have the option of doing those reads
on the master instead. Even if you can't do all reads on the replicas, you should be able
to move many of them off the master.
