This use case covers scalability options for MySQL, which involves ways to spread the database and the load of application queries across multiple servers in order to improve performance. Scalability is one aspect of creating a high availability (HA) database installation.
Scenario 1: Scaling with MySQL Replication
In this use case, the user hosts a vacation rental website which is rapidly growing in popularity. The website needs to be able to scale quickly in order to meet demand and provide fast response to visitor queries.
- The majority of the traffic to the user's site is from visitors looking up rental properties, which executes SELECT queries on the database. Properties are not updated or added very frequently, so WRITE queries are not as common.
- Because of the nature of the website, the user feels that slight delays in propagating new WRITE queries are acceptable.
- The site lists properties around the world. The user wants to spread the servers in data centers in several different countries in order to provide the best vistior experience.
The best option for this user is MySQL Replication. This scalability option is a robust and mature technology, which was added to MySQL in version 3.23.
MySQL Replication uses an asynchronous master-node setup. This means that one server is designated as the master. The master server receives all of the WRITE queries. It executes the WRITE queries, logs them, then ships the log to the node servers to keep the data the same across all of the nodes.
MySQL strives to keep the replication as real-time as possible. However, the asynchronous nature of this method means that sometimes there will be brief periods of time when the various servers in the installation do not have the same data.
MySQL Replication is a good fit for situations where reliability and failover services are more important than instantaneous data consistency.
It is also a good choice for installations where the main traffic involves SELECT queries, and INSERT and UPDATE queries are less common. Because SELECT queries can be handled by any of the node servers, the performance of the installation can be greatly improved, and can be built out as needed to keep response times low.
- MySQL Replication works well with servers located in multiple data centers across the world.
Scenario 2: Scaling with MySQL Cluster
In this scenario, the user runs an online gambling website which is quickly growing in popularity. The traffic includes visitors placing and checking on bets, and scores being updated live.
Because this site's traffic involves both READ and WRITE queries, and because the time-sensitive nature of the business requires instant updates, the user decides that MySQL Replication is not the best solution for their business.
MySQL Cluster is more complex to set up and deploy than MySQL Replication, and it can require more ongoing management. However, it provides synchronous data replication, which means that each node in the cluster will instantly receive updates as soon as a write query is committed.
MySQL Cluster uses a two-part process to ensure synchronous replication. This process guarantees that data is written to all of the nodes in the cluster as soon as the data is committed.
MySQL Cluster is a good fit for situations where data has to be consistent across all nodes in the cluster at all times.
It is also a good fit for installations where the traffic includes INSERT and UPDATE queries, in addition to SELECT queries.
- Due to international legalities, the user's site is restricted to visitors from one geographic area, so there is no problem with locating all of their servers in the same data center. Because of latency issues, it is considered "best practices" to locate all of the servers in a MySQL Cluster in the same data center.