maybe we can use io.pressure (PSI - https://docs.kernel.org/accounting/psi.html) like we used memory pressure to determine some/full as an interactive OS feedback on IO capacity.

This reminds me of some discussion in MDEV-19895 related how we should automatically set some parameters. This would require significant performance testing with a wide range of different workload types (in terms of number distinct pages being read or written, and the key distribution of the reads or writes) in various environments. I think that such effort needs to be tracked in MDEV-19895.

I do not think that there is any ‘one size fits all’ logic for innodb_log_file_size. The ideal value depends a lot on the type of workload. At one extreme, only a few database pages are being updated (say, a non-indexed column of a single record is being updated in single-statement transactions). Another extreme would be that there are constant writes to all database pages. Therefore, advancing the log checkpoint would require lots of distinct pages to be written out. These extremes could be also combined with large batch read jobs, which will require LRU eviction from the buffer pool, possibly involving additional page writes.

Thanks to MDEV-27812 it is easy to experiment with SET GLOBAL innodb_log_file_size without restarting the server. I would encourage oli and others to come up with some rules of thumb, say, if you have 10% of writes and 90% of reads, then the ratio of innodb_log_file_size to innodb_buffer_pool_size should be this much.

danblack, the idea to adapt innodb_io_capacity to the I/O pressure sounds reasonable to me. We could implement an option for that.

@Marko: I assume MarkC would love to do the benchmarks...?

With io pressure feedback it should be plausible to get to the io_capacity with a low risk of interfering with SQL fetching of pages from disk.

I think its too early to jump to benchmarks. They will get QPS measures for a few benchmark workloads to see if that corresponds to a new fixed value for innodb_log_file_size.

As Marko highlights the ideal innodb_log_file_size covers a variety of factors. Lets try to get concrete measure of a few things and at least see if a general change is needed, more guidance/status recommendations/ something adaptive:

• Innodb_buffer_pool_pages_flushed / minute
• emergency flushing
• innodb_log_file_size

oli do you have workloads that have required changing to log_file_size or flushing system variables. What does an interval (covering a few flush cycles) of innodb global flushing statistics look like? With what innodb settings?

@danblack no, unfortunately we do not have such workloads. We only "size" innodb log files by the 2 rules: a) 1 hour of workload should fit in b) bulk load should fit in. But customer never wants to verify the recommendations if they make sense or not. And typically users do not have uniform workload. So also this can not be tested/verified... Sorry!
I just brought this topic up because I checked the changes in 8.4.