[stextbox id=alert]Hi Everyone! I have taken the XLS file offline. There were a ton of bugs, as pointed out in comments and through emails/Twitter. I am working on solidifying the functions and migrating this to an actual application instead of Excel. In the meantime, you can check out other calculators here.[/stextbox]

**Updated: **I am going to take this offline for awhile. I really need to sit back down and go over what I had intended this to be used for since I’m starting to mix all types of things on this sheet and my terminology I’m sure does not match correctly with what I’m trying to express in the sheet. I’ll repost the download when I have had time to review.

**Updated**: You can download the Excel workbook RAID-and-IOPS which has updates/bugfixes through December 3, 2012 as noted in comments below.

I’m working on an Excel spreadsheet (cheatsheet) that will allow for user input to calculate some of the following:

- Calculate IOPS, Usable Space, MB/s based on Number of Disks, Spindle Speed, RAID type and Read/Write Percentages.
- Calculate Number of Disks required for IOPs
- Calculate/Convert MB/s to IOPS
- Calculate/Convert IOPS to MB/s

It also contains some basic information and formulas:

- Formula for Total Raw IOPS
- Formula for Functional IOPS
- Formula for MB/s from IOPS
- Formula for IOPS from MB/s
- Formula to determine number disks required for IOPs based on RAID type and spindle speed.

Here’s a screenshot:

Here are some of the formulas used:

- Total Raw IOPS = Disk Speed IOPS * Number of Disks
- Functional IOPS = (((Total Raw IOPS*Write%))/(RAID Penalty))+(Total Raw IOPS*Read%)
- MB/s = (IOPS * KB per IO) / 1024
- IOPS=(MBps Throughput / KB per IO) * 1024
- Formula to determine disks required for IOPs (total required IOPS * read%) + (total required IOPS * write% * RAID penalty) = total IOPS required take that and divide by IOPS provided by disk type (15k=175, 10k=125, etc.)

JustLooks like a good spread sheet however i am not sure what the io tye is and wher it derives from.

Rich KreiderPost authorThe IO Type is more or less the workload characterization such as 8K request size, 32K request size, 64K, etc. This is the size of the reads/writes workload which has an impact on overall performance. IO Type (or size this sheet) can impact latency and throughput.

JustThanks Rich. So it sounds like in order to specify this you would need to know how the application or o/s presents the i/o type e.g. 4k, 8k etc? I guess what i am asking is where do you get this information from so that you can specify it in the spread sheet.

Thanks

Justin

Rich KreiderPost authorIf you are in a Windows shop, you can use perfmon counters such as Avg. Disk Bytes/Read to determine average I/O size. If you are running a VMware-virtualized workload, you can take advantage of a great tool – vscsiStats – to identify your I/O request size.

Pingback: IOPs Calculator and RAID Calculators / Estimators | techish.net

JBODCan you add a calculation if someone wanted to just use JBOD, instead of RAID?

Jeremy ChiversHi Rick,

Thanks for posting this up I have found it very useful.

I did detect an error though in the SSD calculation. The error relates to a vlookup which I correct and uploaded to http://www.myvmwareblog.com/wp-content/uploads/2012/11/RAID-and-IOPS.xlsx

Hope it help and thanks.

Jeremy

Rich KreiderPost authorHi Jeremy,

Thanks for the update. While working on a few calculations for Just I had noticed the range of vlookup was off a bit and corrected my local copy but did not upload. Thanks again!

Rich

Jeremy ChiversAnytime and thanks again for the great post.

Jeremy

Pingback: IOPS Calculator - My VMware blog

Pingback: IOPS Calculator | techish.net

Umar SyyidWhy are total iops in the picture a sum of front end and backend?

Rich KreiderPost authorHi all,

I have updated the spreadsheet and fixed the bugs noted in the comments:

1. vlookup for SSD

2. frontend/backend properly calculating now

I also put comments in a few cells to help you understand what I was doing with some of the calculations in case there are more questions.

Direct link: https://techish.net/wp-content/uploads/2012/07/RAID-and-IOPS.xlsx

Thanks for all the feedback!

-Rich

Michael CarverWhat is the difference between RAW IOPS and backend IOPS? I was under the impression that they were the same thing.

I calculate the RAW amount of IOPS by multiplying the amount of disks by the IOPS a single disk can handle (ie. 5 [15k] disks * 175 IOPS = 875 IOPS). Then, I thought you took the RAW IOPS to find the functional IOPS for an aggregate/RAID group by using the formula : (Raw IOPS * Write % / RAID Penalty) + (Raw IOPS * Read %).

I noticed that before you made the most recent updates to the sheet, these were the formulas you were using.

After looking at the way the sheet is now, I am confused. How can the total backend IOPS be a higher number than the amount of RAW IOPS available from a group of disks? What is the difference between funcitonal IOPS and frontend IOPS? If you could please share your thoughts to help me understand, I would appreciate it.