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 only takes a minute to sign up.
Sign up to join this community
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?
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.
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.
I use iostat to determine the amount of IOPS my application is performing. iostat reports this as tps. KB/t helps you determine if the amount of the transfer is less than the chunk size, 256 KiB. I run iostat with a one second wait time, i.e. iostat -w 1.
