Optimize MySQL on Low-RAM VPS for Better Performance Without Upgrading

June 2026 · 7 min read

MySQL installed with default settings on a 1–2 GB RAM VPS often underperforms because many values are set to support diverse hardware. Tuning these settings to match your actual workload can make a significant difference.

The Most Critical Variable: innodb_buffer_pool_size

This is by far the most impactful setting. InnoDB uses the buffer pool to cache data and indexes in RAM to reduce disk I/O. The default is usually just 128MB, which is far too low.

General rule: Set it to 50–70% of total RAM.

# VPS with 2 GB RAM → set to 1G
# VPS with 4 GB RAM → set to 2G–3G
innodb_buffer_pool_size = 1G

Config File: /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# Buffer pool: 50-70% of RAM
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 2   # 1 per 1G

# Log file — larger size reduces checkpoint overhead
innodb_log_file_size = 256M

# Max connections — lower from default 151 if RAM is tight
max_connections = 100

# Thread cache — reduces overhead of creating new threads
thread_cache_size = 8

# Table cache
table_open_cache = 400

# Sort and join buffers — per-connection RAM
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M

# Tmp table size
tmp_table_size = 64M
max_heap_table_size = 64M

systemctl restart mysql

Enable Slow Query Log to Find Slow Queries

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1     # log queries taking more than 1 second
log_queries_not_using_indexes = 1

Analyze the log with mysqldumpslow:

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

Check Status and Buffer Pool Hit Rate

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_reads: disk reads
-- Innodb_buffer_pool_read_requests: total reads
-- Hit rate = (requests - reads) / requests * 100
-- Should be > 99%; if lower, buffer pool is still too small

MariaDB vs MySQL: What's Different?

MariaDB is a drop-in replacement for MySQL that many distributions prefer. Almost all config settings are compatible. MariaDB includes the Aria storage engine and sometimes optimizes better for specific workloads, but the core tuning principles are identical.

Check Actual RAM Usage by MySQL

Before adjusting buffer pool, understand what MySQL is consuming:

# Check RAM usage of MySQL process
ps aux | grep mysqld | awk '{print $6/1024 " MB"}'

# Check current values
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';

# Check theoretical maximum RAM MySQL could use
# (often exceeds actual RAM, but won't all be used simultaneously)
SELECT (@@key_buffer_size + @@query_cache_size + @@tmp_table_size
  + @@innodb_buffer_pool_size + @@innodb_log_buffer_size
  + @@max_connections * (
      @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
      + @@join_buffer_size + @@binlog_cache_size + @@thread_stack
      + @@tmp_table_size
  )) / 1024 / 1024 AS 'Est. Max RAM (MB)';

Find Missing Indexes via Slow Query Log

Queries without indexes are usually the slowest. Check the slow log:

# Analyze slow query log
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Check query execution plan
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- If type = ALL, it means full table scan — should add INDEX

-- Add an index
ALTER TABLE users ADD INDEX idx_email (email);

Config Values by RAM Size

Verify the Optimization is Working

After adjusting config and restarting, confirm the new values are loaded and measure results with real numbers, not just "feels faster":

-- 1. Confirm new buffer pool was loaded
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 2. Measure buffer pool hit ratio (should be > 99%)
SHOW ENGINE INNODB STATUS\G   -- Look for "Buffer pool hit rate"

-- 3. Find slowest queries (enable slow query log first)
SHOW VARIABLES LIKE 'slow_query_log%';
-- Then analyze with: mysqldumpslow -s t /var/log/mysql/slow.log | head

-- 4. Check actual connections used vs max
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

Common Issues & Troubleshooting

MySQL crashes after changing innodb_buffer_pool_size

This happens when you set the value too high for actual RAM. Check the error log:

tail -100 /var/log/mysql/error.log | grep -i "error\|warn\|oom"

# If you see "Cannot allocate memory", reduce the value
# Check available RAM first
free -h

MySQL uses more RAM than configured

This occurs because per-connection buffers allocate RAM per max_connections. If you have many concurrent connections, reduce max_connections if traffic isn't actually that high, or lower sort_buffer_size and join_buffer_size:

SHOW STATUS LIKE 'Max_used_connections';
-- See what you actually use, then adjust max_connections appropriately

Slow query log grows too large and consumes disk space

Set up logrotate or manually rotate logs:

mysqladmin flush-logs
# Or add to /etc/logrotate.d/mysql
/var/log/mysql/slow.log {
    daily
    rotate 7
    compress
    missingok
    notifempty
}

SHOW STATUS displays high Innodb_buffer_pool_reads

This means buffer pool is still too small — data frequently needs to be read from disk. Gradually increase innodb_buffer_pool_size and check hit rate after restart (give it 1–2 hours):

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- hit rate = (read_requests - reads) / read_requests * 100
-- Target: > 99%

Frequently Asked Questions

What should innodb_buffer_pool_size be for a 1 GB RAM VPS?

Set it to 512M (50% of RAM) as a safe starting point. This leaves RAM for the OS, PHP-FPM, and other processes. If MySQL is the only service on the VPS, you might increase it to 640–700M, but never exceed 70%.

Can MariaDB and MySQL use the same config?

Almost entirely. Parameter names are the same, config paths are the same. MariaDB has some extra parameters MySQL doesn't have (like aria_pagecache_buffer_size), but you can leave those at defaults if you're not using Aria tables.

Does slow query log consume a lot of disk space?

It depends on how many slow queries occur. Set long_query_time = 2 (2 seconds) instead of 1 second to reduce noise. Use logrotate to rotate logs daily to keep disk usage under control.

Should I enable query cache?

MySQL 8.0 removed query cache entirely. In MySQL 5.7 and MariaDB, query cache helps with read-heavy workloads where data rarely changes. For write-heavy workloads like WooCommerce, query cache can slow things down because the cache must be invalidated on every write.

Summary

Tuning innodb_buffer_pool_size alone often provides the biggest improvement. Do this first, then measure results with slow query logs and cache hit rates before adjusting other settings. Never guess — always use real data.