1. Experiment
1.1 Knowledge points
This experiment uses Alibaba Cloud ApsaraDB for RDS (RDS). The experiment introduces the read/write splitting feature of RDS and describes how to add read-only instances. Read/write splitting implements the automatic forwarding of read/write requests through a read/write splitting address. A single instance may be unable to address the read pressure in a use case where there are few write requests but massive read requests. In this case, main services may be affected. To achieve auto scaling of the read capability and relieve the database pressure, you can add one or more read-only instances in a region so that massive data can be read from the database.
1.2 Experiment process
1.3 Cloud resources required
1.4 Prerequisites
If you’re using your own Alibaba Cloud account instead of the account provided by this lab to operate the experiment, please note that you’ll need to choose the same Ubuntu 16.04 operating system for your ECS in order to run the experiment smoothly.
Before starting this experiment, make sure that you have closed and exited from the previous experiment.
2. Start the experiment environment
Click Start Lab in the upper-right corner of the page to start the experiment.
.
After the experiment environment is successfully started, the system has deployed resources required by this experiment in the background, including the ECS instances, RDS instances, Server Load Balancer (SLB) instances, and Object Storage Service (OSS) buckets. An account that consists of the username and password for logging on to the Alibaba Cloud console is also provided.
After the experiment environment is started and related resources are properly deployed, the experiment starts a countdown. You have two hours to perform experimental operations. After the countdown ends, the experiment stops and related resources are released. During the experiment, pay attention to the remaining time and arrange your time wisely. Then, use the username and password provided by the system to log on to the Alibaba Cloud console and view related resources.
Go to the logon page of the Alibaba Cloud console.
Enter the Resource Access Management (RAM) user name and click Next.
Enter the RAM user password and click Log on.
After you log on to the console, the following page appears.
3. Prepare data
Go to the RDS console, as shown in the following figure.
Select US(Silicon Valley) region, You can see that an RDS instance is created.
click Manage in the Actions column.
Then, you can see details about the RDS instance.
Click Configure Whitelist.
Click Modify.
Configure the whitelist and click OK, as shown in the following figure.
The whitelist has been added, as shown in the following figure.
Check Internal Endpoint of the RDS instance again.
3.2 Create a database and a database account
Create a database named labex, as shown in the following figures.
Click Create Account, as shown in the following figure.
Set the parameters to create an administrator account, as shown in the following figure. The database account name and password are as follows:
Database account name: labex
Password: Aliyun-test
The account creation takes a few minutes. Please wait patiently.
3.3 Import data
Click Elastic Compute Service, as shown in the following figure.
We can see one running ECS instance in Silicon Valley region.
Copy this ECS instance’s Internet IP address and remotely log on to this ECS (Ubuntu system) instance. For details of remote login, refer to login。
The default account name and password of the ECS instance:
Account name: root
Password: nkYHG890..
After successful logon, run the following command. A sysbench directory is displayed. Go to the directory.
ls
cd sysbench
Run the following command to import test data to the database. Note: Replace YOUR-RDS-PRIMARY-ADDRESS with the internal endpoint of your RDS instance.
sysbench --test=sysbench/tests/db/oltp.lua --mysql-host=YOUR-RDS-PRIMARY-ADDRESS --mysql-port=3306 --mysql-user=labex --mysql-password=Aliyun-test --mysql-db=labex --mysql-table-engine=innodb --oltp-table-size=25000 --oltp-tables-count=250 --db-driver=mysql prepare
This command will import 250 sample tables, each with 25,000 entries.
The following figure shows the method for obtaining YOUR-RDS-PRIMARY-ADDRESS.
The data has been imported.
4. Add read-only instances
Return to the RDS console and add read-only instances, as shown in the following figure.
Select 1-core 1 GB and 50 GB storage. Click Next:Instance Configuration.
Select VPC and click Next:Confirm Order.
Set Purchase Plan to 2 and click Pay Now.
The purchase is completed.
You can see that two read-only instances are being created.
5. Enable the database proxy feature
Click the ID of the primary instance.
Click Enable now, as shown in the following figure.
Click Enable.
The proxy is being created. It will take about seven to eight minutes. Please wait.
After the proxy is created, enable the read/write splitting feature, as shown in the following figure.
Configure the parameters, as shown in the following figure. In this way, all read requests will be evenly distributed to two read-only instances. Click OK.
After the read/write splitting feature is enabled, you can see the read/write splitting address, as shown in the following figure. After a client sends requests to this address, the requests are automatically allocated to each internal instance based on the request type and the preset read weight.
At this point, the user can access the read-only instance through the database read-write separation address (that is, the database proxy address).
6. Conduct tests
6.1 Test the Database proxy address
Return to the command line of the ECS instance that you logged on remotely. Then, run the following command in the /root/sysbench directory to perform a read test on the Database proxy address. The test will last about three minutes.
Note: Replace YOUR-DATABASE-PROXY-ADDRESS with the databse proxy address of your RDS instance.
sysbench --test=sysbench/tests/db/oltp.lua --mysql-host=YOUR-DATABASE-PROXY-ADDRESS --oltp-tables-count=250 --mysql-user=labex --mysql-password=Aliyun-test --mysql-port=3306 --db-driver=mysql --oltp-tablesize=25000 --mysql-db=labex --max-requests=0 --oltp_simple_ranges=0 --oltp-distinct-ranges=0 --oltp-sum-ranges=0 --oltp-order-ranges=0 --max-time=180 --oltp-read-only=on --num-threads=30 --oltp-test-mode=complex run
After the test is completed, you can see the number of read requests and the RDS throughput per second.
<font color='red'>The user can cut off the above result picture when doing the experiment and send it to the teacher, indicating that the part of the current chapter has been completed.</font>
It can be seen that the number of visits per second is about 16000.
Enter the following command, Note: Replace YOUR-RDS-PRIMARY-ADDRESS with the internal endpoint of your RDS instance.
sysbench --test=sysbench/tests/db/oltp.lua --mysql-host=YOUR-RDS-PRIMARY-ADDRESS --oltp-tables-count=250 --mysql-user=labex --mysql-password=Aliyun-test --mysql-port=3306 --db-driver=mysql --oltp-tablesize=25000 --mysql-db=labex --max-requests=0 --oltp_simple_ranges=0 --oltp-distinct-ranges=0 --oltp-sum-ranges=0 --oltp-order-ranges=0 --max-time=180 --oltp-read-only=on --num-threads=30 --oltp-test-mode=complex run
Wait 3 minutes, you can see the results.
It can be seen that the number of visits per second is about 10,000.
Because the backend of the database proxy address has two read-only instances, it can bear greater system throughput.
6.2 Increase read-only throughput
Refer to the method in Section 4 and add a third read-only instance again.
Wait until the status of the third read-only instance is “Running”, refer to the figure below to add the third read-only instance.
After the addition is complete.
Enter the following command to test the database proxy address again.Note: Replace YOUR-DATABASE-PROXY-ADDRESS with the databse proxy address of your RDS instance.
sysbench --test=sysbench/tests/db/oltp.lua --mysql-host=YOUR-DATABASE-PROXY-ADDRESS --oltp-tables-count=250 --mysql-user=labex --mysql-password=Aliyun-test --mysql-port=3306 --db-driver=mysql --oltp-tablesize=25000 --mysql-db=labex --max-requests=0 --oltp_simple_ranges=0 --oltp-distinct-ranges=0 --oltp-sum-ranges=0 --oltp-order-ranges=0 --max-time=180 --oltp-read-only=on --num-threads=30 --oltp-test-mode=complex run
<font color='red'>Users can cut off the above result picture when they are doing the experiment and send it to the teacher, indicating that the current experiment has been completed.</font>
It can be seen that the number of visits per second is about 24000.
It can be seen that when business development requires, users only need to add the number of read-only instances to continuously expand the processing capacity of the system, and the application does not need to be modified.
Note:
Log off your Alibaba RAM user before you click stop to leave this lab. Otherwise, you will encounter some issues when opening a new lab session in the same browser.
7. Experiment summary
The experiment introduces the read/write splitting feature of RDS and describes how to add read-only instances. When a read-only instance is being created, the data is copied from the secondary instance. The data is consistent with that of the primary instance. After the data of the primary instance is updated, the data will be automatically synchronized to all read-only instances immediately. You can add one or more read-only instances to offload read requests from the primary instance and increase application throughput.
