1

I want to configure the innodb_io_capacity option for my MariaDB server. In the documentation I found the following:

Faster hard drives, RAID configurations, and solid state drives (SSDs) benefit from higher values.

Does anyone have experience in configuring the value? In what unit is the value measured? Is it IOPS?

For example my setup is:

  • Samsung NVMe SSD
    • Reading 500.000 IOPS
    • Writing 400.000 IOPS

My current value for innodb_io_capacity is 2000 and innodb_io_capacity_max is set to 3000.

Am I right in thinking that I can set innodb_io_capacity up to 500.000, because the hard disk has such a high IOPS value (minus X because of some operation system processes)?

How does the innodb_read_io_threads setting affect the innodb_io_capacity parameter? My current value is 16 threads (having Intel Xeon E5-1660v4 [8x 3.2 Ghz])

1
  • If you have a fully running system, grab the variables and global status. From these, I can do some computations for you. See mysql.rjweb.org/doc.php/mysql_analysis#tuning (You may not be be using the system so much that these settings matter!)
    – Rick James
    Feb 9, 2020 at 4:54

1 Answer 1

3

Basic Google-Fu produced the following information:

The innodb_io_capacity variable defines the number of I/O operations per second (IOPS) available to InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer.

I wouldn't set this to the maximum IOPS value of your SSD disk as you need IOPS for InnoDB foreground tasks. This is also commented on in the link that can be found just underneath the documentation for the parameter.

For information about configuring the innodb_io_capacity variable, see Section 14.8.8, “Configuring InnoDB I/O Capacity”.

This lead to:

The InnoDB master thread and other threads perform various tasks in the background, most of which are I/O related, such as flushing dirty pages from the buffer pool and writing changes from the change buffer to the appropriate secondary indexes. InnoDB attempts to perform these tasks in a way that does not adversely affect the normal working of the server. It tries to estimate the available I/O bandwidth and tune its activities to take advantage of available capacity.

The innodb_io_capacity variable defines the overall I/O capacity available to InnoDB. It should be set to approximately the number of I/O operations that the system can perform per second (IOPS). When innodb_io_capacity is set, InnoDB estimates the I/O bandwidth available for background tasks based on the set value.

You can set innodb_io_capacity to a value of 100 or greater. The default value is 200. Typically, values around 100 are appropriate for consumer-level storage devices, such as hard drives up to 7200 RPMs. Faster hard drives, RAID configurations, and solid state drives (SSDs) benefit from higher values.

Ideally, keep the setting as low as practical, but not so low that background activities fall behind. If the value is too high, data is removed from the buffer pool and change buffer too quickly for caching to provide a significant benefit. For busy systems capable of higher I/O rates, you can set a higher value to help the server handle the background maintenance work associated with a high rate of row changes. Generally, you can increase the value as a function of the number of drives used for InnoDB I/O. For example, you can increase the value on systems that use multiple disks or SSDs.

Carrying on to the innodb_read_io_threads parameter...

InnoDB uses background threads to service various types of I/O requests. You can configure the number of background threads that service read and write I/O on data pages using the innodb_read_io_threads and innodb_write_io_threads configuration parameters. These parameters signify the number of background threads used for read and write requests, respectively. They are effective on all supported platforms. You can set values for these parameters in the MySQL option file (my.cnf or my.ini); you cannot change values dynamically. The default value for these parameters is 4 and permissible values range from 1-64.

The purpose of these configuration options to make InnoDB more scalable on high end systems. Each background thread can handle up to 256 pending I/O requests. A major source of background I/O is read-ahead requests. InnoDB tries to balance the load of incoming requests in such way that most background threads share work equally. InnoDB also attempts to allocate read requests from the same extent to the same thread, to increase the chances of coalescing the requests. If you have a high end I/O subsystem and you see more than 64 × innodb_read_io_threads pending read requests in SHOW ENGINE INNODB STATUS output, you might improve performance by increasing the value of innodb_read_io_threads.

I interpret this as the parameters not being really related to each other. Each parameter can be configured individually based on the performance of the system.

4
  • 1
    So in your opinion, what should be a great value for innodb_io_capacity? The information in the documentation is very vague.
    – Thomas Z.
    Feb 5, 2020 at 9:59
  • There is no one single recommendation as the number depends on how your system performs, the load on the system, how much data is altered, etc. You will have to test this in your environment and with your databases. As the innodb_io_capacity is related to background processcing, you might not require a change at all.
    – John K. N.
    Feb 5, 2020 at 10:21
  • So when I do a server hardware upgrade what settings should be altered to benefit from the performance?
    – Thomas Z.
    Feb 5, 2020 at 12:15
  • That's a too broad question and would require more focus. There is no one shoe fits all solution to configuring a MySQL instance.
    – John K. N.
    Feb 5, 2020 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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