Add Documentation about changing --innodb-buffer-pool-size in mariadb correctly

[iluvatyr]

Sometimes, a user would want to make his --innodb-buffer-pool-size larger for a quicker DB.

Using https://github.com/major/MySQLTuner-perl, which is a tool to check, review and optimize databases, and running it within the mariadb container shows (among other things), that the "innodb_log_file_size" size should be 25% of the "innodb-buffer-pool-size".

Here is part of the output from the tool:

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 2.0G/206.9M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 512.0M * 1/2.0G should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk : 16 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (396856174 hits/ 396867174 total)
[!!] InnoDB Write Log efficiency: 575.94% (14145 hits/ 2456 total)
[OK] InnoDB log waits: 0.00% (0 waits / 16601 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[OK] Aria pagecache hit rate: 99.9% (543K cached / 565 reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
Performance schema should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
Variables to adjust:
join_buffer_size (> 256.0K, or always use indexes with JOINs)
performance_schema = ON enable PFS
key_buffer_size (~ 24M)

Therefore when changing the inodb-buffer-pool-size to a higher size, the innodb_log_file_size should probably be changed accordingly.

e.g. when changing the buffer pool size to 2G, then innodb_log_file_size should be 512M, if there is one log file (256 if 2 etc.).
So it will look like following in the docker-compose.yaml of the database container:
command: mysqld --innodb-buffer-pool-size=2G --innodb_log_file_size=512M --transaction-isolation=READ-COMMITTED --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --max-connections=512 --innodb-rollback-on-timeout=OFF --innodb-lock-wait-timeout=120

More info about log file:
https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html

Maybe info about this can be updated within: https://docs.photoprism.app/getting-started/troubleshooting/performance/

[lastzero]

Thanks a lot! 👍

I ran the latest version of the linked Perl script inside the MariaDB container using these commands:

docker-compose exec mariadb bash
cd /root
apt-get -y update
apt-get -y install perl wget nano

wget https://github.com/major/MySQLTuner-perl/archive/refs/tags/1.8.3.tar.gz
tar -xzf 1.8.3.tar.gz
mv MySQLTuner-perl-1.8.3 mysqltuner
cd mysqltuner
./mysqltuner.pl --skippassword --user root --pass='photoprism' /run/mysqld/mysqld.sock

This is the updated mysqld command based on the recommendations:

mysqld --table_open_cache=32768 --tmp_table_size=128M --max_heap_table_size=128M --join_buffer_size=1048576 --innodb-buffer-pool-size=1G --innodb_log_file_size=256M --transaction-isolation=READ-COMMITTED --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --max-connections=512 --innodb-rollback-on-timeout=OFF --innodb-lock-wait-timeout=50

New parameters:

--table_open_cache=32768 --tmp_table_size=128M --max_heap_table_size=128M --join_buffer_size=1048576 --innodb_log_file_size=256M

In addition, innodb-buffer-pool-size was increased from 256M to 1G.

Although all the recommendations sounded plausible, I can't really say that it makes search queries any faster. On the contrary, they might even be slower now.

So I'll have to change settings back again next for comparison...

Especially the impact of a large join_buffer_size isn't completely clear to me. The MariaDB docs say it makes sense to leave it low globally, while the script said it should be increased way above the default:

Increase to get faster full joins when adding indexes is not possible, although be aware of memory issues, since joins will always allocate the minimum size. Best left low globally and set high in sessions that require large full joins.