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