Optimizing Your MySQL my.cnf for High-Traffic WordPress

The Out-of-the-Box MySQL Trap

When you install MySQL or MariaDB via apt-get, the default configuration file (/etc/mysql/my.cnf) is designed to run efficiently on a $5 server with 1GB of RAM.

If you deploy a massive WooCommerce store on a $40 server with 8GB of RAM, MySQL will not utilize that extra RAM automatically. It will continue bottlenecking as if it were on the tiny server. You must tell it to scale up.

Step 1: Locating the Config File

SSH into your server and edit the configuration file with nano:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# OR on MariaDB
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Step 2: The InnoDB Buffer Pool (The Most Important Setting)

The InnoDB Buffer Pool is the memory area where InnoDB caches table data and indexes. If your entire database fits inside the buffer pool, queries execute from RAM (lightning fast) instead of disk (slow).

The golden rule is to set innodb_buffer_pool_size to 50% - 60% of your total server RAM (leaving the rest for PHP and the OS).

[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4 

(Tip: Set instances to 1 for every 1GB in your pool).

Step 3: Managing Concurrent Connections

By default, MySQL only allows 151 simultaneous connections. If you suddenly get a traffic spike, visitor #152 gets a database error.

max_connections = 400
wait_timeout = 600
interactive_timeout = 600

Step 4: The Query Cache (MySQL 5.7 / MariaDB only)

Note: MySQL 8 removed the query cache entirely. If you use MariaDB, it is still highly effective.

query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 64M

Restart the database engine to apply changes:

sudo systemctl restart mysql

---

---

Evaluating the Changes

Use a tool called MySQLTuner. It's a Perl script that analyzes your running database and tells you exactly what variables need adjusting.

wget http://mysqltuner.pl/ -O mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

Wait at least 24 hours after changing your my.cnf before running MySQLTuner, as it relies on cumulative statistics.

FAQs

Q: I increased my buffer pool and the server crashed! Why?
A: You allocated more RAM than your server actually has free. Run the free -m command. If your server only has 2GB of RAM and PHP is using 1GB, allocating a 1.5GB buffer pool will cause Linux's Out-Of-Memory (OOM) killer to terminate MySQL instantly.