مرکز آموزش

The default MySQL/MariaDB configuration is conservative and rarely optimal for a dedicated VPS. This guide covers the highest-impact tuning settings for typical small-to-medium workloads.

Prerequisites

• MySQL or MariaDB installed
• Root or sudo access
• Knowledge of your VPS's total RAM

Locating the Configuration File

MySQL:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

MariaDB:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

InnoDB Buffer Pool Size (The Most Important Setting)

This is the primary cache for data and indexes — the single highest-impact tuning value.

innodb_buffer_pool_size = 1G

A common guideline is 50–70% of total RAM on a dedicated database server, leaving enough headroom for the OS, connections, and other services running on the same VPS.

Max Connections

max_connections = 100

Set based on your application's actual concurrency needs — too high a value can allow memory exhaustion under load, since each connection consumes RAM.

Query Cache (Deprecated in Modern MySQL)

Query cache was removed in MySQL 8.0 and is generally not recommended even where still available in MariaDB, due to scalability issues under write-heavy workloads — leave it disabled unless you have a specific tested reason to enable it.

InnoDB Log File Size

innodb_log_file_size = 256M

Larger log files reduce checkpoint frequency, improving write performance for busier databases, at the cost of slightly longer crash recovery time.

Slow Query Log (For Finding Problem Queries)

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Logs any query taking longer than 2 seconds — the fastest way to find what's actually slowing your application down.

Applying Changes

sudo systemctl restart mysql

Or for MariaDB:

sudo systemctl restart mariadb

Analyzing Slow Queries

sudo tail -f /var/log/mysql/slow.log

Use EXPLAIN on any slow query to see whether it's using indexes efficiently:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Checking Current Buffer Pool Usage

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

Common Errors

"Too many connections" — either max_connections is too low for actual load, or the application isn't closing connections properly; investigate the application layer first.

MySQL fails to start after config change — check for typos and confirm the buffer pool size doesn't exceed available RAM:

sudo journalctl -u mysql

Best Practices

• Change one setting at a time and measure the real-world impact
• Never set innodb_buffer_pool_size so high it starves the OS or other services on the same VPS
• Use the slow query log to find and fix actual bottlenecks instead of guessing

FAQ

Do these settings apply to a shared VPS running other services too?
Yes, but be more conservative with innodb_buffer_pool_size on a VPS running MySQL alongside a web server and other apps — leave adequate RAM for everything else.

Related Articles

• How to Install and Secure MySQL 8 on Ubuntu & Debian
• How to Check VPS Resource Usage (CPU, RAM & Disk)
• How to Back Up and Restore MySQL/MariaDB Databases