AUTHOR: http://aws.typepad.com/aws/2006/09/amazon_ec2_mysq.html LINK!

Recent AWS Customer Success Stories & Videos

More AWS Customer Success Stories...

« Quick Links | Main | Jets3t Cockpit »

TrackBack

TrackBack URL for this entry:
http://www.typepad.com/services/trackback/6a00d8341c534853ef00d83431797953ef

Listed below are links to weblogs that reference Amazon EC2, MySQL, Amazon S3:

» Featured on BuzzTracker from BuzzTracker.com
[Read More]

Comments

Feed You can follow this conversation by subscribing to the comment feed for this post.

Tom Hume

There are 2 different considerations here, aren't there?

1. How do I ensure reliability so that my application can continue to see the database in the event of an instance failing?

2. How do I ensure I lose no data in the event of a failure.

I can see how this solves (2), but not (1)...

Jinesh

I am wondering whether we could use one of MySQL pluggable Storage Engines (Table Types) "S3 Storage Engine" as another alternative - http://dev.mysql.com/tech-resources/articles/mysql_5.0_psea1.html?

Any MySQL experts can validate this?

Colin Percival

As I've mentioned several times on the EC2 forum, writing transaction logs to S3 isn't good enough given the unpredictable propagation time of S3. When I start up a new database instance on EC2 and read the transaction logs, I want to know that I have _all_ of the transaction logs and that none of them are stuck in S3 waiting to propagate.

As I've also mentioned on the forum, if EC2 provided a "remember this sequence number for me" service, this problem could be avoided by persistently storing the number of the last transaction logged so that a new instance would know whether all the data had arrived through S3 yet.

Thorsten

Yes, by and large you are correct. But the devil is in the details, many of them!

For example, MyISAM tables do not replicate correctly because the replication (binary) log does not obey transactions. You can have a transaction that rolls back on the master but it's in the log and happily executed on the slave. Ouch!

Even if you use InnoDB tables, you are not safe. For example, you can write non-deterministic SQL statements that may produce different results on the slave than on the master. An example is creating a table with an auto-increment key using a select from another table. The keys assigned depend on the order in which the select produces results. This may be different on the slave than the master and you end up with different keys, which is not going to match subsequent operations! (A friend ran into this one, one slave matched the master while two others became inconsistent quickly, he spent hours and hours figuring out what went wrong!)

You also assume multiple EC2 instances in your description, but there is no control over their location. They may well be on the same power circuit that goes out. What about the S3 node? I forget the semantic details: when S3 ACKs the store request, does it guarantee that the data is replicated already or does it only guarantee that it is on persistent storage? In other words, could it be on a machine in the same datacenter on the same UPS that goes down with the EC2 mysql box and doesn't come back up until the UPS is repaired a few hours later? In that case, yes, the data would be safe on S3, but unavailable for a few hours. That wouldn't help me in quicly restoring from S3 onto a new mysql instance, would it?

Someone else also mentioned restore time on the EC2 forum. How long would it take to restore a 70GB database? (70GB is probably the max you can put on an EC2 instance if you ever want to be able to make a backup copy without jumping through hoops.) My guess is at least 10-15 minutes, and that doesn't count the time to apply the logs.

Oh, talking about logs, when do you start your logs? You need a clean full backup and then you can start the (incremental) replication logs. After a while, you really should start afresh so you don't collect endless logs that would take forever to apply. If your app is not 24/7 it's easy, but if it's 24x7 you need to do what's called a hot-backup. Ahhh, no such support in mysql/innodb unless you pay for it.

And performance? Did you know that a mysql slave can easily be slower than the master? The reason is that the slave reads and applies the replication log using a single thread. So while your app server bangs on your master with high concurrency, your slave performs one operation at a time. Usually this is not an issue because the master has the additional load of reads, but the problem does exist.

The bottom line is that in theory everything is available to set-up a nice mysql installation on EC2/S3, but in practice it's far from easy to actually pull it off in a reliable manner.

[Sorry for the long comment]

Tushar Jain

Also, MySQl replication is statement based, not data based. Thus you can't use the binary logs to back up to S3. To use S3 for back up, one one would have to periodically run queries to take diffs from your db and put that data into S3.

So consider something like the following:

1. Beginning of every week, take a full dump of your db and put that data into S3.
2. From then on, with whatever frequency you consider appropriate for your app, run queries to capture deltas and put those into S3.

This way it is possible to use S3 as a backup for you datastore. However, that is very distinct from a MySQL slave. A slave is meant to serve as a hot swap in. So if your master goes down, you should be able to switch to the slave.

None of this ofcourse guarentees 0 data loss, since there is a delay. Still it may work for certain types of applications.

mathie

Jinesh: That pretty much strikes me as the neatest solution. I have no idea if/now it would work in practice, though.

Basically, the overall problem is one thing: the application framework I use (Ruby on Rails) talks to an SQL backend. That's pretty well ingrained into it and so I want an SQL backend to talk to. Really, I don't care how it's implemented, and I don't want to have to worry about the fiddly details, and I don't want to go to the effort of *verifying* that it does in fact work.

Maybe there's a market there for somebody to take Jeff's suggestion, run with it and sell the resulting service to the rest of us who don't want to worry about the details?

Jeff Barr

A contributor named Navin sent me the following:

This would mostly work, except that there will be a lag (however small) between the time a transaction is committed into the MySQL instance, and it is copied by the slave "service'. The lag occurs because the MySQL instance is not aware of the slave, and so does not wait for it before returning from its commit. The above lag is not a problem if one is willing to lose some transactions (imagine the MySQL instance becoming unavailable after writing the last few records that have still not been copied by the slave), but certainly not acceptable where one is not allowed to lose any data (imagine if the last transaction was a credit of $1M to your account, and it is not there the next day!).

thanks
Navin

Robert Hostetter

The "real" solution seems very simple to me, EC2 should not throw away the hard drive instance when the server crashes, it should reboot the machine. It should also be possible to "mount" that drive in another EC2 should the instance be permanently corrupted and you need to pull the data off the original instance. This gives you the same level of uptime you have in any other data center, and if you can do it faster due to web services and faster up/down speed of bringing up new instances all the better. I think amazon found out that the biggest market for EC2 isn't in giant clusters doing mathematical calculations (although I do have a few ideas for using it for this) but instead in a redundant high speed, high bandwidth hosting service.

Paddy Sreenivasan

Thorsten's idea is similar to what Amanda developers have been working on. Our idea to use S3 as one of the backup media supported by Amanda. With S3 device plugin (http://wiki.zmanda.com/index.php/Device_API), Amanda can do full and incremental backups of
MySQL to S3.

The comments to this entry are closed.

Featured Events

The AWS Report


Brought to You By

Jeff Barr (@jeffbarr):



Jinesh Varia (@jinman):


Email Subscription

Enter your email address:

Delivered by FeedBurner

April 2014

Sun Mon Tue Wed Thu Fri Sat
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30