Optimize Mariadb/MySQL server for 4 GB RAM VPS

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