RDS or MySQL – Ten Use Cases

Amazon’s Relational Database Service is based on MySQL under the hood.  So many colleagues and clients ask me – should I go with RDS or MySQL? As with every technology question, the answer is – it depends.

Here are some scenarios to help you decide.

  1. I’m replicating into Amazon from a physical datacenter
  2. A: This setup is common if you’re using Amazon’s VPC or Virtual Private Cloud.  With a router dropped into your datacenter, VPC allows you to extend and spinup virtual instances from Amazon as if they’re sitting in your own existing datacenter.  Great stuff, but you won’t be able to replicate from your existing master MySQL instance to cloud Amazon RDS instances.  To do that, roll your own with MySQL 5.5 or Percona 5.5.  RDS can only work with other RDS instances.  At least for right now.

  3. I’m using Amazon and replicating to another cloud provider
  4. A: Again roll your own, as RDS won’t work with this configuration.

  5. I’m developer or small startup building a new application.
  6. A: Use RDS.  It’s easier to deploy, and though there are fewer levers and dials you can control, you can always dump your data and move it to roll your own deployment later.

  7. We’re a small shop without a fulltime DBA
  8. A: Use RDS as it is easier to deploy.  You’ll have a couple of replication options at your disposal.  There are read replicas which are basic slaves using MySQL’s built-in asyncronous replication.  You can scale horizontally with these read-only copies, but cannot failover to these instances.  For that you’ll need a multi-az configuration where you deploy across availability zones.  Multi-az is said to be syncronous so it’s likely built on top of a distributed filesystem such as DRBD.

    Keep in mind though there is no free lunch. RDS lacks the slow query log, which is the primary way you’ll identify errant queries, and fix them. You’ll need to be extra vigilant about QA & Test as code deploys add new SQL to your application. There *IS* a way to log slow queries to a table, which is ok for off and on use. However there’s quite some overhead to this feature being turned on all the time.

  9. We want to do master-master for easy failover and failback
  10. A: Use MySQL 5.5 or Percona 5.5.  The two configurations for RDS don’t support this.  With AWS Read Replicas you have one master and multiple read-only slaves, but no failover.  With Multi-AZ you can’t access the inactive secondary database *until* you failover.  Once you failover that instance becomes the primary.

  11. I’m concerned about clean data. Is replication bulletproof?
  12. A: No not out of the box.  Whether you are using RDS or MySQL there are various scenarios where MySQL slaves can drift out of sync from the master, without throwing an error. This likely impacts Read Replicas though it’s hard to say if it affects Multi-AZ.

    If you’re concerned about data integrity with MySQL replication (and you should be!) take a look at our Guide to MySQL Integrity Checking with Percona Toolkit.

  13. Our use case is to scale reads with multiple instances
  14. A: RDS can do this handily with Read Replicas.  As long as your requirements are for vanilla replicas, this will work well for you.

    To learn more about this checkout our Guide to Autoscaling MySQL on Amazon.

  15. I don’t want to be beholden to Amazon for my database
  16. A: Well then your choice is simpler.  Locking in with Amazon RDS means when you hit a bug, your hands are a bit tied.  Amazon’s DNA is as a DIY infrastructure provider, and although they’ve added support contracts to the mix, they’re not a Rackspace.  At least not yet.

    So there’s a tradeoff.  Go with a roll your own solution where you have control over all the nuts and bolts of your technology or go with something more managed.  RDS is a managed database solution, so to the extent things are automated, your hands are also tied when you hit a snag.

  17. We want to use an alternate MySQL distribution
  18. A: Again if you want to go with a Percona, MariaDB or Drizzle, you’re going to be using a roll your own distribution.

  19. We want to use an exotic replication topology

A: If you’re in this camp, you probably already know RDS isn’t going to support you.  MySQL’s replication technology can support a myriad of configurations. Here are a few that might work for you:

  • Master-master or Circular Replication
  • Distribution Master
  • Tree Replication Setup
  • Multi-source Replication
  • Logging Only Slave Server
  • Slaving off of a Slave