We have a large MySQL database on AWS RDS, with a huge table around ~1.8TB with massive write workload. We recently hit a write throughput bottleneck, which caps our provisioned IOPS way below the nominal value. We can't upgrade our instance further, so now we have to shard or optimize our write operations. Sharding is not an option now.
- Most of the workload is on a single large table.
- The table has a few dozen columns with small data types. No huge text or blob fields.
- Lots of indexes, several of them composite indexes with 2-3 columns.
- Around a dozen million new rows are updated everyday, then another dozen million new rows inserted.
- Updates are not sequential.
- It's trivial to change our application to perform only updates or only inserts for while, and avoid and inserting and updating simultaneously, if that makes any difference.
We already optimized the aplication and the table schema and indexes as much as we could to reduce writes, but it's not enough. Are there any other practices I can adopt to further optimize this database write throughput?