Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am migrating part of a mysql database into AWS. The data in question is 99% write, and each row has about 1k of varchar fields, a datetime and 4 ints.

I estimate that we will need between 20-25k records inserted/hour during peak times.

I ran iostat -h on the current database, and it reported around 40 tps.

How do I figure out what type of IOPS I'll need?

share|improve this question

2 Answers 2

up vote 6 down vote accepted

You'll have to test it.

You could do some back of the envelope calculations to approximate the number of I/Os per insert, multiply it by the number of transactions per second, add in some buffer room etc, but it's much easier to just test it.

The easiest thing to do is to allocate a best guess, then go back and increase or decrease it to match you're real world testing. This is one of the luxuries of using a cloud based environment, hardware changes are low in capital costs and such changes usually require only config updates. With EBS volumes you can't just increase the number of IOPS, you'd have to scale up the size of the volume as well1. You can always just create a new volume and copy your data over. There will be some downtime but if you're data isn't huge it shouldn't be much as it'd be a raw copy.

Here's a guess at the number of I/Os necessary. Again it's just a guess though as the specifics depend on the number of indexes and whether your traffic flow will be smooth or spikey. At 25K tx/hour you've got ~7 tx/sec. The size of each row isn't particularly relevant as it's less than the size of a single I/O (4K). Each transaction will do somewhere between 1-5 IOP (primary insert plus a couple index tree inserts) so let's say ~35/s.

I say start out with the bare minimum of 100 IOPS and scale up if necessary.

share|improve this answer
    
BTW, as a followup to this: we set up a 300GB database, and that meant a minimum of 1000 IOPS. After about 6 months live on RDS we have been 0 incidents due to I/O issues. Probably overkill, but as close to fire & forget as you can get. –  chris Apr 28 at 22:00
    
Based on your load - you don't need provisioned IOPs. Unless you're going to be needing in excess of 2500-3500 total IOPs, the standard storage will do at 300GB (because of EBS striping). –  Ross May 7 at 16:50

I used the basic iostat (iostat -h) tool to get an idea of how many iops I was currently using. From that I extrapolated how much I'd use if I was under 4 times that amount of load and went with that amount. For me that worked out to 780 IOPS so I went with 800 IOPS.

share|improve this answer
    
iostat -h is reporting around 40 tps, so does that correspond to 40 iops? That would match the numbers that @sehrope calculated. –  chris Aug 1 '13 at 15:39
    
That's correct. –  Mark D Aug 2 '13 at 13:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.