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.

Name Configuration HA Disk Price
Amazon RDS db.t2.large (2cpu, 8GB RAM) Multizone A-Z 100GB SSD (provisioned IOPS) 219+$
Google SQL 2nd gen db-n1-standard-2 (2cpu, 7.5GB RAM) HA failover replica 100GB SSD 220$
MariaDB Docker on GKE 2x n1-standard-2 (2cpu, 7.5GB RAM) 100GB SSD 117$ 

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.

google sql vs. amazon rds benchmark
Google Cloud SQL has higher TPS (transactions per second) than RDS for MySQL. With more than 64 threads the perfomance goes rapidly down. Our MariaDB performs mostly better than the rest due to no replication lag.

 

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.

google sql vs. amazon rds benchmark
Cloud SQL sustains higher TPS (transactions per second) per thread than RDS for MySQL. It outperforms Aurora in configurations of up to 16 threads.

Latency

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

google sql vs. amazon rds benchmark

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.

google sql vs. amazon rds benchmark
Average latency for Google Cloud SQL vs. Amazon RDS with less than 64 threads. Amazon RDS has much higher latency possibly caused by slower SSD and replication strategy.
google sql vs. amazon rds benchmark
Average latency for Google Cloud SQL vs. Amazon RDS with up to 256 threads. Amazon RDS has still much higher latency.

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.

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

This article is nothing but an experiment of repeating the benchmarks mentioned in 2ndwatch blogpost  and Google blog post.

5 KOMENTÁŘŮ:

  1. Using a burstable db.t2.large instance for RDS is cheating. Once CPU credits are depleted it will become 3x slower. For correct comparison db.m4.large should be used.

    And how did you get such pricing? I’ve tried and numbers just don’t add up.

    db.t2.large, 100 GB SSD, 1k IOPS, Multi AZ:
    0.272/h + 0.25/gb + 0.2/iops = 196 + 25 + 200 = $421

    Even with 3-year all upfront (which doesn’t fit many companies) it is $313

    Overall this comparison seems biased towards AWS pricing-wise

    1. db.t2.large, 100 GB SSD, 1k IOPS, Multi AZ:
      0.272/h + 0.25/gb + 0.2/iops = 196 + 25 + 200 = $421

      why is 0.25*100 = 25, but 0.2*100= 200? :)) the price for storage is in GB-month and IOPS-month units 🙂 I didn’t count any discounts nor upfront payments as it wouldn’t be fair and as you agree it doesn’t fit many companies

      db.t2.large costs 0.272/h while db.m4.large costs 0.35/h. I mentioned that I was trying to compare instances that cost the same. I run the tests multiple times in a really long timespan. The burst is negligible. Despite that possible advantage of RDS, it has lost on performance as well.

      1. Because for 1000 IOPS I calculated is as 0.2*1000 and not 0.2*100, hence $200. Did I get it wrong?

        I’m looking at pricing for both clouds and still don’t understand how come that most google services are only like 30% cheaper for similar performance but for DB IOPS it’s around 5x price difference. I have a feeling that I’m calculating something wrong and trying to figure it out.

        1. Hmm, I assumed the IOPS are being multiplied by 100 as per the storage capacity. Because for 100 GB you can get extra 1k IOPS (you can’t have less or more, just 1k extra).

          Can’t find a proper explanation and the official calculator doesn’t seem to work properly http://calculator.s3.amazonaws.com/index.html
          It gives me 12.5$ for the 100 GB SSD and 65$ for the 1k IOPS. I’m confused a bit now.

Leave a Reply

Your email address will not be published. Required fields are marked *