Optimize MySQL Config for a Low-RAM VPS

A database on a small VPS competes with PHP workers, the web server, backups, and the operating system. Increasing a famous tuning variable without accounting for those neighbors can turn occasional slow queries into an out-of-memory restart.

This workflow starts with evidence, introduces a conservative configuration, and measures the result under the same workload. MySQL and MariaDB share many settings, but their versions and packaging differ: verify every directive against the server you actually run.

Separate configuration evidence from tuning folklore

Identify your server version, configuration include directories, current memory pressure, and application concurrency. Distribution defaults, panel-generated files, and manual overrides can all contribute settings. The last effective value may not come from the file you edited most recently.

Use the Regex Tester & Match Highlighter below to inspect a sanitized configuration excerpt. For example, the JavaScript pattern ^\s*(innodb_buffer_pool_size|max_connections)\s*=\s*([^#;\r\n]+) with multiline mode highlights relevant assignments and captures their values. It helps review repeated settings; it does not calculate an optimal configuration.

Treat this as text inspection, not a full parser. Included files, option groups, command-line arguments, and runtime changes affect the result. Do not paste production secrets, customer queries, or complete connection strings into troubleshooting tools. Verify the running values through SQL after every restart.

Establish a memory and concurrency budget

On a 2 GiB VPS running a small web application, start by measuring the database's neighbors during a representative busy period. Reserve memory for the kernel, filesystem activity, PHP or application workers, and backup jobs. Leave safety headroom instead of assigning every remaining byte to the database.

The InnoDB buffer pool is an important shared allocation, but it is not the process's entire memory footprint. Connections, execution buffers, temporary tables, metadata, and instrumentation also consume memory. Many buffers allocate on demand, so multiplying every maximum by every connection is an upper-bound warning rather than an exact forecast.

Capture an initial baseline with these commands, adapting the database service name and authentication method to your installation:

free -h
vmstat 1 5
sudo journalctl -k --since '24 hours ago' | grep -Ei 'oom|out of memory|killed process'
sudo mysql -e "SELECT VERSION();"
sudo mysql -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected','Threads_running','Max_used_connections','Created_tmp_disk_tables','Created_tmp_tables');"
sudo mysql -e "SHOW GLOBAL VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','max_connections','tmp_table_size','max_heap_table_size');"

These examples assume local socket administration works through sudo mysql. Some installations use the mariadb client or another documented administrative login. Never place a database password directly in a command argument merely to make an example work.

Use peak measurements, not a single quiet snapshot. Status counters generally accumulate since startup, and some can reset. Record uptime and calculate interval changes when comparing behavior. Also check application worker limits: allowing many more database connections than the application needs wastes safety margin without increasing useful throughput.

Apply a small, version-checked configuration

Find the included server configuration directory first. Ubuntu packaging often uses /etc/mysql/mysql.conf.d/ for MySQL and /etc/mysql/mariadb.conf.d/ for MariaDB, but containers and hosting panels can differ. Keep a copy of the original configuration and a tested backup before scheduling a restart.

This starting example is for a modest combined application-and-database server, not a recommendation for every 2 GiB instance:

[mysqld]
innodb_buffer_pool_size = 512M
max_connections = 40
tmp_table_size = 16M
max_heap_table_size = 16M
slow_query_log = ON
long_query_time = 1

A 512 MiB buffer pool deliberately leaves room for other services. Forty connections may be too few for your application or too many for a heavier query workload. Match it to measured demand and bounded application pools. Temporary-table settings are not universal hard caps on all temporary memory; modern MySQL's TempTable engine has additional controls and version-specific behavior.

Consult the MySQL documentation and MariaDB knowledge base for your exact version. Avoid copying obsolete query-cache settings into modern MySQL, or assuming a MariaDB-specific variable exists in MySQL. Do not change redo-log sizing or durability settings as an incidental part of memory tuning.

Keep durability enabled unless a documented business decision accepts data loss. Faster benchmarks achieved by weakening commit guarantees are not equivalent performance improvements. The same caution applies to disabling instrumentation before it has helped identify the bottleneck.

Validate, restart, and prove the effective values

Use the server's supported validation mechanism before restart. MySQL versions that support mysqld --validate-config can check configuration without starting normal service, but the invocation must load the same defaults as the service. Do not assume that flag exists in MariaDB or older MySQL releases.

Consult the service definition and version documentation, then restart during a controlled maintenance window with console access available. The following sequence illustrates MySQL service management; replace mysql with mariadb where that is the actual unit:

sudo systemctl restart mysql
sudo systemctl status mysql --no-pager
sudo journalctl -u mysql --since '10 minutes ago' --no-pager
sudo mysql -e "SHOW GLOBAL VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','max_connections','slow_query_log','long_query_time');"

If startup fails, restore the previous file and inspect the first actionable error before trying more changes. Unknown directives, invalid units, permissions, or duplicate overrides are different problems and need different fixes. Never delete data files to silence a configuration error.

Return to the regex inspection tool to compare sanitized old and new assignments. Remember that its JavaScript expression syntax is not interchangeable with every command-line regex dialect. Finish with runtime verification because a valid file alone does not prove that the running process loaded it.

Improve queries before expanding every limit

Replay the same bounded workload and compare latency and memory together. Track application response times, swap activity, database restarts, and connection errors. A bigger buffer pool that improves warm reads but triggers swapping during backups is not a successful change.

Locate the slow log through SHOW VARIABLES LIKE 'slow_query_log_file' and ensure log rotation is configured. Query text can contain sensitive values; limit permissions, retention, and access. Use EXPLAIN on representative statements, check index selection, and investigate repeated queries before raising memory ceilings.

Temporary tables on disk are evidence to investigate, not automatic proof that every temporary-table limit should increase. Sorting large results or returning unnecessary columns may be the real issue. Likewise, many sleeping connections often point to application pool settings rather than inadequate database capacity.

Coordinate database limits with the PHP-FPM worker guide. Before making storage assumptions, review the database storage test workflow. The PHP-FPM manual explains the application-side process manager that often shares this memory budget.

Key takeaways

• Budget whole-server memory before enlarging the InnoDB buffer pool.
• Bound connection concurrency and validate every directive for your exact database version.
• Change a small set of settings, preserve rollback, and verify effective values after restart.
• Optimize expensive queries and coordinate application workers before buying capacity or relaxing durability.