Creating a Replication Strategy

< Day Day Up >

Creating a Replication Strategy Now that you've seen some circumstances in which replication might help boost responsiveness, let's look at a collection of processing scenarios. To make these more meaningful, let's return to our friends at High-Hat Airways as they roll out a homegrown reservation system. To make these replication opportunities more understandable, we won't cite any of the modern processing appliances (for example, intelligent routers, load balancers, shared storage devices) that would likely be found in this kind of environment. In fact, for now we'll omit one of the most compelling MySQL features: clustering; clustering performance topics are explored in Chapter 17, "Optimal Clustering." To begin, the Java-based reservation system serves internal reservation agent users based at High-Hat headquarters. Some of these users connect via PC-based sessions, whereas others connect using dumb terminals. These users directly connect to an application server, which, in turn, connects to the MySQL server (see Figure 16.1). Figure 16.1. The initial reservation system configuration. As time passes, this configuration proves to be problematic. In particular, the delays caused by the necessary nightly backups translate into lost revenue and user frustration. High-Hat administrators decide to experiment with MySQL's replication features. This first trial simply consists of reproducing the main database server's information on a secondary, backup server. This backup server does not need to be as modern, fast, or expensive as the primary server. It simply needs a sufficient disk, network bandwidth, and a backup device (see Figure 16.2). Figure 16.2. The original environment with replication enabled for backup. [View full size image] This replication strategy yields immediate benefits. First, High-Hat now has increased their data security simply by duplicating it to another machine. Second, backups no longer need to be delayed to off-hours; they can be conducted from the secondary server at any time during the day. However, like all computing solutions since the beginning of time, this new architecture soon needs performance enhancements. High-Hat recently invested in sophisticated data analysis software to support their fleet of expensive MBAs. These highly paid, finance department�based experts are running enormous queries that consume abundant system resources, in the middle of the day no less. In fact, ticket sales have begun to suffer: Many customers are hanging up on the reservations agents before their bookings are complete. Fortunately, after you've done the research to successfully implement a single master/slave pair, adding additional slaves is quite easy. In this case, you can simply add a new slave server to your replication topography (see Figure 16.3). Figure 16.3. Replication extended to support users in another department. [View full size image] Now, data analysts can run queries against a near-instantaneous copy of the production database. Aside from running endless queries that bog down production servers, the data analysts also create value-added aggregations and compilations of information in their own database. This database resides on the data analysis slave, yet other users throughout the company want access to this information. In particular, the marketing department wants access to this derived data to help formulate new campaigns. After a while, these new demands bog down the data analysis server. In this situation, it's easy to configure the data analysis server to simultaneously play the role of both master and slave (see Figure 16.4). Figure 16.4. Departmental database server now functions as both a master and a slave. [View full size image] How is this possible? Recall that the primary database server is replicating reservation information onto the finance server. Users on that server are creating their own information in a separate database. It's this latter database that is then replicated down the line. In fact, the marketing server could act as a slave to both the finance and primary servers, as long as it was registering interest in different databases (or at least different tables in the same databases) on these servers. Finally, High-Hat's new web-based customer self-service portal is designed to let customers manage many of the tasks previously handled by reservation agents. However, management wants these agents and customers to perform real-time updates of the primary database, without resource conflicts. Replication might help here as well. In this type of situation, you can design the web-based application to read information from a slave server yet write updates back to the primary server (see Figure 16.5). Figure 16.5. Reads serviced by a replicated server, while writes are performed on the original database server. [View full size image] As you've seen in the previous examples, you can create sophisticated replication interchanges throughout your organization. In fact, replication slaves can even be hosted by separate entities; the data interchanges can be encrypted to protect against eavesdropping. Optimal replication security is examined a little later in this chapter. Now that you've seen how to design a replication strategy, it's time to review how to remove as many performance bottlenecks as possible.

< Day Day Up >