[ Back to log ]

Optimizing MySQL/MariaDB to prevent high memory usage

Database

Running a database on a $5 VPS usually ends in tears. The Linux Out of Memory (OOM) killer will quietly terminate MySQL when it gets hungry, taking your site down with it.

Out of the box, MySQL assumes it has plenty of RAM. You have to force it to run lean.

The low-memory configuration

Edit your database configuration file (usually /etc/mysql/my.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf).

Add or modify these values under the [mysqld] section:

[mysqld]
performance_schema = off
max_connections = 50
key_buffer_size = 16K
max_allowed_packet = 1M
thread_stack = 64K
table_open_cache = 4
sort_buffer_size = 64K
net_buffer_length = 2K
query_cache_size = 8M
query_cache_type = 1

# InnoDB Settings
innodb_buffer_pool_size = 32M
innodb_log_file_size = 32M
innodb_log_buffer_size = 8M

Restart the service to apply the changes:

sudo systemctl restart mariadb  # or mysql

Why these settings work

Here is exactly what you are changing and why it saves RAM.

General settings

  • performance_schema = off: Turn this off. It eats hundreds of megabytes of RAM just by existing. Unless you’re actively debugging database bottlenecks, you don’t need it on a small server.
  • max_connections = 50: Every concurrent connection requires its own memory buffers. The default is usually 151. Dropping this to 50 stops a sudden traffic spike from exhausting your memory.
  • thread_stack = 64K: This limits the memory allocated for each database thread. 64K is tiny, but it’s enough for standard web apps.

Buffers and caches

  • key_buffer_size = 16K: This caches indexes for the old MyISAM engine. You are probably using InnoDB, so you can shrink this to almost nothing.
  • max_allowed_packet = 1M: Caps the memory used for handling large queries or payloads.
  • table_open_cache = 4: Controls how many tables stay open in memory. A smaller cache saves RAM but might cause slightly more disk reads.
  • sort_buffer_size = 64K: Queries that sort data (ORDER BY) use this buffer. Keeping it small saves memory per connection.
  • net_buffer_length = 2K: The starting size of connection buffers. It grows if needed, but starting small saves memory on idle connections.
  • query_cache_size = 8M: A small pool to cache exact SELECT queries. (Note: MySQL 8.0 removed this, but MariaDB still uses it).

InnoDB settings

InnoDB relies heavily on memory to be fast.

  • innodb_buffer_pool_size = 32M: This is where InnoDB caches table data. The default is 128MB. Dropping it to 32MB frees up a ton of RAM, but forces the database to read from the disk more often.
  • innodb_log_file_size = 32M: The size of the redo logs. Smaller logs save memory overhead.
  • innodb_log_buffer_size = 8M: The buffer for writing data to disk. 8MB is a good compromise between memory usage and write performance.

A warning

To be clear: these settings trade speed for survival. Your database won’t crash, but it will rely heavily on disk I/O. If your site gets slow, you just need to pay for more RAM and increase the innodb_buffer_pool_size.