aws rds

What is AWS RDS (Relationship Database Service)?

/ Sagar/Shanky / Leave a comment

In this Post you will learn everything you must know end to end about AWS RDS. This tutorial will give you glimpse of each components starting from what is DB instance to scaling and multi availability zone cluster configurations and details.

Lets get started.

Table of Content

  • What is AWS RDS (Relationship Database Service)?
  • Database Instance
  • Database Engines
  • Database Instance class
  • DB Instance Storage
  • Blue/Green Deployments
  • Working with Read Replicas
  • How does cross region replication works?
  • Multi AZ Deployments
  • Multi AZ DB instance deployment
  • How to convert a single DB instance to Multi AZ DB instance deployment
  • Multi-AZ DB Cluster Deployments
  • DB pricing
  • AWS RDS performance troubleshooting
  • Tagging AWS RDS Resources
  • Amazon RDS Storage
  • Monitoring Events, Logs and Streams in an Amazon RDS DB Instance.
  • How to grant Amazon RDS to publish the notifications to the SNS topic using the IAM Policy.
  • RDS logs
  • AWS RDS Proxy
  • Amazon RDS for MySQL
  • Performance improvements on MySQL RDS for Optimized reads.
  • Importing Data into MySQL with different data source.
  • Database Authentication with Amazon RDS
  • Connecting to your DB instance using IAM authentication from the command line: AWS CLI and mysql client
  • Create database user account using IAM authentication
  • Generate an IAM authentication token
  • Connecting to DB instance
  • Connecting to AWS Instance using Python boto3 (boto3 rds)
  • Final AWS RDS Troubleshooting’s

What is AWS RDS (Relationship Database Service)?

  • It allows you to setup relational database in the AWS Cloud. AWS RDS is managed database service.
  • It is cost effective and resizable capacity because you if you invest in your own hardware, memory, CPU and it is time consuming and very costly.
  • With AWS RDS, it manages everything starting from Scaling, availability, backups, software patching, software installing, OS patching, OS installation, hardware lifecycle, server maintenance.
  • You can define permissions of your database users and database with IAM.

Database Instance

DB instance is a database environment which you launch your database users and user created databases.

  1. You can run your database instance in various AZ’s also known as multi-AZ deployments. Amazon automatically provisions and maintains secondary standby instance in different Availability zones. With this approach the primary DB replicates the data written into it to standby instance located in another AZ. Note: Instance in secondary can also be configured as read
  2. You can attach security groups to your database instance to protect your instance.
  3. You can launch DB instance in Local zones as well by enabling local zone in Amazon EC2 console.
  4. You can use Amazon CloudWatch to monitor the status of your database instance. You can monitor the following metrics:
    1. IOPS (I/O operations per second)
    1. Latency (Submitted I/O request until completed)
    1. Throughput (Number of bytes transferred per second) to or from disk.
    1. Queue depth: how many requests are pending in the queue.
  5. DB instance has a unique DB instance identifier that a customer or a user provider and should be different in AWS Region. If you provide the DB instance identifier as testing, then your endpoint formed will be as below.
testing. <account-id><region>.rds.amazonaws.com
  • DB instance supports various DB engines such as MySQL, MariaDB, PostgreSQL, Oracle, Microsoft SQL server, Amazon Aurora database engines.
  • A DB instance can host multiple databases with multiple schemas.
  • When you create any DB instance using AWS RDS service then by default it creates a master user account, and this user has all the permissions. Note: Make sure to change the password of this master user account.
  • You can create a backup of your Database instance by creating database snapshots.  You can also store your snapshots in AWS S3 bucket.
  • You can enable IAM database authentication on your database instance so that you don’t need any password to login to the database instance.
  • You can also enable Kerberos authentication to support external authentication of database users using Kerberos and Microsoft Active directory.
  • DB Instance are billed per hour.

Database Engines

Db engines are specific software’s that runs on your DB instance such as MariaDB, Microsoft SQL server, MySQL, Oracle and Postgres.

Database Instance class

Db instance class determines the computation, memory and storage capacity of a DB instance.  AWS RDS supports three types of DB instance classes:

  • General purpose:
  • Memory optimized:
  • Burstable Performance
  1. DB instance class supports Intel Hyper threading technology which enables multiple threads to run parallelly on single Intel Xeon CPU Core. Each thread is represented as vCPU on DB Instance. For example db.m4.xlarge DB Instance class has 2 CPU Core and two threads per CPU Core which makes to total of 4 vCPU’s. Note: You can disable Intel Hyper threading by specifying a single thread per CPU core when you need a high-performance computing workload.
  2. To set the Core count and Threads per core you need to edit the processor features.
  3. Quick note: To compare the CPU capacity between different DB instance class you should use ECU (Amazon EC2 instance compute units). The amount of CPU that is allocated to a DB instance is expressed in terms of EC2 compute units.
  4. You can use EBS optimised volumes which are good for your DB instance as it provides better performance by minimizing contention between I/O and other traffic from your instance.

DB Instance Storage

You can attach EBS the block level storage volumes to a running instance. DB Instance storage comes with:

  • General purpose (SSD) [gp2 and gp3]: They are cost effective which is ideal for board range of workload on medium sized Generally, they have throughput limit of 250MB/second.
  • For GP2
    • 3 IOPS for each GB with min 100 IOPS (I/O Operations per second)
    • 16000 IOPS for 5.34TB is max limit in gp2  
    • Throughput is max 250MB/sec where throughput is how fast the storage volume can perform read and write.
  • For GP3
    • Up to 32000 IOPS
  • Provisioned IOPS (PIOPS) [io1]: They are used when you need low I/O Latency, consistent I/O throughput. These are suited for production environments.
    • For io1 – up to 256000 (IOPS) and throughput up to 4000 MB/s
    • Note: Benefits of using provisioned IOPS are
      • Increase number of I/O requests that system cab process.
      • Decreased latency because less I/O requests will be in queue.
      • Faster response time and high database throughput.

Blue/Green Deployments

Blue/Green deployments copies database environments in a separate environment. You can make changes in staging environment and then later push those changes in production environments. Blue/ Green deployments are only available for RDS for MariaDB and RDS for MySQL.

Working with Read Replicas

  • Updates from primary DB are copied to the read replicas.
  • You can promote read replica to be standalone DB as well in case you require sharing (Share nothing DB)
  • You can use or create read replica in different AWS Region as well.

You can create one or more replicas of a given source DB Instance and serve high-volume application read traffic from multiple copies of your data, thereby increasing aggregate read throughput. 

Note: With Cross region read replicas you can create read replicas in a different region from the source DB instance.

How does cross region replication works?

  • IAM role of Destination must have access to Source DB Instance.
    • Source DB acts as source
    • RDS creates automated DB Snapshot of source DB
    • Copy of Snapshot starts
    • Destination read replica uses copied DB Snapshot

Note: You can configure DB instance to replicate snapshots and transaction logs in another AWS region.

Multi AZ Deployments

  • You can run your database instance in various AZ’s also known as multi-AZ deployments. Amazon automatically provisions and maintains secondary standby instance in different Availability zones. With this approach the primary DB replicates the data written into it to standby instance located in another AZ. Note: Instance in secondary can also be configured as read replicas.
  • You can align one standby or two standby instances.
  • When you have one standby instance it is known as Multi AZ DB instance deployment where one standby instance provides failover support but doesn’t act as read replica.
  • With Two standby instance it is known as Multi AZ DB cluster.
  • The failover mechanism automatically changes the Domain Name System (DNS) record of the DB instance to point to the standby DB instance

Note: DB instances with multi-AZ DB instance deployments can have increased write and commit latency compared to single AZ deployment.

Multi AZ DB instance deployment

In a Multi-AZ DB instance deployment, Amazon RDS automatically provisions and maintains a synchronous standby replica in a different Availability Zone.  You can’t use a standby replica to serve read traffic

If a planned or unplanned outage of your DB instance results from an infrastructure defect, Amazon RDS automatically switches to a standby replica in another Availability Zone if you have turned on Multi-AZ.

How to convert a single DB instance to Multi AZ DB instance deployment

  • Take a snapshot of primary DB instances EBS volume.
  • Creates a new volume for standby replicas from snapshot.
  • Next, turn on block level

Multi-AZ DB Cluster Deployments

  • It has one writer DB instance
  • It has two reader DB instances and allows clients to read the data.
  • AWS RDS replicates writer  
  • Data is synched from Writer instance to both the reader instances.
  • If a failover happens on of the writer instance then the reader instance acts as a automatic failover targets.  It does so by promoting a reader DB instance to a new writer DB instance. It happens automatically within 35 seconds and you can also do by going on Failover tab.

Cluster Endpoint

The cluster endpoint can write as well as read the data. The endpoint cannot be modified.

Reader Endpoint

Reader endpoint is used for reading the content from the DB cluster.

Instance Endpoint

These are used to connect to the DB instance directly to address the issues within instance or your application might require fine grained load balancing.

DB cluster parameter group

DB cluster parameter group acts as a container for engine configuration values that are applied to every DB instance in the Multi-AZ DB cluster

Rds Replica Lag

The Difference in time between latest transaction on writer DB instance and latest applied transaction on reader instance. This could be because of high write concurrency or heavy batch updating.

How to Solve Replica Lag

You can solve the replica lag by reducing the load on your writer DB instance. You can also use Flow control to reduce the replica lag. In Flow log you can add a delay into the end of a transaction, which decreases the write throughput on writer instance. To turn on flow control use the below parameter. By default it is set to 120 seconds and you can turn off by setting to 84000 seconds or less than 120 .

Flow control works by throttling writes on the writer DB instance, which ensures that replica lag doesn’t continue to grow unbounded. Write throttling is accomplished by adding a delay. Throttling means queue or let it flow.

rpl_semi_sync_master_target_apply_lag

To check the status of flow control use below command.

SHOW GLOBAL STATUS like '%flow_control%';

DB pricing

  • DB Instance are billed per hour.
  • Storage are billed per GB per month.
  • I/O requests (per 1 million requests per month.
  • Data transfer per GB in and out of your DB Instance.

AWS RDS performance troubleshooting

  1. Setup CloudWatch monitoring
  2. Enable Automatic backups
  3. If your DB requires more I/O, then to increase migrate to new instance class, convert from magnetic to general or provisioned IOPS.
  4. If you already have provisioned IOPS, consider adding more throughput capacity.
  5. If your app is caching DNS data of your instance, then make sure to set TTL value to less than 30 seconds because caching can lead to connection failures.
  6. Setup enough memory (RAM)
  7. Enable Enhanced monitoring to identify the Operating system issues
  8. Fine tune your SQL queries.
  9. Avoid tables in your database to grow too large as they impact Read and Writes.
  10. You can use options groups if you need to provide additional security for your database.
  11. You can use DB parameter group acts as a container for engine configuration values that are applied to one or more DB instances.

Tagging AWS RDS Resources

  • Tags are very helpful and are basically key value pair formats.
  • You can use Tags in IAM policies to manage access to AWS RDS resources.
  • Tags can be used to produce the detailed billing reports.
  • You can specify if you need tags to be applied to snapshots as well.
  • Tags are useful to determine which instance to be stopped, started, enable backups.

Amazon RDS Storage

Increasing DB instance storage capacity:

Click on Modify in Databases and then Allocated Storage and apply immediately.  

Managing capacity automatically with Amazon RDS storage autoscaling

If workload is unpredictable then enable autoscaling for an Amazon RDS DB Instance. While creating the database engine, enable storage autoscaling and set the maximum storage threshold.

Modifying settings for Provisioned IOPS SSD storage

You can change that is reduce the amount of IOPS for your instance (throughput ) i.e read and write operations however with Provisioned IOPS SSD Storage you cannot reduce the storage size.

Monitoring Events, Logs and Streams in an Amazon RDS DB Instance.

Amazon Event Bridge: Serverless Event bus service that allows to connect apps with data from various sources.

Cloud trail logs and Cloud Watch logs are useful.

Database Activities Streams: AWS RDS push activities to Amazon Kinesis data stream

How to grant Amazon RDS to publish the notifications to the SNS topic using the IAM Policy.

The IAM Policy will be attached to the SNS service.

{
  "Version": "2008-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "events.rds.amazonaws.com"
      },
      "Action": [
        "sns:Publish"
      ],
      "Resource": "arn:aws:sns:us-east-1:123456789012:topic_name",
      "Condition": {
        "ArnLike": {
          "aws:SourceArn": "arn:aws:rds:us-east-1:123456789012:db:prefix-*"
        },
        "StringEquals": {
          "aws:SourceAccount": "123456789012"
        }
      }
    }
  ]
}

RDS logs

  • Amazon RDS doesn’t provide host access to the database logs on the file system of your DB instance. You can Choose the Logs & events tab to view the database log files and logs on the console itself.
  • To publish SQL Server DB logs to CloudWatch Logs from the AWS Management Console. In the Log exports section, choose the logs that you want to start publishing to CloudWatch Logs.

Note: In CloudWatch Logs, a log stream is a sequence of log events that share the same source. Each separate source of logs in CloudWatch Logs makes up a separate log stream. A log group is a group of log streams that share the same retention, monitoring, and access control settings.

  • Amazon RDS provides a REST endpoint that allows access to DB instance log files and you can find the log using REST Endpoint.
GET /v13/downloadCompleteLogFile/DBInstanceIdentifier/LogFileName HTTP/1.1
Content-type: application/json
host: rds.region.amazonaws.com
  • RDS for MySQL writes mysql-error.log to disk every 5 minutes. You can write the RDS for MySQL slow query log and the general log to a file or a database table. You can direct the general and slow query logs to tables on the DB instance by creating a DB parameter group and setting the log_output server parameter to TABLE
    • slow_query_log: To create the slow query log, set to 1. The default is 0.
    • general_log: To create the general log, set to 1. The default is 0.
    • long_query_time: To prevent fast-running queries from being logged in the slow query log

MySQL removes log files more than two weeks old. You can manually rotate the log tables with the following command line procedures, 

CALL mysql.rds_rotate_slow_log;

AWS RDS Proxy

  • RDS Proxy allows you to pool and share db connections to improve ability to scale.
  • RDS Proxy makes applications more effective to db failures by automatically connecting to Standby DB instance.
  • RDS Proxy establishes a database connection pool and reuses connections in this pool and avoids the memory and CPU overhead of opening a new database connection each time.
  • You can enable RDS Proxy for most applications with no code changes.

You can use RDS Proxy in the following scenarios.

  • Any DB instance or cluster that encounters “too many connections” errors is a good candidate for associating with a proxy.
  • For DB instances or clusters that use smaller AWS instance classes, such as T2 or T3, using a proxy can help avoid out-of-memory conditions
  • Applications that typically open and close large numbers of database connections and don’t have built-in connection pooling mechanisms are good candidates for using a proxy.

Amazon RDS for MySQL

There are two versions that are available for MySQL database engines i.e. version 8.0  and 5.7. MySQL provides the validate_password plugin for improved security. The plugin enforces password policies using parameters in the DB parameter group for your MySQL DB instance

To find the available version in MySQL which are supported:

aws rds describe-db-engine-versions --engine mysql --query *[].{Engine:Engine,EngineVersion:EngineVersion}" --output text

SSL/TLS on MySQL DB Instance

Amazon RDS installs SSL/TLS Certificate on the DB Instance. These certificate are signed by CA.  

To connect to DB instance with certificate use below command.

mysql -h mysql–instance1.123456789012.us-east-1.rds.amazonaws.com --ssl-ca=global-bundle.pem --ssl-mode=REQUIRED -P 3306 -u myadmin -p

To check if applications are using SSL.

mysql> SELECT id, user, host, connection_type

       FROM performance_schema.threads pst

       INNER JOIN information_schema.processlist isp

       ON pst.processlist_id = isp.id;

Performance improvements on MySQL RDS for Optimized reads.

  • An instance store provides temporary block-level storage for your DB instance.
  • With RDS Optimized reads some temporary objects are stored on Instance store. These objects include temp files, internal on disk temp tables, memory map files, binary logs, cached files.
  • The storage is located on Non-Volatile Memory express SSD’s that are physically attached.
  • Applications that can uses RDS for Optimized reads are:
    • Applications that run on-demand or dynamic reporting queries.
    • Applications that run analytical queries.
    • Database queries that perform grouping or ordering on non-indexed columns
  • Try to add retry logic for read only queries.
  • Avoid bulk changes in single transaction.
  • You can’t change the location of temporary objects to persistent storage (Amazon EBS) on the DB instance classes that support RDS Optimized Reads.
  • Transactions can fail when the instance store is full.
  • RDS Optimized Reads isn’t supported for multi-AZ DB cluster deployments.

Importing Data into MySQL with different data source.

  1. Existing MySQL database on premises or on Amazon EC2: Create a backup of your on-premises database, store it on Amazon S3, and then restore the backup file to a new Amazon RDS DB instance running MySQL.
  2. Any existing database: Use AWS Database Migration Service to migrate the database with minimal downtime
  3. Existing MySQL DB instance: Create a read replica for ongoing replication. Promote the read replica for one-time creation of a new DB instance.
  4. Data not stored in an existing database: Create flat files and import them using the mysqlimport utility.

Database Authentication with Amazon RDS

For PostgreSQL, use one of the following roles for a user of a specific database.

  • IAM Database authentication: assign rds_iam role to user
  • Kerberos authentication  assign rds_ad role to the user.
  • Password authentication don’t assign above roles.

Password Authentication

  • With Password authentication, database performs all the administration of user accounts. Database controls and authenticate the user accounts.

IAM Database authentication

  • IAM database authentication works with MySQL and PostgreSQL. With this authentication method, you don’t need to use a password when you connect to a DB instance

Kerberos Authentication

Benefits of using SSO and centralised authentication of database users.

Connecting to your DB instance using IAM authentication from the command line: AWS CLI and mysql client

  • In the Database authentication section, choose Password and IAM database authentication to enable IAM database authentication.
  • To allow an IAM user or role to connect to your DB instance, you must create an IAM policy.
{

   "Version": "2012-10-17",

   "Statement": [

      {

         "Effect": "Allow",

         "Action": [

             "rds-db:connect"

         ],

         "Resource": [

             "arn:aws:rds-db:us-east-2:1234567890:dbuser:db-ABCDEFGHIJKL01234/db_user"

         ]

      }

   ]

}

Create database user account using IAM authentication

CREATE USER jane_doe IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
CREATE USER db_userx;
GRANT rds_iam TO db_userx;

Generate an IAM authentication token

aws rds generate-db-auth-token --hostname rdsmysql.123456789012.us-west-2.rds.amazonaws.com --port 3306 --region us-west-2  --username jane_doe

Connecting to DB instance

mysql –host=hostName –port=portNumber –ssl-ca=full_path_to_ssl_certificate –enable-cleartext-plugin –user=userName –password=authToken

Connecting to AWS Instance using Python boto3 (boto3 rds)

import pymysql
import sys
import boto3
import os

ENDPOINT="mysqldb.123456789012.us-east-1.rds.amazonaws.com"
PORT="3306"
USER="jane_doe"
REGION="us-east-1"
DBNAME="mydb"

os.environ['LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN'] = '1'

#gets the credentials from .aws/credentials
session = boto3.Session(profile_name='default')
client = session.client('rds')
token = client.generate_db_auth_token(DBHostname=ENDPOINT, Port=PORT, DBUsername=USER, Region=REGION)
try:
    conn =  pymysql.connect(host=ENDPOINT, user=USER, passwd=token, port=PORT, database=DBNAME, ssl_ca='SSLCERTIFICATE')

    cur = conn.cursor()
    cur.execute("""SELECT now()""")
    query_results = cur.fetchall()
    print(query_results)

except Exception as e:
    print("Database connection failed due to {}".format(e))

   

Final AWS RDS Troubleshooting’s

Can’t connect to Amazon RDS DB instance

  • Check Security group
  • Check Port
  • Check internet Gateway
  • Check db name

Error – Could not connect to server: Connection timed out

  • Check hostname and port
  • Check security group
  • Telnet to the DB
  • Check the username and password

Error message “failed to retrieve account attributes, certain console functions may be impaired.”

  • Account is missing permissions, or your account hasn’t been properly set up.
  • lack permissions in your access policies to perform certain actions such as creating a DB instance

Amazon RDS DB instance outage or reboot

  • You change the backup retention period for a DB instance from 0 to a nonzero value or from a nonzero value to 0. You then set Apply Immediately to true.
  • You change the DB instance class, and Apply Immediately is set to true.
  • You change the storage type from Magnetic (Standard) to General Purpose (SSD) or Provisioned IOPS (SSD), or from Provisioned IOPS (SSD) or General Purpose (SSD) to Magnetic (Standard).

Amazon RDS DB instance running out of storage

  • Add more storage in  EBS volumes attached to the DB instance.

Amazon RDS insufficient DB instance capacity

The specific DB instance class isn’t available in the requested Availability Zone. You can try one of the following to solve the problem:

  • Retry the request with a different DB instance class.
  • Retry the request with a different Availability Zone.
  • Retry the request without specifying an explicit Availability Zone.

Maximum MySQL and MariaDB connections

  • The connection limit for a DB instance is set by default to the maximum for the DB instance class. You can limit the number of concurrent connections to any value up to the maximum number of connections allowed.
  • A MariaDB or MySQL DB instance can be placed in incompatible-parameters status for a memory limit when The DB instance is either restarted at least three time in one hour or at least five times in one day or potential memory usage of the DB instance exceeds 1.2 times the memory allocated to its DB instance class. To solve the issue:
    • Adjust the memory parameters in the DB parameter group associated with the DB instance.
    • Restart the DB instance.

Conclusion

This tutorial will gave you glimpse of each components starting from what is DB instance to scaling and multi availability zone cluster configurations and AWS RDS details.

Advertisement