Detailed MySQL Memory Optimization Configuration for Low-RAM VPS

MySQL Memory Optimization Guide for Low-RAM VPS

On VPS instances with limited memory (e.g., 512MB or less), the default MySQL configuration can consume excessive RAM, leading to system instability or service crashes. This guide provides an optimized my.cnf configuration example tailored for low-memory environments, aiming to reduce memory usage while preserving core functionality.

Configuration File Details

Add the following settings to your MySQL configuration file (typically /etc/my.cnf or /etc/mysql/my.cnf) under the appropriate sections like [mysqld]. Always back up your original configuration file before making changes.

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-external-locking

# Key memory buffer settings (optimized for low RAM)
key_buffer_size         = 16K
max_allowed_packet      = 1M
table_open_cache        = 4
sort_buffer_size        = 64K
read_buffer_size        = 256K
read_rnd_buffer_size    = 256K
net_buffer_length       = 2K
thread_stack            = 64K

# Disable unused storage engines to save memory
skip-innodb
default-storage-engine = MyISAM

# Query cache settings (keep if query repetition is high)
query_cache_limit       = 256K
query_cache_size        = 2M
query_cache_type        = 1

[mysqldump]
quick
max_allowed_packet      = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer              = 8M
sort_buffer_size        = 8M

[myisamchk]
key_buffer              = 8M
sort_buffer_size        = 8M

[mysqlhotcopy]
interactive-timeout

Key Configuration Explanations

• key_buffer_size (16K): Size of the index buffer. Can be set very low for small-memory setups using MyISAM tables.
• table_open_cache (4): Limits the number of simultaneously open tables. Reducing this conserves file descriptors.
• skip-innodb: Disables the InnoDB storage engine. If your application doesn't use InnoDB, disabling it frees significant memory. Warning: This is irreversible and only suitable for pure MyISAM environments.
• Query Cache: Enabling query cache (query_cache_type=1) can improve performance for read-heavy, low-update sites (like blogs). For frequently updated data, set it to 0 to avoid maintenance overhead.

Applying and Verifying the Configuration

1. Backup the original config: cp /etc/my.cnf /etc/my.cnf.bak
2. Edit the config file: Use vi or nano to merge the optimized settings into the appropriate sections.
3. Restart MySQL: service mysql restart or systemctl restart mysqld.
4. Verify settings: Use mysqladmin status or run SHOW VARIABLES LIKE '%buffer%'; within MySQL to confirm changes.
5. Monitor memory usage: Check MySQL's RAM footprint with free -m or top.

Note: This is an aggressive memory-optimization profile suitable for small websites (e.g., personal blogs) with minimal databases. If your application requires transaction support or higher concurrency, consider upgrading your VPS RAM or performing InnoDB-specific tuning. Thoroughly test your site after applying these changes.