In this article, we have described the basic configuration settings that must be set when deploying a standalone server with MySQL or MariaDB. We’ll look at the initial configuration parameters that must be checked before starting the server.
First, you need to adjust the memory consumption of the MySQL/MariaDB service. It’s best to use the MySQLTuner script (https://github.com/major/MySQLTuner-perl).
You can install this script on CentOS, RHEL, Rocky, or Oracle Linux from the standard repos:
# yum install mysqltunerOn Ubuntu and Debian:
$ sudo apt install mysqltunerOr you can download the Perl script sources from git:
# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
# chmod +x mysqltuner.plIf you installed the package through a repository, run the command:
# msqltunerIf you downloaded the script manually:
# perl mysqltuner.pl > settings.txtThe script will ask for an account credential to connect to the database.
The script will analyze the environment and offer a number of settings, which you need to change in the MySQL or MariaDB configuration file (Variables to adjust).
Let’s consider what parameters in the configuration file determine the memory usage by the SQL server:
Global:
innodb_buffer_pool_size
innodb_log_file_size
key_buffer_size
innodb_log_buffer_size
query_cache_size
aria_pagecache_buffer_size
These values are summed up.
Thread:
sort_buffer_size
join_buffer_size
read_buffer_size
read_rnd_buffer_size
max_allowed_packet
thread_stack
Sum the values of these parameters and multiply by the value of max_connections.
If you have configured all values using the mysqltuner, it is enough to manually check the values in the most critical parameters innodb_buffer_pool_size and max_connections.
Be sure to check the value of the bind-address parameter. If you don’t want MySQL to accept external connections, leave localhost here:
bind-address = 127.0.0.1
Check the log paths here. Add a log file for slow SQL queries:
log_error = /var/log/mysql/error.log
slow_query_log
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2.0
Check a character set and collation. For example, for Swedish projects set the following:
character-set-server=latin1
collation-server=latin1_swedish_ci
The next two parameters set the maximum number of connections and tables in the database. They significantly affect the MariaDB/MySQL performance.
open_files_limit
table_open_cache
If you encounter the error Errcode: 24 “Too many open files” while using SQL databases, it means that the number of open files for the mysqld process has exceeded the system limit. To increase this limit, you need to change the service settings. For example:
# mkdir /etc/systemd/system/mysqld.service.d
# touch limit.conf[Service]
LimitNOFILE=65535
The innodb_temp_data_file_path option allows you to manage the size of the temporary tablespace file. This file can grow to a huge size and cause you to run out of disk space. Restrict the size and growth of the temporary file. For example, to set a maximum size of 4 GB and allow growth in increments of 24 MB:
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2G