How do you solve the contention issues between these different workloads on the same data?
Enable Multi-AZ mode on the RDS instance
Use ElastiCache to offload the analytics job data
Create RDS Read-Replicas for the analytics work
Run the RDS instance on the largest size possible
Explanations:
Enabling Multi-AZ mode provides high availability and failover support, but it does not address contention between analytics and other workloads. It will not reduce the impact of heavy analytics jobs on the primary workload.
ElastiCache is typically used for caching frequently accessed data to speed up reads, but it does not offload analytics jobs. Analytics often require complex queries that need to be executed on the database itself, not just cached data.
Creating RDS Read-Replicas is a good solution to separate read-heavy analytics workloads from transactional workloads. Analytics queries can be directed to the read replicas, which prevents them from impacting the performance of the primary instance.
Running the RDS instance on the largest size possible may improve performance to an extent, but it does not resolve contention between different types of workloads. This only postpones the issue as the analytics jobs grow in size and complexity.
If I had to guess, I’d say the answer is:
Create RDS Read-Replicas for the analytics work