How to optimize MariaDB for WordPress

Quick answer: Set innodb_buffer_pool_size so your working set fits in RAM (25–50% of server RAM on a shared web+DB box), enable the slow-query log, and clean WordPress-side bloat: oversized wp_options autoload data, post revisions and transients. Database latency is behind most “slow admin” complaints.

Overview

WordPress on InnoDB lives or dies by the buffer pool: when hot data fits in it, queries answer from RAM. The second lever is finding the few bad queries — usually a plugin — via the slow log. The third is WordPress hygiene: the autoload rows in wp_options are loaded on every request, and plugins love stuffing them.

Before you start

• RAM picture from free -h, database size from:

  sudo mariadb -e "SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024) AS mb FROM information_schema.tables GROUP BY table_schema;"

• A fresh backup before config changes.

Step-by-step guide

1. Create /etc/mysql/mariadb.conf.d/60-wordpress.cnf (example for a 4 GB VPS running the full stack):

   [mysqld]
   innodb_buffer_pool_size = 1G
   innodb_log_file_size    = 256M
   max_connections         = 100
   slow_query_log          = 1
   slow_query_log_file     = /var/log/mysql/slow.log
   long_query_time         = 1

   then sudo systemctl restart mariadb.
2. After a day, read the slow log and fix or replace the offending plugin/query (add an index only when you understand the query).
3. Shrink autoload bloat:

   sudo -u www-data wp db query "SELECT ROUND(SUM(LENGTH(option_value))/1024) AS autoload_kb FROM wp_options WHERE autoload IN ('yes','on');"
   sudo -u www-data wp db query "SELECT option_name, LENGTH(option_value) len FROM wp_options WHERE autoload IN ('yes','on') ORDER BY len DESC LIMIT 15;"

   Anything from long-removed plugins can have autoload switched off or be deleted. Healthy total: under ~800 KB.
4. Cap revisions and clear expired transients in wp-config.php / cron:

   define('WP_POST_REVISIONS', 10);

   sudo -u www-data wp transient delete --expired
5. Verify buffer-pool efficiency after a warm day: SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'; — disk reads should be a tiny fraction of read requests.

Common issues

• Buffer pool set too big: MariaDB gets OOM-killed under PHP load — on a combined box leave most RAM for PHP + OS cache.
• Slow log fills the disk: keep long_query_time at 1s+ and rotate the log.
• Millions of transient rows: a misbehaving plugin — delete expired ones and identify the writer before it regrows.

When to contact support

Database tuning is in your domain on an unmanaged VPS, but when IO itself is the bottleneck (high disk wait with a right-sized buffer pool), ask about NVMe plans — database workloads benefit from NVMe more than anything else.

Frequently asked questions

What is the most important MariaDB setting for WordPress?

innodb_buffer_pool_size — it decides how much of your database lives in RAM. When the working set fits, most queries never touch the disk and the whole site feels faster.

What are autoloaded options and why do they matter?

Rows in wp_options flagged autoload are loaded on every single request. Plugins accumulate them for years; totals above roughly 1 MB measurably slow all pages, so audit and trim them.

How do I find which plugin makes the database slow?

Enable the MariaDB slow-query log with long_query_time=1 and run the site for a day — the log names the exact queries, and Query Monitor in wp-admin attributes them to a plugin.

Related articles

• How to troubleshoot a slow WordPress admin panel
• How to improve WordPress speed on a VPS
• How to optimize PHP for WordPress
• How to install MariaDB or MySQL

Ready to get started? Order a WordPress VPS at Cloud2Y →