Optimize Mariadb/MySQL server for 4 GB RAM Server

[Sandeep B.]

If you have a MariaDB/MySQL VPS server with 4GB of RAM, you can optimize its performance by following these steps:

If you’re looking for more than 4gb of ram configuration just divide the value with 4 and multiply the result with the amount of RAM, for example : 256/4 = 64 and you want it for 8 gb of ram just multiply it with the result i.e. 64 x 8 = 512

For MariaDB :
Edit the MariaDB configuration file /etc/my.cnf.d/server.cnf using your favorite text editor:

nano /etc/my.cnf.d/server.cnf
Add the following lines under the [mysqld] section:

default-storage-engine = InnoDB
innodb_flush_method = O_DIRECT
innodb_log_file_size = 128M
innodb_buffer_pool_size = 128M
max_allowed_packet = 128M
max_connections = 200
key_buffer_size = 256M
tmp_table_size = 64M
max_heap_table_size = 64M

For MySQL :
Edit the MySQL configuration file /etc/my.cnf using your favorite text editor:

nano /etc/my.cnf

Add the following lines under the [mysqld] section:

default-storage-engine = InnoDB
innodb_flush_method = O_DIRECT
innodb_log_file_size = 128M
innodb_buffer_pool_size = 128M
max_allowed_packet = 128M
max_connections = 200
key_buffer_size = 256M
tmp_table_size = 64M
max_heap_table_size = 64M

After you’ve added this values restart MariaDB/Mysql server :

MariaDB server restart :

systemctl restart mariadb

MySQL server restart :

systemctl restart mysql mysqld

 

[Netino]

It seems like a good configuration, except for the parameter:
tmp_table_size = 64MB

This resource is used per connection, so it should be calculated as follows:
tmp_table_size = [total memory available] / max_connections

For a server with 4Gb total memory, with max_connections = 200, it should be something around:
tmp_table_size = 20.5Mb

Or, reduce max_connections to a more realistic number, compatible with the user's needs.

All of the following parameters must be considered per connection:
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
thread_stack
binlog_cache_size
tmp_table_size

The following parameters make up the base memory consumed by mariadb, which is consumed only once:
key_buffer_size
query_cache_size
innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_buffer_size

You can use the following script to calculate the total memory to be consumed by mariadb/mysql, included per connection:

#!/bin/sh
# you might want to add some user authentication here
mysql -e "show variables; show status" | awk '
{
VAR[$1]=$2
}
END {
MAX_CONN = VAR["max_connections"]
MAX_USED_CONN = VAR["Max_used_connections"]
BASE_MEM=VAR["key_buffer_size"] + VAR["query_cache_size"] + VAR["innodb_buffer_pool_size"] + VAR["innodb_additional_mem_pool_size"] + VAR["innodb_log_buffer_size"]
MEM_PER_CONN=VAR["read_buffer_size"] + VAR["read_rnd_buffer_size"] + VAR["sort_buffer_size"] + VAR["join_buffer_size"] + VAR["binlog_cache_size"] + VAR["thread_stack"] + VAR["tmp_table_size"]
MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN
MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONN
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576
printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN
printf "| %40s | %18d |\n", "max_connections", MAX_CONN
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576
printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576
printf "+------------------------------------------+--------------------+\n"
}'

Note that the 'Max_used_connections' parameter gives a realistic number for the total number of connections since the last start of the mariadb server, and can be used to adjust the 'max_connections' parameter.

Regards,
Netino