How can I measure the number of IOPs for a certain query I need to estimate the max IOPs for a certain MySQL DB (one that does not exist yet).

The OS is Linux and the DB is MySQL 5.1.x (though I don't think the DB brand is important for the asked topic)

The questioned table is extremely simple. user table with some attributes. and the dominant query is:

SELECT * FROM users WHERE username='';


I think about populating a table with huge amount of records and use iostat to calculate the IOPs average per query. Later I can use the same method to test SELECT.

Is this a reasonable test for measuring IOPs?
Are there other better methods to accomplish this?

Thanks
This is trickier than it looks. I/O per second is a difficult thing to pin down. Just because the file system sees so many I/Os per second, doesn't mean that MySQL sees the same thing - there is such a thing as I/O buffering. I would look into MySQL 5.6 - in particular the performance schema (P_S)(Marc Alff wrote the P_S (*)). One SQL SELECT could read from multiple tables. One transaction could read and write to many tables. Are you using MyISAM? If you are using InnoDB, are you using one file per table (advisable IMHO) - otherwise iostat will just give a database wide figure. I would suggest that you read around this topic in some detail and be clear about what, exactly, it is you're measuring.

(*) this comment obviously depends on whether you have the choice to upgrade, but the server version is always important on this forum - the more those who are trying to help know about your system, the more help you can receive.