Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

MySQL in the cloud at Airbnb

nerds.airbnb.com

Post reply on HN
Print view View original

11–20 of 22 posts

Re: MySQL in the cloud at Airbnb

#11

Post by spudlyo »

dan_manges wrote: post #7

Does anybody how much Multi-AZ synchronous replication affects performance? I searched, but I couldn't find any benchmarks. Amazon's FAQ[1] says "You may observe elevated latencies relative to a standard DB Instance deployment in a single Availability Zone as a result of the synchronous data replication performed on your behalf" but they don't quantify it. [1] http://aws.amazon.com/rds/faqs/#39

Since the hot standby can't serve read requests and is synchronous, I assume something like DRBD is being used on the block level to replicate the data to a separate AZ. I assume AMZN has adequate interconnect bandwidth between their AZs. Gigabit Ethernet has around 100-200μs RTT latency, and then there is the fact that you're writing the data twice...

Re: MySQL in the cloud at Airbnb

#12

Post by [deleted] »

dan_manges wrote: post #7

Does anybody how much Multi-AZ synchronous replication affects performance? I searched, but I couldn't find any benchmarks. Amazon's FAQ[1] says "You may observe elevated latencies relative to a standard DB Instance deployment in a single Availability Zone as a result of the synchronous data replication performed on your behalf" but they don't quantify it. [1] http://aws.amazon.com/rds/faqs/#39

[deleted]

Re: MySQL in the cloud at Airbnb

#13

Post by rbranson »

davcro wrote: post #8

I can't imagine life without RDS. I'm a solo developer responsible for a large web app. Before RDS I hosted MySQL on dedicated hardware from Softlayer. I had a dedicated box for the master, one for the slave, and another for storing backups. The DB was about 100GB which made backing up a total nightmare. Mysqldump was too slow, I ended up having to use xtrabackup from Percona. Anyhow it got to the point were I was sp…

FYI: On Linux, you can do instant snapshots with LVM. This is pretty useful as it's possible to snapshot an InnoDB database as it's running without losing data.

Re: MySQL in the cloud at Airbnb

#14

Post by adient »

davcro wrote: post #8

I can't imagine life without RDS. I'm a solo developer responsible for a large web app. Before RDS I hosted MySQL on dedicated hardware from Softlayer. I had a dedicated box for the master, one for the slave, and another for storing backups. The DB was about 100GB which made backing up a total nightmare. Mysqldump was too slow, I ended up having to use xtrabackup from Percona. Anyhow it got to the point were I was sp…

The ability to snapshot hot MySQL databases is nothing new and certainly not unique to RDS. You most likely could have implemented a similar solution when you were running on dedicated hardware. But as a solo dev who isn't an SA/DBA you shouldn't have to deal with that sort of thing, which I think does make you the target market for RDS.

Re: MySQL in the cloud at Airbnb

#15

Post by mikeyk »

Would have loved to have seen some actual perf numbers, especially in terms of how much of a cost the Multi-AZ writes entail. We're doing Postgres on EC2, which means RDS doesn't quite help, but Postgres 9's hot standby/streaming replication at least gets a bit closer (and could be easily extended to a Multi-AZ set-up if one of our hot standbys was in a different AZ).

Re: MySQL in the cloud at Airbnb

#16

Post by davcro »

rbranson wrote: post #13
davcro wrote: post #8

I can't imagine life without RDS. I'm a solo developer responsible for a large web app. Before RDS I hosted MySQL on dedicated hardware from Softlayer. I had a dedicated box for the master, one for the slave, and another for storing backups. The DB was about 100GB which made backing up a total nightmare. Mysqldump was too slow, I ended up having to use xtrabackup from Percona. Anyhow it got to the point were I was sp…

FYI: On Linux, you can do instant snapshots with LVM. This is pretty useful as it's possible to snapshot an InnoDB database as it's running without losing data.

This doesn't work at scale. The IO performance with LVM is terrible. I lost a lot of money trying to implement this. Percona wrote Xtrabackup as a replacement for LVM.

Documentation:

http://www.mysqlperformanceblog.com/2009/02/05/disaster-lvm-...

http://www.mysqlperformanceblog.com/2009/02/24/xtrabackup-op...

Re: MySQL in the cloud at Airbnb

#17

Post by blantonl »

I run a fairly busy but normal implementation of MySQL on Amazon EC2 - 1 Master server and 3 read only slaves, with about 50GB of data spread between our portal database, a mediawiki instance, a forums database, a large dataset data for FCC license data, and a large core content database.

In initial testing of RDS after their recent updated release (we were excited about it!), we've found that RDS performance is significantly lower than running your own MySQL implementation with your storage being a 4 disk RAID-0 implementation on EBS. I suspect RDS uses a single EBS disk which is probably the root of the performance issues.

We didn't run actual benchmarks, but we quickly determined that RDS wasn't going to cut it from a performance perspective. In some cases, queries that take 3 seconds on our own MySQL implementation took well over 20 secs on RDS.

I see the value of RDS for many use cases, but in our case the performance of RDS is so significantly lower for a high-traffic use case that it wasn't going to work out for us.

Now, we still see performance problems when running MySQL on RAID-0 EBS - so we're considering going to an ephemeral disk implementation on EC2 and letting a single slave run on EBS and then snapshot from there often. If we lose an instance, we could restore from a snapshot quite quickly (within minutes).

In any case, regardless of our approach, RDS isn't going to be a part of it for now.

Re: MySQL in the cloud at Airbnb

#18

Post by spudlyo »

davcro wrote: post #16
rbranson wrote: post #13

Earlier quoted context omitted.

FYI: On Linux, you can do instant snapshots with LVM. This is pretty useful as it's possible to snapshot an InnoDB database as it's running without losing data.

This doesn't work at scale. The IO performance with LVM is terrible. I lost a lot of money trying to implement this. Percona wrote Xtrabackup as a replacement for LVM. Documentation: http://www.mysqlperformanceblog.com/2009/02/05/disaster-lvm-... http://www.mysqlperformanceblog.com/2009/02/24/xtrabackup-op...

The write performance with LVM is terrible.

LVM snapshots on read slaves or during times when you have sufficient write capacity work as advertised.

Re: MySQL in the cloud at Airbnb

#20

Post by tknaup »

blantonl wrote: post #17

I run a fairly busy but normal implementation of MySQL on Amazon EC2 - 1 Master server and 3 read only slaves, with about 50GB of data spread between our portal database, a mediawiki instance, a forums database, a large dataset data for FCC license data, and a large core content database. In initial testing of RDS after their recent updated release (we were excited about it!), we've found that RDS performance is sign…

Assuming you use InnoDB, is your innodb_buffer_pool_size big enough to hold your data (or a significant amount of it)? It's by far the most important setting to get speedy reads, which form 95% of the queries in our case. When you did the testing, did you warm up the RDS instance before, for example by running a number of common queries? It naturally takes a while after booting until MySQL can make efficient use of caches and buffers.

I'm not sure if instance-local storage will give you better performance compared to EBS. From the EBS page: "The latency and throughput of Amazon EBS volumes is designed to be significantly better than the Amazon EC2 instance stores in nearly all cases."

There is a problem with the recovery approach you described: as far as I know you can't use an EBS snapshot to populate local storage on a new instance, so you would be forced to use EBS for the new master. Promoting the slave to be the new master would be the better approach, with less downtime and data loss, provided that MySQL replication didn't break unnoticed.

- Tobi

Post reply on HN