Tuning MySQL/MariaDB

An undersized MySQL or MariaDB server uses too much RAM or lets slow queries go unnoticed. This guide targets a HolyCloud production VPS: InnoDB buffer pool tuning, slow query logging, and reading metrics.

Prerequisites

• MySQL 8.x or MariaDB 10.6+ installed
• MySQL root access and sudo on the system
• Snapshot before changing configuration
• Understanding of the workload (OLTP, WordPress, etc.)

Assess available RAM

free -h
mysql --version

Rule of thumb for a VPS dedicated to the database: innodb_buffer_pool_size ≈ 50–70% of RAM if MySQL is the main service; otherwise 25–40% if Nginx, PHP, and Redis share the machine.

Example for 4 GB RAM, MySQL as primary service:

innodb_buffer_pool_size = 2G

InnoDB configuration

Typical file:

• Debian/Ubuntu: /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf

Backup:

sudo cp -a /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak

Add or change under [mysqld]:

[mysqld]
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
max_connections = 150

Warning: innodb_flush_log_at_trx_commit = 2 improves performance but reduces durability on power loss. Keep = 1 for critical financial data.

Apply:

sudo systemctl restart mysql
# or: sudo systemctl restart mariadb

Verify active variables:

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool%';"
mysql -e "SHOW ENGINE INNODB STATUS\G" | less

Enable the slow query log

In the same [mysqld] file:

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

Create the directory if needed and restart:

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo systemctl restart mysql

Hot enable (temporary):

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

Analyze slow queries

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

Or with pt-query-digest (Percona Toolkit):

sudo apt install -y percona-toolkit
sudo pt-query-digest /var/log/mysql/mysql-slow.log | less

In MySQL, inspect running queries:

SHOW FULL PROCESSLIST;
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

Indexes and query cache

Check tables without a primary key:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
AND table_type = 'BASE TABLE';

MySQL 8+ no longer uses the query cache; focus on indexes and the buffer pool.

Quick monitoring

mysqladmin status
mysqladmin extended-status | grep -i innodb

Troubleshooting

Need help?

For a cluster or replication, open a HolyCloud ticket with your load (QPS, database size).