Amazon RDS vs. Google SQL benchmark

During app development at Ackee, we have tested two database-as-a-service solutions from two major players: Google Cloud SQL Second Generation, Amazon RDS for MySQL and official MariaDB Docker container running as a single instance without any replication on Google Container Engine. MariaDB was used in the test as a baseline, only to see if there is any significant performance overhead of replication and the difference between MySQL and MariaDB. What are the benchmark results?

SQL Database benchmark

The workload compares multi-zone (highly available) instances of Google Cloud SQL Second Generation, Amazon RDS for MySQL and latest offered MariaDB version docker container on Google Container Engine. The replication technology used by these three services differs significantly, and has a big impact on performance and latency on big heavy instances (16+ CPU, 104+ GB RAM as mentioned here) but it doesn’t seem so significant on smaller instances. Google Cloud SQL Second Generation uses MySQL’s semi-synchronous replication, RDS for MySQL uses block-level synchronous replication and the Mariadb Docker Container doesn’t use any replication.

We chose smaller instances with the same storage size (with as much IOPS as we could get) with a comparable price for the service.

To determine throughput, a Sysbench OLTP workload was generated from a MySQL client in the same zone as the primary database instance. The workload is a set of step load tests that double the number of threads (connections) with each run. The data set used is almost three times larger than total memory of the database instance to ensure that reads go to disk.

Transaction per second (TPS) results show that Google Cloud SQL and the MariaDB are faster than RDS for MySQL. Google Cloud SQL’s TPS is much higher than RDS at up to 16 threads. At 64 threads, variance and the potential for replication lag increase, causing Cloud SQL’s perfomance drop at higher thread counts. The workload illustrates the differences in replication technology between the three services. Unfortunately, the MariaDB runnning on GKE wasn’t able to accept more than 100 connections (threads). Otherwise it would probably outperform both Cloud SQL’s. Google Cloud SQL emphasizes performance, allowing for replication lag, which can increase failover times, but without putting data at risk.

Latency

We measured average end-to-end latency with a single client thread (i.e., “pure” latency measurement).

The latency comparison changes as additional threads are added. Google Cloud SQL exhibits lower latency than RDS for MySQL across all tests. Compared to the MariaDB, Google Cloud SQL’s latency is about the same.

Running the benchmark

Environment configuration and sysbench parameters for our testing.
We used the following environment configuration and sysbench parameters for our testing.

Test instances:

• Google Cloud SQL v2, db-n1-standard-2 (2 CPU, 7.5 GB RAM), MySQL 5.7, 100 GB PD SSD (3k IOPS) + Failover Replica
• Amazon RDS Multi-AZ, db.t2.large (2 CPU, 8 GB RAM), MySQL 5.7.17, 100 GB SSD, 1k Provisioned IOPS + Multi-AZ Replica
• Google Container Engine with MariaDB Docker Container,  db-n1-standard-2 (2 CPU, 7.5 GB RAM), 100 GB PD SSD (3k IOPS), No Failover Replicas

Test overview:

Sysbench runs were 1 table of 100M rows. With 100M rows, the data set size as loaded was ~25 GB inorder to ensure that the data set didn’t fit in RAM. Each step run was 5 minutes with a one minute “cool down” period in between.

#prepare
sysbench --test=oltp --oltp-table-size=10000000 
         --mysql-host=$host --mysql-user=$user  
         --mysql-password=$pw --db-driver=mysql 
           prepare

#run oltp for different number of threads
for i in 1 2 4 8 16 32 64 128 256 512 1024 2048 4096;
do
 echo running test for $i threads
 sysbench --test=oltp --max-requests=0 --num-threads=$i     
    --max-time=3600 --mysql-host=$host --mysql-password=$pw 
    --mysql-user=$user --db-driver=mysql                    
    --oltp-test-mode=complex run >/data/set-${newDataSet}/${host}-${i}.txt
 echo "cooling down for one minute."
 sleep 60
done

First, we prepare the data (i.e. fill the mysql database with random data). Second, we run the test for 1, 2, 4, 8 .. 4096 threads (such a high number as 4096 threads will most likely fail on most platforms/services) for –max-time seconds each test, unlimited number or requests (the test goes without stopping for the given number of threads) and after each test there is a cooldown period for the mysql to “rest and take a breath”.

All the scripts to run and prepare the OLTP tests, environment settings and provisioning as well as plotting the data with gnuplot can be found in this github repo

Result

Google Cloud SQL Second Generation outperformed RDS for MySQL when active thread count is low, as is typical for many web applications. MariaDB performed slightly better than Google Cloud SQL‘s latest MySQL but wasn’t able to accept more than 100 db connections.

Credits