MySQL and MariaDB Optimization: Buffer Pool, Indexes, Slow Query Log

Your MySQL or MariaDB instance is running on a VPS, but queries that used to be snappy now feel sluggish. Before blaming the hardware, something like an 8GB RAM VPS from a provider such as thueVPS, look inside the database configuration. The three most impactful knobs you can turn are the InnoDB buffer pool, index design, and the slow query log. This guide walks through each on Ubuntu 24.04 with MySQL 8.0 (commands also apply to MariaDB 10.11+). You will leave with a baseline you can apply immediately to any Linux VPS.

Prerequisites

• A VPS running Ubuntu 24.04 (or Debian 12) with MySQL 8.0 or MariaDB 10.11 installed.
• A non-root sudo user.
• Root or sudo access to edit /etc/mysql/mysql.conf.d/mysqld.cnf (MySQL) or /etc/mysql/mariadb.conf.d/50-server.cnf (MariaDB).
• Basic familiarity with mysql CLI and SQL.

Why Buffer Pool Size Matters More Than You Think

Every read and write to InnoDB tables goes through the buffer pool, an in-memory cache of data pages and indexes. If the pool is smaller than your working dataset, the database spends I/O time fetching pages from disk, which is orders of magnitude slower. For a dedicated database VPS, a good starting point is 70, 80 % of available RAM. On a system with 4 GB, set it to 3 GB. On a 2 GB machine, 1.5 GB is a safe ceiling. Never leave the default (128 MB) on a production server, that is the #1 cause of unnecessary disk churn.

Step 1, Adjust the InnoDB Buffer Pool Size

Check the current value

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

You will see something like 134217728 (128 MB) if unmodified.

Calculate the target

Determine total RAM:

free -m

Multiply by 0.75 for a typical dedicated VPS. If you run other services (nginx, PHP), use 0.6.

Apply the change

Open the MySQL configuration file:

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

Find or add the line under [mysqld]:

innodb_buffer_pool_size = 3G

For a 4 GB VPS, 3G. For 2 GB, 1.5G. Do not assign more than 80 % unless you know other processes can live with the rest.

Verify after restart

sudo systemctl restart mysql
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

The value will reflect your new setting. To confirm the pool is warm after some traffic:

mysql -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';"
mysql -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool_reads';"

Ideally, Innodb_buffer_pool_reads (disk reads) is a tiny fraction of Innodb_buffer_pool_read_requests (total reads). A ratio under 1:1000 is healthy.

Step 2, Enable and Tune the Slow Query Log

Without the slow query log, you are debugging in the dark. This log captures every query that exceeds a given duration (by default 10 seconds). Lower the threshold to 1 second or even 0.5 seconds to catch any query that a user would feel.

Enable via configuration

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

Add these lines:

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

• long_query_time = 1, queries taking more than 1 second are logged.
• log_queries_not_using_indexes = 1, also logs full table scans (even if fast). This is gold for index tuning.

Restart and check

sudo systemctl restart mysql
sudo ls -lh /var/log/mysql/mysql-slow.log

If the file exists and grows, the log is active. Read it with mysqldumpslow after a day of traffic:

sudo mysqldumpslow -s t /var/log/mysql/mysql-slow.log | head -20

The -s t flag sorts by query time. The top entries are your biggest performance enemies.

What to look for

Frequent SELECT without a WHERE clause, or queries that sort entire tables. Each of these is a candidate for index addition or query rewrite.

Step 3, Design Indexes That Actually Work

Adding indexes blindly is as bad as having none. The slow query log tells you which tables and columns are the source of pain. The rule: index columns used in WHERE, JOIN, and ORDER BY clauses, but keep the number of composite indexes low.

Analyze a problematic query

Suppose your slow log shows:

SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;

Run EXPLAIN to see the execution plan:

EXPLAIN SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;

If type is ALL (full table scan) and rows is high, you need a composite index on (status, created_at).

Create the index

CREATE INDEX idx_status_created ON orders(status, created_at);

Then re-run EXPLAIN, the type should change to ref or range, and rows shrinks dramatically.

Remove unused indexes

Indexes are not free; each slows down INSERT/UPDATE and consumes disk space. Use the sys schema (MySQL 8.0+) to find unused ones:

SELECT * FROM sys.schema_unused_indexes;

Consider dropping any index that has not been used for weeks:

DROP INDEX old_unused_index ON your_table;

Troubleshooting

MySQL refuses to start after changing buffer pool size

Check the error log:

sudo journalctl -u mysql --since "5 minutes ago" | grep -i error

Most common cause: you set innodb_buffer_pool_size larger than available RAM. Reduce it to a safe value (e.g., 2.5G instead of 3.5G on a 4 GB system).

Slow query log is empty even with traffic

Verify the file path is writable by the MySQL user:

sudo ls -la /var/log/mysql/
sudo chown mysql:mysql /var/log/mysql/mysql-slow.log

Also confirm long_query_time is in seconds (not microseconds). A value of 0.5 works, but 0.5 means half a second, not 5 seconds.

Adding an index does not speed up the query

Run ANALYZE TABLE to update index statistics, then re-run EXPLAIN:

ANALYZE TABLE orders;
EXPLAIN SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;

If the plan still shows a full scan, the condition may not be selective enough (e.g., 90 % of rows have status='pending'). In that case, reconsider the query logic or partition the table.

FAQ

Related articles

• Configure swap and optimize memory on a low-ram VPS
• Security audit and hardening with Lynis on a VPS
• Install PostgreSQL on a Debian 12 VPS
• Self-hosting n8n for workflow automation on a VPS