IOPS - What are they and what do they mean?

Question

OK I know technically what IOPS are. The technical definition is great but I'm not understanding the broader implications. My knowledge is basically:

• IOPS are the input and output operations an application makes
• There are HD IOPS, ram IOPS, network IOPS, etc.
• IOPS can be sequential or random (on average for a specific application)
• Sequential IOPS are good, random IOPS are bad.

But that's about the depth of my understanding. So when someone asks "Does the application you are developing have high or low IOPS count?" I don't know what that entails. The application is web based, so does that mean I need to count the IOPS of IIS or just the IOPS my web app makes? There isn't a lot of file level IO going on, but there is a lot of database traffic. Is "average" based on a single session, all sessions over a day, all days over a week, etc. How can I tell if my database accesses are high or low? What is high and what is low for that matter? How can I tell is they are sequential or random?

And once I figure out all that out and understand what it all means, how do I make it better? How can you lower the IOPS or change it from random to sequential? Or should I even worry or bother with such things? Are there tools to measure this stuff? How can I see what my database IOPS are for example?

Answer 1

Unless your web application is very simple, it almost certainly requires a database as a backend. If you expect your web application to support many simultaneous users, you can see where the speed of the database will be crucial in determining how responsive your site is. So this is what you should focus on.

Unless your web application is one where users submit things and then they get tacked on to the end of the database (in which case you really would not need a database), you are going to have users doing things that want things from all locations of the database. So you can consider a database to fall in the "random IOPS" category.

Spinning hard drives can read and write faster sequentially than randomly, because seeking to different tracks takes time. An NCQ-enabled drive, which all new drives are nowadays, can mitigate this a little by allowing the drive to determine the order it performs I/O to a certain extent, but it only helps a little. SSDs can write faster sequentially than randomly, especially if they are TRIM enabled and you are writing to a freshly erased section of flash. However as you are servicing many unique and random users, you should assume worst-case random I/O for your situation, as you can't control what the users will be doing.

In your situation you are likely going to care about disk IOPS more than network IOPS. So you are going to want to monitor how heavily your database is being accessed, over time, over a day, a month, and several months, collecting information that's meaningful to your system. You will want to be sure your database server has plenty of RAM to allow it to cache frequently used data, and utilize SQL concepts such as partitioning and proper indexing. You may want to put your database log on a separate physical disk than your actual database, for example. These are advanced topics and will require some serious research on your part.

I believe SQL Server Performance Monitor, and SQL Profiler if you want to dig down into exactly what your web app is doing, are what you should begin looking into.