Our experience using Amazon's RDS managed database

Amazon AWS the global leading cloud platform of the world.

Amazon is the global leader in the cloud computing sphere powering the computing needs of many of the world's largest companies. At Kreloses we make use of all of Amazon's Relational Database Service's (RDS) availability and reliability services, including multiple concurrent copies of our databases in separate availability regions, daily automated scheduled snapshots, manual snapshots and private key encrypted off-site backups, ensuring we have absolute confidence in our data's availability, resilience and security. So how did we arrive at our decision to use Amazon's RDS for our database needs?

As a DBA/SQL Developer for well over 25 years, with some 15 years working for Lehman Brothers and Bank Of America Merrill Lynch in Tokyo, my number one recurring nightmare has always revolved around database backups not being usable when push came to shove and a production database needed to be restored from backups. Database performing poorly, great, a chance to get in fix the issue and get a pat on the back, a database suddenly not available, also perhaps a challenge but not generally fatal, but carefully taken database backups not being able to be restored, a nightmare. No matter how well backups were tested by loading to test database servers there still remained that nagging feeling that when they were really needed for production restores they either would not load or would prove to be corrupted in some shape or form. So my concerns as a DBA have always been, in order, availability (including backup reliability), security and performance. So whenever I moved into a new DBA position with a new company I would first make sure that these bases were all covered and covered well.

My previous DBA work has been almost exclusively with Sybase during its various incarnations from Version 4.2 and, although I had worked a little with Oracle and MS SqlServer, I had always been and still am a Sybase devotee. However, for various reasons, Sybase was not an option for Kreloses so it was time to get to grips with a new product. Strategically, the company had decided that all our production hosting would be cloud provided and Amazon's Amazon Web Services (AWS) would be the provider. AWS had been providing RDS using Mysql since 2009 and this would factor into our decision as to what database system we were going to use. Although we looked at a few options, free and otherwise, our evaluations decided that Mysql was a reliable, well-proven product which met all our requirements, offered excellent performance, had a wide established base of existing users and, importantly, gave us the option of using RDS if we so chose.

So the only real decision to be made was would we run Mysql with full control on an AWS linux instance or accept the trade-off of loosing some control, as you do with RDS, and run a RDS Mysql instance with the administrative benefits that it offers. I had been used to having full control over every database server I had ever managed and so the idea of not having the same level of control I was used to did not exactly fill me with enthusiasm. With RDS you do loose control, such as there being no 'SUPER' user, and moreover there are some parameters that you have no control over and you have no access to the binary logs. Memory usage is set for you based on the class of RDS instance you run and, although you do have control over a lot of parameters, you need to use Parameter Groups to do so. However, my experience has always been that once you get the amount of memory for the database server correct, and RDS optimises and controls this for you, that (this obviously varies depending on the database platform) greater than 90% of performance issues always come down to poorly written SQL or poor indexing. So loosing control over a few parameters etc did overly concern me. What you do gain by using an RDS instance instead of running your own instance is that most of the potentially nightmare causing administrative tasks are reliably taken care of for you. Whether using the AWS GUI console or command line utilities it is very easy to spin up a Mysql RDS instance which can be later be scaled up and down to a different class with a couple of clicks of the mouse and which is optimised for the hardware it runs on and therefore typically produces better performance than Mysql running on an EC2 instance.

What about my nightmares? Take backups for example. RDS takes care of this via automatic snapshots which are taken for you during a nominated window once a day allowing, with the RDS controlled binary logs, restores to another RDS instance up to a nominated time. Sure beats setting up scripts to do backups, to verify the backups and to do potential restores. Then similarly you can take manual snapshots any time you want as well which can be shared with other AWS accounts so for example you can have a production AWS account and a backup AWS account and can share the production database snapshots with your backup account and restore them to a new RDS instance and run some custom validation checks to remove any lingering doubts you might have about the snapshots being unusable. Restoring from a snapshot means restoring the whole snapshot (to a new RDS instance) which of course takes time so for those 'oops I deleted some data can you get it back' occasions you can still take logical backups with mysqldump etc.

What about availability? Well with the click of a button you can convert your single Availability Zone (AZ) RDS instance to a Multi Availability Zone instance where RDS synchronously physically replicates data to a standby and automatically will fail over to the standby if the primary becomes unavailable. In the case of a fail-over there is bit of delay before the standby takes over but it is all handled internally for you and so you need to do nothing. Moreover with Multi-AZ , automatic and manual snapshots are taken from the standby so the impact on the primary is slight and maintenance requiring restarts of the instances mean less impact on a running system as the standby and primary are maintained in turn and so one side is always available. Finding that your database reads, perhaps from reports, are impacting the database excessively? Then with another couple of clicks you can spin up a read replicate to handle your reads and reports.

RDS Mysql has been around since 2009 and has matured into a very performant and reliable product and the ease of creating instances, snapshoting them, creating Multi AZ instances and read replicates more than makes up for the small amount of control you loose over running Mysql in an AWS EC2 instance and more than made up our minds for us. So by the company using a Multi-AZ RDS instance with its automatic snapshots together with verified manual snapshots and verified off-site encrypted logical backups we have absolute confidence in the availability, resilience and consistency of our customer's data. The fact that we can easily upgrade the RDS instance to better class with more memory as our customer's needs increase, a feature we have already used, and the fact that we have an almost effortless upgrade path to move to AWS's Aurora if we decide, was the icing on the cake. Basically allowing RDS to take care of the tedious but critical administrative tasks means not only a better sleep for the team but frees us up to spend more time on ensuring the performance of the database parts of our applications are running optimally which is where the fun part lies.