With the surge in the number of Database-as-a-Service providers in the market today, many DBaaS providers claim to offer competitive database performance for your applications. The only true way of evaluating the performance of the database is by running performance benchmarks for the databases running on the cloud. In this article, we run performance benchmarks to evaluate, analyze, and compare the performance of MySQL database engines running on the AWS cloud for Tessell. We have used the Sysbench benchmarking tool for the benchmarking process.

Before starting the benchmarking process, you need to get your environment ready.

Prepare your environment

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

1. 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.

2. 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.

3. 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:

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:

Login to MySQL server via any client and follow below steps.

https://github.com/akopytov/sysbench

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

1. Database Name to run the Test: CREATE DATABASE sysbench

Run the benchmark

1. Load the instance with desired database size

   1. 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

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

2. Run Write-Only Load Test and capture results.

   1. 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.

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

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

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

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

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

Results

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

1. QPS - Questions/Query Per Second

2. TPS - Transactions Per Second

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

1. Read Only Load

   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

   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

   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

   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: