MariaDB Tuning: The `my.cnf` Guide for High-RAM VPS
Most Linux distributions ship with a default MariaDB configuration designed for a 512MB Raspberry Pi, not a production server. If you have a 4GB+ VPS and haven't touched /etc/mysql/my.cnf, you are wasting 80% of your hardware.
Optimizing your database isn't about magic; it's about memory management. We need to tell MariaDB to stop reading from the slow disk and start using the RAM you paid for.
1. The Golden Rule: InnoDB Buffer Pool
This is the single most important variable in MySQL/MariaDB. It determines how much data and how many indexes are cached in RAM.
How It Works
If your database size is 2GB and your Buffer Pool is 128MB (default), the database has to read from the disk constantly. This increases I/O Wait and slows down your site.
The Fix: Set the Buffer Pool to hold your entire dataset (if you have the RAM).
- For Dedicated DB Servers: Set to 70-80% of total RAM.
- For Shared Web/DB Servers: Set to 50% of total RAM (save room for Nginx/PHP).
[mysqld]
# Example for a 4GB VPS hosting Nginx + MariaDB
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 22. Write Performance: Log File Size
The innodb_log_file_size controls how much data holds in memory before flushing it to the disk.
- Too Small (Default 48MB): MariaDB flushes to disk constantly, causing write spikes.
- Too Big: Recovery time after a crash takes longer.
For a write-heavy WordPress site (WooCommerce), bump this up significantly.
innodb_log_file_size = 512M3. Connection Limits & Timeouts
Don't just set max_connections to 10,000. Each connection consumes RAM buffers. If you run out of RAM, the Linux OOM Killer will crash the database.
- max_connections: 150 is usually enough for a high-traffic site if your PHP scripts are efficient.
- wait_timeout: Lower this to kill "sleep" connections that hang around eating memory.
max_connections = 200
wait_timeout = 600
interactive_timeout = 6004. Disable the Query Cache
Stop using query_cache_size. In modern multi-core servers, the Query Cache causes a "mutex lock." It actually slows you down because the database locks the cache every time a write occurs.
MariaDB 10.5+ disables it by default, but check to be sure.
query_cache_type = 0
query_cache_size = 0Conclusion: Restart and Monitor
Changes to my.cnf only take effect after a restart: systemctl restart mariadb. Watch your RAM usage with htop immediately after.
Need a VPS with serious RAM?
Stop squeezing your database into tiny instances. Get high-performance NVMe VPS Hosting designed for database throughput.
