Measuring IOPS for innodb_io_capacity

Question

Aurimas Mikalauskas's 17 Key MySQL Config File Settings gives the following advice regarding innodb_io_capacity and innodb_io_capacity_max:

measure random write throughput of your storage and set innodb_io_capacity_max to the maximum you could achieve, and innodb_io_capacity to 50-75% of it, especially if your system is write-intensive.

But how do I measure these IOPS? What block size do I use (I assume the page size, so 16K by default)? Do I fsync after every block?

A fio command like this one gives me around 300 IOPS maximum, which seems very slow for these drives NVMe SSDs these are supposed to be. Most advice I can find online has these variables in the thousands.

fio --name fio --readwrite=randwrite --ioengine=libaio --direct=1 --filename=fio --numjobs=1 --size=4G --gtod_reduce=1 --iodepth=64 --bs=16k --fsync=1

Answer 1

If you have a running instance of mysql, throw some data at it, then compute

( Innodb_pages_read + Innodb_pages_written + 
       Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) / Uptime

Something like:

SHOW GLOBAL STATUS;
run some heavy I/O queries
SHOW GLOBAL STATUS;

Tediously subtract the 5 values and evaluate the expression.

This will be a crude number, and you can probably set the innodb_io_capacity* higher than what the expression gives you.