MySQL and MariaDB Performance Tuning for VPS Environments with Limited RAM

MySQL and MariaDB are the most popular database engines for VPS-hosted web applications, but their default configurations are designed for dedicated servers with abundant RAM. On a 1–4 GB VPS, the default values for buffer pools, cache sizes, and connection limits can quickly exhaust memory and trigger swapping. This guide provides a data-driven methodology for tuning MySQL/MariaDB on a RAM-constrained VPS, with specific configuration values for 1 GB, 2 GB, and 4 GB instances.

The Baseline: Why Defaults Are Wrong for a VPS

A default MySQL installation on Ubuntu 22.04 allocates approximately 600–800 MB of RAM through the InnoDB buffer pool (128 MB), query cache (disabled), temporary tables, and per-connection buffers. This is reasonable for a 4 GB server but dangerously high for a 1 GB VPS where the operating system, web server, and PHP-FPM all share the same memory. When MySQL exceeds available RAM, the kernel swaps, which degrades database performance by orders of magnitude — a single swap page fault can take 10–50 ms compared to 1–10 µs for a RAM access.

The first step is measuring your current memory usage:

# Check MySQL memory usage by performance_schema
SELECT * FROM sys.memory_global_total;

# Or use the process list
SELECT * FROM information_schema.PROCESSLIST;

# System-wide memory
free -h
# Look at the "available" column, not "free"

Key Configuration Parameters

1. InnoDB Buffer Pool Size

The InnoDB buffer pool is the single largest consumer of memory in MySQL. It caches table data and indexes. The optimal size depends on your working set (the data you access frequently) and available RAM. A common rule of thumb is 50–70% of available RAM, but on a VPS this must be reduced to leave room for the OS, web server, and PHP workers.

VPS RAM	Recommended Buffer Pool	Percentage	Max Working Set
1 GB	128–256 MB	12–25%	~200 MB
2 GB	512–1024 MB	25–50%	~800 MB
4 GB	1536–2560 MB	37–62%	~2 GB

Set in my.cnf:

[mysqld]
# 1 GB VPS: 256M
# 2 GB VPS: 768M
# 4 GB VPS: 2G
innodb_buffer_pool_size = 256M

Monitor buffer pool efficiency after setting:

SHOW ENGINE INNODB STATUS\G
# Look for "Buffer pool hit rate" - should be above 99%
# If below 95%, increase the buffer pool size if RAM permits

2. Per-Thread Buffers

Each MySQL connection allocates a set of per-thread buffers. These multiply by the number of concurrent connections, so they must be kept small on a VPS.

[mysqld]
# Sort buffer: used for ORDER BY and GROUP BY operations
# Default: 256K, keep at 256K for small VPS
sort_buffer_size = 256K

# Join buffer: used for joins that cannot use indexes
# Default: 256K, keep small
join_buffer_size = 256K

# Read buffer: sequential scans
# Default: 128K
read_buffer_size = 128K

# Read random buffer: random reads
read_rnd_buffer_size = 256K

# Temp table: for complex queries creating temporary tables
tmp_table_size = 32M
max_heap_table_size = 32M

3. Connection Limits

Each connection consumes RAM. On a VPS with limited memory, cap the maximum connections and reduce the connection timeout to prevent runaway connection accumulation.

[mysqld]
# 1 GB VPS: 20-50 connections
# 2 GB VPS: 50-100 connections
# 4 GB VPS: 100-200 connections
max_connections = 50

# Close idle connections after 60 seconds (default: 28800 = 8 hours!)
wait_timeout = 60
interactive_timeout = 120

# Thread cache: reuse thread structures instead of creating new ones
thread_cache_size = 8

Calculate your maximum connection memory consumption:

# Approximate memory per connection:
# sort_buffer (256K) + join_buffer (256K) + read_buffer (128K) + read_rnd_buffer (256K)
# + thread stack (~256K) + tmp table (up to 32M but only for active queries)
# = ~1.2 MB per idle connection, up to ~33 MB for active connections with temp tables
# With 50 connections: ~60 MB idle, ~1.6 GB worst-case with all temp tables

4. Log File Size and Flush Method

The InnoDB redo log size affects write performance and crash recovery time. On a VPS, keep log files small enough for fast recovery but large enough to avoid log contention:

[mysqld]
# InnoDB log file size (total = innodb_log_file_size * innodb_log_files_in_group)
# 1 GB VPS: 128M * 2 = 256M total
# 2 GB VPS: 256M * 2 = 512M total
# 4 GB VPS: 512M * 2 = 1G total
innodb_log_file_size = 128M
innodb_log_files_in_group = 2

# Flush method: O_DIRECT avoids double buffering (page cache + buffer pool)
# This is recommended for VPS environments
innodb_flush_method = O_DIRECT

# Flush log at every transaction commit (safe but slower)
# For most web applications, this is the right balance
innodb_flush_log_at_trx_commit = 1

5. Query Cache (Disable on Modern MySQL)

The query cache is deprecated in MySQL 5.7 and removed in MySQL 8.0. In MariaDB, it still exists but is typically counterproductive on multi-core VPS because it requires a global mutex that serializes query execution. Disable it:

[mysqld]
# MySQL 5.7+ (deprecated)
query_cache_type = 0
query_cache_size = 0

# MariaDB (still exists)
query_cache_type = 0
query_cache_size = 0

Use application-level caching (Redis, Memcached) instead of the query cache for better performance and scalability.

Complete Configuration Templates

1 GB VPS (e.g., WordPress, small application)

[mysqld]
innodb_buffer_pool_size = 256M
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT
max_connections = 50
wait_timeout = 60
sort_buffer_size = 256K
join_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 256K
tmp_table_size = 32M
max_heap_table_size = 32M
thread_cache_size = 8
query_cache_type = 0
query_cache_size = 0
innodb_flush_log_at_trx_commit = 1

2 GB VPS (e.g., WooCommerce, moderate traffic)

[mysqld]
innodb_buffer_pool_size = 768M
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
max_connections = 100
wait_timeout = 120
sort_buffer_size = 256K
join_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
tmp_table_size = 64M
max_heap_table_size = 64M
thread_cache_size = 16
query_cache_type = 0
query_cache_size = 0
innodb_flush_log_at_trx_commit = 1

4 GB VPS (e.g., high-traffic application, multiple sites)

[mysqld]
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_flush_method = O_DIRECT
max_connections = 200
wait_timeout = 180
sort_buffer_size = 512K
join_buffer_size = 512K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
tmp_table_size = 128M
max_heap_table_size = 128M
thread_cache_size = 32
query_cache_type = 0
query_cache_size = 0
innodb_flush_log_at_trx_commit = 1

Monitoring and Tuning After Configuration

After applying the configuration, monitor these metrics to verify the settings are working:

# Buffer pool hit rate (should be > 99%)
SHOW ENGINE INNODB STATUS\G
# Look for: "Buffer pool hit rate 1000 / 1000"

# Current connections vs max
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

# Slow queries
SHOW GLOBAL STATUS LIKE 'Slow_queries';

# Temporary tables created on disk (should be near zero)
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

# Sorting using files (should be minimal)
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';

If Created_tmp_disk_tables is high, increase tmp_table_size and max_heap_table_size. If Sort_merge_passes is high, increase sort_buffer_size. If the buffer pool hit rate is below 99%, increase innodb_buffer_pool_size if RAM allows.

Additional Memory-Saving Techniques

• Use MariaDB instead of MySQL: MariaDB uses significantly less memory for the same workload. On a 1 GB VPS, MariaDB can save 50–100 MB compared to MySQL 8.0.
• Disable performance_schema: The performance schema consumes approximately 200 MB of memory on MySQL 8.0. Disable it with performance_schema = OFF in my.cnf if you do not need detailed instrumentation.
• Reduce InnoDB buffer pool instances: Set innodb_buffer_pool_instances to 1 for buffer pools under 1 GB. Each instance adds memory overhead for bookkeeping structures.
• Enable InnoDB page compression: For tables with compressible data (text, JSON), enable innodb_page_compression. This trades CPU for memory, which is a favorable trade-off on a RAM-constrained VPS.

What to Avoid on a Low-RAM VPS

• Do not set innodb_buffer_pool_size too high: If the buffer pool causes swapping, performance drops catastrophically. Measure your actual hit rate and adjust conservatively.
• Do not increase per-thread buffers without monitoring: A single large sort buffer is fine, but multiply it by 50 connections and it becomes 12.5 MB of unnecessary allocation.
• Do not enable the query cache on MySQL 8.0: It does not exist. On MariaDB, keep it disabled.
• Do not use MyISAM tables: MyISAM uses table-level locking and is not crash-safe. InnoDB is the default for good reason.

Tuning MySQL for a VPS is about understanding your workload’s working set and allocating memory accordingly. The configuration templates above are starting points — measure, adjust, and remeasure. When choosing a VPS for your database workload, check out the best VPS performance specs to find providers that offer the RAM-to-price ratio that matches your database’s working set. A database with a 2 GB working set on a 4 GB VPS will outperform the same database on a 2 GB VPS with NVMe storage, because the data lives in RAM where it belongs.