AWS Databases re:Invent 2019 WISH LIST

It is that time of the year when all AWS fans dream about new features. I love Aurora and run some big databases in it. And yes I sometimes run into trouble and then I read more and learn more from some smart people because I just didn’t realize there is one more thing I need to do to make my system more resilient. But still there are some thing that need to be improved on AWS side and I hope AWS will find the way to get things done.

publish known bugs list

whenever new Aurora update is released you can read something like this:

Fixed a bug in binlog replication that can cause an issue on Aurora instances configured as binlog slave.
Fixed an error in handling certain kinds of ALTER TABLE commands.

what bug? what ALTER TABLE command? why didn’t we know about that bug before? I wrote about this in relation to replication error in https://madabout.cloud/2019/04/26/binary-replication-error-aws-aurora-5-6-v-1-19-0/ If you have a problem that you can’t fix for months please let us know about it so we don’t upgrade or at least prepare us for the issues so we can handle it. And if you fix something, please provide us with exact details of what did you just fix and how we can replicate the problem on previous versions.

Aurora Mysql 8.x support

I hope this is something we don’t need to ask and that it is already in some internal preview. We need NOWAIT and SKIP LOCKED. When I talked to Aurora manager at re:invent 2017 about 5.7 it looked like he didn’t realize that JSON support and other 5.7 stuff is relevant at all. No, we can’t stay at 5.6 anymore. There are some bugs in 5.6 that you just can’t fix (XA PREPARE state …). Also, we need urgent attention on upgrading from 5.6 to 5.7. I can’t upgrade some large databases for two years (Backlog enabled).

fix XA Transactions issues

XA transactions locked in PREPARE state can hurt you a lot. Yes, you should STOP using XA transactions NOW. But if you can’t and you are stuck with MQ and DB XA transactions make sure you have Binary log replica enabled because you may not be able to recover your cluster if it needs restart. Make sure you commit or rollback transactions left in PREPARE state as often as you can (I check that every 10min) as you may end up with snapshots that you can’t restore too. AWS, please add support to Performance insights and CloudWatch to monitor XA transactions left in PREPARE state.

enable sys schema in MySQL Aurora

ok, tell me how to get UNUSED INDEXES in Aurora Mysql. It should be easy in MySQL: select* fromsys.schema_unused_indexes;

allow upgrade to Aurora Mysql 5.7 after backtrack is enabled

I’m stuck with 2 TB aurora 5.6 database which can’t be upgraded to 5.7 because I enabled Backtrack once. Disabling Backtrack doesn’t help. And yes, Backtrack is useless if you can’t start the cluster due to XA issues or some storage layer corruption.

support SCT in DMS init phase

if I use Data Migration Service I usually use it to connect two different types of databases. For example, I want to replicate MySQL to RedShift. Please let me use Schema Conversion Tool to define optimal data types and table definition in target database BEFORE data starts flowing because I can’t set SORT column later.

enable DATA API using CDK/CloudFormation

it is now possible to enable DATA API while creating Aurora Serverless database using Web Console but it is not possible to do that in CDK and CloudFormation. Please enable it so we can completely automatize creating and destroying entire Serverless/DataAPI environment.

make aurora serverless start FASTER

30-60 seconds to start a database is too much. This makes Serveless databases unusable for any user interaction based applications. Cut it to 3-5 sec and we are fine. At least extend timeouts for for DATA API to be higher than start-up time. Read this for more details: https://madabout.cloud/2019/09/01/aws-data-api-for-amazon-aurora-serverless/

add managed pgBouncer and ProxySQL services

now that we have Proxy handling connections to Serverless Aurora it is clear that AWS invested time to help us handling MANY connections from Lambdas to Serverless MySQL or PostgreSQL and created DATA API so it can rescue us. Guess what, we have millions of line of code written using JDBC or similar binary protocols. Please allow us to go to your proxy directly via binary protocols so we don’t have to create pgBouncer or ProxySQL dockers in ECS/Fargate.

shrink storage after deleting data

if you are not aware, please note that Aurora does not shrink storage usage after you delete your data, drop table or database. It is happening at storage layer and there is no way to get that space back. You can use deleted space but you will always be charged the maximum storage you’ve ever reached. So, AWS, please allow us to stop the database and shrink it or make some automatic background process that will do it by itself.

… and if you read all this please let me know if I should add some of your problems here.