RDS ¶

Tldr

Amazon Relational Database Service is a service that enables you to run relational databases in the AWS Cloud.
Amazon RDS is a managed service that automates tasks such as hardware provisioning, database setup, patching, and backups.
With these capabilities, you can spend less time completing administrative tasks and more time using data to innovate your applications.

RDS offerings 🕊️¶

Amazon RDS is available on 6 database engines, which optimize for memory, performance, or input/output (I/O). Supported database engines include:

Amazon Aurora
PostgreSQL
MySQL
MariaDB
Oracle Database
Microsoft SQL Server

DB Instance A DB instance is an isolated database environment in the AWS Cloud. The basic building block of Amazon RDS is the DB instance. Your DB instance can contain one or more user-created databases. The following diagram shows a virtual private cloud (VPC) that contains two Availability Zones, with each AZ containing two DB instances.

DB Instance Example

RDS Proxy ⚡️¶

Many applications, including those built on modern serverless architectures, can have a large number of open connections to the database server and may open and close database connections at a high rate, exhausting database memory and compute resources.

Amazon RDS Proxy allows applications to pool and share connections established with the database, improving database efficiency and application scalability.

Handling surge and throttling requests?

Using RDS Proxy, you can handle unpredictable surges in database traffic. Otherwise, these surges might cause issues due to oversubscribing connections or new connections being created at a fast rate. RDS Proxy establishes a database connection pool and reuses connections in this pool. This approach avoids the memory and CPU overhead of opening a new database connection each time. To protect a database against oversubscription, you can control the number of database connections that are created.

RDS Proxy queues or throttles application connections that can't be served immediately from the connection pool. Although latencies might increase, your application can continue to scale without abruptly failing or overwhelming the database. If connection requests exceed the limits you specify, RDS Proxy rejects application connections (that is, it sheds load). At the same time, it maintains predictable performance for the load that RDS can serve with the available capacity.

RDS Proxy Architecture

Basics 📝¶

Connection Pooling 🏊¶

Connection pooling is an optimization that enables applications to share and re-use database connections, thus reducing the load on the database itself. Opening and closing a new database connection is CPU-intensive whereas additional memory is needed for each open connection. Connection pooling also removes the need to worry about database connections in the application code.

Each database transaction uses one underlying database connection which can be reused once the transaction has finished. This transaction-level reuse is called connection multiplexing (or connection reuse).

Pinnning 📌¶

In some cases, RDS proxy can’t safely reuse a database connection outside of the current session. In such scenarios, the same connection is used for the session until the session ends. This behavior is called pinning.

AWS recommends trying to avoid pinning as much as possible since it makes it harder to share connections and thus reduces the benefits of using RDS proxy.

Some reasons why a connection might be pinned are:

Change of session variable
Change of configuration parameter

Benefits 💵¶

Better Performance: Your Amazon RDS Proxy instance maintains a pool of established connections to your RDS database instances, reducing the stress on database compute and memory resources that typically occurs when new connections are established. RDS Proxy also shares infrequently used database connections, so that fewer connections access the RDS database. This connection pooling enables your database to efficiently support a large number and frequency of application connections so that your application can scale without compromising performance.
Increased Availability: RDS Proxy minimizes application disruption from outages affecting the availability of your database by automatically connecting to a new database instance while preserving application connections. When failovers occur, RDS Proxy routes requests directly to the new database instance.

Monitor RDS Proxy 🔭¶

RDS proxy can be monitored by using Amazon CloudWatch. CloudWatch is well integrated with RDS proxy and provides useful metrics that can be used to understanding the performance and behavior of the proxy.

Some key metrics to keep an eye are:

DatabaseConnections: Number of database connections to the backend database
DatabaseConnectionsCurrentlyBorrowed: Number of connections currently being used by your application. Important to set an alarm on this metric.
DatabaseConnectionsCurrentlySessionPinned: Number of connections in the pinned state. This number should ideally be as low as possible to maximize RDS proxy performance.-

Aurora 🌟¶

Amazon Aurora is an enterprise-class relational database. It is compatible with MySQL and PostgreSQL relational databases. It is up to 5 times faster than standard MySQL databases and up to 3 times faster than standard PostgreSQL databases.

Amazon Aurora helps to reduce your database costs by reducing unnecessary input/output (I/O) operations, while ensuring that your database resources remain reliable and available.

Consider Amazon Aurora if your workloads require high availability. It replicates 6 copies of your data across 3 Availability Zones and continuously backs up your data to Amazon S3.

Backup and Restore 📦¶

You can turn on automated backups, or manually create your own backup snapshots. You can use these backups to restore a database. The Amazon RDS restore process works reliably and efficiently.

By default, Amazon RDS creates and saves automated backups of your DB instance securely in Amazon S3 for a user-specified retention period. In addition, you can create snapshots, which are user-initiated backups of your instance that are kept until you explicitly delete them.

Automated Backups 🛺¶

Turned on by default, the automated backup feature of Amazon RDS will backup your databases and transaction logs. Amazon RDS automatically creates a storage volume snapshot of your DB instance, backing up the entire DB instance and not just individual databases.

This backup occurs during a daily user-configurable 30 minute period known as the backup window. Automated backups are kept for a configurable number of days (called the backup retention period). Your automatic backup retention period can be configured to up to 35 days.

Point-in-time Restores ⏰¶

You can restore your DB instance to any specific time during the backup retention period, creating a new DB instance.

To determine the latest restorable time for a DB instance, use the AWS Console or Command Line Interface to look at the value returned in the LatestRestorableTime field for the DB instance. The latest restorable time for a DB instance is typically within 5 minutes of the current time.

Database Snapshots 📸¶

Database snapshots are user-initiated backups of your instance stored in Amazon S3 that are kept until you explicitly delete them. You can create a new instance from a database snapshots whenever you desire. Although database snapshots serve operationally as full backups, you are billed only for incremental storage use.

RDS Snapshots

If you copy a snapshot to a different region then you must ensure that the target region supports cross-region snapshot copies.

Copy an encrypted database

RDS Snapshots

If you are working with an encrypted snapshot then there are a few things you need to be aware of. If a snapshot is taken from an encrypted database, then the snapshot will also be encrypted through the use of a KMS key. If you copy the snapshot to the same region as the source snapshot, then you can use the same KMS encryption key. However, if you copy the encrypted snapshot to a different region, then during the copy configuration you will need to specify a new KMS key in the target region, this is because KMS is a regional service, and KMS keys only exist in one region.

As a part of the copy process, you can choose to encrypt an unencrypted snapshot by selecting a KMS key. This provides the opportunity to create an encrypted version of an unencrypted database. You can take your source database, create a snapshot, copy the snapshot, and select a new KMS key to encrypt it with, and then restore the database using that encrypted snapshot. This will result in a new encrypted database instance.

Snapshot Copies 🗒️¶

With Amazon RDS, you can copy DB snapshots and DB cluster snapshots. You can copy automated or manual snapshots. After you copy a snapshot, the copy is a manual snapshot. You can copy a snapshot within the same AWS Region, you can copy a snapshot across AWS Regions, and you can copy a snapshot across AWS accounts.

Using Amazon RDS, you can share a manual DB snapshot or DB cluster snapshot with other AWS accounts. Sharing a manual DB snapshot or DB cluster snapshot, whether encrypted or unencrypted, enables authorized AWS accounts to copy the snapshot.

Sharing an unencrypted manual DB snapshot enables authorized AWS accounts to directly restore a DB instance from the snapshot instead of taking a copy of it and restoring from that. This isn't supported for encrypted manual DB snapshots.

Sharing a manual DB cluster snapshot, whether encrypted or unencrypted, enables authorized AWS accounts to directly restore a DB cluster from the snapshot instead of taking a copy of it and restoring from that.

HA 🎛️¶

You can get high availability with a primary DB instance and a synchronous secondary DB instance that you can fail over to when problems occur. You can also use read replicas to increase read scaling.

Highly Available Architecture for RDS

Multi-Region deployment 🌍¶

The following scenario shows an RDS DB instance in one Region that replicates asynchronously to a standby DB instance in a different Region. If one Region becomes unavailable, the instance in the other Region is still available.

HA in another region with Async replication for RDS

Multi-AZ deployment 🌐¶

You can run your DB instance in several Availability Zones, an option called a Multi-AZ deployment. When you choose this option, Amazon automatically provisions and maintains one or more secondary standby DB instances in a different AZ. Your primary DB instance is replicated across Availability Zones to each secondary DB instance.

A Multi-AZ deployment provides the following advantages:

Providing data redundancy and failover support
Eliminating I/O freezes
Minimizing latency spikes during system backups
Serving read traffic on secondary DB instances (Multi-AZ DB clusters deployment only)

HA in same region with Sync replication

Database Performance 📊¶

Average Active Sessions (AAS): is the unit for the DBLoad metric in Performance Insights. It measures how many sessions are concurrently active on the database.
Wait event: It causes a SQL statement to wait for a specific event to happen before it can continue running. Wait events are an important dimension, or category, for DB load because they indicate where work is impeded.

Every active session is either running on the CPU or waiting. For example, sessions consume CPU when they search memory for a buffer, perform a calculation, or run procedural code. When sessions aren't consuming CPU, they might be waiting for a memory buffer to become free, a data file to be read, or a log to be written to. The more time that a session waits for resources, the less time it runs on the CPU.

Performance Insights Concepts

Example of CPU saturation is shown below

CPU Saturation Example

Performace Insights¶

With the Performance Insights dashboard, you can visualize the database load on your Amazon RDS DB instance load and filter the load by waits, SQL statements, hosts, or users