On Wed, Jan 18, 2023 at 3:10 PM Gordan Bobic <gordan.bobic@gmail.com> wrote:
A lot of MySQL Tuner's advice is based on extensive misconceptions and questionable understanding of MySQL internals. Realistically - the best option is to just pretend it doesn't exist.
Ignorance is bliss; I did not even know that it existed.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals 25% of buffer pool size.
I have no idea where this notion that innodb_log_file_size is in any way related to the buffer pool size, but it is completely non-sensical.
It might be motivated by the fact that MySQL as well as MariaDB Server before 10.5 before MDEV-21351 and some follow-up fixes could easily run out of memory on recovery. Yes, there is some logic to run recovery in multiple batches, but that does not work reliably. For MariaDB before 10.5, we have a known bug MDEV-22512 that I am not going to fix. I think that the two by far most important InnoDB parameters are innodb_buffer_pool_size and innodb_log_file_size. If you can afford potentially longer crash recovery times as well as the space usage, it could make sense to set innodb_log_file_size even multiple times innodb_buffer_pool_size. That can help avoid write amplification if the same data pages are being overwritten many times. Page writes only have to take place as part of a log checkpoint flush, or when the buffer pool is full and the least recently used pages are dirty. It should be worth noting that starting with MariaDB 10.5, there will no longer be writes of garbage pages that were freed from the underlying file, for example, as part of DROP INDEX, TRUNCATE TABLE, or a massive DELETE. In https://jira.mariadb.org/browse/MDEV-19895 I would welcome constructive feedback on how to set sane default values of some parameters, based on a small number of parameters, say, the available size of memory, CPU cores, storage, and the size of the database. With best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB Corporation