How to setup WordPress high availability hosting using Percona XtraDB cluster
Your website is only as fast as its slowest component. In a WordPress server infrastructure, database servers are often the slowest. This is because databases depend on good old hard disks instead of the super fast server memory to fetch data.
In high traffic WordPress websites, database servers are known to incur intermittent downtime and performance bottlenecks. Today we’ll see how a Percona XtaDB cluster can be used to deliver WordPress high availability hosting.
Database bottlenecks in WordPress websites
WordPress stores all its posts, pages, comments, settings and plugin information in its database. So, for each information displayed on the website, several database accesses need to be made.
In our role as Outsourced hosting support specialists for web hosts, we maintain WordPress websites of many online businesses that range from small eCommerce shops to large market analytics companies.
While we’ve been able to minimize database requests using caching systems (such as Varnish, APC, Memcached, etc.), about 40% of site visits still required access to the database server.
In these websites, the following top causes we’ve noted for database performance issues.
- Unoptimized database tables – Data gets written and deleted from tables all the time. Over time, this can lead to empty spaces in database files, and makes the database size very large. It results in slower query execution.
- Unoptimized load distribution – On busy websites, there can be many simultaneous users on the website. If all visitors are served using a single database server, the queries will be put on a queue, leading to slow access speeds.
- Heavy WordPress plugins – Some plugins such as “dynamic price listing” or “related posts” executes several database queries for a single page. This increases the database load, and results in slow page load speed.
- Unoptimized database configuration – Depending on the number and complexity of database queries, several settings such as heap memory size, sort table size, etc. need to be adjusted. If not, queries will take longer to execute.
How to make WordPress database faster?
Every website visit leaves a record in the WordPress database. Even a small business website issues thousands of database operations per day.
So, it is natural that the database size changes, its memory requirements changes, and its processing overhead increases. If left unattended, these factors can quickly add up to form a performance bottleneck.
To prevent the formation of a bottleneck, we use several methods:
- Periodically audit the database for performance issues. We manually check the optimization levels of each table, check if the database settings (such as query_cache size) are adequate for the changed table sizes, and see if there are any anomalies in operations (such as slow queries, aborted queries, etc.). Any noted issues are fixed on the spot.
- Optimize the tables regularly to minimize database size. A large database quickly brings down the site performance. We audit database table size at least once a week, clean out logs and session tables that consume space. The tables are then optimized to compress data, and minimize file size.
- Monitor the database for serious performance issues. We keep an eye on several critical database performance metrics (such as query_count, query_time, queue_wait, etc.) that gives us an idea about database health. If we detect an anomaly, we quickly find the root cause and fix the issue before it can affect website performance.
- Identify and fix heavy site code that’s present in themes, plugins, etc. When we note a slow database query, we backtrace it to the WordPress function that sent in the query. We then work with the site developers or our WordPress developers to streamline the code.
- Change WordPress server architecture as the site grows. The performance and uptime needs of a small site is different from a large one. As the site traffic increase, it might be necessary to split the database load into multiple servers. A great way to spread the load is to use database clusters. We’ve found Percona XtraDB cluster to be a great option for this purpose.