maria-discuss team mailing list archive

[Marko Mäkelä <marko.makela@xxxxxxxxxxx>]

On Wed, Jan 18, 2023 at 3:10 PM Gordan Bobic <gordan.bobic@xxxxxxxxx> 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