136

I realize a couple of basic differences between the two, i.e.

  1. EC2 is going to be cheaper

  2. RDS I wouldn't have to do maintenance

Other than those two, are there any advantages to running my database from RDS as opposed to a separate EC2 server acting as a MySQL server. Assuming similar instance sizes, are both going to run into the same limitations in terms of being able to handle a load?

To give you a little bit more info about my use, I've got a database, nothing too big or anything (biggest table 1 million rows), just high SELECT volume.

franzlorenzon
  • 5,845
  • 6
  • 36
  • 58
Macgyver
  • 1,831
  • 2
  • 16
  • 19
  • There's a cross-site duplicate at http://dba.stackexchange.com/questions/34525/amazon-rds-for-mysql-vs-installing-mysql-on-an-amazon-ec2-instance that got some good answers. – Mark Amery Mar 12 '15 at 09:36
  • Just adding on ec2 consistent backup method. http://alestic.com/2009/09/ec2-consistent-snapshot I use that tool with a 300gb server and around 5,000 databases. At this time with a 3000 IOPS volume it takes around 1.2 hours to start mysql as it starts from a unclean shutdown so mysql has to scan every table. – jozwikjp Aug 24 '14 at 15:20

8 Answers8

137

This is a simple question with a very complicated answer!

In short: EC2 will provide maximum performance if you go with a RAID0 EBS. Doing RAID0 EBS requires a pretty significant amount of maintenance overhead, for example:

http://alestic.com/2009/06/ec2-ebs-raid

http://alestic.com/2009/09/ec2-consistent-snapshot

EC2 without RAID0 EBS will provide crappy I/O performance, thus it's not even really an option.

RDS will provide very good (though not maximum) performance out of the box. The management console is fantastic and it's easy to upgrade instances. High availability and read only slaves are a click away. It's REALLY awesome.

Short answer: Go with RDS. Still on the fence? Go with RDS!!! if you enjoy headaches and tuning every last little bit for maximum performance, then you can consider EC2 + EBS RAID 0. Vanilla EC2 is a terrible option for MySQL hosting.

efalcao
  • 5,106
  • 1
  • 24
  • 19
  • 1
    Good answer. This is EXACTLY what I want: http://aws.typepad.com/aws/2010/10/amazon-rds-announcing-read-replicas.html - Thanks for leading me in the right direction – Macgyver Jan 26 '11 at 17:07
  • Good answer. How do you handle the 4 hour per week downtime? – Tihom Jan 26 '11 at 20:29
  • 9
    One thing that's important to know about the 4 hour maintenance window is that your server is not down for 4 hours a week! That's just when they'll do maintenance IF there is maintenance to be done. I've had RDS servers running for months and months with zero downtime. – efalcao Jan 28 '11 at 17:14
  • 2
    We've had RDS servers running for YEARS with zero downtime. One major outage (about 6 hours) once, everything returned to normal once AWS sorted itself out. (I should point out this was a multi-AZ instance but it failed to fail over to the backup). – cjm2671 Mar 13 '13 at 15:33
  • You know about RDS DNS Failover and Shared Database Security issues right? And their downtimes. – BigSack Aug 20 '13 at 06:14
  • 1
    These questions should be time-sensitive. Many of these answers and the benchmarks are obsolete. New m3 and i2 instances have SSD drives which kill EBS and RDS (essentially EBS with more optimized latency infrastructure). PLUS - you can now run read-replicas out of cloud to roll your own hardware for super-nasty SSD replicas. In short - we use multi-az RDS as primary to ease backups, restore etc, then do replica backups for offsite, then use Route53 with short TTL just in case RDS failover is slow on the DNS side (or worse) so we can elect our own new masters. Things have changed. – Ross May 05 '14 at 00:48
  • @Ross So, if you manually failover to your offsite replica backup, does RDS still restart the failed instance and re-elect it to master? I'm not sure if you bypass RDS failover completely or use it in parallel with your custom failover. – paulkon Jun 25 '14 at 04:59
  • 1
    @paulkon - we don't failover to offsite replicas, we use the RDS failover, otherwise promotion etc back to new master would become tricky. The offsite replicas mainly are for out-of-cloud backup DR as well as for read/write splitting for reporting environments (our application level is aware of them). HTH – Ross Jun 26 '14 at 10:53
  • We've been using RDS, only to realize our bills went 4 fold. One needs to note that it is an expensive service. – Santosh Achari Oct 03 '14 at 08:00
26

In this post there is an excellent benchmark between:

  • Running MySql on a Small EC2 + EBS
  • Running MySql on a Small EC2 + EBS + adjusted MySql parameters
  • A Small RDS

The benchmark is very good since it is not focused only in ideal conditions (only one thread) but also in more realistic scenarios, with 50 threads hitting the database.

Andres
  • 1,484
  • 19
  • 29
  • 2
    It's nice to publish benchmarks, but in good faith the author admitted at the end he did not properly tune Innodb (The big one param to change is of course innodb_buffer_pool_size.... which he did not) – phil_w Jun 12 '13 at 16:18
14

RDS is not really a high availability system. Read the fine print in the RDS faq. During a failover event it can take up to 3 minutes to failover. Additional amazon will decide it needs to "upgrade" your rds instance and do a failover at that point which will take your database down for "up to 3 minutes" (our experience is that it can take a longer than that).

RDS high availability is very different than master - master or master - slave replication and is much slower. They don't use mysql replication but uses some kind of ebs replication. So in a failover situation it will mount the ebs on the backup machine, start mysql, wait for mysql to do failure recover (hopefully nothing got corrupted too bad), then do a dns switch.

I hope this helps you with you evaluation.

Jeff Whiting
  • 432
  • 4
  • 5
  • 1
    adding a read slave on a db with 40gb data took over 20min for me. This and the cost and lack of read-slaves in regular ec2 and/or offsite being unavailable is pretty much a non-starter for me. I'd say RDS is good for small shops not needing true high-availability and failover response times. Its more to eliminate a DBA than anything IMHO. – Ross Apr 16 '12 at 23:14
  • Good news here (Mar 2020). With Aurora things have gotten a lot better. It still doesn't run a master - slave system but because they've created a new "cloud based" storage system failover is now very quick. Also it provides fast snapshots and backups. Aurora has really addressed many of the shortcomings of RDS MySQL. – Jeff Whiting Mar 24 '20 at 16:49
7

We chose to use EC2 MySQL instances because we have a high read volume and need master-slave replication. Of course, you can spin up multiple RDS instances and setup MySQL replication between them yourself, but we use Scalr.net, which manages that for you using EC2 instances.

Basically, we just tell Scalr how many MySQL instances we want at it keeps them up, automates the setup of replication, handles automatic failover of slave promotion to master if the master gets terminated etc. It does both SQL dump backups and EBS volume snapshots of the master. So, when it needs to create a new slave, it automatically temporarily mounts an EBS volume of the last master snapshot to initialize the slave DB, then starts replication from the appropriate point. All point and click :) (and no, I don't work for Scalr or anything. Scalr is available as Open Source if you don't want to use their service)

DavidJ
  • 4,369
  • 4
  • 26
  • 42
  • Note that since I posted the above answer, Amazon has introduced explicit read-replica support for RDS instances (currently MySQL only). – DavidJ Jan 31 '14 at 22:40
5

Regarding the maintenance window question. If you use Multi-AZ then RDS will create a standby replica in another availability zone so that there's no down time for maintenance and you protect yourself against a zone failure.

That's what I'm planning to do in the next week or so. Of course it's going to cost you more but I haven't worked that bit out yet.

Shin
  • 181
  • 2
  • 5
4

MySQL on EC2 vs RDS MySQL

Advantages of MySQL on EC2 Amazon EC2 Inter Region Replication

Copy Snapshots across Amazon EC2 regions

RAID 0 with EBS Striping in MySQL EC2

More than 3TB of Disk space ( You will not need this for your size) can be attached on MySQL on EC2.

Disadvantages of MySQL on EC2

Configuration, Monitoring and Maintenance compared to RDS

Point in time backups available in RDS

IOPS lesser than RDS MySQL ( even after RAID 0) currently, 10800 with 6 disks for MySQL on EC2 whereas 12500 IOPS 16KB on RDS MySQL

Harish Ganesan
  • 743
  • 5
  • 4
4

I have been trying out RDS for a few months and here are some issues I have:

  1. Using SQL profiler is tricky. Since you cannot connect profiler directly to the server, you have to run some stored procedures to create a log file that you can analyze. While they offer some suggestions about how that is done, it is far from user friendly. I would only recommend that you have a certified SQL professional do this kind of work.

  2. while Amazon backs up your instance, you cannot restore an individual database. I have a web app with several separate customer-specific databases and my solution was to launch an EC2 instance with SQL running on it to attach to the production RDB database and import the data and then back it up on the EC2 instance. The other solution was to use a 3rd party tool that creates a massive SQL script (on the app server) that will recreate the schema and populate the data back to a restore point.

Eccountable
  • 622
  • 3
  • 13
1

I had the same question this weekend. There is a 4 hour downtime window per week for RDS where they do maintenance. RDS seemed more expensive if you can get away with a micro instance of EC2. (This is true of test instances which has minimum traffic) I also wasn't able to change the timezone of the RDS instance because I dont have permission.

I am now actually looking at http://xeround.com/ which is mysql on EC2 by another company. They do not use InnoDB, instead they have their own engine called IDG. I am just starting to investigate that but they are in BETA and will give 500MB of space.

Tihom
  • 3,384
  • 6
  • 36
  • 47
  • Note that the maintenance window is not downtime every week; it's just the time when any maintenance would be done if it were needed: http://aws.amazon.com/rds/faqs/#12 Also see @efalcao's comment on his answer above. – mpdaugherty Jul 04 '11 at 07:20
  • Looks really cool but really $$ if you have any amount of data at xeround.com – csharp4me Aug 11 '11 at 17:20