Overview

During the application of the database, with a constant increase in traffic, you need to expand the RDS instance's capacity to meet high-concurrency performance requirements. Usually, there are two capacity expansion solutions:

• Scale-Up solution: upgrade RDS instance's package specification, and improve the database performance by raising CPU and memory's configuration.
• Scale-out solution: By the MySQL's master-slave synchronization feature, you can add RDS read-only instance to build "One Master, Multiple Slaves" database architecture, and in turn, to improve the RDS performance and capacity.

The following focuses on the best practices of Scale-Out solution.

Applicable Scenarios

How to adopt proper Scale-out solutions for the database with the following features:

• The current RDS instance's traffic is relatively large, the instance load is relatively high. In MySQL's slow log, there is a multitude of read-only SQL requests.
• High SQL's read/write ratio: The higher SQL read ratio is, the better the scale-out solution is.
• OLAP queries in the SQL (e.g., statistics query, BI analysis query, and batch or bulk queries): Based on the traffic isolation ideology, make the time-consuming SQL access the read-only instance separately, avoiding influence on the normal real-time traffic.
• It is predicted that in the future, the database's read SQL requests would increase rapidly.

Operating Steps

• Step 1: Create a read-only instance: before creating a read-only instance, you need to create a standard instance first. Just enter the standard instance console to select "Create a Read-only Instance".
• Step 2: Create a proxy instance: before creating a proxy instance, you need to create a standard instance first. Just enter the standard instance console to select "Create a Proxy Instance". Only one proxy instance may be created for every RDS standard instance (the number of proxy instance nodes expanded on demand)
• Step 3: Create a proxy instance's exclusive account: to connect to the cloud database's RDS proxy instance, you need to use a proxy instance's exclusive account and password. During the creation of the proxy instance's exclusive account, this account is automatically synchronized to the master instance.
• Step 4: Configure the proxy instance's IP/domain name, port, account, and password into the application codes.
• Step 5: Set the read traffic limit and read traffic weight on proxy instance

【References】Read-only Instance and Proxy Instance Operations Guide

Case demonstration

Failure Phenomenon

Due to the rapid rise of services, one customer's RDS instance's read/write traffic increases quickly. Thus, it results in a high load on the RDS instance and a CPU usage rate of more than 85!

Analysis of cause

The customer sends a ticket to Baidu AI Cloud Aftersales Technical Support for help. Through earnest analysis, we find that the database instance has the following features:

• Read/write ratio: 5:1
• SQL read consumes massive master instance resources.
• Predict that the subsequent traffic would keep on rising, and the read/write ratio stays at 5:1

Solution

Recommend the customer to adopt a scale-out solution. Thus, the customer can add a proxy instance to realize traffic scheduling and read/write separation, or a read-only instance to carry the SQL read requests. See the figure below for architecture after capacity expansion:

Optimization effect

• The proxy instance diverts the traffic so that the master instance load is alleviated, and the CUP usage rate declines to 21% (1/4 of original rate).

Notices

• Recommend that you should estimate the traffic rise trend, create a proxy instance and read-only instance beforehand. Then, you can modify the program configuration information for the use of proxy instance.
• The write concurrency can lead to a high load. So, recommend you to upgrade the configuration or use the DRDS product.
• For the limits for the use of the proxy instance, see: Limitation on the Use of Proxy Instance