Table of Contents [+/-]
MySQL is deployed into many applications demanding availability and scalability.
Availability refers to the ability to cope with, and if necessary recover from, failures on the host, including failures of MySQL, the operating system, or the hardware and maintenance activity that may otherwise cause downtime. Scalability refers to the ability to spread both the database and the load of your application queries across multiple MySQL servers.
Because each application has different operational and availability requirements, MySQL offers a range of certified and supported solutions, delivering the appropriate levels of High Availability (HA) and scalability to meet service level requirements. Such solutions extend from replication, through virtualization and geographically redundant, multi-data center solutions delivering 99.999% uptime.
Selecting the right high availability solution for an application largely depends on:
The level of availability required.
The type of application being deployed.
Accepted best practices within your own environment.
The primary solutions supported by MySQL include:
MySQL Replication. Learn more: http://dev.mysql.com/doc/refman/5.5/en/replication.html
MySQL Cluster. Learn more: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster.html
Oracle VM Template for MySQL. Learn more: Section 14.1, “Oracle VM Template for MySQL Enterprise Edition”.
Further options are available using third-party solutions such as DRBD (Distributed Replicated Block Device) and Heartbeat, and more complex scenarios can be solved through a combination of these technologies.
Each architecture used to achieve highly available database services is differentiated by the levels of uptime it offers. These architectures can be grouped into three main categories:
Data Replication.
Clustered & Virtualized Systems.
Shared-Nothing, Geographically-Replicated Clusters.
As illustrated in the following figure, each of these architectures offers progressively higher levels of uptime, which must be balanced against potentially greater levels of cost and complexity that each can incur. Simply deploying a high availability architecture is not a guarantee of actually delivering HA. In fact, a poorly implemented and maintained shared-nothing cluster could easily deliver lower levels of availability than a simple data replication solution.
The following figure maps common application types to architectures, based on best practices observed from the MySQL user base. It serves as a reference point to investigate which HA architectures can best serve your requirements.
The following table compares the HA and Scalability capabilities of the various MySQL solutions:
| Requirement | MySQL Replication | MySQL Replication + Linux Heartbeat | Heartbeat + DRBD | Oracle VM Template | MySQL Cluster |
|---|---|---|---|---|---|
| Availability | |||||
| Platform Support | All Supported by MySQL Server | Linux | Linux | Oracle Linux | All Supported by MySQL Cluster |
| Automated IP Failover | No | Yes | Yes | Yes | Depends on Connector and Configuration |
| Automated Database Failover | No | No | Yes | Yes | Yes |
| Automatic Data Resynchronization | No | No | Yes | N/A - Shared Storage | Yes |
| Typical Failover Time | User / Script Dependent | Configuration Dependent, 60 seconds and Above | Configuration Dependent, 60 seconds and Above | Configuration Dependent, 60 seconds and Above | 1 Second and Less |
| Synchronous Replication | No, Asynchronous and Semisynchronous | No, Asynchronous and Semisynchronous | Yes | N/A - Shared Storage | Yes |
| Shared Storage | No, Distributed | No, Distributed | No, Distributed | Yes | No, Distributed |
| Geographic redundancy support | Yes | Yes | Yes, via MySQL Replication | Yes, via MySQL Replication | Yes, via MySQL Replication |
| Update Schema On-Line | No | No | No | No | Yes |
| Scalability | |||||
| Number of Nodes | One Master, Multiple Slaves | One Master, Multiple Slaves | One Active (primary), one Passive (secondary) Node | One Active (primary), one Passive (secondary) Node | 255 |
| Built-in Load Balancing | Reads, via MySQL Replication | Reads, via MySQL Replication | Reads, via MySQL Replication | Reads, via MySQL Replication & During Failover | Yes, Reads and Writes |
| Supports Read-Intensive Workloads | Yes | Yes | Yes | Yes | Yes |
| Supports Write-Intensive Workloads | Yes, via Application-Level Sharding | Yes, via Application-Level Sharding | Yes, via Application-Level Sharding to Multiple Active/Passive Pairs | Yes, via Application-Level Sharding to Multiple Active/Passive Pairs | Yes, via Auto-Sharding |
| Scale On-Line (add nodes, repartition, etc.) | No | No | No | No | Yes |
User Comments
Note: With MySQL Replication + Heartbeat it is possible to simulate a MySQL cluster having less than 3 seconds failover time without any of the restrictions imposed by moving to a NetDB storage engine.
Configuration
- two master nodes running MySQL (Master-Master Replication setup) with heartbeat IP failover. apps write to one Master (which moves transparently on failure)
- as many MySQL Slave nodes to handle reads (as needed)
- distribute reads using MySQL Load Balancer or DNS (heartbeat failover to another node)
A major benefit is that this setup can provide both load distribution and failover starting from only 2 physical nodes, unlike the MySQL cluster, which only makes sense after a certain number of nodes.
You can see this kind of setup in action here:
http://ehealthforum.com/
Daniel Volkovich
System Architect
A lot of options exist for scaling MySQL and improving availability. Unfortunately, there isn’t one silver-bullet solution that best suits all projects. Each project has unique requirements that force us to go through the selection process all over again. After several trips through the analysis, I wrote up this high-level summary of the options:
http://www.oshyn.com/_blog/General/post/A_Summary_of_Scaling_Options_for_MySQL/ .
Add your own comment.