Database performance and read replicas, in plain terms

When a WordPress or Drupal site slows down and adding web servers does not help, the database is usually why. Every web server talks to the same database, so extra web capacity just means more requests waiting in the same line. The encouraging news is that most database slowness comes from a small number of well-known causes, and many of them are fixable without new hardware.

Signs the database is the bottleneck

• Pages are slow even at low traffic, especially admin pages and search.
• Database CPU is high while web servers are relatively idle.
• The slow query log keeps showing the same few queries.
• Errors such as "Error establishing a database connection" (WordPress) or PDO connection errors (Drupal) appear under load.
• A specific feature, such as a filtered listing or a report, is slow while the rest of the site is fine.

The usual culprits

Quick checks

For WordPress, this shows how much autoloaded data is loaded on every request (newer WordPress versions use several autoload values, so the query covers them):

wp db query "SELECT ROUND(SUM(LENGTH(option_value))/1024) AS kb FROM wp_options WHERE autoload IN ('yes','on','auto-on','auto');"

A few hundred kilobytes is a good sign. Several megabytes means cleanup is overdue. The Query Monitor plugin shows slow and duplicate queries on any page.

For Drupal, the Webprofiler module shows the queries behind a page, and Views has a setting to display its SQL during preview. On both platforms, the database's slow query log shows the real offenders under real traffic.

What a read replica is

A read replica is a copy of the database that continuously receives changes from the primary. Queries that only read data can be sent to the replica, taking load off the primary, which keeps handling all the writes. For sites where most activity is reading, which is most websites, replicas can add a lot of capacity.

What a replica will not fix

• Slow queries. A query that takes four seconds on the primary takes four seconds on the replica. Fix the query first.
• Write-heavy workloads. Every write still goes to the primary.
• Replication lag. A replica can be a fraction of a second, occasionally more, behind the primary. An editor who saves and immediately reloads may see the old version if that read went to the replica. Applications must send reads that follow a write to the primary.

Both platforms can use replicas: Drupal supports replica connections in its database settings, and WordPress uses a database drop-in that routes reads and writes. Neither does it automatically, and both need testing.

Order of operations

1. Add an object cache, so repeated queries never reach the database.
2. Fix the worst queries and add missing indexes.
3. Clean up autoloaded options, old revisions, and log tables.
4. Right-size the database server.
5. Add read replicas when read volume, not query quality, is the limit.

One more habit helps: after any large content import, plugin installation, or new feature, look at the slow query log again. Most database problems arrive with a specific change, and they are much easier to fix in the week they appear than a year later.

How eWay manages databases

eWay runs WordPress and Drupal databases on managed services (Amazon RDS or Aurora on AWS, Azure Database for MySQL on Azure) with Multi-AZ availability, automated backups, and monitoring of connections, slow queries, storage growth, and replication lag. eWay reviews slow query logs, tunes queries and indexes, puts Redis object caching in front, and adds read replicas when the workload calls for them, configuring the application so reads after writes stay consistent.

What to include in a support ticket

• The severity you would assign, P1 to P4 (see severity levels and response times)
• Which pages or features are slow and roughly how slow
• Whether it happens all the time or only under load
• Any recent plugin, module, or content import

eWay customers can open a ticket in the support portal or email support@ewaycorp.com at any time. See how to request support for all the options. Related reading: object caching with Redis and database failover and high availability for Drupal. More on our WordPress and Drupal pages.