MySQL Configuration (my.cnf) Optimization Guide for 1GB RAM VPS

Introduction

This guide provides an optimized MySQL configuration file (my.cnf) template for VPS environments with 1GB of RAM. Please note: the original configuration was based on MySQL 5.6. Some parameters may be deprecated or behave differently in newer versions. It is crucial to adjust settings based on your specific server load, application requirements, and MySQL version. Never directly overwrite the configuration file on a production server without first creating a backup.

Core Configuration Parameters Explained

Below are explanations of key settings, with a focus on optimization for low-memory environments.

Basic & Connection Settings

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
basedir     = /usr/local/mysql
datadir     = /data/mysql/data
log-error   = /data/mysql/mysql_error.log
pid-file    = /data/mysql/mysql.pid
skip-name-resolve

• skip-name-resolve: Disables DNS reverse lookups, speeding up connection establishment.
• max_connections: A value of 1000 is likely too high for a 1GB VPS. Lower it (e.g., 150-300) based on actual concurrent connections to prevent memory exhaustion.
• open_files_limit & table_open_cache: Adjust according to the system's maximum file descriptor limit.

Memory & Cache Optimization

This is the core of tuning for 1GB RAM. Strictly control buffer sizes.

key_buffer_size = 4M
tmp_table_size = 16M
max_heap_table_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_stack = 192K

• key_buffer_size: Only for MyISAM tables. Can be set lower if using primarily InnoDB.
• Per-connection buffers (read_buffer_size, sort_buffer_size, etc.): Memory is allocated per connection. Total consumption = connections × sum of buffers. Set cautiously to avoid Out-Of-Memory (OOM) errors.
• Query Cache Note: The query_cache_size parameter is deprecated from MySQL 5.7.20 and removed in MySQL 8.0. Omit it for modern versions.

InnoDB Storage Engine Configuration

innodb_buffer_pool_size = 128M
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_thread_concurrency = 4

• innodb_buffer_pool_size: The most important cache for InnoDB, holding table data and indexes. 16M (in the old template) is too small. Adjusted to 128M for better performance while leaving memory for the OS and other processes on a 1GB VPS.
• innodb_flush_log_at_trx_commit = 1: Provides full ACID compliance but is the slowest for writes. Can be set to 2 for better performance if data consistency requirements can be slightly relaxed.

Binary Logging & Timeouts

log-bin = /data/mysql/binlog/binlog
binlog_format = mixed
expire_logs_days = 30
server-id = 1

interactive_timeout = 86400
wait_timeout = 86400

• expire_logs_days: Automatically purges old binary logs to prevent disk space exhaustion.
• Timeout settings (86400 seconds): Reduces connection establishment overhead but holds connections open longer. Adjust based on your application's connection pattern.

Important Updates & Considerations

1. MySQL Version Evolution: The original template was for MySQL 5.6. For MySQL 5.7 or 8.0, many parameters have changed. Always consult the official manual for your version.
2. Storage Engine: Modern applications should use InnoDB as the default engine for its transaction support, row-level locking, and crash recovery.
3. Memory Allocation Principle: On a 1GB VPS, the sum of all memory parameters (especially per-connection buffers) must be significantly less than available RAM. Reserve 20-30% for the OS and other applications.
4. Monitoring & Adjustment: Configuration is not set-and-forget. Monitor using SHOW GLOBAL STATUS and SHOW ENGINE INNODB STATUS, and optimize continuously based on actual load.

Summary

Configuring MySQL for a low-memory VPS is a balancing act between performance and resource consumption. This template is a starting point. The most important step is understanding each parameter's impact and testing adjustments based on your specific hardware, software version, and application load. Always back up before making changes, restart the MySQL service to apply them, and monitor system resource usage closely.