How should this issue be resolved?
Modify the application to commit transactions in batches
Add a new Aurora Replica to the Aurora DB cluster.
Add an Amazon ElastiCache for Redis cluster and change the application to write through.
Change the Aurora DB cluster storage to Provisioned IOPS (PIOPS).
Explanations:
Committing transactions in batches can significantly reduce the number of individual INSERT statements, thus reducing the frequency of the IOwait event. This can improve database performance, particularly during peak times.
Adding an Aurora Replica improves read scalability, but it will not address the underlying issue of slow write performance caused by single INSERT statements and the IOwait events.
Adding ElastiCache for Redis is more suited for caching and read-heavy operations. It does not address the issue related to slow writes and IOwaits caused by INSERT statements.
Changing Aurora storage to Provisioned IOPS (PIOPS) can improve IO throughput, but the main issue is the frequency of single INSERT statements, not storage performance. Batch processing would be a more effective solution.