Amazon Relational Database Service (RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud.

RDS provides cost-efficient, resizable capacity for an industry-standard relational database and manages common database administration tasks.

RDS features & benefits

• CPU, memory, storage, and IOPS can be scaled independently.

• Manages backups, software patching, automatic failure detection, and recovery.

• Automated backups can be performed as needed, or manual backups can be triggered as well. Backups can be used to restore a database, and the RDS restore process works reliably and efficiently.

• Provides high availability with a primary instance and a synchronous secondary instance that can be failovered to seamlessly when a problem occurs.

• Provides elasticity & scalability by enabling MySQL, MariaDB, or PostgreSQL Read Replicas to increase read scaling.

• Supports MySQL, MariaDB, PostgreSQL, Oracle, Microsoft SQL Server, and the new, MySQL-compatible Amazon Aurora DB engine.

• In addition to the security in the database package, IAM users and permissions can help to control who has access to the RDS database service.

• Databases can be further protected by putting them in a VPC, using SSL for data in transit and encryption for data in rest.

• However, as it is a managed service, shell (root ssh) access to DB instances is not provided, and this restricts access to certain system procedures and tables that require advanced privileges.

DB instances replicas can be created in two ways Multi-AZ & Read Replica.

To reuse Reserved DB Instances between accounts in the same organization the instances between the accounts needs to have the same: region, DB Engine, the DB Instance Class, and the deployment type (one zone, Multi-AZ, and so on) and and License Model .

DB Instance

• It is a basic building block of RDS

• It is an isolated database environment in the cloud

• Each DB instance runs a DB engine. AWS currently supports MySQL, MariaDB, PostgreSQL, Oracle, and Microsoft SQL Server & Aurora DB engines

• Can be accessed from Amazon AWS command line tools, Amazon RDS APIs, or the AWS Management RDS Console.

• Computation and memory capacity of an DB instance is determined by its DB instance class, which can be selected as per the needs for each DB instance, 5 GB to 6 TB of associated storage capacity can be selected.

• The storage comes in three types: Magnetic, General Purpose (SSD), and Provisioned IOPS (SSD), which differ in performance characteristics and price.

• Each DB instance has a DB instance identifier, which is customer-supplied name and must be unique for that customer in an AWS region. It uniquely identifies the DB instance when interacting with the Amazon RDS API and AWS CLI commands.

• Each DB instance can host multiple databases, or a single Oracle database with multiple schemas.

• Can be hosted in an AWS VPC environment for better control.

Regions and Availability Zones

• DB instances can be hosted in several AZs, an option called a Multi-AZ deployment.

• Amazon automatically provisions and maintains a synchronous standby replica of the DB instance in a different AZ.

• Primary DB instance is synchronously replicated across AZs to the standby replica.

• Provides data redundancy, failover support, eliminate I/O freezes, and minimize latency spikes during system backups.

Security Groups

• security group controls the access to a DB instance, by allowing access to the specified IP address ranges or EC2 instances.

Instance class

• Pricing is based on the class (e.g., micro, small, large, xlarge) of the DB instance consumed.

Running time

• Billed by the instance-hour, which is equivalent to a single instance running for an hour for e.g., a single instance running for two hours = two instances running for one hour, both consume 2 instance-hours.

• if a DB instance runs for only part of an hour, full instance-hour is charged.

Storage

• Storage capacity provisioned for the DB instance is billed per GB per month.

• If the provisioned storage capacity is scaled within the month, the bill will be pro-rated.

Multi-AZ deployment provides high availability and failover support

RDS automatically provisions and manages a synchronous standby replica in a different AZ (independent infrastructure in a physically separate location).

RDS automatically fails over to the standby so that database operations can resume quickly without administrative intervention in case of:

• Planned database maintenance

• Software patching

• Rebooting of the Primary instance

• Primary DB instance connectivity or host failure, or an

• Availability Zone failure

Multi-AZ deployments for Oracle, PostgreSQL, MySQL, and MariaDB DB instances use Amazon technology, while SQL Server DB instances use SQL Server Mirroring.

Copies of data are stored in different Availability Zones for greater levels of data durability.

Primary DB instance is synchronously replicated across Availability Zones to a standby replica to provide

• data redundancy,

• eliminate I/O freezes during snapshots and backups

• and minimize latency spikes during system backups.

DB instances may have increased write and commit latency compared to a Single AZ deployment, due to the synchronous data replication.

Transaction success is returned only if the commit is successful both on the primary and the standby DB.

There might be a change in latency if the deployment fails over to the standby replica, although AWS is engineered with low-latency network connectivity between Availability Zones.

When using the BYOL licensing model, a license for both the primary instance and the standby replica is required.

For production workloads, it is recommended to use Multi-AZ deployment with Provisioned IOPS and DB instance classes (m1.large and larger), optimized for Provisioned IOPS for fast, consistent performance.

When Single-AZ deployment is modified to a Multi-AZ deployment (for engines other than SQL Server or Amazon Aurora)

• RDS takes a snapshot of the primary DB instance from the deployment and restores the snapshot into another Availability Zone.

• RDS then sets up synchronous replication between the primary DB instance and the new instance.

• This avoids downtime when conversion from Single AZ to Multi-AZ.

RDS Multi-AZ Failover Process

• In the event of a planned or unplanned outage of the DB instance,

  • RDS automatically switches to a standby replica in another AZ, if enabled for Multi-AZ.

  • Time it takes for the failover to complete depends on the database activity and other conditions at the time the primary DB instance became unavailable.

  • Failover times are typically 60-120 secs. However, large transactions or a lengthy recovery process can increase failover time.

  • Failover mechanism automatically changes the DNS record of the DB instance to point to the standby DB instance.

  • Multi-AZ switch is seamless to the applications as there is no change in the endpoint URLs but just needs to re-establish any existing connections to the DB instance.

• RDS handles failover automatically so that database operations can be resumed as quickly as possible without administrative intervention.

• Primary DB instance switches over automatically to the standby replica if any of the following conditions occur:

  • An Availability Zone outage

  • Primary DB instance fails

  • DB instance’s server type is changed

  • Operating system of the DB instance is undergoing software patching

  • A manual failover of the DB instance was initiated using Reboot with failover (also referred to as Forced Failover)

• If the Multi-AZ DB instance has failed over, can be determined by

  • DB event subscriptions can be setup to notify you via email or SMS that a failover has been initiated.

  • DB events can be viewed via the Amazon RDS console or APIs.

  • Current state of your Multi-AZ deployment can be viewed via the RDS console and APIs.

RDS uses the PostgreSQL, MySQL, and MariaDB (version 9.3.5 and later) DB engines’ built-in replication functionality to create a special type of DB instance called a Read Replica from a source DB instance.

• Updates made to the source DB instance are asynchronously copied to the Read Replica.

• Load on the source DB instance can be reduced by routing read queries from applications to the Read Replica.

• Read Replicas allow elastic scaling beyond the capacity constraints of a single DB instance for read-heavy database workloads.

Read Replica operates as a DB instance that allows read-only connections; applications can connect to a Read Replica the same way they would to any DB instance.

Read Replica creation

• Up to five Read Replicas can be created from one source DB instance.

• Creation process

  • Automatic backups must be enabled on the source DB instance by setting the backup retention period to a value other than 0.

  • Existing DB instance needs to be specified as the source.

  • RDS takes a snapshot of the source instance and creates a read-only instance from the snapshot.

  • RDS then uses the asynchronous replication method for the DB engine to update the Read Replica for any changes to the source DB instance.

• RDS replicates all databases in the source DB instance.

• RDS sets up a secure communications channel between the source DB instance and the Read Replica, if that Read Replica is in a different AWS region from the DB instance.

• RDS establishes any AWS security configurations, such as adding security group entries, needed to enable the secure channel.

• During the Read Replica creation, a brief I/O suspension on the source DB instance can be experienced as the DB snapshot occurs.

• I/O suspension typically lasts about one minute and can be avoided if the source DB instance is a Multi-AZ deployment (in the case of Multi-AZ deployments, DB snapshots are taken from the standby).

• Read Replica creation time can be slow if any long-running transactions are being executed and should wait for completion

• For multiple Read Replicas created in parallel from the same source DB instance, only one snapshot is taken at the start of the first create action.

• A Read Replica can be promoted to a new independent source DB, in which case the replication link is broken between the Read Replica and the source DB. However, the replication continues for other replicas using the original source DB as the replication source.

Read Replica Deletion & DB Failover

• Read Replicas must be explicitly deleted, using the same mechanisms for deleting a DB instance.

• If the source DB instance is deleted without deleting the replicas, each replica is promoted to a stand-alone, single-AZ DB instance.

• If the source instance of a Multi-AZ deployment fails over to the standby, any associated Read Replicas are switched to use the secondary as their replication source.

Storage & Compute requirements

• A Read Replica, by default, is created with the same storage type as the source DB instance.

• For replication to operate effectively, each Read Replica should have the same amount of compute & storage resources as the source DB instance.

• Source DB instance, if scaled, Read Replicas should be scaled accordingly.

Read Replica Features & Limitations

• RDS does not support circular replication.

• DB instance cannot be configured to serve as a replication source for an existing DB instance; a new Read Replica can be created only from an existing DB instance for e.g., if MyDBInstance replicates to ReadReplica1, ReadReplica1 can’t be configured to replicate back to MyDBInstance. From ReadReplica1, only a new Read Replica can be created, such as ReadRep2.

• Cross-Region Replication

  • MySQL, PostgresSQL (update from June 2016) or MariaDB Read Replica can be created in a different region than the source DB instance which helps to improve

    • disaster recovery capabilities (reduces RTO and RPO),

    • scale read operations into a region closer to end users,

    • migration from a data center in one region to another region

• Read Replica can be created from other Read replicas as well. However, the replica lag is higher for these instances and there cannot be more than four instances involved in a replication chain.

Read Replica Use cases

• Scaling beyond the compute or I/O capacity of a single DB instance for read-heavy database workloads, directing excess read traffic to Read Replica(s)

• Serving read traffic while the source DB instance is unavailable for e.g. If the source DB instance cannot take I/O requests due to backups I/O suspension or scheduled maintenance, the read traffic can be directed to the Read Replica(s). However, the data might be stale.

• Business reporting or data warehousing scenarios where business reporting queries can be executed against a Read Replica, rather than the primary, production DB instance.

RDS storage uses Elastic Block Store (EBS) volumes for database and log storage.

RDS automatically stripes across multiple EBS volumes to enhance IOPS performance, depending on the amount of storage requested.

RDS Storage Types

• RDS storage provides three storage types: Magnetic, General Purpose (SSD), and Provisioned IOPS (input/output operations per seconds.

• MySQL, MariaDB, PostgreSQL, and Oracle RDS DB instances can be created with up to 6TB of storage and SQL Server RDS DB instances with up to 4TB of storage when using the Provisioned IOPS and General Purpose (SSD) storage types.

• Existing MySQL, PostgreSQL, and Oracle RDS database instances can be scaled to these new database storage limits without any downtime.

Magnetic (Standard)

• Magnetic storage, also called standard storage, offers cost-effective storage that is ideal for applications with light or burst I/O requirements.

• They deliver approximately 100 IOPS on average, with burst capability of up to hundreds of IOPS, and they can range in size from 5 GB to 3 TB, depending on the DB instance engine.

• Magnetic storage is not reserved for a single DB instance, so performance can vary greatly depending on the demands placed on shared resources by other customers.

General Purpose (SSD)

• General purpose, SSD-backed storage, also called gp2, can provide faster access than disk-based storage.

• They can deliver single-digit millisecond latencies, with a base performance of 3 IOPS per Gigabyte (GB) and the ability to burst to 3,000 IOPS for extended periods of time up to a maximum of 10,000 PIOPS.

• Gp2 volumes can range in size from 5 GB to 6 TB for MySQL, MariaDB, PostgreSQL, and Oracle DB instances, and from 20 GB to 4 TB for SQL Server DB instances.

• Gp2 is excellent for small to medium-sized databases.

Provisioned IOPS

• Provisioned IOPS storage is designed to meet the needs of I/O-intensive workloads, particularly database workloads, that are sensitive to storage performance and consistency in random access I/O throughput.

• For any production application that requires fast and consistent I/O performance and consistent throughput, Amazon recommends Provisioned IOPS (input/output operations per second) storage.

• Provisioned IOPS storage is optimized for I/O intensive, online transaction processing (OLTP) workloads that have consistent performance requirements.

• Provisioned IOPS helps performance tuning.

• Provisioned IOPS volumes can range in size from 100 GB to 6 TB for MySQL, MariaDB, PostgreSQL, and Oracle DB engines. SQL Server Express and Web editions can range in size from 100 GB to 4 TB, while SQL Server Standard and Enterprise editions can range in size from 200 GB to 4 TB.

• Dedicated IOPS rate and storage space allocation is specified, when a DB instance is created. RDS provisions that IOPS rate and storage for the lifetime of the DB instance or until its changed.

• RDS delivers within 10 percent of the provisioned IOPS performance 99.9 percent of the time over a given year.

Adding Storage and Changing Storage Type

• DB instance can be modified to use additional storage and converted to a different storage type.

• However, storage allocated for a DB instance cannot be decreased

• MySQL, MariaDB, PostgreSQL, and Oracle DB instances can be scaled up for storage, which helps improve I/O capacity.

• We cannot change the storage capacity or the type of storage for a SQL Server DB instance can be changed due to extensibility limitations of striped storage attached to a Windows Server environment.

• During the scaling process, the DB instance will be available for reads and writes, but may experience performance degradation.

• Adding storage may take several hours; the duration of the process depends on several factors such as load, storage size, storage type, amount of IOPS provisioned (if any), and number of prior scale storage operations.

• While storage is being added, nightly backups are suspended and no other RDS operations can take place, including modify, reboot, delete, create Read Replica, and create DB Snapshot.

Performance Metrics

• IOPS

  • the number of I/O operations completed per second, it is reported as the average IOPS for a given time interval.

  • RDS reports read and write IOPS separately on one minute intervals.

  • Total IOPS is the sum of the read and write IOPS.

  • Typical values for IOPS range from zero to tens of thousands per second.

• Latency

  • the elapsed time between the submission of an I/O request and its completion, it is reported as the average latency for a given time interval.

  • RDS reports read and write latency separately on one minute intervals in units of seconds.

  • Typical values for latency are in the millisecond (ms)

• Throughput

  • the number of bytes per second transferred to or from disk, it is reported as the average throughput for a given time interval.

  • RDS reports read and write throughput separately on one minute intervals using units of megabytes per second (MB/s).

  • Typical values for throughput range from zero to the I/O channel’s maximum bandwidth.

• Queue Depth

  • The number of I/O requests in the queue waiting to be serviced.

  • These are I/O requests that have been submitted by the application but have not been sent to the device because the device is busy servicing other I/O requests.

  • RDS reports queue depth in one minute intervals. Typical values for queue depth range from zero to several hundred.

  • Time spent waiting in the queue is a component of Latency and Service Time (not available as a metric).

Amazon RDS Storage Facts

• First time a DB instance is started and accesses an area of disk for the first time, the process can take longer than all subsequent accesses to the same disk area. This is known as the “first touch penalty”. Once an area of disk has incurred the first touch penalty, that area of disk does not incur the penalty again for the life of the instance, even if the DB instance is rebooted, restarted, or the DB instance class changes. Note that a DB instance created from a snapshot, a point-in-time restore, or a read replica is a new instance and does incur this first touch penalty.

• RDS manages the DB instance and it reserves overhead space on the instance. While the amount of reserved storage varies by DB instance class and other factors, this reserved space can be as much as one or two percent of the total storage.

• Provisioned IOPS provides a way to reserve I/O capacity by specifying IOPS.

• Current maximum channel bandwidth available is 4000 megabits per second (Mbps) full duplex. In terms of the read and write throughput metrics, this equates to about 210 megabytes per second (MB/s) in each direction. A perfectly balanced workload of 50% reads and 50% writes may attain a maximum combined throughput of 420 MB/s, which includes protocol overhead, so the actual data throughput may be less.

• Provisioned IOPS works with an I/O request size of 32 KB. Provisioned IOPS consumption is a linear function of I/O request size above 32 KB. An I/O request smaller than 32 KB is handled as one I/O; for e.g. 1000 16 KB I/O requests are treated the same as 1000 32 KB requests. I/O requests larger than 32 KB consume more than one I/O request; while, a 48 KB I/O request consumes 1.5 I/O requests of storage capacity; a 64 KB I/O request consumes 2 I/O requests

Factors That Impact Storage Performance

• Several factors can affect the performance of a DB instance, such as instance configuration, I/O characteristics, and workload demand.

• System related activities as:

  • DB snapshot creation

  • Nightly backups

  • Multi-AZ peer creation

  • Read replica creation

  • Scaling storage

• System resources can constrain the throughput of a DB instance, but there can be other reasons for a bottleneck. Database could be the issue if :-

  • Channel throughput limit is not reached

  • Queue depths are consistently low

  • CPU utilization is under 80%

  • Free memory available

  • No swap activity

  • Plenty of free disk space

  • Application has dozens of threads all submitting transactions as fast as the database will take them, but there is clearly unused I/O capacity.

Scale your Amazon RDS instances up or down with the RDS APIs or a few clicks in the console, often with no downtime

Scale from micro to 8xlarge and everything in between

Scale storage up with zero downtime

Scale throughput (when using provisioned IOPS RDS storage

For write-heavy workloads

Splits data into large chunks (shards)

Can give you higher performance and better operating efficiency in many circumstances

RDS creates a storage volume snapshot of the DB instance, backing up the entire DB instance and not just individual databases.

RDS provides two different methods Automated and Manual for backing up your DB instances

Point-in-time recovery creates a new database.

Backups of the DB instance are automatically created and retained

• Automated backups are enabled by default for a new DB instance.

• Automated backup occurs during a daily user-configurable period of time, known as preferred backup window.

  • If a preferred backup window is not specified when an DB instance is created, RDS assigns a default 30-minute backup window which is selected at random from an 8-hour block of time per region.

  • Changes to the backup window take effect immediately.

  • Backup window cannot overlap with the weekly maintenance window for the DB instance.

• Backups created during the backup window are retained for a user-configurable number of days , known as backup retention period

  • If the backup retention period is not set, RDS defaults the period retention period to one day if created using RDS API or the AWS CLI, or seven days if created AWS Console

  • Backup retention period can be modified with valid values are 0 (for no backup retention) to a maximum of 35 days.

• Manual snapshot limits (50 per region) do not apply to automated backups.

• If the backup requires more time than allotted to the backup window, the backup will continue to completion.

• An immediate outage occurs if the backup retention period is changed

  • from 0 to a non-zero value as the first backup occurs immediately

  • from non-zero value to 0 as it turns off automatic backups, and deletes all existing automated backups for the instance.

• RDS uses the periodic data backups in conjunction with the transaction logs to enable restoration of the DB Instance to any second during the retention period, up to the LatestRestorableTime (typically up to the last few minutes).

• During the backup window,

  • for Single AZ instance, storage I/O may be briefly suspended while the backup process initializes (typically under a few seconds) and a brief period of elevated latency might be experienced.

  • for Multi-AZ DB deployments, there is No I/O suspension since the backup is taken from the standby instance

• Automated DB snapshots are deleted when;

  • the retention period expires

  • the automated DB snapshots for a DB instance is disabled

  • the DB instance is deleted

• When a DB instance is deleted,

  • a final DB snapshot can be created upon deletion; which can be used to restore the deleted DB instance at a later date.

  • RDS retains the final user-created DB snapshot along with all other manually created DB snapshots.

  • All automated backups are deleted and cannot be recovered

Manual DB snapshots are user-initiated backups that enables to back up a DB instance to a known state, and restore to that specific state at any time.

RDS keeps all manual DB snapshots until explicitly deleted

DB Snapshots Creation

• DB snapshot is a user-initiated storage volume snapshot of DB instance, backing up the entire DB instance and not just individual databases.

• DB snapshots enable backing up of the DB instance in a known state as needed, and can then be restored to that specific state at any time.

• DB snapshots are kept until explicitly deleted

• Creating DB snapshot on a Single-AZ DB instance results in a brief I/O suspension that typically lasting no more than a few minutes.

• Multi-AZ DB instances are not affected by this I/O suspension since the backup is taken on the standby instance.

DB Snapshot Restore

• DB instance can be restored to any specific time during this retention period, creating a new DB instance.

• New DB instance with a different endpoint is created by restoring from a DB snapshot.

• RDS uses the periodic data backups in conjunction with the transaction logs to enable restoration of the DB Instance to any second during the retention period, up to the LatestRestorableTime (typically up to the last few minutes).

• Option group associated with the DB snapshot is associated with the restored DB instance once it is created. However, option group is associated with the VPC so would apply only when the instance is restored in the same VPC as the DB snapshot

• However, the default DB parameter and security groups are associated with the restored instance. After the restoration is complete, any custom DB parameter or security groups used by the instance restored from should be associated explicitly

• A DB instance can be restored with a different storage type than the source DB snapshot. In this case the restoration process will be slower because of the additional work required to migrate the data to the new storage type for e.g. from GP2 to Provisioned IOPS

• A DB instance can be restored with a different edition of the DB engine only if the DB snapshot has the required storage allocated for the new edition.

DB Snapshot Copy

• Amazon RDS supports two types of DB snapshot copying.

  • Copy an automated DB snapshot to create a manual DB snapshot in the same AWS region. Manual DB snapshot are not deleted automatically and can be kept indefinitely.

  • Copy either an automated or manual DB snapshot from one region to another region. By copying the DB snapshot to another region, a manual DB snapshot is created that is retained in that region

• Manual DB snapshots can be shared with other AWS accounts and copy DB snapshots shared to you by other AWS accounts.

• Snapshot Copy Encryption

  • DB snapshot that has been encrypted using an AWS Key Management System (AWS KMS) encryption key can be copied

  • Copying an encrypted DB snapshot, results in an encrypted copy of the DB snapshot

  • When copying, DB snapshot can either be encrypted with the same KMS encryption key as the original DB snapshot, or a different KMS encryption key to encrypt the copy of the DB snapshot.

  • An unencrypted DB snapshot can be copied to an encrypted snapshot, a quick way to add encryption to a previously encrypted DB instance.

  • Encrypted snapshot can be restored only to an encrypted DB instance.

  • If a KMS encryption key is specified when restoring from an unencrypted DB cluster snapshot, the restored DB cluster is encrypted using the specified KMS encryption key

  • Copying an encrypted snapshot shared from another AWS account, requires access to the KMS encryption key that was used to encrypt the DB snapshot.

  • Because KMS encryption keys are specific to the region that they are created in, encrypted snapshot cannot be copied to another region.

  • NOTE – AWS now allows copying encrypted DB snapshots between accounts and across multiple regions as seamlessly as unencrypted snapshots.

DB instance can be hosted in a VPC for the greatest possible network access control.

IAM policies can be used to assign permissions that determine who is allowed to manage RDS resources

Security groups allow to control what IP addresses or EC2 instances can connect to the databases on a DB instance-

Secure Socket Layer (SSL) connections with DB instances.

RDS encryption to secure RDS instances and snapshots at rest.

Network encryption and transparent data encryption (TDE) with Oracle DB instances.

RDS Authentication and Access Control

• IAM can be used to control which RDS operations each individual user has permission to call

Encrypting RDS Resources

• RDS encrypted instances use the industry standard AES-256 encryption algorithm to encrypt data on the server that hosts the RDS instance with a minimal impact on performance, and with no need to modify your database client applications.

• Data at Rest Encryption

  • can be enabled on RDS instances to encrypt the underlying storage

  • encryption keys are managed by KMS

  • can be enabled only during instance creation

  • once enabled, the encryption keys cannot be changed

  • if the key is lost, the DB can only be restored from the backup

• Once encryption is enabled for an RDS instance,

  • logs are encrypted

  • snapshots are encrypted

  • automated backups are encrypted

  • read replicas are encrypted

• You can easily create cross-region read replicas for MariaDB, MySQL and PostgreSQL database instances encrypted at rest with KMS.

• RDS DB Snapshot considerations

  • DB snapshot encrypted using an KMS encryption key can be copied

  • Copying an encrypted DB snapshot, results in an encrypted copy of the DB snapshot.

  • When copying, DB snapshot can either be encrypted with the same KMS encryption key as the original DB snapshot, or a different KMS encryption key to encrypt the copy of the DB snapshot.

  • An unencrypted DB snapshot can be copied to an encrypted snapshot, a quick way to add encryption to a previously unencrypted DB instance.

  • Encrypted snapshot can be restored only to an encrypted DB instance.

  • If a KMS encryption key is specified when restoring from an unencrypted DB cluster snapshot, the restored DB cluster is encrypted using the specified KMS encryption key.

  • Copying an encrypted snapshot shared from another AWS account, requires access to the KMS encryption key used to encrypt the DB snapshot.

  • If you copy an encrypted snapshot to a different AWS Region, then you must specify a KMS key for the destination AWS Region. KMS encryption keys are specific to the AWS Region that they are created in, and you cannot use encryption keys from one AWS Region in another AWS Region.

• Transparent Data Encryption (TDE)

  • Automatically encrypts the data before it is written to the underlying storage device and decrypts when it is read from the storage device.

  • is supported by Oracle and SQL Server

    • Oracle requires key storage outside of the KMS and integrates with CloudHSM for this

    • SQL Server requires a key but is managed by RDS

SSL to Encrypt a Connection to a DB Instance

• Encrypt connections using SSL for data in transit between the applications and the DB instance

• Amazon RDS creates an SSL certificate and installs the certificate on the DB instance when RDS provisions the instance.

• SSL certificates are signed by a certificate authority. SSL certificate includes the DB instance endpoint as the Common Name (CN) for the SSL certificate to guard against spoofing attacks.

• While SSL offers security benefits, be aware that SSL encryption is a compute-intensive operation and will increase the latency of the database connection.

RDS Security Groups

• Security groups control the access that traffic has in and out of a DB instance

• VPC security groups act like a firewall controlling network access to your DB instance.

• VPC security groups can be configured and associated with the DB instance to allow access from an IP address range, port, or EC2 security group.

• Database security groups default to a “deny all” access mode and customers must specifically authorize network ingress.

Master User Account Privileges

• When you create a new DB instance, the default master user that used gets certain privileges for that DB instance

• Subsequently, other users with permissions can be created

Event Notification

• Event notifications can be configured for important events that occur on the DB instance.

• Notifications of a variety of important events that can occur on the RDS instance, such as whether the instance was shut down, a backup was started, a failover occurred, the security group was changed, or your storage space is low can be received.

Changes to a DB instance can occur when a DB instance is manually modified for e.g. DB engine version is upgraded, or when Amazon RDS performs maintenance on an instance.

Amazon RDS Maintenance

• Periodically, Amazon RDS performs maintenance on Amazon RDS resources, such as DB instances and most often involves updates to the DB instance’s operating system (OS).

• Maintenance items can either

  • be applied manually on a DB instance at ones convenience

  • or wait for the automatic maintenance process initiated by Amazon RDS during the defined weekly maintenance window.

• Maintenance window only determines when pending operations start, but does not limit the total execution time (can continue beyond the specified end time) of these operations.

• Maintenance update availability can be checked both on the RDS console and by using the RDS API. And if an update is available, one can

  • Defer the maintenance items.

  • Apply the maintenance items immediately.

  • Schedule them to start during the next defined maintenance window

• Maintenance items marked as

  • Required cannot be deferred indefinitely, if deferred AWS will send a notify the time when the update will be performed next

  • Available can be deferred indefinitely

• Required patching is automatically scheduled only for patches that are related to security and instance reliability.

• Maintenance items require that RDS take your DB instance offline for a short time, includes scale compute operations, and required operating system or database patching.

• Multi-AZ deployment for the DB instance reduces the impact of a maintenance event by following these steps:

  • Perform maintenance on the standby.

  • Promote the standby to primary.

  • Perform maintenance on the old primary, which becomes the new standby.

• When database engine for the DB instance is modified in a Multi-AZ deployment, RDS upgrades both the primary and secondary DB instances at the same time. In this case, the database engine for the entire Multi-AZ deployment is shut down during the upgrade.

Operating System Updates

Database Engine Version Upgrade

• DB instance engine version can be upgraded when a new DB engine version is supported by RDS.

• Database version upgrades consist of major and minor version upgrades.

  • Major database version upgrades

    • can contain changes that are not backward-compatible

    • RDS doesn’t apply major version upgrades automatically

    • DB instance should be manually modified and thoroughly tested before applying it to the production instances.

  • Minor version upgrades

    • Each DB engine handles minor version upgrade slightly differently, for e.g. RDS automatically apply minor version upgrades to a DB instance running PostgreSQL, but must be manually applied to a DB instance running Oracle.

• Amazon posts an announcement to the forums announcement page and sends a customer e-mail notification before upgrading an DB instance

• Amazon schedule the upgrades at specific times through the year, to help plan around them, because downtime is required to upgrade a DB engine version, even for Multi-AZ instances.

• RDS takes two DB snapshots during the upgrade process.

  • First DB snapshot is of the DB instance before any upgrade changes have been made. If the upgrade fails, it can be restored from the snapshot to create a DB instance running the old version.

  • Second DB snapshot is taken when the upgrade completes. After the upgrade is complete, database engine can’t be reverted to the previous version. For returning to the previous version, restore the first DB snapshot taken to create a new DB instance.

• If the DB instance is using read replication, all of the Read Replicas must be upgraded before upgrading the source instance.

• If the DB instance is in a Multi-AZ deployment, both the primary and standby replicas are upgraded at the same time and would result in an outage. The time for the outage varies based on your database engine, version, and the size of your DB instance.

RDS Maintenance Window

• Every DB instance has a weekly maintenance window defined during which any system changes are applied.

• Maintenance window is an opportunity to control when DB instance modifications and software patching occur, in the event either are requested or required.

• If a maintenance event is scheduled for a given week, it will be initiated during the 30 minute maintenance window as defined.

• Maintenance events mostly complete during the 30 minute maintenance window, although larger maintenance events may take more time.

• 30-minute maintenance window is selected at random from an 8-hour block of time per region. If you don’t specify a preferred maintenance window when you create the DB instance, Amazon RDS assigns a 30-minute maintenance window on a randomly selected day of the week.

• RDS will consume some of the resources on the DB instance while maintenance is being applied, minimally effecting performance.

• For some maintenance events, a Multi-AZ failover may be required for a maintenance update to complete.

RDS integrates with CloudWatch and provides metrics for monitoring.

CloudWatch alarms can be created over a single metric that sends an SNS message when the alarm changes state.

RDS also provides SNS notification whenever any RDS event occurs.

CloudWatch RDS Monitoring

• The statistics are recorded for a period of two weeks.

• By default, RDS metric data is automatically sent to Amazon CloudWatch in 1-minute periods

• CloudWatch RDS Metrics

  • BinLogDiskUsage – Amount of disk space occupied by binary logs on the master. Applies to MySQL read replicas.

  • CPUUtilization – Percentage of CPU utilization.

  • DatabaseConnections – Number of database connections in use.

  • DiskQueueDepth – The number of outstanding IOs (read/write requests) waiting to access the disk.

  • FreeableMemory – Amount of available random access memory.

  • FreeStorageSpace – Amount of available storage space.

  • ReplicaLag – Amount of time a Read Replica DB instance lags behind the source DB instance. Applies to MySQL, MariaDB, and PostgreSQL Read Replicas.

  • SwapUsage – Amount of swap space used on the DB instance.

  • ReadIOPS – Average number of disk I/O operations per second.

  • WriteIOPS – Average number of disk I/O operations per second.

  • ReadLatency – Average amount of time taken per disk I/O operation.

  • WriteLatency – Average amount of time taken per disk I/O operation.

  • ReadThroughput – Average number of bytes read from disk per second.

  • WriteThroughput – Average number of bytes written to disk per second.

  • NetworkReceiveThroughput – Incoming (Receive) network traffic on the DB instance, including both customer database traffic and Amazon RDS traffic used for monitoring and replication.

  • NetworkTransmitThroughput – Outgoing (Transmit) network traffic on the DB instance, including both customer database traffic and Amazon RDS traffic used for monitoring and replication.

RDS Event Notification

• RDS uses the SNS to provide notification when an RDS event occurs.

• RDS groups the events into categories, which can be subscribed so that a notification is sent when an event in that category occurs.

• Event category for a DB instance, DB cluster, DB snapshot, DB cluster snapshot, DB security group or for a DB parameter group can be subscribed.

• Event notifications are sent to the email addresses provided during subscription creation.

• Subscription can be easily turn off notification without deleting a subscription by setting the Enabled radio button to No in the RDS console or by setting the Enabled parameter to false using the CLI or RDS API.

Operational Guidelines

• Monitoring

  • Memory, CPU, and storage usage should be monitored.

  • CloudWatch can be setup for notifications when usage patterns change or when the capacity of deployment is approached, so that system performance and availability can be maintained.

• Scaling

  • Scale up the DB instance when approaching storage capacity limits.

  • There should be some buffer in storage and memory to accommodate unforeseen increases in demand from the applications.

• Backups

  • Enable Automatic Backups and set the backup window to occur during the daily low in WriteIOPS.

• On a MySQL DB instance,

  • Do not create more than 10,000 tables using Provisioned IOPS or 1000 tables using standard storage. Large numbers of tables will significantly increase database recovery time after a failover or database crash. If you need to create more tables than recommended, set the innodb_file_per_table parameter to 0.

  • Avoid tables in the database growing too large. Provisioned storage limits restrict the maximum size of a MySQL table file to 6 TB. Instead, partition the large tables so that file sizes are well under the 6 TB limit. This can also improve performance and recovery time.

• Performance

  • If the database workload requires more I/O than provisioned, recovery after a failover or database failure will be slow.

  • To increase the I/O capacity of a DB instance,

    • Migrate to a DB instance class with High I/O capacity.

    • Convert from standard storage to Provisioned IOPS storage, and use a DB instance class that is optimized for Provisioned IOPS.

    • if using Provisioned IOPS storage, provision additional throughput capacity.

• Multi-AZ & Failover

  • Deploy applications in all Availability Zones, so if an AZ goes down, applications in other AZs will still be available.

  • Use Amazon RDS DB events to monitor failovers.

  • Set a TTL of less than 30 seconds, if the client application is caching the DNS data of the DB instances. As the underlying IP address of a DB instance can change after a failover, caching the DNS data for an extended time can lead to connection failures if the application tries to connect to an IP address that no longer is in service.

  • Multi-AZ requires transaction logging feature to be enabled. Do not use features like Simple recovery mode, offline mode or Read-only mode which turn of transaction logging.

  • To shorten failover time

    • Ensure that sufficient Provisioned IOPS allocated for your workload. Inadequate I/O can lengthen failover times. Database recovery requires I/O.

    • Use smaller transactions. Database recovery relies on transactions, so break up large transactions into multiple smaller transactions to shorten failover time

  • Test failover for your DB instance to understand how long the process takes for your use case and to ensure that the application that accesses your DB instance can automatically connect to the new DB instance after failover.

DB Instance RAM Recommendations

• An Amazon RDS performance best practice is to allocate enough RAM so that the working set resides almost completely in memory.

• Value of ReadIOPS should be small and stable.

• ReadIOPS metric can be checked, using AWS CloudWatch while the DB instance is under load, to tell if the working set is almost all in memory

• If scaling up the DB instance class with more RAM, results in a dramatic drop in ReadIOPS, the working set was not almost completely in memory.

• Continue to scale up until ReadIOPS no longer drops dramatically after a scaling operation, or ReadIOPS is reduced to a very small amount.

Security Best Practices

• Do not use AWS root credentials to manage Amazon RDS resources; and IAM users should be created for everyone.

• Grant each user the minimum set of permissions required to perform his or her duties.

• Use IAM groups to effectively manage permissions for multiple users.

• Rotate your IAM credentials regularly.

Using Enhanced Monitoring to Identify Operating System Issues

• Amazon RDS provides metrics in real time for the operating system (OS) that your DB instance runs on.

• Enhanced monitoring is available for all DB instance classes except for db.t1.micro and db.m1.small.

Using Metrics to Identify Performance Issues

• Performance metrics should be monitored on a regular basis to benchmark the average, maximum, and minimum values for a variety of time ranges. to help identify performance degradation.

• Amazon CloudWatch alarms can be set for particular metric thresholds to be alerted when they are reached or breached.

• A DB instance has a number of different categories of metrics which includes CPU, memory, disk space, IOPS, db connections and network traffic, and how to determine acceptable values depends on the metric.

• One of the best ways to improve DB instance performance is to tune the most commonly used and most resource-intensive queries to make them less expensive to run.

Recovery

• MySQL

  • InnoDB is the recommended and supported storage engine for MySQL DB instances on Amazon RDS.

  • However, MyISAM performs better than InnoDB if you require intense, full-text search capability.

  • Point-In-Time Restore and snapshot restore features of Amazon RDS for MySQL require a crash-recoverable storage engine and are supported for the InnoDB storage engine only.

  • Although MySQL supports multiple storage engines with varying capabilities, not all of them are optimized for crash recovery and data durability.

  • MyISAM storage engine does not support reliable crash recovery and might prevent a Point-In-Time Restore or snapshot restore from working as intended which might result in lost or corrupt data when MySQL is restarted after a crash.

• MariaDB

  • XtraDB is the recommended and supported storage engine for MariaDB DB instances on Amazon RDS.

  • Point-In-Time Restore and snapshot restore features of Amazon RDS for MariaDB require a crash-recoverable storage engine and are supported for the XtraDB storage engine only.

  • Although MariaDB supports multiple storage engines with varying capabilities, not all of them are optimized for crash recovery and data durability. For e.g although Aria is a crash-safe replacement for MyISAM, it might still prevent a Point-In-Time Restore or snapshot restore from working as intended. This might result in lost or corrupt data when MariaDB is restarted after a crash.