I'm currently using Aurora MySQL, but I have a use case that's pushing the limits of table sizes. Right now, I'm manually partitioning the tables, but I'm considering DSQL since it seems like a suitable alternative without needing the unsupported features of PostgreSQL. I've done some preliminary benchmarks using YCSB. My tests compared a single region DSQL cluster to an Aurora MySQL 3 db.r8g.8xlarge instance with I/O Optimization enabled. I expected the selects to be slow due to the lack of built-in caching, but I was surprised that simple inserts took 2-4 times longer and reads were 6-8 times slower. Updates were also slow, and I encountered several "change conflicts with another transaction" errors. The cost of DSQL during these tests was slightly lower than using two reserved db.r8g.8xlarge instances. I'm just curious to see if others have had similar experiences with DSQL performance.
3 Answers
Performance can really vary depending on your schema and usage patterns. It would help if you shared more details! Just a heads-up, though: when comparing a distributed system like DSQL with Aurora MySQL, DSQL might be slower due to network quorum requirements that MySQL doesn't have. It's a trade-off for better scalability with DSQL.
I work with DSQL, and if you share your cluster ID and region, I'd be happy to take a look—it's not private info! One thing to consider is whether you're running your benchmarks long enough. We typically see better YCSB results with longer test durations.
Not directly related to YCSB, but Mark has written about his experiences testing with TPC-B, which you might find interesting: [https://marc-bowes.com/dsql-tcpb.html](https://marc-bowes.com/dsql-tcpb.html).
Make sure you understand the different concurrency models before making a switch. They can impact performance and behavior significantly.
Yeah, I checked the concurrency control documentation and it seems that's what's affecting the updates in my benchmarks. It might not be as much of an issue with my actual use case.
Great point! My benchmark was with a super simple schema, which I thought would be a best-case scenario. I created a usertable with two text fields. Do you think using text fields might be affecting performance? My actual use case is more complex with around 20 columns, including DATETIME, floats, and various integer sizes. The row size is similar, but I also have a primary key and five secondary indexes.