MySQL powers many WordPress sites, web apps, and databases. On a low‑spec VPS, MySQL can become a bottleneck. Slow queries, high memory usage, and disk I/O issues are common.
This guide covers practical optimizations for MySQL on a typical VPS (1-4 GB RAM).
1. Determine Your Current Usage
Before tuning, know what's happening. Run these commands.
mysqladmin status
mysqladmin extended-status
Check query cache hit rate, connection count, and slow queries. Also watch your system's memory and swap usage with htop or free -m.
2. Use the Right Storage Engine
InnoDB is the default and recommended engine for most workloads. It supports transactions, row‑level locking, and crash recovery. MyISAM is only useful for read‑only tables. Ensure your tables use InnoDB:
SELECT ENGINE, COUNT(*) FROM information_schema.TABLES GROUP BY ENGINE;
3. Adjust MySQL Configuration File
The main config file is /etc/mysql/my.cnf or /etc/my.cnf. Start with these recommended changes (adjust based on your RAM).
[mysqld]
# InnoDB settings
innodb_buffer_pool_size = 1G # Set to ~70% of your RAM (e.g., 1G for 2GB VPS)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2 # Better performance, slight durability trade‑off
innodb_flush_method = O_DIRECT
# Query cache (disable in MySQL 8.0+, removed)
# For MySQL 5.7, set query_cache_size = 0 (deprecated)
# Connection settings
max_connections = 150
thread_cache_size = 8
table_open_cache = 400
# Temp table settings
tmp_table_size = 64M
max_heap_table_size = 64M
# Slow query log (for debugging)
slow_query_log = 1
long_query_time = 2
After editing, restart MySQL and verify changes:
sudo systemctl restart mysql
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
4. Optimize Your Queries
Enable the slow query log (see above). Review it with mysqldumpslow:
sudo mysqldumpslow /var/log/mysql/mysql-slow.log
Common fixes: add indexes, avoid SELECT *, use appropriate JOINs, and break complex queries.
To add an index: CREATE INDEX idx_name ON table(column);
5. Use a Caching Plugin for WordPress
If running WordPress, install a caching plugin like Redis Object Cache or W3 Total Cache. Offload queries to Redis, reducing database load.
Install Redis server:
sudo apt install redis-server php8.2-redis -y
sudo systemctl restart php8.2-fpm
Then install and configure the Redis Object Cache plugin in WordPress.
6. Partition Large Tables
If you have tables with millions of rows, consider partitioning by date or key. This allows MySQL to scan only relevant partitions.
ALTER TABLE large_table PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026)
);
7. Keep MySQL Updated
Newer versions (MySQL 8.0+) have better performance and security. Check your version:
mysql --version
On Ubuntu, update to MySQL 8.0 or MariaDB 10.11 if still on 5.7.
8. Monitor and Tune Regularly
Use tools like mysqltuner for ongoing recommendations:
wget http://mysqltuner.pl/ -O mysqltuner.pl
perl mysqltuner.pl
Run it monthly to spot new issues.
Next Steps
After optimizing MySQL, monitor your site's load times and database response. Even small improvements can drastically reduce page load times.
Need a VPS to practice on? Check our recommended VPS providers.