A warning about Aurora: It's opaque tech. I've been on a project that switched to it by recommendation by the hosting provider, and had to switch away because it turns out that it does not support queries requiring temporary storage, i.e. queries exceeding the memory of the instances.
It manifested the way that the Aurora instances would use up their available (meagre) memory, then start thrashing, taking everything down. Apparently the instances did not have access to any temporary local storage. There was no way to fix that, and it took some time to understand. After having read all the little material I could find on Aurora, my personal conclusion is that Aurora is perhaps best thought of as a big hack. I think it's likely there are more gotchas like that.
We moved the database back to a simple VM on SSD, and Postgres handled everything just fine.
We’ve generally been happy with Aurora, but we run into gotchas every so often that don’t seem to be documented anywhere and it’s very annoying.
Example: in normal MySQL, “RENAME TABLE x TO old_x, new_x TO x;” allows for atomically swapping out a table.
But since we moved to Aurora MySQL, we very occasionally get stuff land in the bug tracker with “table x does not exist”, suggesting this is not atomic in Aurora.
Is this documented anywhere? Not that I’ve been able to find. I’m fine with there being subtle differences, especially considering the crazy stuff they’re doing with the storage layer, but if you’re gonna sell it as “MySQL compatible” then please at least tell me the exceptions.
I believe this issue is (or was) real. There are important differences in how Aurora treats temporary data. Normal postgres and rds postgres write it into the main data volume (unless configured otherwise). Aurora however always separates shared storage from local storage and it's not entirely clear to me what is this local storage physically for non-read-optimized instance types. The only way to increase it is to increase the instance size. [1][2] This is indeed frustrating because with postgres or rds postgres you just increase the volume and that's it.
Luckily since November 2023 it also has r6gd/r6id classes with local NVMEs for temp files. [3] This should in theory solve this problem but I haven't tried it yet.
I think Aurora has to go through the same development process as every database. They changed essential patterns in the database, and there are severe side effects that need to be addressed. You can see the same with Aurora Serverless and the changes in V2; there were some quite quirky issues in the first versions.
Calling it a hack is pretty unfair. The log storage engine is a huge innovation, in my experience makes large MySQL/pg clusters much more reliable and performant at scale in a variety of different ways.
It has a couple of quirks, but on balance it feels like the future - the next evolution of what traditional rdbms are capable of.
But if you don’t have scale or resiliency needs it probably doesn’t matter to you.
Isn't Aurora mainly about their unique handling of logging and replica which leads to high availability and fast recovery? If you switch to VM, how do you handle availability in multiple locations and backups? If database checkpoints are good enough for you, sounds like Aurora is overkill in the first place.
I’m going to preface this with I’m not an OPS person. But yes multi region. The main writer instance is us-east-1 it performs excellently when hitting this region. We have read replicas in us-west-2 and some in Europe/emea and Asia/pacific.
When hitting one of these with a write you end up with massive delays. The 7 seconds and below tends to be from us-west-2 and the higher numbers are from our Japanese users.
Our OPS team has struggled to figure out why the delays happen. There’s some code fixes we could probably do (i.e always write to the writer) but as team lead for the development side the deadline is too close and I don’t want to rewrite core parts of the app to split reads and writes. They engaged AWS support so I’m hoping something is just misconfigured or maybe this just isn’t the use case for Aurora.
Oh. Yeah the log file system and thus the consistent replica latency are local to a region.
It sounds like you might be using global aurora with write forwarding? That’s pretty new and not something I have experience sorry. AFAIU though it’s a whole different thing under the hood.
> It sounds like you might be using global aurora with write forwarding
Yes I believe this is what they chose. Honestly I’m going to leave it up to them and aws support. I have other fish to fry to get the functionality finished.
It manifested the way that the Aurora instances would use up their available (meagre) memory, then start thrashing, taking everything down. Apparently the instances did not have access to any temporary local storage. There was no way to fix that, and it took some time to understand. After having read all the little material I could find on Aurora, my personal conclusion is that Aurora is perhaps best thought of as a big hack. I think it's likely there are more gotchas like that.
We moved the database back to a simple VM on SSD, and Postgres handled everything just fine.