I'm having some performance issues with my Amazon Aurora PostgreSQL setup. We have a cluster with a writer and a reader instance, both are r6g.8xlarge, and I noticed that the writer instance frequently hits 100% CPU load, while the reader stays under 10%. We had previously upgraded from r6g.4xlarge due to similar issues, but the situation hasn't improved. The writer often sits at over 60% CPU usage while the reader rarely crosses 5%. Since our workload is mostly write-intensive, I'm looking for strategies to:
- Lower the CPU load on the writer,
- Offload more queries to the reader if possible,
- Optimize how Aurora handles this scaling and architecture.
Has anyone faced a similar situation and found effective solutions to balance CPU usage between the writer and reader?
4 Answers
I found these links useful for reference: AWS Database Products, RDS, DynamoDB, and Aurora documentation. They might give you some insights or optimization tips that you haven't considered. Definitely worth checking out!
Remember that Aurora doesn't automatically direct read queries to the reader instance. Make sure your application is specifically configured to use the reader endpoint, typically labeled as 'readonly'. This might be the issue if all your connections are hitting the writer instead.
In addition to the connection setup, consider looking into query performance. If you're not tracking which queries are the most demanding on your resources, it might lead you to unnecessary worrying about the reader. Enabling performance insights could save you a lot of trouble. Missing indexes or inefficient queries (like N+1 queries) can be the root cause of issues like this.
You might want to check out Performance Insights and CloudWatch DB Insights to pinpoint exactly what's consuming your CPU resources. It’s crucial to verify that your read queries are actually going to the reader endpoints and not the writer.
Good call! If you don't have your application properly set to communicate with the reader, it could be sending everything to the writer, causing those spikes.
Totally agree, I had a similar problem and that was exactly what was wrong on my end.