I'm running an Amazon Aurora PostgreSQL cluster with two instances — one for writing and the other for reading. Both instances are r6g.8xlarge, and we recently upgraded from r6g.4xlarge because our writer instance was consistently hitting 100% CPU, while the reader stayed below 10%. After the upgrade, the writer still runs at over 60% CPU while the reader is around 5%. The workload is mainly write-intensive, and I'm looking for suggestions to help: 1) Lower the CPU load on the writer, 2) Delegate more workload to the reader if possible, and 3) Optimize the Aurora setup to better manage this usage pattern. Has anyone faced similar issues or have strategies for balancing CPU usage between the writer and reader?
3 Answers
You might want to check your performance insights and CloudWatch database insights; they can highlight which queries or processes are hogging the CPU. Also, ensure that your read workloads are correctly tapping into the reader endpoints; that's crucial for balancing the load.
Just to add, Aurora won’t automatically direct read queries to the reader instance. You need to adjust your application to use both instances properly. Also, keep an eye on replication lag when doing this. And do check for any missing indexes or inefficient queries, they often lead to higher CPU usage.
True! I've run into similar problems before, usually a lack of indexing or too many N+1 queries make a big impact on performance.
Are you sure your application is connecting correctly to the reader? You need to explicitly direct some of the requests there instead of it all going to the writer. Look for the 'readonly' connection endpoint—making that switch might help reduce the writer's CPU load.
Exactly! Ensuring the connections are set up right is key. It's common for connections to default to the writer if not specified.
Good point! It’s easy to overlook endpoint settings, and it could make a big difference.