[Solved] Where do I find tuning parameters for Mariadb

ERPNext
1

several years ago I remember tuning my v10 system by editing /etc/mysql/my.cnf

I had to edit the following parameters to get better performance:

innodb-buffer-pool-size
innodb-log-file-size
tmp-table-size & max-heap-table-size
query_cache_limit & query-cache-size

Where do I find these settings now?

The my.cnf file only points to includes of other cnf files. After tracing all of them down I never was able to find the settings.

Can anyone point me to the proper location to tune these settings with modern v13 erpnext system?

Thanks in advance for any clues…

BKM :nerd_face:

1 Like
2

if I’m not mistaken, some guidance tells to configure mariadb in /etc/mysql.cnf or /etc/mysql/mariadb.cnf

1 Like
3

The location of your mariadb conf files isn’t determine by erpnext, so it doesn’t make a difference whether you’re on v10 or v13. Different distros structure these files very differently.

On most systems, you can run which mysqld to find the path of your database binary, and then /path/to/mysqld --verbose --help | grep -A 1 "Default options". That will show you which conf files are being loaded. It gets a bit complicated because each of those files can load other files, but this is a place to start at least.

Better instructions are probably available in your distro’s documentation.

4

Good topic. Anyone can give me a most optimal settings for a 4 cores 8GB RAM server which is used for retail sales. Let’s say about 8 POS profile working simultaneously for about 20 hours per day. I have already given 8 GB of swap with pre configured mysql. Haven’t made any changes to SQL from time of using easy install script.

1 Like
5

Well, after much google searching, server searching, and manual reading, I have finally found a way to do this.

The Good News is that it is the same for Ubuntu v20.04 and Debian 10.

I tried setting up a Ubuntu server to see if there was a difference (yeah, I know, sometimes I overthink this stuff and do way more than is needed).

The file location is /etc/mysql/mariadb.conf.d/50-server.cnf

You will have to use sudo to do anything in this directory.

First make a copy of the file:

cd /etc/mysql/mariadb.conf.d
sudo cp 50-server.cnf 50-server_cnf.orig

Now that you have a copy of the original it is safe to edit the original one:

sudo nano 50-server.cnf

I copied a section of one of the .cnf file from one of my old v10 servers and edited it to work on this new v13 server. Here are the lines I used:

default_storage_engine  = InnoDB
    # you can't just change log file size, requires special procedure
    innodb_log_file_size    = 254M
    innodb_buffer_pool_size = 16G
    innodb_buffer_pool_instances = 8
    innodb_log_buffer_size  = 254M
    innodb_file_per_table   = 1
    innodb_open_files       = 1200
    innodb_io_capacity      = 1200

Here is a screen shot of where in the file I added them:

50-server_cnf
50-server_cnf642Ă—545 91.8 KB

The next step is to restart mysql to get the setting recognized in your system:

sudo service mysql restart

Finally, you will want to prove to yourself that the settings were in fact changed and are being used by mysql/mariadb. To do this you start mysql from the command line:

mysql -u root -p[your-password]

(notice there is NO SPACE between the -p and the first character of your password)

Once in the mysql user interface, type the following command to get a list of all innodb settings the system us using:

show variables like 'inno%';

This will display a long list of InnoDB settings that the system is using. You can actually go through the InnoDB manual and add any of these settings to your system if you want to take fine tuning to the maximum level.

The only 4 settings I was interested in were:

innodb_log_file_size = 254M
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 8
innodb_log_buffer_size = 254M

Here is partial screenshots from the output of the mysql command to show that my settings were used:

Settings1

settings2

→ NOTE: These settings are my first attempt at tuning my own system and are NOT recommended for anyone else to use. These settings will likely change as I continue my testing.

Hope this helps someone else trying to find the place to do settings like this.

As always… Your Mileage May Vary! :sunglasses:

BKM

13 Likes
6

Hi @bkm,

FYI, appears the 'innodb_buffer_pool_instances' isn’t needed starting with MariaDB 10.5+

1 Like
7

Thank you @bkm