AWS Databases - Quick Notes

Amazon Relation Database Service (RDS)

The following is the summary of the various features/capabilities of Relation Database Service:

• Is a managed relational database service that makes it easier to set up, operate, and scale in the AWS Cloud

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

• A DB Instance is an isolated database environment that can contain multiple user-created databases, which can be accessed using the same client tools and applications one might use to access a standalone database instance

• Supported databases include MySQL, MariaDB, PostgreSQL, Oracle, Microsoft SQL Server, AWS Aurora

• Useful for applications with UNPREDICTABLE workloads

• The following are some details on the features:

  Managed Service

  • Runs on EC2 Instances and one needs to choose the Instance Type

  • One CANNOT SSH into the EC2 instances since it is managed

  • Automatic provisioning, patching, and upgrades

  • Supports continuous backups and point in time restore (to specific timetamp)

  • Exposes a monitoring dashboard

  • Support for Read Replicas for improved read performance

  • Supports Multi Availability Zone setup for disaster recovery (DR)

  • Maintenance windows for upgrades

  • Support for both vertical (change the instance type) and horizontal scaling for queries only (using read replicas)

  • Storage backed by Elastic Block Storage (EBS) (gp3 or io1)

  Storage Auto Scaling

  • Allows one to increase storage on the DB instance dynamically

  • Will detect if is running out of storage space (based on some threshold) and automatically scale (add more storage space)

  • Need to set the Maximum Storage Threshold which indicates the maximum capacity for the DB storage

  • Automatically modify storage if - free storage is less than 10 percent of allocated storage AND low storage condition lasts at least 5 mins AND 6 hours have passed since the last modification

  Read Replicas

  • Allows one to improve the read performance

  • One can create up to 15 read replicas

  • The read replicas can be in the same availability zone or cross availability zone OR cross-region

  • The replication mode is ASYNCHRONOUS between the master DB instance and the read replica DB instances for cross-region

  • Reads are eventually consistent for cross-region

  • There is a network cost going from one availability zone to another

  • For read replicas in the same Region but different availability zone, there is NO network cost

  • For read replicas in a different Region, there IS a network cost to pay (cross region incurs replication fees)

  • The read replica(s) will ALWAYS have the same encryption status (encrypted or unencrypted) as the primary DB instance

  Multi Availability Zone

  • SYNCHRONOUS replication from the master DB instance in one availability zone to a standby DB instance in another availability zone in the same Region

  • There is one DNS name that includes the master DB instance and the standby DB instance

  • If the master DB instance fails, automatic fail-over to the standby DB instance and it is promoted to be the master

  • Increases availability

  • Failover in case of loss of availability zone, loss of network, storage failure

  • Read replicas CAN be setup as multi availability zone instances

  RDS Custom

  • Only for Oracle, Microsoft SQL Server

  • Allows for the operating system (OS) and database customizations

  • Configure optimization settings

  • Install patches

  • Enable native features

  • Access the underlying EC2 Instance using SSH

• The following are the supported Backup Types:

  Automated DB Backups

  • Choose a Backup window or RDS will choose one

  • Daily full backup (during the backup window) taken as Snapshots

  • Transaction Logs backed every 5 mins

  • Can easily restore to any point in time (oldest backup 5 mins ago)

  • Retention period for backup between 1 to 35 days. Setting to 0 means automatic backup OFF

  Manual DB Snapshots

  • Manually triggered by the user

  • No Retention period limit and can exist as long as one wants

  • For a single availability zone DB instance there will be a brief suspension of I/O (will effect production applications)

  • For a multi availability zone Microsoft SQL Server, there is a brief suspension of I/O on the primary DB instance

  • For a multi availability zone MySQL, PostgreSQL, MariaDB, and Oracle, the snapshots are ALWAYS taken from the standby (hence no I/O suspension)

• Restoring a backup (from a snapshot) ALWAYS creates a new DB instance

• To migrate from a single availability zone to a multi availability zone - a DB instance snapshot needs to be taken, a new DB instance in another availability zone needs to be restored from the snapshot and finally synchronous replication enabled

• To optimize cost, when stopping a DB for a long period (one will pay for storage irrespective), it would be more cost effective to take a snapshot, delete the storage, and later restore the DB instance from the snapshot

• To create an encrypted DB instance from an unencrypted DB instance, take a snapshot from the unencrypted DB instance, then COPY the snapshot to an encrypted snapshot, and finally use the encrypted snapshot to restore a new encrypted DB instance

Amazon Aurora

The following is the summary of the various features/capabilities of Aurora:

• Is a fully managed proprietary relational database engine that is compatible with MySQL and PostgreSQL

• Is optimized for the cloud with 5x performance improvements over MySQL and 3x performance improvements over PostgreSQL

• Features a distributed, fault tolerant, self-healing storage that automatically grows in increments of 10 GB up to 128 TB

• Can have up to 15 replicas and the replication is faster that MySQL or PostgreSQL (less than 10 ms replica lag)

• Failover is instantaneous since High Availability (HA) is built-in natively

• Costs 20 percent more than RDS but is more efficient

• The following are some details on the features:

  High Availability

  • Stores 6 copies of data across 3 availability zones

  • One of the instances is the Primary (master) and handles the writes to all the 3 availability zones

  • If the primary fails, one of the replicas will be promoted as primary and the failover happens in less than 30 secs

  • Self-healing with peer-to-peer replication

  • Storage is striped across 100s of volumes

  • Auto scaling of replicas

  • Support for cross-region replication

  DB Cluster

  • Consists of one or more DB instances and a cluster volume that manages the data for those DB instances

  • A Cluster Volume is a virtual database storage volume that spans 3 availability zones, with each availability zone having 2 copies of the DB cluster data

  • A Cluster Endpoint (or Writer Endpoint) connects to the current primary DB writer instance for that DB cluster for performing writes

  • A Reader Endpoint provides load-balancing support for read-only connections to a DB reader instance for performing queries

  • Support for Backtracking which quickly lets one rewind the DB cluster to a specific point in time

  Global Database

  • One primary Region for read/write

  • Up to 5 replica secondary Regions with replication lag less than 1 sec (cross-region replication takes less than 1 sec)

  • Up to 15 replicas per secondary Region

  • Promoting another Region as primary has an RTO of less than 1 min

  DB Cloning

  • Create a new DB cluster from an existing one

  • Much FASTER than snapshot and restore

  • Uses copy-on-write protocol (initially the new DB cluster uses the same data volume as the existing DB cluster. When updates are made to the new DB cluster only then additional storage is allocated and the data is copied over)

  • Very cost effective

  Serverless Option

  • On-demand auto scaling configuration

  • Does not support read replicas

  • No public IPs exposed

  • Can only be accessed through VPC or Direct Connect

• The following are the supported Backup Types:

  Automated DB Backups

  • Retention period for backup between 1 to 35 days and CANNOT be disabled

  • Point in time to any point in the timeframe

  Manual DB Snapshots

  • Manually triggered by the user

  • No Retention period limit and can exist as long as one wants

Amazon RDS Proxy

The following is the summary of the various features/capabilities of RDS Proxy:

• Fully managed database proxy for RDS

• Serverless, auto scaled, highly available in multiple availability zones

• Allows a client application to pool and share database connections to improve their ability to scale

• Helps increase scalability, fault tolerance, and security for connections to the RDS DB instance

• Makes applications more resilient to database failures by automatically connecting to a standby DB instance while preserving application connections

• By pooling DB connections, avoids the memory and CPU overhead of opening a new DB connection each time

• One can enforce IAM authentication for databases and securely store credentials in AWS Secrets Manager

• Support for MySQL, PostgreSQL, MariaDB, SQL Server, Aurora

• Never publicly accessible and can be accessed ONLY from a customers VPC

Amazon ElasticCache

The following is the summary of the various features/capabilities of ElasticCache:

• Is a managed service that makes it easy to set up, manage, and scale a distributed in-memory key-value data store or cache environment in the AWS cloud

• Provides a high performance, low-latency, resizable, and cost-effective in-memory cache

• Supports both the Redis and Memcached engines

• Helps reduce load off RDS databases for read intensive workloads

• Relieves the customer of setup, maintenance, patching, upgrades, configuration, optimizations, monitoring, failover recovery, and backups

• Runs on EC2 Instances and one needs to choose the Instance Type

• Useful for storing web session state, leaderboards (Redis only)

• The following are some details on the two supported engines:

  Redis

  • Multi availability zone with auto failover

  • Read replicas to scale and have high availability

  • Backup (automatic and manual) and restore features

  • Support for data persistence

  • Supports Sets and Sorted Sets

  • Support for IAM Authentication

  • Support for SSL/TLS for in-transit encryption

  Memcached

  • Multi node for partitioning data (sharding)

  • No high availability (no fail-over, no replication)

  • No Backup and restore features

  • No support for IAM Authentication

  • No support for data encryption

  • Multi threaded architecture

Amazon DynamoDB

The following is the summary of the various features/capabilities of DynamoDB:

• Is a fully managed, serverless, NoSQL database service that provides fast and predictable performance with seamless scalability

• Is automatically replicated across multiple availability zones in a Region (synchronous) thus providing built-in high availability and data durability

• Is a key-value and document type NoSQL database with transaction support

• Can support strong consistency or eventual consistency

• Scales to massive workloads as it is a distributed database

• Can handle millions of requests per sec

• Can store trillions of rows with 100s of TB of storage

• Fast and consistent single digit millisec performance

• Integrated with IAM for security and authorization

• Low cost and has auto-scaling capabilities

• No maintenance or patching and is always available

• Two types of table classes - Standard and Infrequently Access

• Is made of Tables with Items (similar to rows), which in turn is made up of Attributes

• Each table can have an infinite number of Items (similar to rows)

• Each Item has many Attributes (similar to columns)

• Flexible table schema that can evolve over time meaning the attributes can be null and can be added over time

• Each table has a Partition Key and optional Sort Key to form the Primary Key which MUST be decided at the creation time

• Maximum size of an Item is 400 KB

• Supported data types - scalar (string, number, boolean, binary, null), document types (list, map), set types (string set, number set, binary set)

• An item can have an associated Time-to-Live (TTL) and they are automatically delete after an expiration period

• The following are the two Capacity Modes for Read and Write:

  Provisioned Mode (default)

  • One needs to plan the capacity beforehand

  • One needs to specify the number of reads and writes per sec

  • Pay for provisioned Read Capacity Units (RCU) and Write Capacity Units (WCU)

  • Possible to add capacity using auto-scaling mode for RCU and WCU

  • Useful for predictable workloads and cost-effective

  On-Demand Mode

  • No capacity planning needed

  • The reads and writes capacity automatically scales up and down with the workload

  • Pay for the usage and can be more expensive

  • Useful for unpredictable bursty workloads

• The following are the two type of Backups supported:

  Continuous

  • Support for point-in-time recovery (with per second granularity)

  • Optionally enabled for the last 35 days (backup window)

  • Recovery process creates a new table

  On-Demand

  • Full backups for long-term retention, until explicitly deleted

  • Does not effect the performance or latency

  • Can use the AWS Backup for a managed service (enables cross-region copy)

  • Recovery process creates a new table

• The following are some details on the other features:

  DynamoDB Streams

  • Captures a time-ordered stream of item-level modifications (create/update/delete) into a table

  • Useful for inserting into another table, for cross-region replication, for invoking a Lambda function

  • 24 hour retention

  • Can have a limited number of consumers

  • Process using Lambda functions or Kinesis Client Library (KCL)

  DynamoDB Accelerator (DAX)

  • Is a fully managed, highly available, seemless in-memory cache for DynamoDB

  • Can be a read-through and a write-through cache

  • Helps solve the read and write performance by caching items

  • Improves performance from millisecs to microsecs latency with cached data

  • No changes to the application using DynamoDB (sits in front of the application)

  • Need to set IAM policy for DAX to access DynamoDB

  • Has a default TTL of 5 mins

  DynamoDB Global Tables

  • Fully managed multi region, multi master (or multi active)

  • One MUST enable DynamoDB Streams before enabling this option

  • Replicated across multiple Regions with two-way asynchronous Replication using DynamoDB Streams

  • Allows one to access a table with low-latency in multiple Regions

  • Is an Multi Active replication meaning one can perform read and write operations in any Region

  DynamoDB Integration with S3

  • One can export a table to S3 with point-in-time recovery

  • The exported format can be JSON or ION

  • One can also import from S3 to a table

  • The imported format can be CSV, JSON, or ION

Amazon Redshift

The following is the summary of the various features/capabilities of Redshift:

• Is a fast, fully managed, petabyte-scale data warehouse service that makes it simple and cost-effective to efficiently analyze customer data using their existing business intelligence tools

• Is optimized for datasets ranging from a few hundred gigabytes to a petabyte or more, has 10x better performance, and costs a tenth of most traditional data warehousing solutions

• Based on PostgreSQL and uses a columnar storage with parallel query engine

• Uses the industry standard SQL to perform very complex queries

• Uses EC2 Instances and hence must choose the Instance Type

• Pay as you go based on the EC2 Instances provisioned

• Maintains 3 copies of the data

• Has a Leader Node and one or more Compute Nodes

• Leader Node is for query planning and result aggregation

• Compute Nodes for performing the actual queries and sending results back to the Leader Node

• Can be run in single availability zone OR multi availability zone modes

• Can take a point-in-time backup as snapshots which are stored in AWS S3

• Snapshots are incremental meaning will only save what has changed from the previous snapshot

• Automatic snapshots are scheduled for every 8 hours or after every 5 GB or on a customer schedule

• Can configure the retention of the automated snapshots

• Users can trigger a manual snapshot and can be retained for as long as the user wishes

• Snapshots can be automatically copied to another Region for disaster recovery

• One can leverage Amazon Kinesis Data Firehose or S3 Copy to ingest data into the data warehouse

• One can use Redshit Spectrum to access data from S3 directly without loading into the data warehouse

References

Official AWS RDS Documentation

Official AWS Aurora Documentation

Official AWS RDS Proxy Documentation

Official AWS Redis ElasticCache Documentation

Official AWS Memcached ElasticCache Documentation

Official AWS DynamoDB Documentation

Official AWS Redshift Documentation

© PolarSPARC