I want to know about the IOPS (I/O Per Second) and How it influences the DB CRUD operation

Question

I want to get a dedicated server for my Database (MySQL) which runs InnoDB Engine. There is this option in InnoDB capacity that you can define the IOPS in it. Let's say that I have a SATA 72K-RPM HDD that allows 100 IOPS. Does it mean that my DB can run 100 concurrent SQL query per second only or it means 100 single Read or Write in 1 second ? How a DB can handle 3000 query per second being launched on a Hardware like that ? I'm a bit confused so please explain it for me. I want to get more than 200 query execution on my dedicated server.

Answer 1

A hard drive's IOPS tells you how many I/O operations that particular drive can perform per second. Since random reads, random writes, sequential reads, and sequential writes have different performance characteristics, if you're looking at a single number, that implies that this is a weighted average of these four numbers that represents some workload. That workload may or may not be similar to the workload that a database would actually perform so your system may end up getting more or fewer operations per second.

A SQL query would, assuming it does physical I/O (many queries do not need to do any I/O because they are simply reading from data already in the database's memory or in the file system cache or in the SAN cache), tend to perform many I/O operations. If your queries are actually doing physical I/O, a single query could easily do 100 I/O operations per second. You could potentially have 100 queries running simultaneously each only getting to do 1 physical I/O operation per second but that would likely mean that all 100 queries would take an unacceptable amount of time to return.

Realistically, if you need to handle 3000 queries per second, you'll need to ensure that the vast majority of your queries do not need to do physical I/O by ensuring that the data they need is cached (preferrably by the database). And you'll need to spread the I/O over a relatively large number of drives (with an appropriate RAID configuration) to increase the total IOPS that the system can perform. This sort of capacity planning is going to require a reasonably good understanding of your application, your data, how much I/O each query is likely to really need, etc. And you'll likely need to balance cost vs. performance-- adding memory or adding drives or changing the RAID configuration will involve different tradeoffs between cost, performance, and available space that will have to be considered. You may also want to consider splitting the workload among multiple servers (which may require architectural changes to your application).