aws_db - henk52/knowledgesharing GitHub Wiki

AWS DB

Introduction

Aurora serverless

Two things to watch: platform v3/v4 adds up to 12 ACUs within a second, but after a large scale-up the buffer pool is cold and latency can spike during warm-up. So set min ACU high enough that the baseline keeps your working set cached

Aurora references

Setup

  • Deploy default Aurora Serverless via console
    • 16 ACU
  • Configure advanced insigts
  • run pgbench via aws console in the Aurora serverless AWS console
  • Observe Write IOPS in the cloudwatch insights page(page 7)

Running pgbench

  • Prep
    • pgbench -i -s 200
  • Start tmux
    • split ctrl-b + %
  • Simulate 2k write IOPS
    • pgbench -c 16 -j 4 -T 3600 --rate=350 -P 1
  • Simulate spike to 6k
    • pgbench -c 16 -j 4 -T 3600 -P 1 -M prepared --rate=700

Installing pgbench

  • Start the AWS console on the Aurora serveless db page
  • Chose the tab to the left of the tab with the psql commands
  • sudo -i
  • sudo dnf install -y postgresql16-contrib
  • pgbench --version

pgbench results



  • The Aurora Serverless 16 ACU is able to scale to 6k write IOPS and sustain it.
    • was also running 12k+ write IOPS during the first part of the investigation.
  • I believe that the graph shows the scale-up from one minute to the next, is due to the lack of granularity.
    • None of the pgbench executions showed any increase in lag.
  • PLEASE NOTE:
    • the pgbench are small operations, where our application creates write IOPS due to changing a huge amount of rows.

PLEASE NOTE:

the pgbench are small operations, where our application creates write IOPS due to changing a huge amount of rows.  

Notes on Serverless

Scaling

According to this article Aurora serverless can scale in a second: https://aws.amazon.com/blogs/database/faster-scaling-for-aurora-serverless-to-support-agentic-ai-and-other-spiky-workloads/

Security

According to AWS Support:

  • there are no additional security concerns, it is just a different platform and the same best practices apply like for RDS in terms of IAM, Security Groups etc
  • there is complete isolation on a hypervisor level for your compute instances, and storage is on your own volume.
    • So other customers can’t accidentally access our data.

Parameters

that affect scaling: https://aws.amazon.com/blogs/database/understanding-how-certain-database-parameters-impact-scaling-in-amazon-aurora-serverless-v2/

  • max_connections
  • max_locks_per_transaction
  • track_activity_query_size

Compare the output of the below command with: https://gitlab.com/ganintegrity/infrastructure/-/blob/master/terraform/aws/environments/production-na/core/rds_parameter_groups.tf?ref_type=heads

aws rds describe-db-cluster-parameters \
  --db-cluster-parameter-group-name default.aurora-postgresql13 \
  --query 'Parameters[*].{ParameterName:ParameterName,SupportedEngineModes:SupportedEngineModes} |
    [?contains(SupportedEngineModes, `provisioned`) == `true`] | [*].[ParameterName]' \
  --output text

Extensions

According to AWS support Serverless support the same extensions as RDS: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html

Memory available

https://www.amazon.science/publications/resource-management-in-aurora-serverless

scaling is vertical which means your process has access to the newly added resources

Write/read consistency

If you need a reader to reflect the latest write, you have a few options:

  • Route the read to the writer. The simplest guarantee. Reads on the writer always see the most recent committed state. Cost: those reads don't get the horizontal scaling of readers.
    • From AWS support: doing r/w on is the same as for RDS, no difference (assuming you currently have no read replicas)
  • Write forwarding (read-after-write without splitting endpoints). You can send a read/write transaction to a reader, and Aurora forwards the write to the writer, commits it, then lets you read back with a chosen consistency level. This exists for both engines now — local write forwarding is supported in Aurora PostgreSQL 16.4+, 15.8+, and 14.13+ — and gives read-after-write consistency without maintaining two connection paths. The consistency knob is an LSN-wait: amazon
    • EVENTUAL — no wait; may see old or new data.
    • SESSION — the reader waits so you always see your own session's writes (the usual default).
    • GLOBAL — the reader waits until it's caught up to the writer's state as of when your read started, so you see everyone's committed writes to that point.
  • The trade-off is latency: as you raise the consistency level, the query spends more time waiting for changes to propagate — you're choosing between fast response and guaranteed freshness. One footnote for MySQL: you must set aurora_replica_read_consistency when using write forwarding, or Aurora won't forward writes at all — it defaults to empty. amazonamazon
  • Application-level LSN gating (do-it-yourself GLOBAL). The general pattern behind all of this: capture the commit LSN from the write, hand it to the read path, and have the reader wait until its applied LSN ≥ that token before serving. This is the same "commit token / read-your-writes" idea used across replicated databases. Aurora's write-forwarding modes are essentially this, productized.

Aurora DSQL - transactional workloads

DSQL Introduction

https://docs.aws.amazon.com/aurora-dsql/latest/userguide/what-is-aurora-dsql.html

DSQL Limitations

https://docs.aws.amazon.com/aurora-dsql/latest/userguide/CHAP_quotas.html#SECTION_database-limits

DSQL Cost

https://aws.amazon.com/rds/aurora/dsql/pricing/

  • Aurora DSQL charges for all database activity using a billing unit called the Distributed Processing Unit (DPU).
    • DPU measures how much work the system does to run your SQL workload.
    • This includes compute resources used to execute query logic (e.g., joins, functions, aggregations), the input/output (I/O) required to read from and write to storage, and change data capture (CDC) streaming when enabled.
    • If you configure a multi-Region cluster, Aurora DSQL automatically replicates writes to each peered cluster in other AWS Regions. These replicated writes incur extra DPU charges equal to the cost of the originating writes.
    • DPU $9.50 per 1M Units
  • Storage is billed based on the total size of your database, measured in GB-month.
    • Storage $0.36 per GB-month

DSQL Trying it out

https://docs.aws.amazon.com/aurora-dsql/latest/userguide/getting-started.html

In the cluster settings unselect: Enable deletion protection