There is a suggestion to raise innodb_io_capacity=4000
I was wondering what the reasoning was behind this. From what I understand, increasing that value above default could have detrimental effects on the longevity of an SSD. It also doesn’t seem to gain you anything unless you are consistently seeing a lot of unapplied changes in the InnoDB Buffer. Also, if you are constantly writing the changes back to the database then you don’t benefit from caching.
I was just wondering if anyone had any insight as to why that is a suggestion or thoughts on tuning that parameter.
Thanks.
They seem to have removed this section from the current docs…
In the documentation for the " latest " version, to which the link in the original post refers, this section is no longer included. The latest version of the documentation, that still does contain this section, is the one for version 21… https://docs.nextcloud.com/server/21/admin_manual/installation/server_tuning.html. So I guess this has not to be considered a general recommendation anymore…
Looks like they have just moved the entire section on database tuning to: https://docs.nextcloud.com/server/latest/admin_manual/configuration_database/linux_database_configuration.html
But it no longer contains the suggestion to raise innodb_io_capacity=4000
This is probably not a good thing to do as I understand it. In fact all the suggestions on there should be considered as pertains to the given server. My number of Nextcloud users is not very large so my instance has different requirements than what others might have. Probably the most important innodb setting to tune would be: innodb_buffer_pool_size
Here is some of the reasoning I have for not changing innodb_io_capacity from the default value of 200:
This setting refers to the drive that your MariaDB database is sitting on. The important values to check are the Write IOPS. Testing IOPS with a tool like fio is not a great plan for an SSD since it will reduce the lifespan of the drive. The manufacturer will list IOPS with a queue depth of QD32. Queue Depth is how many operations the OS is able to give the device before waiting for a response to any of those operations.
This is basically how many threads the application will be running concurrently.
InnoDB default is set to 4 threads so testing at QD4 would give a realistic value and will be lower than what is given on manufacturer’s pages.
For my Corsair Force MP500 120GB Specs at QD32: Random Write (4KB) IOPS: 90000
From what I understand, setting this variable too high on an SSD will reduce drive life. Also you would have
to be sure that the drive you have your database installed on would be able to handle the IOPS. For installations
that are on non-ssd drives you would not want to increase that number to 4000 unless they could perform that many writes per second.
This site is what I’m basing some of my info off of: https://www.percona.com/blog/2020/05/14/tuning-mysql-innodb-flushing-for-a-write-intensive-workload/