1. RDS vs. Aurora vs. EC2 Benchmark

RDS vs. Aurora vs. EC2 Benchmark

clouds

Originally, the goal of the benchmark is to compare performance and price/performance of Amazon RDS (Aurora) and MySQL server running on an EC2 instance.

MySQL Versions

  • MySQL 5.6 on RDS
  • Aurora on RDS
  • MySQL 5.6 community version on EC2
  • MySQL 5.7 community version on EC2

Sysbench Test

  • Primary Key lookup queries
  • OLTP workload with mix of SELECT, UPDATE, DELETE and INSERT queries
  • IO and CPU bound workloads

Sysbench Benchmarks

CPU Bound

Dataset size – 76G

IO Bound

Dataset size 375G

MySQL Configuration

Configuration

[client]
socket=/var/lib/mysql/mysql.sock

[mysqld]
log-bin=mysql-bin
binlog-format=ROW
sync-binlog=0
server-id=1000136

query-cache-type=0
query-cache-size=0

max-connections=1000
table-definition-cache=4000
table-open-cache=4000
table-open-cache-instances=16

innodb-flush-method=O_DIRECT
innodb-log-file-size=2G
innodb-flush-log-at-trx-commit=2
innodb-file-per-table=1
innodb-buffer-pool-size=100G
innodb-buffer-pool-instances=8

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

performance-schema=1

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

EC2 Instances

Client Instance

Instance type: m4.2xlarge
vCPU: 8
Memory: 32GB
EBS optimized: Yes

MySQL 5.6 Instance

Instance Specifications

Instance type: r3.4xlarge
vCPU: 16
Memory: 122GB
EBS optimized: Yes
EBS Storage:
Volume Type: io1
Volume Size: 1200GB
Provisioned IOPS: 5000

Pricing

Compute: $1.43 per hour (including $0.10 per hour for EBS optimized)
Provisioned IOPS SSD storage: $0.125 per GB-month
Provisioned IOPS IO Rate: $0.065 per provisioned IOPS-month

MySQL 5.7 Instance

Instance Specifications

Instance type: r3.4xlarge
vCPU: 16
Memory: 122GB
EBS optimized: Yes
EBS Storage:
Volume Type: io1
Volume Size: 1200GB
Provisioned IOPS: 5000

Pricing

Compute: $1.43 per hour (including $0.10 per hour for EBS optimized)
Provisioned IOPS SSD storage: $0.125 per GB-month
Provisioned IOPS IO Rate: $0.065 per provisioned IOPS-month

RDS Instances

RDS MySQL 5.6

Instance Specifications

Instance type: db.r3.4xlarge
vCPU: 16
Memory: 122GB
PIOP optimized: Yes
Network performance: High
EBS Storage:
Volume Type: io1
Volume Size: 1200GB
Provisioned IOPS: 5000

Pricing

Instance: $1.890 per hour
PIOPS SSD storage: $0.125 per GB-month
PIOPS IO Rate: $0.10 per IOPS-month

RDS MySQL 5.7

Instance Specifications

Instance type: db.r3.4xlarge
vCPU: 16
Memory: 122GB
PIOP optimized: Yes
Network performance: High
EBS Storage:
Volume Type: io1
Volume Size: 1200GB
Provisioned IOPS: 5000

Pricing

Instance: $1.890 per hour
PIOPS SSD storage: $0.125 per GB-month
PIOPS IO Rate: $0.10 per IOPS-month

RDS Aurora

Instance Specifications

Instance type: db.r3.4xlarge
vCPU: 16
Memory: 122GB
PIOP optimized: Yes
Network performance: High
EBS Storage:
Volume Type: io1
Volume Size: 1200GB
Provisioned IOPS: 5000

Pricing

Instance: $2.320 per hour
Storage Rate: $0.100 per GB-month
IO Rate: $0.200 per 1 million requests

Sysbench Commands

CPU Bound Workload

Prepare command

sysbench \
--test=share/doc/sysbench/tests/db/parallel_prepare.lua \
--oltp-tables-count=8 --oltp-table-size=40000000 \
--rand-init=on --num-threads=8 --mysql-user=$user \
--mysql-password=$password --mysql-host=$host run

Point SELECTs

sysbench --test=share/doc/sysbench/tests/db/select.lua \
--oltp-tables-count=8 --oltp-table-size=40000000 \
--rand-init=on --num-threads=16 --percentile=99 \
--report-interval=2 --max-requests=0 --max-time=3600 \
--mysql-user=$user --mysql-password=$password --mysql-host=$host run

OLTP

sysbench --test=share/doc/sysbench/tests/db/oltp.lua \
--oltp-tables-count=8 --oltp-table-size=40000000 --rand-init=on \
--db-ps-mode=disable --oltp-dist-type=uniform --oltp-read-only=off \
--num-threads=16 --percentile=99 --report-interval=2 \
--max-requests=0 --max-time=3600 --mysql-user=$user \
--mysql-password=$password --mysql-host=$host run

IO Bound Workload

Prepare command

sysbench \
--test=share/doc/sysbench/tests/db/parallel_prepare.lua \
--oltp-tables-count=8 --oltp-table-size=200000000 \
--rand-init=on --num-threads=8 --mysql-user=$user \
--mysql-password=$password --mysql-host=$host run

Point SELECTs

sysbench --test=share/doc/sysbench/tests/db/select.lua \
--oltp-tables-count=8 --oltp-table-size=200000000 \
--rand-init=on --num-threads=16 --percentile=99 \
--report-interval=2 --max-requests=0 --max-time=3600 \
--mysql-user=$user --mysql-password=$password --mysql-host=$host run

OLTP

sysbench --test=share/doc/sysbench/tests/db/oltp.lua \
--oltp-tables-count=8 --oltp-table-size=200000000 --rand-init=on \
--db-ps-mode=disable --oltp-dist-type=uniform --oltp-read-only=off \
--num-threads=16 --percentile=99 --report-interval=2 \
--max-requests=0 --max-time=3600 --mysql-user=$user \
--mysql-password=$password --mysql-host=$host run
Previous Post Next Post