MySQL/MariaDB Tuning on VPS — Database Performance Guide

MySQL and MariaDB power millions of websites and applications worldwide, but a fresh installation using default configuration values is rarely optimal — especially on a VPS with limited RAM and CPU resources. Tuning the right variables in my.cnf to match available resources and the actual workload of your application can improve query throughput by anywhere from 2x to more than 10x. This guide covers practical MySQL/MariaDB tuning for VPS servers, complete with real commands, recommended values, and a production-ready configuration example.

The most important principle in database tuning is that there is no universal "best" configuration. The right settings depend on total RAM, number of concurrent connections, and whether your workload is read-heavy (e.g., a blog or content site) or write-heavy (e.g., an e-commerce platform or analytics system). The recommendations in this guide are calibrated for mid-range VPS servers (2–8GB RAM) running a standard LAMP stack with WordPress or a similar PHP application.

Understanding my.cnf Before You Start

The main MySQL/MariaDB configuration file is typically located at /etc/mysql/my.cnf or /etc/my.cnf. On Ubuntu and Debian systems it may be at /etc/mysql/mysql.conf.d/mysqld.cnf. Always verify which file is actually loaded:

mysql --verbose --help | grep "my.cnf"
# Or check directly
mysqld --verbose --help 2>/dev/null | grep -A 1 "Default options"

Before making any changes, create a dated backup of the current configuration:

sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup.$(date +%Y%m%d)

The configuration file is divided into sections. The [mysqld] section controls the server daemon behavior and is where most tuning parameters belong. The [mysql] and [client] sections configure the command-line client tools. Placing a setting in the wrong section will have no effect — double-check the section header when editing.

Collecting a Baseline Before Tuning

Good tuning always starts with measurement. Collect a baseline so you can compare performance before and after changes. The following commands provide the essential metrics:

# Show current variable values
mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'innodb%';"
mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'key_buffer%';"

# Show global status counters
mysql -u root -p -e "SHOW GLOBAL STATUS;"

# Show currently running queries
mysql -u root -p -e "SHOW PROCESSLIST;"

# Show detailed InnoDB engine status
mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G"

Also check OS-level resource usage:

# Available and used RAM
free -h

# Top processes by memory consumption
ps aux --sort=-%mem | head -10

# MySQL data directory size
du -sh /var/lib/mysql/*

The Most Important Setting — InnoDB Buffer Pool

For any server using InnoDB tables (which is the recommended default for all modern MySQL/MariaDB setups), innodb_buffer_pool_size is by far the most impactful configuration variable. It defines how much RAM MySQL reserves to cache table data and indexes in memory. The default of 128MB is far too small for any database larger than a few hundred megabytes.

Guidelines for setting innodb_buffer_pool_size:

• MySQL is the only significant service on the VPS → allocate 70–80% of total RAM
• Running a LAMP stack (Apache + PHP-FPM + MySQL) → allocate 50–60% of total RAM
• VPS with 1GB RAM or less → limit to 256M–512M to keep the OS and other services functional

A complete InnoDB configuration block for a 4GB RAM VPS running LAMP:

[mysqld]
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2   # 1 instance per 1GB of buffer pool is recommended
innodb_log_file_size = 256M        # Larger = better write performance, longer crash recovery
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2  # 1=fully ACID, 2=faster (risk: lose last second on crash)
innodb_flush_method = O_DIRECT     # Bypass OS page cache to avoid double-buffering

Connection Settings — Handling Concurrent Users

MySQL's default of 151 max_connections is adequate for small sites but insufficient for high-traffic deployments or servers hosting multiple WordPress sites. However, raising the connection limit is not free — each idle connection consumes roughly 1–2MB of RAM, and active connections can use significantly more depending on per-connection buffer sizes. Calculate your memory budget carefully before raising this value.

[mysqld]
max_connections = 200              # Suitable for a 4GB RAM LAMP VPS
thread_cache_size = 50             # Reuse threads to reduce creation overhead
thread_stack = 192K
back_log = 128                     # Connection queue depth when all threads are busy

# Per-connection buffers (allocated on demand, multiplied by active connections)
sort_buffer_size = 2M              # Used when sorting result sets without an index
read_buffer_size = 1M
read_rnd_buffer_size = 2M
join_buffer_size = 2M

A rough formula to estimate maximum MySQL memory usage:

# Max memory ≈ innodb_buffer_pool_size + (max_connections × per_connection_memory)
# Example: 2048M + (200 × 7M) = ~3.4GB
# This must not exceed total VPS RAM minus ~1GB reserved for the OS

Enabling and Analyzing the Slow Query Log

The Slow Query Log is the most effective tool for identifying problematic queries. No amount of buffer pool tuning will compensate for a query that performs a full table scan on a million-row table. Enable the slow query log first, let it collect data for a day or two, then analyze it to find real bottlenecks.

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1               # Log queries that take longer than 1 second
log_queries_not_using_indexes = 1 # Also log queries that skip indexes
min_examined_row_limit = 100      # Skip logging queries that examine fewer than 100 rows

After restarting MySQL and letting it collect data, use mysqldumpslow to analyze the log:

# Top 10 queries by total execution time
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Top 10 most frequently executed queries
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# Top 10 queries by total lock time
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log

Once you identify a slow query, use EXPLAIN to examine the execution plan:

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

-- If type=ALL or rows is very high, an index is missing.
-- Add an appropriate composite index:
ALTER TABLE orders ADD INDEX idx_user_status_date (user_id, status, created_at);

Recommended Settings by VPS RAM Size

The following table provides starting-point values for common VPS RAM configurations running a LAMP stack. Adjust further based on your specific workload and monitoring data.

Table Cache, Temporary Tables, and Query Cache

Every time MySQL opens a table it uses a file descriptor and loads the table definition. When table_open_cache is too low, MySQL constantly closes and reopens files — a significant source of I/O overhead on databases with many tables, such as a WordPress multisite installation.

[mysqld]
table_open_cache = 800            # Increase if Opened_tables grows quickly in SHOW STATUS
table_definition_cache = 1400    # Cache for table schema definitions

# Temporary tables — used for GROUP BY / ORDER BY without covering index
tmp_table_size = 64M              # In-memory temp table size before spilling to disk
max_heap_table_size = 64M         # Must match tmp_table_size

# Detect if tmp_table_size needs to increase:
# SHOW GLOBAL STATUS LIKE 'Created_tmp%';
# If Created_tmp_disk_tables / Created_tmp_tables > 5%, increase both values

Query Cache note: MySQL 8.0 removed the Query Cache entirely. If you are running MariaDB and your workload is heavily read-biased (much more SELECT than INSERT/UPDATE/DELETE), a modest Query Cache can help. However, for any mixed-workload application like WordPress, the Query Cache often hurts performance due to cache invalidation overhead on every write:

[mysqld]
# MariaDB only — read-biased workloads
query_cache_type = 1
query_cache_size = 64M            # Keep below 128M to avoid fragmentation
query_cache_limit = 2M

# MySQL 8.0+ or write-heavy workloads — disable entirely
query_cache_type = 0
query_cache_size = 0

Index Optimization — The Highest-ROI Tuning

No server-side configuration change can compensate for missing or poorly designed indexes. Adding the right index to a frequently queried table often delivers a larger performance improvement than doubling RAM. Always combine my.cnf tuning with a thorough index review.

Finding Tables That Need Better Indexes

-- Tables getting the most full scans (requires sys schema)
SELECT * FROM sys.schema_tables_with_full_table_scans
ORDER BY rows_full_scanned DESC LIMIT 10;

-- Indexes that are never used
SELECT * FROM sys.schema_unused_indexes;

-- Redundant or duplicate indexes
SELECT * FROM sys.schema_redundant_indexes;

Designing Effective Composite Indexes

-- For the query: WHERE user_id=? AND status=? ORDER BY created_at DESC
-- Place equality filter columns first, then the sort column
ALTER TABLE orders ADD INDEX idx_user_status_date (user_id, status, created_at);

-- Verify the index is being used
EXPLAIN SELECT * FROM orders
  WHERE user_id=1 AND status='pending'
  ORDER BY created_at DESC;
-- Look for type=ref or type=range and key=idx_user_status_date

Keep in mind that every index adds overhead to INSERT, UPDATE, and DELETE operations because MySQL must update all relevant indexes on each write. Only create indexes that are actually needed based on real query patterns from the Slow Query Log.

Complete Production-Ready my.cnf for a 4GB RAM VPS

The following configuration is suitable for a 4GB RAM VPS running a standard LAMP stack (Nginx or Apache + PHP-FPM + MySQL/MariaDB) hosting WordPress or similar PHP applications:

[mysqld]
# === Basic Settings ===
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
bind-address = 127.0.0.1

# === Character Set ===
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# === InnoDB Settings ===
default_storage_engine = InnoDB
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2

# === Connection Settings ===
max_connections = 200
max_allowed_packet = 64M
thread_cache_size = 50
thread_stack = 192K
back_log = 128

# === Cache Settings ===
table_open_cache = 800
table_definition_cache = 1400
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
join_buffer_size = 2M

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

# === Binary Log (disable if replication is not needed) ===
# skip-log-bin

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

Verifying Tuning Results After Restart

After restarting MySQL with the new configuration, allow the server to warm up for at least 1–2 hours under normal load before drawing conclusions. The InnoDB buffer pool starts empty and gradually fills with the hot data set — performance improves as the cache warms up.

# Buffer pool hit rate — target above 99%
mysql -u root -p -e "
SELECT
  (bpr.VARIABLE_VALUE /
   (bpr.VARIABLE_VALUE + bpd.VARIABLE_VALUE)) * 100 AS hit_rate_pct
FROM
  performance_schema.global_status bpr,
  performance_schema.global_status bpd
WHERE bpr.VARIABLE_NAME='Innodb_buffer_pool_read_requests'
  AND bpd.VARIABLE_NAME='Innodb_buffer_pool_reads';"

# Check thread cache effectiveness
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Threads_created';"
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Connections';"
# thread_cache_hit_rate = 1 - (Threads_created / Connections)

# Run MySQLTuner for automated recommendations
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl --user root --pass yourpassword

Frequently Asked Questions

What should innodb_buffer_pool_size be set to on a 2GB RAM VPS?

For a VPS primarily running InnoDB, set innodb_buffer_pool_size to 50–70% of total RAM. On a 2GB VPS running MySQL alone, 1G is a good starting point. If Apache/Nginx and PHP-FPM also run on the same server, reduce it to 512M–768M to leave room for other services. Setting this value above available physical RAM causes the OS to swap to disk, which is dramatically slower than RAM and can make performance worse than the default configuration.

What is the Slow Query Log and should it be enabled permanently?

The Slow Query Log records queries that exceed the long_query_time threshold (default 10 seconds; recommended 1–2 seconds to catch real bottlenecks). The overhead of keeping it enabled on production is minimal and generally acceptable. Leave it on for ongoing performance analysis and turn it off once the system is well-tuned and stable. It is invaluable for catching regressions after application updates that introduce new unindexed queries.

What is the difference between MyISAM and InnoDB? Which should I use?

InnoDB supports Transactions, Foreign Keys, and Row-level Locking — making it the right choice for OLTP workloads like e-commerce and CMS platforms. It also provides automatic crash recovery. MyISAM performs full table locks on every write, which causes severe slowdowns under concurrent usage, and it has no built-in crash recovery. InnoDB is the recommended default for all new tables. Only consider MyISAM if you have a legacy application that relies on MyISAM-specific full-text search features not available in the version of InnoDB you are running.

How can I check how much memory MySQL is using and verify tuning results?

Use SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%' to inspect buffer pool utilization. If Innodb_buffer_pool_pages_free stays near zero, the pool is full and should be enlarged. Compare Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads to calculate the cache hit rate — aim for above 99%. Use SHOW PROCESSLIST to see currently running queries, and mysqladmin status to monitor Queries per second and other counters in real time. The MySQLTuner script provides an automated analysis with actionable recommendations.