MySQL and MariaDB Tuning for a 1 GB or 2 GB VPS: The my.cnf Settings That Matter

On a 1 GB or 2 GB VPS, MySQL and MariaDB run fast and stable once a handful of my.cnf settings match the RAM you really have: innodb_buffer_pool_size, max_connections, the temporary table limits and performance_schema. This guide gives ready-to-use configs for both sizes, shows how to apply and check them safely on Debian, Ubuntu, Rocky Linux and AlmaLinux, and how to find the slow queries that no setting can fix.

Step 1: Check the Version and Current Memory Use

A few options exist only in MySQL or only in MariaDB, so first find out which server and version you run, and how much memory it uses right now.

mysql --version                 # MariaDB 11 (Debian 13): mariadb --version
mysql -e "SELECT VERSION();"
free -h
ps -o pid,rss,cmd -C mysqld,mariadbd    # RSS = memory really used, in KB

RSS is in kilobytes (350000 is about 340 MB). If available in free -h is already below about 150 MB under normal load, lower settings before raising any. Typical versions: Debian 12 ships MariaDB 10.11, Debian 13 MariaDB 11.8, and Ubuntu 22.04/24.04 offer MySQL 8.0 or MariaDB.

Step 2: Back Up Your Databases and Config First

A config change does not touch your data, but a mistake while testing can. Take a full dump and copy the config directory first. If the disk is nearly full, free space with Linux Disk Full: How to Find and Free Disk Space on a VPS.

df -h /                         # enough free space for the dump?
mkdir -p /root/db-backup
mysqldump --all-databases --single-transaction --routines --events \
  | gzip > /root/db-backup/all-$(date +%F).sql.gz
# MariaDB 11 (Debian 13): use mariadb-dump with the same options

cp -a /etc/mysql /root/db-backup/etc-mysql-$(date +%F)
# Rocky / AlmaLinux: cp -a /etc/my.cnf /etc/my.cnf.d /root/db-backup/
ls -lh /root/db-backup

--single-transaction copies InnoDB tables consistently without locking the site (MyISAM tables are not covered, so dump those at a quiet time).

Step 3: Find Where the Configuration Lives

Do not edit the large default file. Create your own small file in the include directory instead: it is read after the defaults, so its values win, and package updates never overwrite it.

In each case create zz-tuning.cnf in that directory. Files are read in alphabetical order, so the zz- prefix makes yours load last, and the name must end in .cnf.

Step 4: Recommended Settings for 1 GB and 2 GB

Start with the profile that matches your RAM. Both leave room for a web server and PHP on the same VPS.

1 GB VPS

# zz-tuning.cnf - 1 GB VPS, database shares the server with nginx/Apache and PHP
[mysqld]
performance_schema      = OFF
skip-name-resolve

innodb_buffer_pool_size = 256M
max_connections         = 40
thread_cache_size       = 8

tmp_table_size          = 32M
max_heap_table_size     = 32M

table_open_cache        = 400
table_definition_cache  = 400
key_buffer_size         = 16M

# 1 = safest (default), see Step 5
innodb_flush_log_at_trx_commit = 1

2 GB VPS

# zz-tuning.cnf - 2 GB VPS, database shares the server with nginx/Apache and PHP
[mysqld]
performance_schema      = OFF
skip-name-resolve

innodb_buffer_pool_size = 512M
max_connections         = 80
thread_cache_size       = 16

tmp_table_size          = 64M
max_heap_table_size     = 64M

table_open_cache        = 1000
table_definition_cache  = 800
key_buffer_size         = 32M

# 1 = safest (default), see Step 5
innodb_flush_log_at_trx_commit = 1

• innodb_buffer_pool_size: InnoDB's cache for data and indexes, the most important value. The famous "70-80% of RAM" is for database-only servers. With a web stack on the same VPS, start at about 25% of RAM; a database-only VPS can use about 512M on 1 GB and 1G on 2 GB.
• max_connections: each connection can allocate its own buffers (Step 10), so the default of 151 is risky on a small plan. A PHP site rarely needs more than its PHP-FPM workers plus a few for cron and admin tools.
• tmp_table_size and max_heap_table_size: the limit for in-memory temporary tables (GROUP BY, ORDER BY, DISTINCT). The smaller one applies, so keep them equal. Bigger values mostly raise worst-case RAM per query.
• table_open_cache and table_definition_cache: a few hundred suits a VPS with a few sites; raise them if Opened_tables keeps growing (Step 6).
• performance_schema = OFF: on by default in MySQL 8, where it can take more than 100 MB; already off in MariaDB. You lose some diagnostic views, a fair trade on a small plan.
• skip-name-resolve: no DNS lookup for each new connection, so logins do not hang when DNS is slow. Accounts must then use an IP address or localhost, not a host name.
• key_buffer_size: caches MyISAM indexes only (MariaDB default 128 MB); InnoDB sites need very little.

Before enabling skip-name-resolve, list the accounts; if a host is a name such as app.example.com, recreate that account with its IP (for example 203.0.113.10). On MySQL 8, also check how much space binary logs take:

mysql -e "SELECT user, host FROM mysql.user;"
mysql -e "SHOW BINARY LOGS;"      # MySQL 8: how much space binary logs use

Lines for your server version

Some options exist in only one server. Add the block that matches yours to the same file, under [mysqld], and adjust the values for 1 GB as the comments say:

# --- MySQL 8.0.30 or newer only (Ubuntu MySQL 8.0, MySQL 8.4) ---
# Redo log: default 100M is fine on 1 GB; 256M for write-heavy sites on 2 GB
innodb_redo_log_capacity   = 256M
# RAM cap for internal temporary tables: 64M on 1 GB, 128M on 2 GB
temptable_max_ram          = 128M
# Keep binary logs 3 days instead of the default 30
binlog_expire_logs_seconds = 259200

# --- MariaDB, or MySQL older than 8.0.30 ---
# Redo log: default 96M (MariaDB) is fine on 1 GB; 128M-256M on 2 GB
innodb_log_file_size       = 128M

Binary logs serve replication and point-in-time recovery; 30 days of them from a busy site can fill a 20 GB disk. If you use neither, MySQL 8 also accepts skip-log-bin.

Step 5: innodb_flush_log_at_trx_commit, the Honest Trade-off

This setting decides how much committed data you can lose if the VPS loses power or the kernel crashes. Choose it by what your site can afford to lose, not by benchmarks.

• 1 (default): the redo log is flushed to disk on every commit, so nothing committed is lost. Use it for shops, billing, user accounts and anything involving money.
• 2: written to the OS on each commit, flushed about once per second. An OS or VPS crash can lose up to about 1 second of transactions; a database-only crash loses nothing. Acceptable for blogs, caches and statistics.
• 0: written and flushed about once per second, so even a database crash can lose about 1 second. Rarely better than 2; not recommended.

Value 2 can make many small writes noticeably faster, but it trades durability for speed. If unsure, keep 1; grouping small writes into one transaction in your application is a safer gain.

Step 6: Apply Safely and Check the Result

Check the files, restart, and read the log straight away. Use the service name of your server.

# 1. Show the options the server will read (MariaDB: mariadbd --print-defaults)
mysqld --print-defaults
# MySQL 8.0.16+ only: check the option files without starting the server
mysqld --validate-config

# 2. Restart and read the log
# Service name: mariadb (MariaDB) | mysql (MySQL on Ubuntu) | mysqld (MySQL on Rocky/Alma)
systemctl restart mariadb
systemctl status mariadb --no-pager
journalctl -u mariadb -n 30 --no-pager

Then confirm the new values and look at the counters:

mysql -e "SHOW VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','max_connections','tmp_table_size','performance_schema','skip_name_resolve');"

mysql -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Uptime','Max_used_connections','Created_tmp_tables','Created_tmp_disk_tables','Opened_tables','Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests','Aborted_connects');"

Status counters are totals since the last start, so read them after at least a day of normal traffic (Uptime is in seconds).

• Max_used_connections close to max_connections: the application opens too many connections or runs too many PHP workers. Fix that before raising the limit.
• Created_tmp_disk_tables above about 25% of Created_tmp_tables: find those queries in the slow log (Step 7). An index usually helps more than a bigger tmp_table_size.
• Innodb_buffer_pool_reads above 1% of Innodb_buffer_pool_read_requests: the working set does not fit. Raise the buffer pool only if free -h shows spare available memory.
• Opened_tables still climbing fast after warm-up: raise table_open_cache in steps of about 200.

A rising Aborted_connects often means bots reaching port 3306; if it does not need to be public, block it as shown in Linux Firewall Setup: Configure UFW and firewalld on a VPS.

Step 7: Find Slow Queries with the Slow Query Log

Most "slow database" problems are a few bad queries, not the config. The slow query log records every query slower than long_query_time seconds.

# Turn the slow query log on now (until the next restart)
mysql -e "SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 1;"
mysql -e "SHOW VARIABLES LIKE 'slow_query_log_file';"

To keep it on after a restart, add these lines to your zz-tuning.cnf:

# Add to zz-tuning.cnf to keep it after restarts
[mysqld]
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time     = 1
# Rocky / AlmaLinux with MariaDB: /var/log/mariadb/mysql-slow.log

A new long_query_time applies to new connections only. Let the log collect a day of normal traffic, then summarize it:

# Top 10 query patterns by total time (MariaDB 11: mariadb-dumpslow)
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# Turn the log off again when you are done
mysql -e "SET GLOBAL slow_query_log = 0;"

mysqldumpslow groups similar queries, replacing numbers with N and strings with 'S', and shows how often each ran and how long it took. Start at the top: a query run 10,000 times at 0.2 seconds costs far more than one run once at 5 seconds. The log grows while it is on, so turn it off afterwards or make sure /etc/logrotate.d/ rotates it.

Step 8: Indexes and EXPLAIN Basics

Put EXPLAIN in front of a slow SELECT to see how the server runs it. The example uses a hypothetical orders table:

EXPLAIN SELECT id, total FROM orders
WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

SHOW INDEX FROM orders;

ALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at);

-- Run EXPLAIN again: type should change from ALL to ref, key = idx_customer_created
EXPLAIN SELECT id, total FROM orders
WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

In the output, type = ALL with key = NULL means a full table scan; ref, eq_ref or range means an index is used. A large rows estimate for a query that returns 20 rows points to a missing index, and Using filesort or Using temporary in Extra means extra sorting work.

• Index the WHERE columns first (those compared with = at the front), then the ORDER BY column.
• Avoid functions on indexed columns: WHERE DATE(created_at) = '2026-11-01' cannot use an index, but a range such as created_at >= '2026-11-01' AND created_at < '2026-11-02' can.
• Do not index every column: each index slows writes and uses buffer pool memory.

Step 9: Use mysqltuner as a Second Opinion

mysqltuner reads the server's counters and lists possible improvements. It is packaged for Debian and Ubuntu; on Rocky/AlmaLinux it is usually available from EPEL.

apt install mysqltuner          # Debian / Ubuntu
mysqltuner

Use it with care: run it only after 24 hours or more of normal traffic, and remember it does not know about PHP or the web server on the same VPS, so it may suggest a buffer pool that leaves them no room. Check every suggestion against free -h and change one setting at a time. Treat its output as a list of questions, not as instructions.

Step 10: Avoid the OOM Killer

When RAM runs out, the Linux OOM killer stops the process using the most memory, which is usually the database. Do a rough worst-case sum before you raise anything:

# Per-connection buffers (about 2-3 MB per connection with defaults)
mysql -e "SHOW VARIABLES WHERE Variable_name IN ('sort_buffer_size','join_buffer_size','read_buffer_size','read_rnd_buffer_size','thread_stack');"

# Rough worst case, 1 GB profile:
#   buffer pool 256 MB + overhead ~60 MB + 40 connections x ~2.5 MB ~100 MB
#   = ~420 MB, leaving ~500 MB for the OS, web server and PHP

# Has the kernel already killed the database?
journalctl -k | grep -i -E 'out of memory|killed process'

Real use is usually lower, because these buffers are allocated only when a query needs them, and each query can also use temporary tables up to tmp_table_size; plan for the peak. Never raise sort_buffer_size or join_buffer_size globally to several megabytes: multiplied by max_connections, they quickly exceed a 1 GB plan. If one report needs a large sort buffer, run SET SESSION sort_buffer_size = 8 * 1024 * 1024; in that connection only.

Add swap as a safety buffer for short peaks, as described in How to Add Swap Space on a Linux VPS. Swap prevents a crash but does not make a database that lacks RAM fast; if memory stays tight after tuning, a larger plan is the real fix, see How to Upgrade VPS RAM, SSD Disk or IP.

FAQ

Should I turn on the query cache?

No. MySQL 8 removed it, and in MariaDB (off by default) it becomes a locking bottleneck under concurrent writes. Cache in the application instead, for example with an object cache.

My changes do not show up after a restart. Why?

Usually the file is in a directory that is not included, its name does not end in .cnf, or a file read later sets the same option again. mysqld --print-defaults (MariaDB: mariadbd --print-defaults) shows the final list of options, and SHOW VARIABLES shows what is actually in effect.

Can I run a database on the 512 MB mini plan?

For a small site, yes, with swap and tight settings such as a 128M buffer pool, 20 connections and performance_schema off. MariaDB is usually the lighter choice at this size; busier sites are more comfortable with 1 GB or more.

If the database keeps getting killed or you are not sure which value to change, open a ticket at https://www.justg.com/submitticket.php with your server IP, your zz-tuning.cnf and the output of free -h. JUSTG offers 24/7 AI support by ticket, and engineers follow up on issues that need a person.