Series: WordPress Performance on DirectAdmin (Rocky Linux 9)

Phase 3: Database & Object Caching — Part 12 of 30

MariaDB Performance Tuning for WordPress on Rocky Linux

Optimizing MariaDB is essential for WordPress performance on Rocky Linux 9, especially when managed with DirectAdmin. This guide covers practical, reproducible steps for sysadmins to safely tune MariaDB and validate results, with attention to DirectAdmin-managed environments.

Why Tune MariaDB for WordPress?

• WordPress sites are database-intensive; slow queries or misconfigured buffers increase latency.
• DirectAdmin deployments often host multiple WordPress sites per server—shared resources make tuning even more critical.
• Rocky Linux 9 defaults are conservative; tuning unlocks available RAM and CPU efficiency.

Checklist: Before You Start

• Backup databases: Always backup before database changes.
• Check current resource usage: CPU, RAM, disk I/O, InnoDB buffer pool.
• Know your workload: Number of WordPress sites, average daily visits, typical plugin load.
• Downtime warning: Some parameter changes (e.g., buffer pool size) require MariaDB restart, causing brief downtime.
• DirectAdmin specifics: MariaDB configuration is global, but performance impacts all DirectAdmin users/sites.

Step 1: Audit Existing MariaDB Configuration

Identify current settings and usage:

sudo mysql -e "SHOW VARIABLES LIKE 'innodb%';"
sudo mysql -e "SHOW VARIABLES LIKE 'query_cache%';"
sudo mysql -e "SHOW STATUS LIKE 'Threads%';"
sudo mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool%';"

On Rocky Linux 9 with DirectAdmin, the primary MariaDB config file is /etc/my.cnf and often includes /etc/my.cnf.d/server.cnf.

Step 2: Backup Before Tuning

Downtime/data loss warning: Configuration errors or MariaDB restarts can cause downtime or data loss if not backed up.

sudo mariadb-dump --all-databases --single-transaction --quick --lock-tables=false > /root/all-db-backup-$(date +%F).sql

Or use DirectAdmin’s Admin Backup/Transfer tool for a panel-based backup.

Step 3: Key MariaDB Settings for WordPress

Focus on parameters that impact WordPress query throughput and latency. For most DirectAdmin servers (2–8 CPU cores, 4–32 GB RAM), use these as starting points:

• InnoDB Buffer Pool Size (innodb_buffer_pool_size): For WordPress, set to 60–70% of your server’s available RAM if MariaDB is the main service.
• Max Connections (max_connections): Default is 151. For shared hosting, 100–200 is typical, but avoid setting much higher unless needed.
• Query Cache: Disabled by default in recent MariaDB versions. For WordPress, leave disabled (query_cache_type=0).
• Table Open Cache (table_open_cache): Increase to 1024–2048 for busy multi-site servers.
• Thread Cache Size (thread_cache_size): Set to 8–16 for moderate traffic.

Sample MariaDB Tuning Block

[mysqld]
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=2
max_connections=150
table_open_cache=2048
thread_cache_size=16
innodb_log_file_size=512M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
query_cache_type=0
query_cache_size=0

Edit /etc/my.cnf.d/server.cnf or /etc/my.cnf as appropriate. Make a backup first:

sudo cp /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf.bak.$(date +%F)

Step 4: Apply and Test Configuration Changes

1. Edit the configuration file with sudo vi /etc/my.cnf.d/server.cnf (or your preferred editor).
2. Add or adjust the [mysqld] block as above, modifying values for your RAM.
3. Restart MariaDB (downtime required):

   sudo systemctl restart mariadb

4. Check status:

   sudo systemctl status mariadb

5. Watch MariaDB logs for errors:

   sudo tail -f /var/log/mariadb/mariadb.log

Step 5: Validate Performance Improvements

Quick Functional Test

Use WP-CLI to verify WordPress can connect to the database:

cd /home/USERNAME/domains/DOMAIN/public_html
wp core verify-checksums

Replace USERNAME and DOMAIN as appropriate. This checks DB connectivity and some I/O.

Load Testing and Query Metrics

• Use curl or wrk to benchmark page load times:

  wrk -t4 -c20 -d30s https://example.com/

• Monitor MariaDB metrics:

  mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected';"
  mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';"

• Watch for high Threads_connected or Innodb_buffer_pool_reads (if this is high, buffer pool is too small).

Step 6: Ongoing Monitoring and Adjustment

• Periodically check MariaDB error logs:

  sudo tail -f /var/log/mariadb/mariadb.log

• Monitor slow query log to catch inefficient queries:

  [mysqld]
  slow_query_log=1
  slow_query_log_file=/var/log/mariadb/slow.log
  long_query_time=2

• After enabling, restart MariaDB and review slow queries:

  sudo tail -n 100 /var/log/mariadb/slow.log

Tuning for DirectAdmin Environments

• MariaDB runs as a global service—any tuning affects all hosted WordPress sites.
• PHP-FPM pools (per-user/website in DirectAdmin) interact with MariaDB via sockets or TCP; ensure max_connections can support peak PHP pool concurrency.
• If you adjust PHP-FPM pm.max_children in NGINX/Apache templates, also review MariaDB max_connections to avoid saturating the DB under load.
• For per-domain database issues, adjust PHP-FPM settings and WordPress object cache (see future phases), but MariaDB tuning remains global.

Undoing Changes and Rollback

1. If instability or errors occur, restore your original MariaDB config:

   sudo cp /etc/my.cnf.d/server.cnf.bak.$(date +%F) /etc/my.cnf.d/server.cnf
   sudo systemctl restart mariadb

2. Re-import database dumps if needed:

   sudo mariadb < /root/all-db-backup-YYYY-MM-DD.sql

Recommended Next Steps

• Review slow query logs and optimize problematic WordPress plugins or themes.
• Consider enabling MariaDB’s performance schema for deeper query analytics.
• Proceed to object caching and persistent cache integration (see upcoming phases in this series).

Note: This article offers general technical guidance. Validate all configurations in a safe environment before applying them to production.

Summary Checklist

• Backup all databases before tuning.
• Adjust innodb_buffer_pool_size and related settings for your RAM.
• Restart MariaDB and check logs for errors.
• Benchmark WordPress page loads and monitor MariaDB metrics.
• Regularly review slow query logs for ongoing optimization.

Previous: Redis vs Memcached for WordPress: Which One Should You Use?

Next: Why You Should Disable WP-Cron (And Use Linux Cron Instead)