Several Operational Considerations for Amazon RDS

Amazon Relational Database Service (RDS) is an ultra common cloud based database service. It is easy to setup and operate, with many operational tasks automated, and can scale with demand. RDS offers eight engine types, including six standard RDS engines: RDS for PostgreSQL, RDS for MySQL, RDS for MariaDB, RDS for SQL Server, RDS for Oracle, and RDS for Db2, as well as two Amazon Aurora engine types: Aurora PostgreSQL-Compatible Edition and Aurora MySQL-Compatible Edition.

There can be a number of operational considerations that provide help with the better use of RDS in enterprise settings. This blog piece discusses several of them based on real world experience. It is not intended to be an exhaust list – each organisation is expected to research and apply those considerations that are more relevant to the specific use case of the RDS. It would already be meaningful if this piece can encourage some active research and discussions on this topic. 


When the Provisioned IOPS storage type is used, make sure the DB instances use the instance class that is optimised for Provisioned IOPS


Provisioned IOPS storage is designed to meet the needs of intensive I/O workloads, particularly database workloads, for low I/O latency and predictable, consistent I/O throughput. Good use case examples for Provisioned IOPS storage are online transaction processing (OLTP) workloads that require consistent performance. 


There are many instance types that support RDS. Among these, EBS-optimised instances can enable RDS to fully use the IOPS provisioned on an EBS volume. This is because dedicated throughput between RDS and EBS is ensured for EBS-optimized instances, with options between 500 and 4,000 Mbps depending on the instance type used. Such dedicated throughput minimises the contention between EBS I/O and other traffic from RDS instance, providing the best performance for the EBS volumes. Detailed lists of EBS-optimised instance types are maintained on AWS website:


https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-optimized.html




Ensure the TCP ports used by Amazon RDS instances are not the default endpoint ports


This is called port obfuscation and provides an additional layer of defence against non-targeted attacks, as using default ports for RDS database instances represents a potential security concern. Changing database instance connection accepting ports to non-default ports will help to protect RDS databases from brute-force and dictionary attacks.

The default port numbers for most common RDS database engine types are listed below:


Aurora/MySQL/MariaDB:        3306

PostgreSQL:            5431

Oracle:                    1521

SQL Server:            1433


First, it may be desired to check whether the existing RDS instances are using default ports. This can be done through CLI or the AWS Management Console. The following example shows the use of CLI:


aws rds describe-db-instances

--region <region>

--output table

--query 'DBInstances[*].DBInstanceIdentifier'


A table will be returned with a list of RDS instance names.


Then:


aws rds describe-db-instances

  --region us-east-1

  --db-instance-identifier <instancename>

  --query 'DBInstances[*].Endpoint.Port'


The port number used by the specified DB instance will be shown. 


There are multiple ways to change the default port number for the existing RDS database instances. Using AWS CloudFormation is one of the recommended ways. Following CloudFormation template can be used:


{

"AWSTemplateFormatVersion": "2010-09-09",

"Description": "Change the Default Database Port Number",

"Parameters": {

"DBInstanceName": {

"Default": "mysql-database-instance",

"Description": "RDS database instance name",

"Type": "String",

"MinLength": "1",

"MaxLength": "63",

"AllowedPattern": "^[0-9a-zA-Z-/]*$",

"ConstraintDescription": "Must begin with a letter and must not end with a hyphen or contain two consecutive hyphens."

},

"DBInstanceClass": {

"Default": "db.t2.micro",

"Description": "DB instance class/type",

"Type": "String",

"ConstraintDescription": "Must provide a valid DB instance type."

},

"DBAllocatedStorage": {

"Default": "20",

"Description": "The size of the database (GiB)",

"Type": "Number",

"MinValue": "20",

"MaxValue": "65536",

"ConstraintDescription": "Must be between 20 and 65536 GiB."

},

"DBName": {

"Default": "mysqldb",

"Description": "Database name",

"Type": "String",

"MinLength": "1",

"MaxLength": "64",

"AllowedPattern": "[a-zA-Z][a-zA-Z0-9]*",

"ConstraintDescription": "Must begin with a letter and contain only alphanumeric characters."

},

"DBUsername": {

"Description": "Username for database access",

"Type": "String",

"MinLength": "1",

"MaxLength": "16",

"AllowedPattern": "[a-zA-Z][a-zA-Z0-9]*",

"ConstraintDescription": "Must begin with a letter and contain only alphanumeric characters."

},

"DBPassword": {

"NoEcho": "true",

"Description": "Password for database access",

"Type": "String",

"MinLength": "8",

"MaxLength": "41",

"AllowedPattern": "[a-zA-Z0-9]*",

"ConstraintDescription": "Must contain only alphanumeric characters."

}

},

"Resources": {

"RDSInstance": {

"Type": "AWS::RDS::DBInstance",

"Properties": {

"DBInstanceIdentifier": {

"Ref": "DBInstanceName"

},

"DBName": {

"Ref": "DBName"

},

"MasterUsername": {

"Ref": "DBUsername"

},

"MasterUserPassword": {

"Ref": "DBPassword"

},

"DBInstanceClass": {

"Ref": "DBInstanceClass"

},

"AllocatedStorage": {

"Ref": "DBAllocatedStorage"

},

"Engine": "MySQL",

"EngineVersion": "5.7.36",

"Port": "3691"

}

}

}

}





Query Tuning


The performance of RDS instances can be increased by tuning the most commonly used and most resource-intensive queries in an organisation’s use case. This is a big topic and case specific, and will not fit into a blog piece - so the intention here is to have this discussed to encourage active considerations. 


Using Oracle DB as an example, its so-called SQL Tuning can reduce user response time and improve throughput. There are both proactive SQL tuning, where you regularly use SQL Tuning Advisor to determine whether you can make SQL statements perform better, and reactive SQL tuning, where you correct a SQL-related problem that a user has experienced.

A typical SQL tuning session involves all or most of the following tasks:


  • Identifying high-load SQL statements
  • Gathering performance-related data
  • Determining the causes of the problem
    • Inefficiently designed SQL statements
    • Suboptimal execution plans
    • Missing SQL access structures
    • Stale optimizer statistics
    • Hardware problems
  • Defining the scope of the problem
  • Implementing corrective actions for suboptimally performing SQL statements
  • Preventing SQL performance regressions



GuardDuty RDS Protection


It is always recommended to have Amazon GuardDuty configured. That is a given. But what can be further done is RDS Protection in GuardDuty, which analyses and profiles RDS login activity for potential access threats to several types of RDS engines:


• Aurora MySQL-Compatible Edition 

• Aurora PostgreSQL-Compatible Edition

• RDS for PostgreSQL 


Using this feature, potentially suspicious login behaviours can be identified. RDS Protection does not affect the database instance performance.


Once RDS Protection in GuardDuty is enabled, it starts monitoring RDS login activities in the AWS account. GuardDuty continuously monitors and profiles this for suspicious activity. 


Let’s say there is a new DB instance being created and GuardDuty RDS Protection enabled, then a learning period is required to baseline the ‘normal behavior’. This learning period may be up to two weeks. 


Then when RDS Protection detects a potential threat, such as an unusual pattern in a series of successful, failed, or incomplete login attempts, GuardDuty generates a new finding with details about the potentially compromised database instance. 



Above are just several RDS operational considerations from some real world experience. More considerations are there for various database scenarios. This blog encourages active and comprehensive investigation into this topic by any organisation that uses Amazon RDS.  


--Simon Wang

 

Comments

Post a Comment

Popular posts from this blog

Fairness Evaluation and Model Explainability In AI

Image
Artificial Intelligence (AI) relies on machine learning and its modelling to provide outcomes. Imagine a credit card company receives hundreds of thousands of applications each year and would like to use AI to do the first round of application filtering. If it is not developed properly, the AI decisions will be skewed with unfairness – such as rejecting a lot more applications from a certain age group, from a certain gender, or from a certain employment history pattern – UNFAIRLY. Please note unfairness is the key issue here – in other words, AI and machine learning inference that is not reflecting the due course in actual situations. This is typically called bias in AI and machine learning. Biases can come from various stages of a machine learning life cycle: • Biases may exist in the pre-training data -  e.g., the dataset to be used in machine learning training has biases in it • Biases may be introduced by the machine learning exercise  Below is a machine learning lifecycle char
Read more

AWS and Generative AI

Image
 Generative Artificial Intelligence, or Generative AI, GenAI, is one of the hottest topics in the recent months. You would ‘have to be living under a rock to not notice this massive topic’.  ‘Generative AI is a branch of AI that focuses on creating new data. It is a subset of machine learning. The goal of generative AI is to create new data that is similar to the data that was used to train the model.’ – texts generated by an AI tool.  Recently I experimented a couple of random text-to-image GenAI tools to observe how generative AI can create contents and how the data generated by different GenAI tools trained by different models is different. My experiment was straightforward: text-to-image GenAI tools ask you to provide some texts as a prompt, and then use this input to generate an image. So that was what I did. The prompt I supplied was: ‘female student graduation photo in front of a white building with flowers in hand in Paris’. Both GenAI tools I tested could output an image withi
Read more

Amazon CloudFront and Its Primary and Secondary Origins

Nowadays most of the online contents are web service based. A user accesses a specific content, for example a web page, by connecting their client end application (like a web browser) running on their desktop or mobile computing device to the server that hosts the web page. There are billions of websites and webpages. DNS services, online search engines and links on well known ‘portal’ websites help people to find and locate the contents they would like to visit.  A user’s web client device and the server it visits can be worldly apart: for example, let’s think of a user in Tasmania, Australia that uses its smart phone to visit a website hosted on a server that locates in a data centre in Montreal, Canada, which is more than sixteen thousand kilometres away. They are connected by the Internet. The Internet comprises of millions of inter-connected networks. Traffic on the Internet is routed between the users and the servers through those inter-connected networks in-between. Using the ab
Read more