The WordPress Architecture has always been criticised for its considerable reliance on the underlying database.
This dependancy gives WordPress tremendous flexibility allowing it to be installed in a variety of hosting environments however, at the same time it creates unique scalability challenges especially for large complex setups where a considerable number of plugins are involved.
We have been brought in on a number of occasions to help resolve various problems with large scale Wordpress portals and in usually all the cases its the database which is dragging the entire site down.
Here are the standard performance tuning techniques that people usually deploy once the site is in trouble. Most of these are knee jerk reactions that people take on immediately to mitigate the issue given that their site is now down or incredibly slow and its hurting them financially.
The premise being that my hosting provider sucks because they have too many hosts on one machine and I need more ‘Power’ to run my portal now. Hence people usually switch providers in the following order:
Deploy Performance Plugins
There are a ton out there. Your mileage my vary and they usually implement one or all of the following:
Other more Advanced options include:
Depending on the situation the above mentioned solutions may mitigate the issue you are having until your traffic or data increases or may not solve the problem at all.
Usually once the above mentioned solutions have been exhausted without the desired result frustration starts to set in and one starts wondering how the other sites on WordPress work so well and yours does not.
I think it is best to realize at this point that you first have to measure and isolate which layer in the application stack is responsible for giving you sleepless nights. A good tool for measuring your application’s performance is NewRelic. Here is a sample screenshot from the NewRelic Console for one of our clients’ WordPress portals:
As can be seen the majority of the time (over 90%) is being taken up by the database. Optimizing any other layer will only lead to marginal benefits at best. However, if the database can be optimized we can realize significant performance gains. Armed with this information we can now dig deeper into the underlying queries and the scripts that are executing them.
Here is a snapshot of the queries that are currently taking the most time. You can also get these details from the MySQL Slow Query Log.
More Details including the scripts that execute the queries:
More details about the time spent during script execution:
It is clear from these reports that queries that involve the wp_postmeta table seem to be the slowest. Why is that? What is inside the wp_postmeta table that is slowing down the response. A quick count of the two tables shows the following numbers:
mysql> select count(*) from wp_posts; +----------+ | count(*) | +----------+ | 223572 | +----------+ 1 row in set (0.20 sec) mysql> select count(*) from wp_postmeta; +----------+ | count(*) | +----------+ | 2018257 | +----------+ 1 row in set (2.44 sec)
So the wp_postmeta table is about 9 times larger than the wp_posts table! Putting it another way – On average every post inserted into the database leads to an additional nine rows in the post_meta table. Suddenly now it starts making sense why the post_meta related queries take their sweet time in responding to the requests. How do we fix this?
Well not all bloats are created equal. We need to dig deeper and figure out what kind of extra information is being added by each post.
In this particular case we found:
We implemented different solutions to solve different problems. Here is what we did:
Well there are several: