MySQL

Benchmark Tessell RDS MySQL performance using Sysbench

Priyank Parikh

April 19, 2023

Prepare your environment

To prepare the environment for the benchmarking process, perform the following high-level tasks:

Provision a Tessell RDS MySQL instance to benchmark the performance testing. We are using the Tessell shape “tesl_8h_a” on the AWS cloud. This shape comes with 8 vCPUs and 64 GB RAM. While provisioning the instance, note down the username and password to connect to your database instance.
Launch or create an Amazon EC2 instance to install the Sysbench benchmarking tool, and set up the load. It is recommended that you create the instance in the same Virtual Private Cloud (VPC) as your Tessell RDS instance to keep the latency minimum.
Set up the security groups for the client and server machines in a way that the client machine can connect to the server machine over the database port TCP:3306. For more information, see Default security groups for your VPC.

The following diagram shows the recommended environment for running the benchmarking process. The VPC located in the AWS cloud contains the Tessell RDS MySQL instance and the Sysbench client installed in the Amazon EC2 instance.

Provision the Sysbench client machine

Firstly, provision the client Linux machine to install the Sysbench benchmarking tool. For our test, we provisioned the Amazon Linux instance with the following configuration details:

Copied to clipboard!


Image: Amazon Linux 2 Kernel 5.10 AMI 2.0.20221210.1 x86_64 HVM gp2 Shape: m5.2xlarge
VPC: Same as the DB Service

Download Sysbench and configure MySQL

Once Sysbench is installed we need to configure following MySQL server items:

Copied to clipboard!


https://github.com/akopytov/sysbench

Sysbench can be downloaded and configured from the repository link below for respective OS.

Database Name to run the Test: CREATE DATABASE sysbench

Run the benchmark

1. Load the instance with desired database size
• Below Settings will load 100 Million Rows with 26 GB Data. For every 100 Million Rows it is 26 GB Data. Load per the benchmark requirements. Max Limits - Table size: 999 Million

Copied to clipboard!


sysbench oltp_insert --threads=500 --time=3000 --table-size=100000000 --db-driver=mysql --mysql-db=sysbench --mysql-user= --mysql-password=--mysql-storage-engine=InnoDB --report-interval=60 --mysql-host= prepare

2. Run Write-Only Load Test and capture results.
• Adjust the no of threads, table size, time as per the test requirements. Max Limits - Threads: 1000, Time: 9999 seconds, Table_size: 999 Million, report-interval: This option outputs the ongoing test runs every n seconds defined.

Copied to clipboard!


sysbench oltp_insert --threads=500 --time=3000 --table-size=100000000 --db-driver=mysql --mysql-db=sysbench --mysql-user= --mysql-password=--mysql-storage-engine=InnoDB --report-interval=60 --mysql-host= run

3. Run Read-Only Load Test and capture results.

Copied to clipboard!


sysbench oltp_read_write --threads=500 --time=3000 --table-size=10000000 --db-driver=mysql --mysql-db=sysbench --mysql-user= --mysql-password=--mysql-storage-engine=InnoDB --report-interval=60 --mysql-host= run

4. Run Read-Write Load Test and capture results.

Copied to clipboard!


sysbench oltp_read_only --threads=500 --time=3000 --table-size=10000000 --db-driver=mysql --mysql-db=sysbench --mysql-user= --mysql-password=--mysql-storage-engine=InnoDB --report-interval=60 --mysql-host= run

Results

When the performance test completes, We get the following TPCC based output.

QPS - Questions/Query Per Second
TPS - Transactions Per Second

Tessell MySQL Instance High Performance Shape - 8 vCPUs, 64 GB Memory

1. Read Only Load

Copied to clipboard!


SQL statistics:
   queries performed:
     read:                              107064216
     write:                             0
     other:                             15294888
     total:                             122359104
   transactions:                        7647444 (4246.62 per sec.)
   queries:                             122359104 (67945.90 per sec.)
   ignored errors:                      0      (0.00 per sec.)
   reconnects:                          0      (0.00 per sec.)

General statistics:
   total time:                          1800.2757s
   total number of events:              7647444

Latency (ms):
   min:                                    2.41
   avg:                                  235.41
   max:                                 4034.71
   95th percentile:                      253.35
   sum:                            600093627.96

Threads fairness:
   events (avg/stddev):           2549.1480/66.30
   execution time (avg/stddev):   1800.0936/0.06

2. Read Write Load

Copied to clipboard!


SQL statistics:
   queries performed:
     read:                            96958176
     write:                           27702336
     other:                           13851168
     total:                           138511680
   transactions:                      13753764 (7690.98 per sec.)
   queries:                           138511680 (76950.93 per sec.)
   ignored errors:                    0      (0.00 per sec.)
   reconnects:                        0      (0.00 per sec.)

General statistics:
   total time:                          1800.1590s
   total number of events:              13753764

Latency (ms):
   min:                                   13.62
   avg:                                  129.97
   max:                                 1437.25
   95th percentile:                      231.53
   sum:                            300032956.05

Threads fairness:
   events (avg/stddev):           2308.5280/85.96
   execution time (avg/stddev):   1800.0330/0.03

RDS for MySQL - 8 vCPUs, 64 GB Memory

1. Read Only Load

Copied to clipboard!


SQL statistics:
   queries performed:
     read:                              8778868
     write:                             0
     other:                             1254124
     total:                             10032992
   transactions:                        627062 (343.92 per sec.)
   queries:                             10032992 (5502.76 per sec.)
   ignored errors:                      0      (0.00 per sec.)
   reconnects:                          0      (0.00 per sec.)

General statistics:
   total time:                          1823.2650s
   total number of events:              627062

Latency (ms):
   min:                                    104.37
   avg:                                 2871.45
   max:                                60337.62
   95th percentile:                     6026.41
   sum:                           1800577096.12

Threads fairness:
   events (avg/stddev):           627.0620/5.62
   execution time (avg/stddev):   1800.5771/0.78

2. Read Write Load

Copied to clipboard!


SQL statistics:
   queries performed:
     read:                            62039418
     write:                           17725548
     other:                           8862774
     total:                           88627740
   transactions:                      4431387 (2461.48 per sec.)
   queries:                           88627740 (49229.59 per sec.)
   ignored errors:                    0      (0.00 per sec.)
   reconnects:                        0      (0.00 per sec.)

General statistics:
   total time:                          1800.2921s
   total number of events:              4431387

Latency (ms):
   min:                                   20.13
   avg:                                  406.20
   max:                                 2377.74
   95th percentile:                      623.33
   sum:                           1800027924.89

Threads fairness:
   events (avg/stddev):           4431.3870/108.58
   execution time (avg/stddev):   1800.0279/0.03

Conclusion

It is, therefore, safe to conclude that Tessell RDS produced 321% higher IOPS as compared to AWS RDS for the identical Sysbench workload at the same cost. The image below depicts the visual comparison of the performance benchmark results that Tessell RDS and AWS RDS produce: