1. Prepare the experiment environment
1.1 Knowledge points
The experiment uses ApsaraDB for POLARDB. It adopts Sysbench to test the performance of ApsaraDB for POLARDB. ApsaraDB for POLARDB is a next-generation relational database service developed by Alibaba Cloud. It is compatible with MySQL, PostgreSQL, and Oracle databases. With superior performance in storage and computing, ApsaraDB for POLARDB can meet the diverse requirements of enterprises. An ApsaraDB for POLARDB cluster has a maximum storage space of 100 TB and can be configured with a maximum of 16 nodes.
1.2 Experiment process
- Install Sysbench.
- Create an ApsaraDB for POLARDB cluster.
- Test cluster addresses.
1.3 Cloud resources required
- Elastic Compute Service (ECS)
- ApsaraDB for POLARDB
1.4 Prerequisites
- If you are using your own Alibaba Cloud account instead of the account provided by this lab to operate the experiment, please note that you will need to choose the same Ubuntu 16.04 operating system for your ECS in order to run the experiment smoothly.
- Before starting the experiment, confirm that the previous experiment has been closed normally and exited.
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 started, the system has deployed resources required by this experiment in the background, including the ECS instance, ApsaraDB for RDS (RDS) instance, Server Load Balancer (SLB) instance, and Object Storage Service (OSS) bucket. 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. Next, 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 RAM user account and click Next.
Enter the password and click Log on.
After you log on to the console, the following page appears.
3. Install Sysbench
3.1 Log on to ECS
Click Elastic Compute Service, as shown in the following figure.
You can see one running ECS instance in the Singapore region. Click this instance to log on to the ECS console, as shown in the following figure.
Copy the public IP address of this ECS instance and remotely log on to this ECS (Ubuntu system) instance. For more information about remote logon, see Logon.
The default account name and password of the ECS instance are as follows:
Account name: root
Password: nkYHG890..
3.2 Install Sysbench
Run the following command to install related compilation tools:
yum install -y gcc gcc-c++ autoconf automake make libtool bzr mysql-devel git mysql
Run the following command to download the Sysbench project:
git clone https://github.com/akopytov/sysbench.git
Run the following commands to access the project and switch to remote branch 0.5:
cd sysbench
git checkout 0.5
Run the following initialization script:
./autogen.sh
Run the following command to check the system configuration:
./configure --prefix=/usr --mandir=/usr/share/man
Run the following command to start compilation:
make
Run the following command to start installation:
make install
Run the following command to configure the Sysbench client so that it can use all CPU cores available to process the data (two CPU cores are used by default). This will also reduce cross-core context switching.
sudo sh -c 'for x in /sys/class/net/eth0/queues/rx-*; do echo f>$x/rps_cpus; done'
Run the following commands to modify the system configuration:
sudo sh -c "echo 32768 > /proc/sys/net/core/rps_sock_flow_entries"
sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-0/rps_flow_cnt"
4. Create an ApsaraDB for POLARDB cluster
4.1 Add a whitelist
Log on to the ApsaraDB for POLARDB console.
You can see that an ApsaraDB for POLARDB cluster is being created. Wait a few minutes until the cluster is created, as shown in the following figure.
Click the cluster name.
Configure the whitelist, as shown in the following figure.
Enter the private IP address of your ECS instance and click OK.
View the private IP address of the ECS instance.
4.2. Create a database account
Click Create Account, as shown in the following figure.
Set the administrator account “labex” and click OK, as shown in the following figure.
Wait about one minute for the account to be created.
4.3 Test the address of the primary node
Return to the command line interface (CLI) of the ECS instance.
Run the following command to create a database named “sbtest.” Note: Replace YOUR-PRIMARY-DOMAIN with the domain name of the primary node in your ApsaraDB for POLARDB cluster.
mysql -ulabex -pAliyun-test -P3306 -hYOUR-PRIMARY-DOMAIN -e 'CREATE DATABASE sbtest'
View YOUR-PRIMARY-DOMAIN.
Run the following command to prepare the test data. The command will automatically create 250 tables and insert 25,000 pieces of data into each table. The command will last for about five minutes. Note: Replace YOUR-PRIMARY-DOMAIN with the domain name of the primary node in your ApsaraDB for POLARDB cluster.
sysbench --test=sysbench/tests/db/oltp.lua --mysql-host=YOUR-PRIMARY-DOMAIN --mysql-port=3306 --mysql-user=labex --mysql-password=Aliyun-test --mysql-db=sbtest --mysql-table-engine=innodb --oltp-table-size=25000 --oltp-tables-count=250 --db-driver=mysql prepare
Run the following command to perform a read performance test. The test will last for about three minutes. Note: Replace YOUR-PRIMARY-DOMAIN with the domain name of the primary node in your ApsaraDB for POLARDB cluster.
sysbench --test=sysbench/tests/db/oltp.lua --mysql-host=YOUR-PRIMARY-DOMAIN --oltp-tables-count=250 --mysql-user=labex --mysql-password=Aliyun-test --mysql-port=3306 --db-driver=mysql --oltp-tablesize=25000 --mysql-db=sbtest --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=300 --oltp-test-mode=complex run
After the test is completed, the test report shown in the preceding figure appears. The results show that the number of read requests per second is about 21,000, which may be slightly different in your experiment.
Run the following command to perform a write performance test. The test will last for about three minutes. Note: Replace YOUR-PRIMARY-DOMAIN with the domain name of the primary node in your ApsaraDB for POLARDB cluster.
sysbench --test=sysbench/tests/db/oltp.lua --mysql-host=YOUR-PRIMARY-DOMAIN --oltp-tables-count=250 --mysql-user=labex --mysql-password=Aliyun-test --mysql-port=3306 --db-driver=mysql --oltp-tablesize=25000 --mysql-db=sbtest --max-requests=0 --max-time=180 --oltp_simple_ranges=0 --oltp-distinct-ranges=0 --oltp-sum-ranges=0 --oltp-order-ranges=0 --oltp-point-selects=0 --num-threads=128 --randtype=uniform --oltp-test-mode=complex run
The results show that the number of write requests per second is about 10,000, which may be slightly different in your experiment.
5. Test cluster addresses
5.1 Learn about cluster addresses
Each ApsaraDB for POLARDB cluster has one primary node and one read-only node by default.
Applications can connect to multiple nodes by connecting to one cluster address. With the read/write splitting function, write requests are automatically sent to the primary node, and read requests are automatically sent to the primary node or read-only node based on the load of each node.
An ApsaraDB for POLARDB cluster contains a default cluster address. You can also create multiple custom cluster addresses as needed. The custom cluster addresses can connect to the specified nodes and set the read/write mode.
5.2 Add the read-only node
Click Add/Remove Node.
Select Add Node and click OK.
Add a second read-only node and click Activate, as shown in the following figure.
If the following error is reported (probably due to network problems), it does not affect. The node has been added.
Return to the ApsaraDB for POLARDB console. You can see that the second read-only node is being created. This takes about five minutes. Only one node can be added at a time. The creation process does not affect the access to ApsaraDB for POLARDB, which can still be used normally.
After the second read-only node is created, repeat the preceding process to add another read-only node.
After this read-only node is added, there are three read-only nodes.
5.3 Add a custom cluster endpoint
Click Create Custom Cluster Endpoint, as shown in the following figure.
Add two nodes to the new cluster endpoint, and click OK, as shown in the following figure.
This new cluster endpoint is added.
Click Create Custom Cluster Endpoint again. Create a second custom cluster endpoint.
Add the other two nodes to the cluster endpoint, and click OK, as shown in the following figure.
The second custom cluster endpoint is added.
5.4 Test
Open a new ECS CLI and run the following command to go to the “sysbench” directory.
cd sysbench
Currently, two ECS CLIs are available, both of which are in the “sysbench” directory.
Run the following commands on both the two CLIs to test the read performance of the current cluster address. We recommend that you first copy the commands to the CLIs. Until you copy both commands, press Enter to run them in sequence and start the test. Note: Replace YOUR-CLUSTER-DOMAIN with the default address of your ApsaraDB for POLARDB cluster.
sysbench --test=sysbench/tests/db/oltp.lua --mysql-host=YOUR-CLUSTER-DOMAIN --oltp-tables-count=250 --mysql-user=labex --mysql-password=Aliyun-test --mysql-port=3306 --db-driver=mysql --oltp-tablesize=25000 --mysql-db=sbtest --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=300 --oltp-test-mode=complex run
Obtain YOUR-CLUSTER-DOMAIN.
After the action, wait for about three minutes.
After the test is completed, you can see that the total number of read/write requests per second is about 10,000 on each of the two CLIs. The sum of read/write requests per second on the two CLIs is about 20,000, the level that we obtained in the previous test.
Next, change the two cluster endpoints used by the performance test commands to different cluster endpoints.
Run the command on two CLIs, as shown in the following figure.
After the test is completed, the total number of read/write requests per second is about 60,000 on the two CLIs, indicating that the increase of custom cluster endpoints and read-only nodes can greatly improve the system throughput of ApsaraDB for POLARDB.
Reminder:
Before you leave this lab, remember to log off from your Alibaba Cloud RAM account before you click the stop button of your lab. Otherwise, you will encounter some issues when opening a new lab session in the same browser.
6. Experiment summary
The experiment adopts Sysbench to test the performance of ApsaraDB for POLARDB. ApsaraDB for POLARDB uses a storage and computing-separated architecture, in which all compute nodes share one copy of data. It can achieve scaling within minutes and crash recovery within seconds. It ensures global data consistency, and offers free services for data backup and disaster recovery. ApsaraDB for POLARDB integrates the benefits of commercial databases and open-source cloud databases. Commercial databases are stable, reliable, high-performance, and scalable while open-source databases are easy to use and self-iterative. For example, the time a POLARDB for MySQL database takes to return results for a query reduces by five times than that a MySQL database takes. However, the cost of a POLARDB for MySQL database is only 10% that of a MySQL database.
