1

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
2
  • Great article on MySQL tuning/configuration - should be required reading before posting MySQL performance questions here = +1 - p.s. welcome to the forum! – Vérace Dec 2 '20 at 7:44
  • @mozygipa Ask Aurimas Mikalauskas to update his tips and point you to a measuring tool, From OS command prompt, iostat -xm 5 3 when the server is busy may give you a clue on current performance. Bump innodb_io_capacity up 10%. Remove innodb_io_capacity_max and the system will calculate innodb_io_capacity * 2 to set the max for you. – Wilson Hauck Dec 2 '20 at 18:19
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.