Enhance database performance with Amazon RDS dedicated log volumes

For those seeking to achieve consistent database transaction performance, Amazon Relational Database Service (Amazon RDS) has introduced a new feature: dedicated log volume (DLV). This feature is an additional storage volume specifically for database transaction logs. The DLV is separate from the volume containing database tables, making transaction write logging more efficient and consistent. A DLV is ideal for databases with large allocated storage, high IOPS requirements, or latency-sensitive workloads. This isolation of the transaction logs avoids contention between query and transaction processing, resulting in reduced commit latency and more consistent database transaction performance.

DLV is now available in all commercial AWS Regions and AWS GovCloud (US) for Amazon RDS for PostgreSQL version 13.10 or higher, version 14.7 or higher, version 15.2 or higher, and version 16 or higher; Amazon RDS for MySQL version 8.0.28 and higher; and Amazon RDS for MariaDB version 10.6.7 and higher.

In this post, we examine common DLV performance benefits, use cases, monitoring capabilities, and the cost of deployment.

Performance

The I/O access patterns for database data files and transaction logs differ considerably and the underlying storage configuration can impact performance.

Relational database data file access is generally random in nature, and performance can be scaled when the underlying storage volume utilizes a large number of storage devices to service I/O requests in parallel. In contrast, database transaction logs are accessed sequentially, and don’t realize the same benefit.

Placing transaction log files on a dedicated volume enables the database to perform data and transaction I/O concurrently, preventing contention between data and log I/O. In our testing, DLV consistently exhibits better performance for average latency and Transactions Per Minute (TPM). The difference is much more pronounced with Multi-AZ deployments. With Multi-AZ instance deployment, a write operation needs to be written to the primary instance’s log volume, then replicated to the secondary instance log volume before it can be acknowledged back to the client. A DLV comes with its own Linux disk queue and replication channel. With DLV enabled, the additional Linux disk queue reduces disk latency by decreasing queuing at the Linux logical volume level. With Multi-AZ instance deployment, the additional replication channel also increases replication parallelism, resulting in better write performance.

In our study, we used the HammerDB benchmarking tool to generate online transaction processing for a TPC-C like workload against RDS for PostgreSQL Multi-AZ instance deployments. Testing was conducted with and without DLV. As the number of virtual users increased, the deployment with DLV was able to support up to 45% more Transactions Per Minute (TPM) compared to deployment without DLV.

Test environment

For this test, the database was allocated sufficient resources to measure the impact of DLV at increasingly elevated workloads. Database parameters were configured to allow testing with a large number of virtual users while avoiding excessive checkpoint activity.

• Database: RDS for PostgreSQL v16.1
• Database size: approximately 2 TiB (20,000 warehouse)
• Database instance class: r6gd.16xlarge (vCPU: 64, RAM: 512 GB)
• EBS io2 volume: 6 TiB / 80K PIOPS

Custom RDS for PostgreSQL configuration parameters used:

• checkpoint_timeout: 300
• max_connections: 10,000
• max_wal_size: 102,400
• rds.force_ssl: 0

DLV provides the physical separation of database transaction logs and data files into appropriate storage volume configurations. This reduces I/O contention between the transaction and data path, improves performance, enhances Multi-AZ write operations, and enables better utilization of the underlying storage and network throughput. Additionally, this separation has allowed RDS to provide higher tolerance for elevated I/O latencies on data files. This leads to better availability experience as it reduces the frequency of expensive recovery actions.

Dedicated Log Volume (DLV) use cases

DLVs are of particular benefit to database workloads where a consistent low-latency transaction response is critical in meeting business SLAs. Example workloads include high-performance computing such as fraud detection, payment processing, and booking engines. DLV is also recommended for databases with a large on-disk footprint (over 5 TiB) where data has been distributed across a large number of storage devices. DLV insulates the transaction commit path from I/O contention, storage events, and latency spikes on the primary data volume.

The following are DLV candidate database characteristics:

• Large allocated storage (over 5 TiB)
• High IOPS requirements
• Transaction-intensive workloads
• Latency-sensitive workloads
• Using io1 or io2 Provisioned IOPS storage

Enable DLV on RDS instances

Enabling DLV creates a new 1 TiB, 3,000 IOPS, fixed size storage volume, dedicated to housing database transaction logs. When DLV is enabled with RDS for PostgreSQL, it becomes the dedicated storage for the Write-Ahead Logging (WAL) file. When enabled with RDS for MySQL or RDS for MariaDB, DLV becomes the dedicated storage for the Binary Log (BINLOG) and redo log files.

You can enable, or disable DLV for new and existing RDS instances using the AWS Management Console, AWS Command Line Interface (AWS CLI), and Amazon RDS API.

To use the console, complete the following steps:

1. On the Amazon RDS console, navigate to your DB instance.
2. Modify the instance and in the Storage section:
   • Check Turn on Dedicated Log Volume to enable DLV, or
   • Uncheck Turn on Dedicated Log Volume to disable DLV
3. Complete the remaining steps to apply the changes.
   

Alternatively, you can enable or disable DLV for new and existing RDS instances with the AWS CLI.

Invoke the command create-db-instance or modify-db-instance with parameters:

--dedicated-log-volume | --no-dedicated-log-volume

Example, create a new RDS for PostgreSQL database with DLV enabled:

aws rds create-db-instance 
        --db-instance-identifier rds-pg-dlv-test 
        --region us-west-2 \
        --master-username db_master --master-user-password ********** \
        --engine postgres --db-instance-class db.r6gd.16xlarge \
        --storage-type io2 --iops 80000 --allocated-storage 6000 
        --dedicated-log-volume

Example, disable DLV for an existing RDS for PostgreSQL database:

aws rds modify-db-instance 
        --db-instance-identifier rds-pg-dlv-test \
        --no-dedicated-log-volume 
        --apply-immediately

Considerations when using DLV

In this section, we share some key considerations and best practices when using DLV.

DLV integration with RDS Multi-AZ and read replica instances

DLV is supported for RDS single instance deployments, Multi-AZ, and read replica instances. Multi-AZ DB cluster deployments are not supported at this time.

When modifying or creating an RDS Multi-AZ instance with DLV enabled, a DLV is created for both the primary and the secondary instances.

For databases with read replicas, if the primary database instance has DLV enabled, all subsequently created read replicas will also have DLV enabled. Any read replicas created prior to enabling DLV on the primary instance will not have it enabled unless explicitly modified. We recommend that the primary and all read replicas have a consistent DLV enabled/disabled setting.

Minimize downtime when enabling DLV

When DLV is enabled, a new storage volume is created and the database transaction logs are relocated from the DATA volume into DLV. Enabling DLV requires database downtime, but this can be reduced by enabling DLV on a new or existing read replica and then promoting it as the primary. The application’s database end-point can be then updated to point to the new DLV-enabled database. This process can be simplified, and downtime further reduced by using RDS Blue/Green Deployments. You can enable DLV on the green instance, once the green instance is fully synchronized, perform a switchover to minimize downtime.

DLV usage monitoring

When enabled, DLV is a crucial component of the database and proactive monitoring is recommended.
Amazon CloudWatch contains a comprehensive list of metrics, providing visibility into the utilization and performance of database transaction logs and the underlying storage volume. When DLV is enabled, additional metrics are available to monitor the utilization and performance of the DLV storage volume.
For additional details, refer to Amazon CloudWatch metrics for RDS.

The following table provides a list of metrics for database transaction logs and DLV.

*Metric is available when DLV is enabled

Amazon Enhanced Monitoring (EM) produces OS metrics that provide insight into DLV storage I/O performance for the logical volume and physical device.

Cost of DLV

An additional cost is required for DLV storage. Pricing is based on the database storage type, io1 or io2, and is consistent with a Provisioned IOPS data volume configured at 1,000 GiB and 3,000 IOPS. DLVs are only supported on Provisioned IOPS storage. You can obtain an estimate of the RDS database and DLV cost with the AWS Pricing Calculator. Select Dedicated Log Volume=yes in the database storage section.

For example, in US East (N. Virginia), an io2 DLV attached to a Single-AZ instance in Amazon RDS for PostgreSQL would cost $0.125 x 1,000 GiB plus $0.10 x 3,000 IOPS, or $425 per month. If you were to run an io2 DLV in the same Region attached to a Multi-AZ instance with one standby, it would cost you $0.25 x 1,000 GiB plus $0.20 x 3,000 IOPS, or $850 per month.

To learn more about data volume pricing, see Provisioned IOPS SSD storage costs.

Conclusion

In this post, we examined the Amazon RDS dedicated log volume (DLV) feature, its use cases, performance benefit, deployment considerations, and cost. Amazon RDS DLV improves the consistency of performance for Amazon RDS for PostgreSQL, MySQL, and MariaDB workloads. It helps insulate the data transaction commit path from storage events impacting latency, enhances performance consistency, and reduces commit latency. DLV is recommended for high-performance computing workloads with defined SLAs or databases with large on-disk footprints (more than 5 TiB).

If you have any questions, leave them in the comments section.

About the Authors

Jeff Tillery is a Senior Database Specialist Solutions Architect at Amazon Web Services. He provides guidance to customers, helping them identify and employ the technology and cloud migration strategies that best align with their business objectives.

Wanda He is a Principal Database Specialist Solutions Architect at Amazon Web Services. She works with customers on design, deploy, and optimize relational databases on AWS.