Follow dba-ninja.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

dba-ninja.com Links

Dba_db2_button

Sqlserver_dba_button

Amazon RDS DB Instance storage

14 December,2022 by Rambler

Deciding storage type & instance type for a specific database workload can be a complicated task . One of the biggest challenges facing DBAs is the ability to utilize the AWS resources in a cost optimal method. One of the main reasons for the challenge is the vast range of storage options &instance types available across  resource types.

Prior to making instance & storage choices  define the parameters of what is required - What are the IOPS requirements for the application ? What are the memory requirements? Are there low latency requirements ?Is the workload CPU bound or memory bound?

Choose the DB Instance Class for the targeted workload. As a starting point read DB instance classes , which gives a great overview of the the Instance classes - covering general purpose, memory optimizes and burstable performance

For storage , broadly speaking there is SSD (general purpose SSD & Provisioned IOPS ) , & HDD . In this post we're looking at SSD

  • General Purpose SSD – General Purpose SSD volumes offer cost-effective storage that is ideal for a broad range of workloads running on medium-sized DB instances. General Purpose storage is best suited for development and testing environments. Wide range of use cases , low latency , cost-efficient , minimum ssd storage 20 GiB .When using SSD, you are charged for the amount of storage provisioned and not for the number of I/Os processed

 

  • Provisioned IOPS (PIOPS) SSD – Provisioned IOPS storage is targeted at  of I/O-intensive workloads, particularly database workloads, that require low I/O latency and consistent I/O throughput. Provisioned IOPS storage is best suited for production environments. Minimum 8000 IOPs , maximum 80000 IOPS (although SQL Server maximum is 40000 IOPS).  The charges for this option are based upon the amount of storage provisioned in addition to the IOPS throughput selected, again, you are charged not for the total number of I/Os processed.   Typically PIOPS is much more expensive than General Purpose 

        Often the question arises about the rationale behind Provisioned IOPS . The main reason gives is : Dedicated IOPS rate  is set at configuration time  and is provisioned for the lifetime of the RDS instance.   

You are strongly advised to test workloads - not just at a database server level but across the whole application

           

 How do I know if i have I/O problem? One thing you can do is check the DiskQueueDepth 

  1. If your DiskQueueDepth is consistsently between 0 and 0.5 you are over provisioned
  2. If your DiskQueueDepth is consistsently above 2 then you are under provisioned

To see how many IOPS are needed to have a steady performance, use the ReadIOPS and WriteIOPS metrics and sum up the values. Choose a descent time interval or a typical day from a performance point of view and also remove outliers. Compare this value with the IOPS you have provisioned.

Once you calculate how many IOPS are needed, then you have two ways to acquire them.

The first is to purchase PIOPS, which is more reliable but a lot more costly. The second is to use a gp2 disk for your RDS instance, which provides 3 IOPS per GB of storage.

    


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Amazon RDS DB Instance storage


dba-ninja.com