Comments on: Maximizing Performance of AWS RDS for MySQL with Dedicated Log Volumes

By: Daniel Nichter

Daniel Nichter — Sun, 21 Jan 2024 18:09:33 +0000

Does "RDS Single DB instances" mean each RDS instance was configured without multi-AZ? I ask because single-AZ and multi-AZ RDS [1] have different write performance, and multi-AZ is (or, should be) the norm in production because there's no HA without it. [1] By "multi-AZ" I mean what AWS now calls "Multi-AZ with one standby", not its relatively newer three-AZ RDS cluster ("Multi-AZ with two readable standbys").

By: Kedar Vaijanapurkar

Kedar Vaijanapurkar — Thu, 04 Jan 2024 14:39:15 +0000

In reply to Scott Lynn. Thanks for pointing this out, Scott. We have updated the pricing section in the article.

By: Kedar Vaijanapurkar

Kedar Vaijanapurkar — Thu, 04 Jan 2024 14:38:26 +0000

Hello Jean and Mike,

I’d completely agree with you that it is not clear why RO benchmarking has shown an improvement. Since the improvements should only impact the transaction logs and improving writes, we are not expected to see improvement in RO workload.
That said, my benchmarking results did show me that. Please find my approach, and commands:

Load test environment consists of total 2 RDS instances with identical instance class and database version and 1 EC2 instance, all in us-east-1.
Following commands were executed to generate test data and execute the tests. Each test was executed at-least twice and the second run was taken in to consideration.

# Setup Data
sysbench --db-driver=mysql --mysql-user=admin --mysql-password=password --mysql-host=rds-endpoint --mysql-port=3306 --mysql-db=test --range_size=500 --table_size=100000000 --tables=10 --threads=10 --events=0 --time=60 --rand-type=uniform /usr/local/share/sysbench/oltp_read_only.lua prepare

# Setup Data

sysbench --db-driver=mysql --mysql-user=admin --mysql-password=password --mysql-host=rds-endpoint --mysql-port=3306 --mysql-db=test --range_size=500 --table_size=100000000 --tables=10 --threads=10 --events=0 --time=60 --rand-type=uniform /usr/local/share/sysbench/oltp_read_only.lua prepare

# RO test	
sysbench --db-driver=mysql --mysql-user=admin --mysql-password=password --mysql-host=rds-endpoint --mysql-port=3306 --mysql-db=test --range_size=500 --table_size=10000000 --tables=10 --threads=1 --events=0 --time=60 --report-interval=5 /usr/share/sysbench/oltp_read_only.lua run


# RW test	
sysbench --db-driver=mysql --mysql-user=admin --mysql-password=password --mysql-host=rds-endpoint --mysql-port=3306 --mysql-db=test --range_size=500 --table_size=10000000 --tables=10 --threads=1 --events=0 --time=60 --report-interval=5 /usr/share/sysbench/oltp_read_write.lua run

# WO test	
sysbench --db-driver=mysql --mysql-user=admin --mysql-password=password --mysql-host=rds-endpoint --mysql-port=3306 --mysql-db=test --range_size=500 --table_size=10000000 --tables=10 --threads=1 --events=0 --time=60 --report-interval=5 /usr/share/sysbench/oltp_write_only.lua run

# RO test

sysbench --db-driver=mysql --mysql-user=admin --mysql-password=password --mysql-host=rds-endpoint --mysql-port=3306 --mysql-db=test --range_size=500 --table_size=10000000 --tables=10 --threads=1 --events=0 --time=60 --report-interval=5 /usr/share/sysbench/oltp_read_only.lua run

# RW test

sysbench --db-driver=mysql --mysql-user=admin --mysql-password=password --mysql-host=rds-endpoint --mysql-port=3306 --mysql-db=test --range_size=500 --table_size=10000000 --tables=10 --threads=1 --events=0 --time=60 --report-interval=5 /usr/share/sysbench/oltp_read_write.lua run

# WO test

sysbench --db-driver=mysql --mysql-user=admin --mysql-password=password --mysql-host=rds-endpoint --mysql-port=3306 --mysql-db=test --range_size=500 --table_size=10000000 --tables=10 --threads=1 --events=0 --time=60 --report-interval=5 /usr/share/sysbench/oltp_write_only.lua run

Thank you,
Kedar.

By: Scott Lynn

Scott Lynn — Thu, 28 Dec 2023 21:18:19 +0000

I need to clear this up. Our support org did not give you the correct information. I will be getting to them about that.
DLV doesn’t require a particular storage size for the data volume. We recommend that it be used on volumes of 5,000 GiB or larger. The DLV is an io1 volume with a fixed size of 1,000 GiB and 3,000 PIOPS, and is priced like an io1 volume of that size/PIOPS. E.g, $425 for a Single-AZ instance in IAD.
This is documented on our pricing page: https://aws.amazon.com/rds/mysql/pricing/

By: Jean-François Gagné

Jean-François Gagné — Tue, 19 Dec 2023 18:54:28 +0000

I am very surprised to see better read-only results with DLV. After all, a RO workload should not write anything, so having dedicated logs should not improve results. So for me, there is something weird with these results.

By: Mike

Mike — Mon, 11 Dec 2023 23:20:51 +0000

The fact that RO benchmark shows 10% improvement, mirroring RW improvement, makes the WO/RW results somewhat questionable. Any observations on why this might be the case?