Understanding how certain database parameters impact scaling in Amazon Aurora Serverless v2

Amazon Aurora Serverless v2 is an on-demand, auto scaling configuration for Amazon Aurora. It automatically starts up, shuts down, and scales capacity based on your application’s needs. It supports various workloads, from development and test environments, websites, and applications with infrequent or unpredictable workloads to demanding, business-critical applications requiring high scale and availability. With Aurora Serverless v2, you create a database, specify the desired capacity range, and connect your applications. You pay per second for capacity used when the database is active and can migrate between standard and serverless configurations on the Amazon Relational Database Service (Amazon RDS) console.

The unit of measure for Aurora Serverless v2 is the Aurora capacity unit (ACU). Each ACU combines approximately 2 GiB of memory, CPU, and networking resources. When using Aurora Serverless v2, you specify a capacity range (minimum and maximum ACU values) for each database (DB) cluster. As it scales, Aurora Serverless v2 adjusts capacity in fine-grained ACU increments, providing the right amount of resources.

Each workload has unique minimum and maximum ACU requirements. Finding the right ACU configuration and understanding factors influencing Aurora Serverless v2 scaling is essential. The minimum capacity can be set as low as 0.5 ACUs, and the maximum is capped at 256 ACUs. You can also enable automatic pause behavior by specifying a minimum capacity of 0 ACUs, as explained in Scaling to Zero ACUs with automatic pause and resume for Aurora Serverless v2. Depending on the additional features you enable on your DB cluster, you might need the minimum ACU to be higher than 0.5. For example, Amazon RDS Performance Insights needs the minimum ACU to be at least 2. For more details, refer to Choosing the minimum Aurora Serverless v2 capacity setting for a cluster.

This post is Part 1 of a two-part blog post series and focuses on understanding how certain database parameters impact Aurora Serverless v2 scaling behavior for PostgreSQL-compatible DB instances. This post considers minimum ACU to be 0.5 or higher and does not include the new automatic pause feature.

Database parameters and ACU scaling

As you plan to migrate your database to Aurora Serverless v2, or when you’re tuning your existing Aurora Serverless v2 database, you might encounter an issue where your Aurora Serverless v2 cluster isn’t scaling down to 0.5 ACU even with no database activity. One reason for the ACU not scaling down to 0.5 is because you’re using nondefault values for certain PostgreSQL DB parameters.

To help you understand how certain DB instance parameters influence Aurora Serverless v2 scaling, we ran through test cases on the following parameters that are most often tuned:

• max_connections – max_connections determine the maximum number of concurrent connections to the database. Because this is a static parameter, it needs an instance restart for it to take effect after being changed. PostgreSQL reserves shared memory for possible connections at the server’s start. Keep the following in mind:
  • The value of max_connections for Aurora Serverless v2 DB instances is based on the memory size derived from the maximum ACUs. However, when you specify a minimum capacity of 0.5 ACU on PostgreSQL-compatible DB instances, the maximum value of max_connections is capped at 2,000.
    NOTE: Having a limit of 2000 for max_connections with 0.5 ACU, does not mean ACU of 0.5 will support 2000 connections in real world.
  • If you’re using connection pooler like Amazon RDS Proxy or pg_bouncer, you can open many simultaneous connections to the connection pooler, and the connection pooler keeps a smaller number of connections open to the DB instance or cluster. Doing so further minimizes the memory overhead for connections on the database server, which might lower your ACU usage and help you overcome the max_connections parameter limit.
• max_locks_per_transaction – max_locks_per_transaction determines the maximum number of locks a single transaction can hold. This parameter makes sure you have enough shared memory to hold these locks. If you’re using extensions or features that need a higher number of locks, you might need to tune this parameter to a nondefault value.
• track_activity_query_size – The track_activity_query_size parameter controls the size of the query string that is logged for each active session in the pg_stat_activity By default, only the first 1,024 bytes of the query string are logged in Amazon RDS for PostgreSQL, and 4,096 bytes are logged in Amazon Aurora PostgreSQL-Compatible Edition. If you want to log longer queries, you can set this parameter to a higher value.

Prerequisites

For this post, we use an Aurora Serverless v2 DB cluster with PostgreSQL compatibility. We use HammerDB to generate a load on the database to evaluate how the Aurora Serverless v2 DB cluster scales with each of our test cases. Before you get started, make sure you complete the following prerequisites:

• Create or have access to an AWS account.
• Create an Aurora Serverless v2 DB cluster with PostgreSQL compatibility with an ACU range of 0.5–64. PostgreSQL 13.6 and higher supports Aurora Serverless v2. We chose PostgreSQL 16.1 for our testing.
• Create a bastion host using Amazon Elastic Compute Cloud (Amazon EC2) with Amazon Linux 2023, which you can use to access Aurora Serverless v2 in a private subnet from your machine’s IP address or from a range of IP addresses, if you’re connecting from your corporate network. For instructions to select the necessary network configuration for a bastion host, refer to Creating and connecting to an Aurora PostgreSQL DB cluster.
• Install the following packages needed to support PostgreSQL connectivity and HammerDB on your bastion host.

  sudo dnf update

  sudo yum -y install postgresql15

  sudo yum -y install python3-psycopg2


  --Download HammerDB

  wget https://github.com/TPC-Council/HammerDB/releases/download/v4.10/HammerDB-4.10-Linux.tar.gz


  --Unzip

  tar -zxvf HammerDB-4.10-Linux.tar.gz


  cd HammerDB*/


  ./hammerdbcli
   
  --this verifies all libraries needed

  hammerdbcli> librarycheck
   
  --Verify you see below message for PostgreSQL:
  Checking database library for PostgreSQL
  Success ... loaded library Pgtcl for PostgreSQL

• Create a schema using HammerDB with 100 warehouses and 50 virtual users.
• Generate a load on the database, using the following HammerDB parameters:
  • Virtual users: 50
  • Ramp-up time: 2 minutes
  • Total test duration: 5 minutes

Test cases

We ran our test with the following test cases, generating a load on an Aurora Serverless v2 PostgreSQL database using HammerDB and observing the scaling behavior:

• Default parameters with minimum ACU at 0.5
• Minimum ACU of 1 and higher max_connections
• Nondefault max_locks_per_transaction
• Nondefault track_activity_query_size

Test case 1: Default parameters with minimum ACU at 0.5

Aurora Serverless v2 PostgreSQL can handle the sudden spiky workload coming from HammerDB by scaling the ACU as needed and gradually scaling down to 0.5 ACU. The following figure illustrates the scale-up and scale-down metrics.

Test case 2: Minimum ACU of 1 and higher max_connections

When the minimum ACU is set to 0.5, max_connections is limited to 2,000, irrespective of the maximum ACU configured.

When the minimum ACU is set to 1 or higher, max_connections is derived from the following formula:

LEAST({DBInstanceClassMemory/9531392},5000)

Changing the ACU maximum is dynamic and immediately takes effect. However, certain database parameters are derived based on the maximum ACU and are static; they only take effect after the reboot. max_connections is also a static parameter and needs a reboot for its new value to take effect when the ACU maximum is changed.

With a minimum ACU of 1 and a maximum ACU of 64, max_connections is derived as 5,000:

initialdb=> SELECT unit, setting, max_val FROM pg_settings WHERE name='max_connections';
unit | setting | max_val
------+---------+---------
| 5000 | 262143
(1 row)

When the database is idle, ServerlessDatabaseCapacity usage stays at 1 because the minimum ACU is set to 1, even with a higher value for max_connections. The following figure shows this metric.

When load is generated on the database using HammerDB, Aurora Serverless v2 can scale up and gradually scale down to a minimum ACU of 1.

You might need to go beyond 5,000 connections. Instead of going with the derived value for max_connections, we changed it to a higher constant value of 7,000. We created a custom parameter group for the cluster with max_connections as 7,000, applied the changes to the DB cluster, and restarted it.

Changing max_connections to a nondefault value isn’t a best practice and might cause out-of-memory issues. We changed max_connections to a nondefault value just for testing purposes.

When the database is idle, ACU usage stayed at 2 or higher, with max_connections set to a constant of 7,000.

NOTE: Set database parameters that impact the size of shared memory to their default values. Setting a value higher than the default increases the shared memory requirement and prevents the database from scaling down to the minimum capacity.

The following figure shows ACU usage with max_connections set to a constant of 7000.

With a higher constant value of 7,000 for max_connections, when the load is generated on the database through HammerDB, Aurora Serverless v2 can scale up and gradually scale down to an ACU of 2, but not to the minimum ACU configured, as shown in the following figure.

Test case 3: Nondefault max_locks_per_transaction

With the ACU minimum at 1 and the maximum at 64, we set max_locks_per_transaction to 400. We observed ServerlessDatabaseCapacity usage with and without the load. max_locks_per_transaction has a default value of 64.

initialdb=> SELECT unit, setting, max_val FROM pg_settings WHERE name='max_locks_per_transaction';
unit | setting | max_val
------+---------+------------
| 400 | 2147483647
(1 row)

When the database is idle, with the minimum ACU as 1 and the maximum as 64 and with a nondefault value of 400 for max_locks_per_transaction, ServerlessDatabaseCapacity stayed at 2 or higher.

NOTE: Set database parameters that impact the size of shared memory to their default values. Setting a value higher than the default increases the shared memory requirement and prevents the database from scaling down to the minimum capacity.

The following figure shows ACU usage with max_locks_per_transaction set to non-default value of 400, when database is idle.

With the load on the database, with the minimum ACU as 1 and the maximum as 64 and with a nondefault value of 400 for max_locks_per_transaction, Aurora Serverless v2 can scale up and gradually scale down, but to the minimum ACU usage of 2, as shown below:

We observed similar behavior when the minimum ACU was 0.5.

Test case 4: Nondefault track_activity_query_size

track_activity_query_size has a default value of 4,096 bytes for Aurora Serverless v2 PostgreSQL. For this test, we changed it to a nondefault value of 100,000 bytes. Because this is a static parameter, we restarted the database for the parameter change to take effect.

When the database is idle, with the minimum ACU as 0.5 and the maximum as 64 and with a nondefault value for track_activity_query_size, ServerlessDatabaseCapacity stayed at 1.5 or higher. The following figure shows how Aurora Serverless v2 scales with the load.

Observations from test cases

Based on our test cases, we made the following observations:

• When using default parameters and no additional features enabled, ACU scales down to 0.5 (minimum configured).
• When using the max_connections parameter, we observed the following:
  • With the minimum ACU at 0.5, max_connections is capped at 2,000 and Aurora Serverless v2 scaled up and scaled back down to 0.5 after the load.
  • With the minimum ACU at 1, max_connections is derived based on a formula and can go up to 5,000. With the database load, Aurora Serverless v2 scaled up and scaled back down to ACU 1 after the load.
  • With the minimum ACU at 1 and max_connections set to 7,000 (constant value), Aurora Serverless v2 ServerlessDatabaseCapacity didn’t go below 2. With higher values for max_connections, Aurora Serverless v2 PostgreSQL needed higher minimum capacity.
  • If you intend to use the Aurora PostgreSQL cluster for a high-connection workload, consider using a minimum ACU setting of 1 or higher. For details about how Aurora Serverless v2 handles the max_connections parameter, refer to Maximum connections for Aurora Serverless v2.
• Using a nondefault configuration for max_locks_per_transaction impacts ACUs not scaling down below 2, and this might vary depending on the value you pick for this parameter.
• Using a nondefault configuration for track_activity_query_size caused the ACU to scale down to 1.5, but not to the minimum that the ACU configured.

Recommendations

Based on our findings, we recommend the following:

• Start provisioning your Aurora Serverless v2 DB cluster with default parameters and only go with nondefault parameters after proper testing and understanding their impact on the scaling behavior of Aurora Serverless v2.
• You should use a minimum ACU of 2 or higher if your DB cluster is supporting a high-connection workload.
• When using RDS Proxy, many simultaneous connections to the proxy translates to a smaller number of connections open to the DB instance or cluster. This helps minimize the memory overhead on the database server, which might lower your ACU usage and help you overcome the max_connections parameter limit.

Conclusion

Aurora Serverless v2 stands out as a powerful solution for businesses needing flexible, cost-effective database management. In this post, we reviewed how fine-tuning certain database parameters plays a significant role in the scalability of Aurora Serverless v2. By optimizing these settings, you can make sure your Aurora Serverless v2 PostgreSQL databases remain efficient, dependable, and cost-effective and be prepared to tackle future challenges and opportunities.

In Part 2 of this series, we review how the minimum and maximum range of ACU influences scaling behavior of Aurora Serverless v2, tailored to different business scenarios.

We welcome your feedback. Please share your experience and any questions in the comments.

About the Authors

Venu Koneru is a Database Specialist Solutions Architect at Amazon Web Services (AWS). His multifaceted career has spanned roles including web developer, data modeler, database administrator, and database architect across the education, travel, and finance sectors. With a wealth of experience in database management and architecture, Venu now leverages his skills at AWS to guide customers through their cloud database modernization journeys.

Priyanka is a Database Specialist Solutions Architect at AWS, focusing on Amazon RDS and Aurora PostgreSQL. She is passionate about designing solutions for customers aiming to modernize their applications and databases in the cloud. She is based in Seattle, Washington. Outside work, Priyanka enjoys reading books and exploring new destinations during her travels.