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

 

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

Sandeep can you please convert this for 24gb ram the numbers seem very large when i did it.

On 2/18/2025 at 8:30 PM, Demonx said:

Sandeep can you please convert this for 24gb ram the numbers seem very large when i did it.

[mysqld]
max_connections = 30000
max_user_connections=10000
innodb_lock_wait_timeout=120
max_heap_table_size = 1G
tmp_table_size = 1024M
thread_cache_size = 128
innodb_buffer_pool_size = 2G
innodb_log_file_size = 2G
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
table_open_cache = 5000
wait_timeout = 28800
interactive_timeout = 28800
long_query_time = 25
max_allowed_packet = 512M
performance_schema = OFF
open_files_limit = 220000
key_buffer_size = 64M
join_buffer_size = 5M
sort_buffer_size = 5M
read_buffer_size = 2048k
read_rnd_buffer_size = 2048k
max_connect_errors = 10
tmp_table_size = 550M
max_heap_table_size = 500M
innodb_file_per_table=1