WordPress Tips

Did you know that your website’s database represents the primary traffic jam when scaling? NOYO Web Development has used several strategies to prevent load on the database, such as making WordPress less dependent on the database, or handling pages prior to them arriving in WordPress.

When Great Caching Isn’t Enough: Database Replicas

However, you will ultimately need to scale your database in order to handle a high volume of read requests, aka select queries. An elastic architecture lets you increase capacity through the use of replicas.

The HyperDB plugin has provided support for scalable read replicas for WordPress for quite some time. This will let you scale out and implement certain strategies to how queries use the master and replica databases. For instance, you can configure your site to use the master database for requests concerning logged-in or administrative functionality. Then you can use replicas solely for anonymous traffic. This method ensures that content editors are able to use the site anytime, even during periods of sustained heavy load for normal content reading usage.

Preventing the Queries of Death

Scaling via database replication presumes that your queries are capable of a good level of efficiency. If your usage results in a content footprint of 100s of thousands or millions of posts, the WordPress default query builder may produce “queries of death:” these are requests that may take several seconds for the database to compute.

These received this apt nickname for a reason. They can swiftly and dramatically impact site performance, even causing downtime. Long running queries are demanding and frequently require the creation of a whole temporary table to compute the results. They slow down database performance for all queries and hinder PHP application capacity….not a good result.

Lethargic queries block the PHP application threads that spin them off. If this happens in high volume periods, these queries can overwhelm even a horizontally scalable elastic infrastructure. Soon all your PHP threads are lined up for slow query responses, and that pretty much takes your site offline.

Even with best practice architecture, you will need to review query performance consistently. Some programs have a built in capability which logs every slow query allowing you to identify the problem and implement a solution.

Challenges:

Query routing: You will need many options for distributing queries across your databases, but it is typically best to keep it simple at the start. While you may ultimately need customization, it’s best to decrease the amount of change and moving parts as you move into new architecture.

Replication lag: While under the best circumstances, replication lag is close to non-existent, real life doesn’t present too make ideal environments. You will need to ensure that your implementation can handle multi-second lag. In addition, you will need to watch for lag spikes, which indicate a problem.

Debuggability: When identifying troublesome queries, you need to look beyond the slow query log. You need to ensure that your system can reliably and safely replicate the situation order to unambiguously resolve the issues. This means taking time to isolate the site code the query has generated, so you can implement an alternative.

Regressions: As we pointed out above, the impact from a query of death can be instant. For large sites with hefty datasets, you must take into consideration the implications of new queries in addition to testing them prior to release.

NOYO Web Development has been assisting enterprise level companies improve their website performance for nearly 10 years. Contact us today to discover how we can help you too.