How to Optimize MySQL my.cnf on a Small VPS (1-2 GB RAM) Without Crashing

Check the current mysqld RSS and how much of total RAM that is:

ps -o pid,rss,cmd -p $(pidof mysqld)
free -m

The rss value is in KB. Divide by 1024 to get MB. On a 1 GB VPS with default MySQL 8, expect something between 380 and 500 MB — well above what is needed for the real load of a small site.

Look at real InnoDB buffer pool usage — whether the default 128 MB pool has headroom or is undersized:

mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages%';"

How to read it:

• pages_total = pool size in 16 KB pages
• pages_free near zero = pool is full, MySQL is recycling pages
• pages_data / pages_total = how much is in productive use

If pages_free is high (>30% of total) and the server has days of uptime, you can shrink the pool without harm. If it is near zero and Innodb_buffer_pool_reads (not read_requests) grows fast, the pool is undersized.

Check performance_schema — alone it eats 70-100 MB:

mysql -u root -p -e "SELECT SUM(MEMORY_USED)/1024/1024 AS mb FROM sys.x\$memory_global_by_current_bytes;"

On a 1 GB VPS with no external profiling tool reading from it, this is the first candidate to switch off.

Create the override file:

sudo nano /etc/mysql/mysql.conf.d/99-hostini-tuning.cnf

On MariaDB: /etc/mysql/mariadb.conf.d/99-hostini-tuning.cnf. The 99 ensures this file is read last — overriding any default.

Paste this base configuration for a 1 GB RAM VPS:

[mysqld]
# InnoDB — the heart of MySQL
innodb_buffer_pool_size = 256M
innodb_buffer_pool_instances = 1
innodb_log_buffer_size = 8M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2

# Connections — a small VPS cannot handle 150
max_connections = 50
thread_cache_size = 8
table_open_cache = 200
table_definition_cache = 400

# Performance schema — off on 1 GB
performance_schema = OFF

# Binary log — off if you do NOT use replication
disable_log_bin
# On MariaDB use: skip-log-bin

# Slow query log for diagnosis
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Temp tables — in RAM up to this limit
tmp_table_size = 16M
max_heap_table_size = 16M

# Sort/join buffers — per connection, watch max_connections
sort_buffer_size = 256K
join_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 256K

For a 2 GB VPS, tweak only these lines:

innodb_buffer_pool_size = 512M
max_connections = 100
performance_schema = ON
performance-schema-instrument='memory/%=COUNTED'

Save (Ctrl+O, Enter, Ctrl+X) and validate the syntax before the restart:

sudo mysqld --validate-config

If any error appears, the service will not come up after the restart. Fix it before continuing. Expected output: no lines (silence is success).

Restart MySQL and check the status:

sudo systemctl restart mysql
sudo systemctl status mysql --no-pager

On MariaDB: sudo systemctl restart mariadb. If the status shows active (running), move on. If it failed, read the reason in sudo journalctl -u mysql -n 50 — it is almost always a typo in the .cnf.

Confirm the values that are now in effect:

mysql -u root -p -e "SHOW VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size', 'max_connections', 'performance_schema', 'log_bin', 'tmp_table_size');"

The values should match what you put in 99-hostini-tuning.cnf. If any still shows the old default, the file is not being read — check the path and permissions (it must be 644 root:root).

Measure RAM consumption again:

ps -o pid,rss,cmd -p $(pidof mysqld)
free -m

Expectation post-tuning on a 1 GB VPS: mysqld at ~280-340 MB (a ~30% drop). On 2 GB with performance_schema on: ~500-580 MB. Wait a few minutes with real traffic before the final measurement — the pool warms up and stabilizes.

Run SHOW STATUS after 1-2 hours of real use and confirm the buffer pool is not choking:

mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';"

Signs of health: pages_free > 5% of pages_total, reads growing slowly compared to read_requests (hit ratio > 99%). If reads spikes, bump the buffer pool by 64 MB and re-test.