How to Optimize MySQL/MariaDB on a Budget VPS: Memory, Caching, and Query Tuning

Running MySQL or MariaDB on a budget VPS (2–4 GB RAM, 1–2 vCPUs) requires careful resource management. Unlike a dedicated database server where you can throw hardware at performance problems, a budget VPS forces you to be efficient with memory, optimize caching, and write queries that don’t waste CPU cycles. This guide walks through practical, actionable steps to get the most out of your database on constrained hardware.

Before we dive in, if you’re still shopping for a VPS to run your database on, compare VPS providers on our comparison table to find plans with fast NVMe storage and dedicated vCPUs — both critical for database I/O performance.

1. Right-Size Your InnoDB Buffer Pool

The InnoDB buffer pool is the single most important memory setting in MySQL/MariaDB. It caches table data and indexes in memory so queries don’t have to read from disk. On a dedicated database server, you might set this to 70–80% of total RAM. On a budget VPS with 2–4 GB RAM, you need to be more conservative because the operating system, web server, PHP/Python workers, and other services all need memory too.

Rule of thumb for a budget VPS:

2 GB RAM VPS: Set innodb_buffer_pool_size to 512 MB–768 MB (leaves 1.2–1.5 GB for OS and applications)
4 GB RAM VPS: Set innodb_buffer_pool_size to 1–1.5 GB (leaves 2.5–3 GB for everything else)
8 GB RAM VPS: Set innodb_buffer_pool_size to 3–4 GB

To check your current buffer pool usage, run:

SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

If Innodb_buffer_pool_reads divided by Innodb_buffer_pool_read_requests is above 1–2%, your buffer pool is too small and you’re hitting disk too often. Increase it if you have spare RAM, or optimize your queries (see step 4) to reduce the working set size.

2. Configure Query Cache for Read-Heavy Workloads

The query cache stores the text of SELECT queries along with their result sets. If the same query runs again, MySQL returns the cached result without executing it. This can dramatically reduce CPU and disk I/O on read-heavy workloads like WordPress, content sites, or API endpoints.

In MySQL 8.0, the query cache was removed (it was deprecated due to scalability issues on multi-core systems). However, MariaDB still includes it, and on a budget VPS with 1–2 vCPUs, it works well.

MariaDB settings to enable query cache:

query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

query_cache_type = 1 enables caching for all SELECT queries (use SQL_NO_CACHE to opt out per-query)
query_cache_size = 64M is a safe starting point for a 1–2 GB VPS. Going higher can cause mutex contention on multi-core systems, but on a budget VPS it’s usually fine up to 128 MB.
query_cache_limit = 2M prevents caching of queries with huge result sets (which would bloat the cache quickly)

Monitor query cache efficiency with:

SHOW STATUS LIKE 'Qcache%';

If Qcache_hits is much lower than Qcache_inserts, your cache is too small or queries aren’t hitting it (e.g., because they use non-deterministic functions like NOW()). If Qcache_lowmem_prunes is high, increase query_cache_size.

3. Tune Connection Handling and Thread Cache

Each database connection consumes memory (typically 256 KB–2 MB per connection depending on buffer sizes). On a budget VPS with limited RAM, you want to minimize the number of simultaneous connections and reuse existing threads.

Key settings:

max_connections = 50
thread_cache_size = 8
thread_handling = one-thread-per-connection

max_connections = 50 is safe for a budget VPS. Each connection consumes memory for its session variables and temporary tables. If you need more, consider a connection pooler like ProxySQL or MariaDB MaxScale.
thread_cache_size = 8 caches up to 8 threads for reuse. This reduces the overhead of creating and destroying threads for each new connection.
thread_handling = one-thread-per-connection is the traditional model. For very high concurrency, consider pool-of-threads (MariaDB 10.5+), but for a budget VPS the default is fine.

Monitor active connections:

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';

If Threads_running regularly approaches max_connections, you need to either optimize queries (to make them faster) or increase the limit cautiously while monitoring memory usage.

4. Identify and Optimize Slow Queries

Slow queries are the biggest performance killer on a budget VPS. One badly-written query can saturate your CPU and block all other database operations. Enabling the slow query log is the first step to identifying them.

In your my.cnf or mariadb.cnf:

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

long_query_time = 1 logs any query taking longer than 1 second. On a 2 GB VPS, very few queries should take that long — if they do, something is wrong.
log_queries_not_using_indexes = 1 catches queries doing full table scans, which are extremely expensive on limited hardware.

Once you have the slow query log, use mysqldumpslow to summarize it:

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

This shows the 10 most frequent slow queries, sorted by count. Common fixes include:

Add missing indexes — Run EXPLAIN SELECT ... on slow queries to check if they’re using indexes. Add composite indexes for columns used in WHERE and JOIN clauses.
Optimize JOINs — Ensure joined columns have the same data type and are indexed. Use STRAIGHT_JOIN if the optimizer picks the wrong join order.
Limit result sets — Add LIMIT clauses to queries that don’t need all rows. Use pagination instead of loading thousands of records at once.
Use covering indexes — An index that contains all columns needed by a query (a covering index) eliminates table lookups entirely.

5. Additional Memory-Saving Tips

Reduce table cache size: Set table_open_cache = 400 and table_definition_cache = 400. Each cached table definition takes ~2 KB, so these settings use under 2 MB total — negligible but still good practice.
Disable performance schema if you’re not debugging: performance_schema = OFF. This saves 100–200 MB of RAM on MariaDB 10.x.
Use MyRocks storage engine (MariaDB 10.2+): RocksDB (MyRocks) uses write-optimized LSM trees that reduce write amplification and use less memory for buffer pools compared to InnoDB. For write-heavy workloads on a budget VPS, switching tables to MyRocks can save 30–50% memory.
Set binary log retention: If you don’t need point-in-time recovery, disable the binary log: skip-log-bin. If you do need it, set expire_logs_days = 3 to prevent logs from piling up and consuming disk space.

Sample my.cnf for a 2 GB VPS

[mysqld]
innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

max_connections = 50
thread_cache_size = 8
table_open_cache = 400
table_definition_cache = 400

slow_query_log = 1
long_query_time = 1

performance_schema = OFF
skip-log-bin

This configuration uses approximately 900 MB–1.1 GB of RAM for MySQL alone, leaving roughly 1 GB for the operating system and application stack on a 2 GB VPS. Adjust upward proportionally for a 4 GB or 8 GB VPS.

Choosing the Right VPS for Your Database

Even with perfect tuning, the underlying hardware matters. For MySQL/MariaDB on a budget, prioritize providers that offer:

NVMe storage — Database I/O is the #1 bottleneck. NVMe is 3–5x faster than SATA SSDs.
Dedicated vCPUs — Shared CPU allocation can cause query latency spikes when your neighbor’s workload surges.
Generous RAM — More RAM means a larger buffer pool, which means fewer disk reads.

Check the full specs on our VPS comparison page to find providers that meet these criteria. For budget-friendly options, InterServer offers VPS plans with NVMe storage and fixed pricing — ideal for running a tuned database without breaking the bank. For managed database hosting where you don’t want to handle the sysadmin work, Cloudways provides managed VPS hosting with pre-configured optimized stacks and automated backups.

Wrapping Up

Tuning MySQL or MariaDB on a budget VPS is about making informed trade-offs. Right-size your InnoDB buffer pool, enable query cache on MariaDB, keep connection counts low, and aggressively identify and fix slow queries. With these optimizations, you can comfortably run a production database on a 2–4 GB VPS — as long as you monitor and adjust as your workload grows.