Mark BurgessOracle RDS on AWS provides an easy way to implement and run Oracle Database in the cloud. Deploying Oracle RDS is relatively simple – it can also be a major contributor to cloud cost blowouts. This article discusses how to achieve AWS cost savings for Oracle by optimising your database workload and AWS configuration.
Generally speaking – we come across two types of scenarios for “problem” Oracle RDS deployments:
- Lift and shift of an existing poorly performing Oracle database.
- New implementation of custom or packaged application that has not been optimised for data growth.
The initial response to poor performance for the above two scenarios is typically:
- Increase the IOPS for the RDS instance EBS volumes.
- Increase the instance size to add more memory and CPU.
One of the big selling points of going to the cloud is that these things are easy to do – and they are. It is also just as easy to you set yourself on a path for ever increasing RDS costs as your database grows. Increasing your cloud service provisioning to fix poor performance locks you into a never ending cycle of adding more IOPS and CPU/memory as data volumes or workload increases.
As the data volumes grow and workloads increase, two things happen to increase the Oracle RDS cost:
- The size of the EBS volumes increase – this cost is multiplied by the number of provisioned IOPS on the RDS block volumes.
- The replica copy in a multi-AZ configuration also increases as well (lets assume here that you are running multi-AZ for production databases).
Before long you are in a position where your Oracle RDS costs are increasing faster than your databases are growing (EBS size + provisioned IOPS + replica copy + non-prod copies etc).
How to save on AWS costs?
Well optimised database workloads are even more important in the cloud. Poorly optimised Oracle RDS will have a direct impact on your cloud service bills.
Let’s start with a few things to look at from the Oracle RDS side:
- Check the SQL workload and execution plans. The usual Oracle optimisation principles apply. For Oracle RDS we are looking for statements that perform much higher physical IO than what would reasonably be expected. Missing or incorrect indexing, empty interface tables that have full scans performed, updating many rows in a table where a CTAS could be used etc.
- Check whether you really need the SQL Tuning Advisor and Segment Space Advisor auto jobs to run. Both can generate additional workload for no benefit if you don’t use the advisor outputs. You want to leave the auto Gather Stats job running – but avoid doing a full gather stats on the schema/database “just because”.
- Check if there are segments or table spaces that have “large” amounts of unused space. “Large” is going to be very subjective here – but we are looking for space in the database that has been allocated but not used.
You can probably see where we are going with this. Optimising the IOPS consumed by the Oracle RDS instance and using correctly sized block storage ensures that we are not paying for wasted IOPS or unused space. This AWS cost saving is multiplied if you have multi-AZ configured and also require matching non-prod environments.
By this stage you should have “right sized” your AWS Oracle RDS storage configuration.
Rightsizing Oracle RDS
If you have been going down the path of increasing the provisioned IOPS and instance size to solve your performance problems, then it might be time to rightsize the instance to match the optimised workload profile.
In cases where SQL statements have been optimised to use indexes compared to full scans, you can reduce the RDS instance size and provisioned IOPS. For workloads that are more full scan based, you can reduce instance size but increase IOPS. For instances that had a large amount of unused space you can reduce the block storage size to what is required – and potentially reduce the IOPS required as well.
The end result of these changes is an instance that is correctly matched to the required database workload.
From this you will find that:
- The database will scale with additional workload much better. In general, you will have more headroom on the instance for additional workload, and more consistent response time.
- Cloud cost management and forecasting is much easier to do as you are not having to accommodate for “waste” workload or inconsistent query response times.
The process of optimising the database workload and configuring the Oracle RDS instance and storage to match that workload puts you in a position to lock in serious cost savings for your Oracle RDS instance.
Locking in Cost Savings for the Long Term
Well optimised Oracle RDS performance is predictable. This enables you to accurately understand your workload profile and what attributes (CPU, memory, storage) of the RDS instance types are needed to service your workload.
This opens up two areas that can be looked at to really drive additional cost savings:
- Consider running your Oracle RDS workload on a smaller M class instance instead of a T class instance for a similar cost. M class instance may offer more consistent response time for your database workloads = more predictable sizing for your instance.
- Configure reserved instances for your database servers. This can offer substantial cost savings when multiplied out across multi-AZ and non-prod environments.
As you will see we have gone from accommodating waste database workloads with over-provisioned cloud services to having right sized cloud instances with predictable performance that can be locked in over the long term.
FAQ
How can one optimize SQL workloads to improve Oracle RDS performance and cost-efficiency on AWS?
Optimizing SQL workloads involves identifying and modifying inefficient SQL queries that consume excessive CPU and I/O resources. This can be achieved by using AWS RDS performance insights and Oracle’s own AWR (Automatic Workload Repository) reports to pinpoint high-load queries. Once identified, these queries can be optimised by rewriting them, creating or adjusting indexes, and considering the use of Oracle’s SQL Plan Management to ensure stable execution plans. Effective SQL optimisation reduces the computational demands on the database, allowing for potential downscaling of the RDS instance type and consequently lowering costs.
What strategies should be considered when reviewing auto jobs in Oracle RDS on AWS for cost savings?
Reviewing and optimising auto jobs involves assessing the necessity and efficiency of scheduled tasks such as backups, statistics gathering, and application-specific jobs. Strategies include scheduling jobs during off-peak hours to minimize impact on performance and potentially allowing for the use of lower-capacity RDS instances. It’s also important to evaluate the frequency of jobs to ensure they’re run only as often as necessary. For example, less critical data might not need to be backed up as frequently, or statistics on stable tables might not need to be gathered after every minor change. Streamlining these jobs can lead to more efficient use of resources and cost savings.
How does configuring reserved instances for Oracle RDS on AWS lock in cost savings over the long term?
Configuring reserved instances (RIs) for Oracle RDS on AWS involves committing to a one or three-year term for a specific instance type in exchange for a significant discount compared to on-demand instance pricing. This long-term commitment allows organisations to lock in lower costs, which can be particularly beneficial for stable workloads with predictable resource needs. When choosing RIs, it’s crucial to accurately assess your database’s resource requirements to select the appropriate instance size and term length. This assessment should take into account not only current needs but also anticipated growth or changes in workload. Additionally, AWS offers Convertible RIs, which provide some flexibility to change the RI’s attributes if your needs change over the RI term, albeit with a slightly lower discount rate than standard RIs.
Looking for help to reduce your AWS costs with Oracle RDS? Book a complimentary workshop today to discuss further.
