Leaving MariaDB settings out of the box may work for some people but not so productive for others, MariaDB can be a resource hog if not checked and result in issues like OOM-Killer(Out of memory) knocking it off.
You can use a tool called MySQLTuner to give you some variables to adjust in your MariaDB/MySQL environment. This guide examines one main variable called the innodb_buffer_pool_size which is the amount of memory set aside for all the databases on the server.
SSH in to your server as root and install the tool:
wget http://mysqltuner.pl/ -O mysqltuner.pl
Set the correct permissions:
chmod +x mysqltuner.pl
Run the tool:
./mysqltuner.pl
This is the type of output you will get:
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] No MyISAM table(s) detected ....
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M / 460.6M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 96.0M * 1 / 128.0M should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 1 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: 99.77% (34248273 hits / 34325995 total)
[!!] InnoDB Write Log efficiency: 318.91% (103378 hits / 32416 total)
[OK] InnoDB log waits: 0.00% (0 waits / 135794 writes)
...
...
...
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 128M) [see warning above]
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_definition_cache (400) > 905 or -1 (autosizing if supported)
performance_schema=ON
innodb_buffer_pool_size (>= 460.6M) if possible.
innodb_log_file_size should be (=32M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
Don’t worry about the Danger Will Robinson lines about dangerously high memory usage as we will be adjusting a couple of variables – you can adjust more if you like and there are a ton of them – see reference on MySQL variables here.
Adjusting MySQL/MariaDB variables
The main adjustable variable is the innodb_buffer_pool_size, this is the sum of all databases on the server, above under the InnoDB Metrics session it tells us what it currently is and what it should be in the 2 figures given:
[!!] InnoDB buffer pool / data size: 128.0M / 460.6M
And also mentions this in the Variables to adjust:
innodb_buffer_pool_size (>= 460.6M) if possible.
You can also double check any adjustable variable in a MySQL session on your server, like so:
root@mel1:~# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8980
Server version: 10.6.14-MariaDB-1:10.6.14+maria~ubu2204 mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.001 sec)
MariaDB [(none)]> \q
Bye
So above I am asking to show the value of innodb_buffer_pool_size and the answer is in bytes 134217728 which is 134MB, the MySQLTuner calculated 128MB but whats 6MB between friends.
But which ever value it is, it’s too low.
So what size should I set the pool size at, well at least 461MB which is the sum of the databases and then some to allow for additional overhead but make sure I leave enough for other processes on the server, I have 4GB of memory on the server.
Some experts say to use between 60-80% of total RAM on the server but this seems high when there is a lot of RAM, so I think doubling the total database size and rounding it off so 1GB and then run the tuner tool later down the track to see any difference.
Where to set the new MySQL variable amount
So I am using a RunCloud panel which uses MariaDB, its config file is as below:
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# Most important is to give InnoDB 80 % of the system RAM for buffer use:
# https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size
#innodb_buffer_pool_size = 8G
innodb_buffer_pool_size = 1G
Save and exit and you are done.
Restart MySQL and the changes are applied.
systemctl restart mariadb
A couple more variables you can adjust in the same config file.
The defaults of these are quite high and the values further below are generally recommended. These values on my server were set as:
There are more variables MySQLTuner suggests changes, do some research and see what fits best for you and as the server load changes over time with more or less web apps run the MySQLTuner tool and fine tune some more.
