GWT Blog – GlobalWorldTech

MySQL Replication is the key enabler of “Scale Out” discussed in the previous post. Scale Out is leveraged extensively by Web 2.0 sites and applications. MySQL natively supports one-way, asynchronous replication. Replication works by simply having one server act as a master, while one or more servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of MySQL Cluster.

Asynchronous data replication means that data is copied from one machine to another, with a resultant delay. Often this delay is determined by networking bandwidth, resource availability or a predetermined time interval set by the administrator. However, with the correct components and tuning, replication itself can appear to be almost instantaneous to most applications. Synchronous data replication implies that data is committed to one or more machines at the same time, usually via what is commonly known as a “two-phase commit”.

In standard MySQL Replication, the master server writes updates to its binary log files and maintains an index of those files in order to keep track of the log rotation. The binary log files serve as a record of updates to be sent to slave servers. When a slave connects to its master, it determines the last position it has read in the logs on its last successful update. The slave then receives any updates which have taken place since that time. The slave subsequently blocks and waits for the master to notify it of new updates.

Below is an illustration of a basic Scale Out implementation using MySQL Replication.

Replication offers the benefits of reliability, performance, and ease of use:

• In the event the master fails, the application can be designed to switch to the slave.
• Better response time for clients can be achieved by splitting the load for processing client queries between the master and slave servers. Queries which simply “read” data, such as SELECTs, may be sent to the slave in order to reduce the query processing load on the master. Statements that modify data should be sent to the master so that the data on the master and slave do not get out of synch. This load-balancing strategy is effective if non-updating queries dominate.
• Another benefit of using replication is that database backups can be performed using a slave server without impacting the resources on the master. The master continues to process updates while the backup is being made.

An overarching goal of Web 2.0 application design is for the system to harness collective intelligence with network effects so that the ownership of unique and difficult to reproduce data can be generated. Obviously, at the core of this story is the database which will store and dispense the data. The importance of data within the context of Web 2.0 cannot be overstated.

There are many reasons why MySQL is being leveraged time and again by established web companies and new emerging Web 2.0 companies. The most glaring one is cost.

Fail Fast – Scale Fast

“Web 2.0 has been summed up as “Fail Fast, Scale Fast”. The point being made is that the operational environment will be a key differentiator in the success of Web 2.0’s adoption, especially in the enterprise. These services are required to fail over and scale transparently without any disruption in service to the consumer. The ability to handle explosive growth in user volume, transactions and storage capacity is critical to delivering services on a web-based platform. To this effect, MySQL addresses these types of issues via Scale Out, MySQL Network and an ecosystem of partner solutions.

Scale Out vs. Scale Up

Scale-out using MySQL enables organizations to cost-effectively solve database capacity issues that result from increased traffic and transaction volumes. In particular, scale-out with MySQL provides organizations with the following advantages:

• Easily and cost-effectively add capacity to your database infrastructure using open source software and commodity hardware.
• Reduced hardware costs by incrementally adding several low-cost commodity systems vs. upgrading high-cost mainframe-class systems
• Reduce software costs and eliminate up-front licensing by scaling out with MySQL
• Improve response time and availability by improving the performance of your system so users experience fewer interruptions.
• Improve scalability using MySQL Replication to distribute large workloads to individual server nodes for execution.
• Increased flexibility to right-size the initial purchase of commodity hardware and software, incrementally add capacity.

MySQL is widely used for the following Scale-Out architectures:

• Web Scale-Out to improve the performance, scalability, and availability of web applications such as e-commerce, content management, session management, search, and security.
• Data Warehousing Scale-Out to improve the performance and availability of traditional data warehousing (e.g. centralized data warehouse and data marts) as well as real-time Operational Data Stores.

Virtually any application that has a rapidly growing volume of users, transactions or data may be a candidate for more cost-effective deployment using open source technology combined with a scale-out architecture.