Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Cross-region Read and Write Performance Testing for PolarDB GDN

Cross-region Read and Write Performance Testing for PolarDB GDN

This article introduces how to benchmark PolarDB for MySQL’s Global Database Network (GDN) read- and write-performance across regions using Sysbench.

Test Tool

Sysbench is a modular, cross-platform, and multi-threaded benchmark tool that can be used to evaluate the performance of a heavily loaded database system based on core metrics. Sysbench allows you to test the performance of a database without the need to configure complicated benchmark settings or install the database engine.

Connect to the Primary Cluster Test Environment

• The Elastic Compute Service (ECS) instance and the PolarDB for MySQL cluster used in the test are deployed in the same region (Hong Kong).

• The test primary cluster is a Hong Kong node with 4 cores and 8 GB of memory, including one primary node and one read-only node.

• The test secondary cluster is a US Silicon Valley node with 4 cores and 8 GB of memory, including one primary node and one read-only node.

• The endpoint is the private IP address of the primary cluster.

Snapshot of test instance specifications:

1

Snapshot of the endpoint:

2

Metrics

• Transactions per second (TPS): the number of transactions that are performed per second in the database. Only committed transactions are counted.

• Queries per second (QPS): the number of SQL statements that are executed per second in the database, including the INSERT, SELECT, UPDATE, and DELETE statements.

Install Sysbench

1.  Run the following commands on the ECS instance to install Sysbench:

yum install gcc gcc-c++ autoconf automake make libtool bzr mysql-devel git mysql
https://github.com/akopytov/sysbench.git
## Download Sysbench from Github
cd sysbench
## Change the current working directory to Sysbench.

./autogen.sh
## Run autogen.sh.
./configure --prefix=/usr --mandir=/usr/share/man
make
## Compile Sysbench.
make install

3

Step 1: Connect to the Primary Cluster on the ECS Instance and Create a Database

  1. Obtain the cluster endpoint and port number of the PolarDB for MySQL cluster.
  2. Connect to the primary cluster on the ECS instance and create a database named testdb for performance testing.
mysql -h xxxxxxxx.aliyuncs.com -P 3306 -u xxxxx -p
Item Description
-h The cluster endpoint of the PolarDB for MySQL cluster.
-P The port number of the PolarDB for MySQL cluster.
-u The username of the PolarDB for MySQL cluster.
-p The password that is used to log on to the PolarDB for MySQL cluster.

4

Step 2: Prepare Data by Using Sysbench

Use Sysbench to load test data to PolarDB for MySQL primary cluster.

sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600  oltp_read_only prepare
## Prepare the test data.

Snapshot of preparing data

5

View the GDN synchronization latency

6

View generated data

7

Step 3: Test the Read Performance of the Cluster

sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600   --threads=XXX --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=1 oltp_read_only run
## Run the workload.

The running process will last about 8 minutes.

8

View the performance monitoring of primary and secondary clusters while running a workload.

Performance monitoring of the primary cluster HK

9

Performance monitoring of the secondary cluster (US Silicon Valley)

10

Test the read performance. The results are as follows:

11

The results of read performance testing are as follows:

• QPS: 15598.40

• Response time (RT): 6.43 ms

Step 4: Test the Write Performance of the Cluster

sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600   --threads=XXX --percentile=95 --report-interval=1 oltp_write_only run
## Run the workload.

The running process will last about 8 minutes.

12

View the performance monitoring of primary and secondary clusters while running a workload.

Performance monitoring of the primary cluster HK

13

Performance monitoring of the secondary cluster (US Silicon Valley)

14

Test the write performance. The results are as follows:

15

The results of write performance testing are as follows:

• TPS: 2287.84

• Response time (RT): 4.1 ms

Connect to the Test Environment of the Secondary Cluster

• The Elastic Compute Service (ECS) instance and the PolarDB for MySQL cluster used in the test are deployed in the same region (US Silicon Valley).

• The test primary cluster is a Hong Kong node with 4 cores and 8 GB of memory, including one primary node and one read-only node.

• The test secondary cluster is a US Silicon Valley node with 4 cores and 8 GB of memory, including one primary node and one read-only node.

• The endpoint is the internal IP address of the secondary cluster.

Node specifications are the same as above.

Snapshot of the endpoint:

16

Metrics

• Transactions per second (TPS): the number of transactions that are performed per second in the database. Only committed transactions are counted.

• Queries per second (QPS): the number of SQL statements that are executed per second in the database, including the INSERT, SELECT, UPDATE, and DETELE statements.

Install Sysbench

The steps are the same as those in the preceding section.

Step 1: Connect to the Secondary Cluster on the ECS Instance and Create a Database

  1. Obtain the cluster endpoint and port number of the PolarDB for MySQL cluster.
  2. Connect to the secondary cluster on the ECS instance and create a database named testdb for performance testing.
mysql -h xxxxxxxx.aliyuncs.com -P 3306 -u xxxxx -p

17

Step 2: Prepare Data by Using Sysbench

Use Sysbench to load test data to PolarDB for MySQL secondary cluster.

sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600  oltp_read_only prepare
## Prepare the test data.

Snapshot of preparing data:

18

View the GDN synchronization latency:

19

View the snapshot of the data generated and loaded by the primary and secondary clusters:

20

Step 3: Test the Read and Write Performance of the Secondary Cluster at the Same Time

1.  Launch two ECS instances respectively and select the secondary clusters that are in the same region (US Silicon Valley).

21

2.  Install Sysbench on the two ECS instances and add them to the same VPC of the secondary cluster. The procedure is the same as above.

22

3.  Run Sysbench on both ECS instances to test the read and write performance of the secondary cluster.

The command for the read performance testing:

sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=10000 --tables=20 --events=0 --time=60   --threads=32 --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=1 oltp_read_only run
## Run the workload.

The command for the write performance testing:

sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=10000 --tables=20  --events=0 --time=60   --threads=32 --percentile=95 --report-interval=1 oltp_write_only run
## Run the workload.

23

Snapshot of test results:

24

The results of read performance testing are as follows:

• QPS: 75473.06

• Response time (RT): 4.74 ms

The results of write performance testing are as follows:

• TPS: 33.02

• Response time (RT): 995.51 ms

Latency snapshots during read/write performance testing:

25

Python Script Latency Test

# Read workload
import mysql.connector
import sys
import os
import logging
import time
import datetime


def do_check_time():
    conn = mysql.connector.connect(
        host='xxxxxxxxx.rds.aliyuncs.com',
        # host='xxxxxxxxxxxxxxx.rds.aliyuncs.com',
        port=3306,
        user='xxxxxx',
        password='xxxxxxxx')
    if not conn:
        logging.info("get connect fail")
        assert False
    i = 1
    total_difference = 0

    delayed = ''

    sum = 0

    avg_delayed = ''
    print("################################################################################################")
    print("\t \t Output as \t hour : minutes : seconds : millisecond")
    print("##########################################Reader data###########################################")
    print()

    with open('delayed.log', 'a') as file:
        file.write("################################################################################################\n \t Output as \t hour : minutes : seconds : millisecond" +
                   "\n##########################################Reader data###########################################\n")

    while i <= 100:
        sqlstmt = "select insert_at from sbtest.test  where id={}".format(i)
        cursor = conn.cursor(dictionary=True)
        cursor.execute(sqlstmt)
        for insert_at in cursor:
            total_difference = 0
            if len(insert_at) == 0:
                print("wait")
            else:
                i += 1
                print("insert_at: ", insert_at)
                result = datetime.datetime.now()-insert_at['insert_at']

                # sum = result.total_seconds()

                sum = sum + result.total_seconds()
                delayed = str(result)

                avg_delayed = sum/100
                avg_delayed = str(avg_delayed)

                print('Read delayed:\t' + str(result) + " ms")

                print('total_delayed ' + str(sum) + " S")

                with open('delayed.log', 'a') as file:
                    file.write("Read delayed:  "+delayed+" ms"+'\n')

    print("########################################## Reader data  End #####################################")
    print()

    print('total_delayed :'+str(sum)[0:7]+' S')
    print('avg_delayed  :'+avg_delayed[0:6] + ' S')

    with open('delayed.log', 'a') as file:
        file.write("###################################### Reader data  End ###############################################"+'\n'
                   + '\t'+"Total_delayed: "+str(sum)[0:5]+' S \n' +
                    + '\t'+'avg_delayed: '+str(avg_delayed)[0:5]+' S'+'\n\n')

    cursor.close()


if __name__ == "__main__":
    do_check_time()
# Insert workload script
#!/bin/sh

HOST=xxxxxxxx.aliyuncs.com
PORT=3306
USER=admin
PASSWORD=xxxxxxx
echo "create table test"
mysql -h$HOST -P$PORT -u$USER -p$PASSWORD -e"use sbtest;create table test(id int primary key, insert_at TIMESTAMP(6));"

i=1

while ((i <= 100))
do
CMD="use sbtest;insert into test values($i, CURRENT_TIMESTAMP(6));"
echo $CMD
mysql -h$HOST -P$PORT -u$USER -p$PASSWORD -e"$CMD"
((i++))
sleep 1
done

Video of test results:

https://csv-imput.oss-cn-shenzhen.aliyuncs.com/video/delayed_test.mp4

Script output:

📎delayed.log

Database performance Developers POLARDB Elastic Compute Service Tutorials Sysbench PolarDB for MySQL GDN
0 1 0
Share on

Read previous post:

Dify on Alibaba Cloud DMS for ChatBI Scenarios

Read next post:

Migrate Data from a Self-managed SQL Server 2012 to an ApsaraDB RDS for SQL Server Instance

ApsaraDB

559 posts | 178 followers

You may also like

Comments

ApsaraDB

559 posts | 178 followers

Related Products

More Posts by ApsaraDB

See All