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.
![image desc](https://labex.io/upload/D/V/E/lDoB6W2UJegC.png)
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.
![openCole](https://labex.io/upload/H/U/J/4cqqVcc2DZXh.png)
Go to the logon page of the Alibaba Cloud console.
![image desc](https://labex.io/upload/W/W/D/r1FGF1dU4Hmp.png)
Enter the Resource Access Management (RAM) user name and click Next.
![image desc](https://labex.io/upload/U/H/M/FWna8Tewo7ua.png)
Enter the RAM user password and click Log on.
![image desc](https://labex.io/upload/N/J/J/CZpKoFKJnCQL.png)
After you log on to the console, the following page appears.
![image desc](https://labex.io/upload/Y/K/F/e4vf4XHIHn8n.jpg)
3. Prepare data
Go to the RDS console, as shown in the following figure.
![image desc](https://labex.io/upload/N/T/C/oPFpjScrxdJ2.jpg)
Select US(Silicon Valley) region, You can see that an RDS instance is created.
![image desc](https://labex.io/upload/T/J/V/INGbY9kcyXcl.jpg)
click Manage in the Actions column.
![image desc](https://labex.io/upload/Y/T/D/pbCjrSM5CpRS.jpg)
Then, you can see details about the RDS instance.
![image desc](https://labex.io/upload/V/Q/O/Q3bBF3ZVkFCa.jpg)
Click Configure Whitelist.
![image desc](https://labex.io/upload/E/C/M/BeGhUVslYuO8.jpg)
Click Modify.
![image desc](https://labex.io/upload/X/C/N/PNGaJY5CDkOi.jpg)
Configure the whitelist and click OK, as shown in the following figure.
![image desc](https://labex.io/upload/V/D/M/fMmwek1iiAnO.jpg)
The whitelist has been added, as shown in the following figure.
![image desc](https://labex.io/upload/R/H/E/qvX95XGFjW7B.jpg)
Check Internal Endpoint of the RDS instance again.
![image desc](https://labex.io/upload/N/L/B/BS5qj7nelQTB.jpg)
3.2 Create a database and a database account
Create a database named labex, as shown in the following figures.
![image desc](https://labex.io/upload/T/M/A/6qzvnvZwPUZe.jpg)
![image desc](https://labex.io/upload/V/G/V/yiYjDqUwTi54.jpg)
Click Create Account, as shown in the following figure.
![image desc](https://labex.io/upload/B/P/Y/7FcmcFyIwuo4.jpg)
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
![image desc](https://labex.io/upload/G/A/V/OfHbLsIpidzH.jpg)
The account creation takes a few minutes. Please wait patiently.
![image desc](https://labex.io/upload/I/N/D/a57QIPGnaDVm.jpg)
![image desc](https://labex.io/upload/H/O/E/gOHbwh4kkbkP.jpg)
3.3 Import data
Click Elastic Compute Service, as shown in the following figure.
![image desc](https://labex.io/upload/Q/C/E/vWl7sSgr7lBx.jpg)
We can see one running ECS instance in Silicon Valley region.
![image desc](https://labex.io/upload/S/E/D/vDZbYfsSiRRV.jpg)
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。
![image desc](https://labex.io/upload/Q/H/O/mbkcyjC1JCxF.jpg)
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
![image desc](https://labex.io/upload/U/P/K/Kdp2079t1sP7.jpg)
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.
![image desc](https://labex.io/upload/H/E/P/A7XaeyhPSwkF.jpg)
![image desc](https://labex.io/upload/Y/U/P/2ZR2HitKjmW5.jpg)
The data has been imported.
![image desc](https://labex.io/upload/F/T/D/b0hyQAo7z7OS.jpg)
4. Add read-only instances
Return to the RDS console and add read-only instances, as shown in the following figure.
![image desc](https://labex.io/upload/I/R/J/goiXGuvs3UzA.jpg)
Select 1-core 1 GB and 50 GB storage. Click Next:Instance Configuration.
![image desc](https://labex.io/upload/I/U/W/JXZIx44Nxvdr.jpg)
Select VPC and click Next:Confirm Order.
![image desc](https://labex.io/upload/V/E/T/R6w0g6EDJQjh.jpg)
Set Purchase Plan to 2 and click Pay Now.
![image desc](https://labex.io/upload/E/N/C/ukyO1olAGmUY.jpg)
The purchase is completed.
![image desc](https://labex.io/upload/A/F/W/53Gqda1v8cb3.jpg)
You can see that two read-only instances are being created.
![image desc](https://labex.io/upload/P/Y/J/rf1C82nzPz5S.jpg)
5. Enable the database proxy feature
Click the ID of the primary instance.
![image desc](https://labex.io/upload/U/T/P/FIP8kcc78FOS.jpg)
Click Enable now, as shown in the following figure.
![image desc](https://labex.io/upload/A/D/E/CPxayeUnjOEz.jpg)
Click Enable.
![image desc](https://labex.io/upload/A/K/E/SismeERk5XLA.jpg)
The proxy is being created. It will take about seven to eight minutes. Please wait.
![image desc](https://labex.io/upload/R/L/L/BGvMt6WyuZDW.jpg)
After the proxy is created, enable the read/write splitting feature, as shown in the following figure.
![image desc](https://labex.io/upload/F/U/W/K1hbpj2ntg1C.jpg)
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.
![image desc](https://labex.io/upload/R/V/H/3f8E74Jpdcss.jpg)
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.
![image desc](https://labex.io/upload/Y/G/K/iVlPz8bl97Vj.jpg)
At this point, the user can access the read-only instance through the database read-write separation address (that is, the database proxy address).
![image desc](https://labex.io/upload/J/E/L/p1XFYB5jQ2df.jpg)
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
![image desc](https://labex.io/upload/S/P/M/WtakZRwUhRE7.jpg)
After the test is completed, you can see the number of read requests and the RDS throughput per second.
![image desc](https://labex.io/upload/E/Q/Q/CrxGlbQvwFXk.jpg)
<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
![image desc](https://labex.io/upload/F/A/A/rPW8CYwinIX2.jpg)
Wait 3 minutes, you can see the results.
![image desc](https://labex.io/upload/S/K/L/8PMFmP64zwMG.jpg)
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.
![image desc](https://labex.io/upload/C/V/S/W1oj1oXCiQDH.jpg)
Wait until the status of the third read-only instance is “Running”, refer to the figure below to add the third read-only instance.
![image desc](https://labex.io/upload/A/X/W/X2m5j9XgAo5i.jpg)
![image desc](https://labex.io/upload/F/T/R/QBUPSv9HJM3L.jpg)
After the addition is complete.
![image desc](https://labex.io/upload/A/L/T/NrMqsm5OnEXs.jpg)
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
![image desc](https://labex.io/upload/E/G/W/prcdyGlPK5gd.jpg)
<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.
![image desc](https://labex.io/upload/L/O/A/wNqHv3R4rO6f.png)
![image desc](https://labex.io/upload/G/Q/S/nohwU5ZhENyN.png)
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.