Optimizing WordPress database performance

I’ve been working with a plugin that was mentioned on the wp-hackers list last week, called debug queries, to optimize the performance on some of my sites, including this one. The plugin was pretty easy to use, and I’ve improved a bit on it. I’ve sent the patch to Frank, who authored the original plugin, and he was kind enough to immediately update the plugin.

When you’re logged in as an admin, this plugin now shows the following info in an HTML comment tag in the footer of your pages:

  • the query that was executed
  • the time it took to execute that query
  • the function path that led to this query, with the last function being the most likely to have created the query

I’ve written down some of the things I encountered that you may encounter as well are the following:

Empty options

A lot of WordPress internal settings and plugin settings are stored in the options table. Values in this table take two forms: those which are autoloaded (which is the default) and those which are not. One of the first queries to the database that WordPress does once it starts loading a page, is the query that loads all options with autoload set to “yes”. Hence, any call the an option after that, that happens on multiple pages, is weird. So when I saw this type of query happen a lot:

SELECT option_value FROM wp_options
  WHERE option_name = 'headspace_global' LIMIT 1

I was wondering what was happening. It turned out that that headspace_global option simply didn’t exist, so it can’t be autoloaded. This wasn’t happening with just this option, but with several options. The solution is pretty simple: just add the option as an empty option to your database. This might break some plugins though, and if it does, you should contact the plugin author…

This is actually something plugin authors have to take note of: doing a get_option to check whether a certain option is set or not, is actually more expensive than just setting the option and leaving it empty…

Adding an option to the database is done like this in your MySQL database:

insert into wp_options ( option_id, option_name, autoload)
  values ( '0', 'test', 'yes')

It should be noted that I’ve emailed John Godley, who has built and maintains HeadSpace2, and this’ll be fixed in the next release.

Non cached queries

This happens, of course. If you see a certain query being performed more than once during the pageload, that’s something to optimize. Check what is causing the queries, and then either solve them, or email the responsible plugin or theme author with what you’ve seen (be sure to copy all the queries you see).

Checking whether the blog is installed

The very first query WordPress does is a query to check whether the blog is installed, it’s a pretty useless query if your blog is up and running, so I wanted to get rid of it. There’s no pretty fix to this, so we’ll do it in the not so pretty way:

  1. open up wp-includes/functions.php
  2. find the function is_blog_installed()
  3. add return true; as the first line of the function, and you’re done!

“Heavy” plugins

It turns out, that of the 16 queries my site now still does for a single post page, 7 are for the related posts plugin I’m using, and if you’re wondering: yes that’s making me reconsider the plugin. I’m going to try a couple other plugins and see how good they do in both relevance and (database) performance.

Your experiences with WordPress performance optimization?

Have any experience in optimizing your WordPress performance by tweaking database calls yourself? Any cases I’ve missed? Let me know in the comments!!

Tags: , , ,


Yoast.com runs on the Genesis Framework

Genesis theme frameworkThe Genesis Framework empowers you to quickly and easily build incredible websites with WordPress. Whether you're a novice or advanced developer, Genesis provides you with the secure and search-engine-optimized foundation that takes WordPress to places you never thought it could go.

Read our Genesis review or get Genesis now!

71 Responses

  1. KelBy Kel on 30 March, 2009

    I haven’t used this plugin for optimization, however I wonder how much, percentage-wise, WP calls the DB when using something like WP Supercache. Would “most users” need to optimize the DB in such a way?

    • Joost de ValkBy Joost de Valk on 30 March, 2009

      When using WP Super Cache, it would do these calls only when re-generating the page. However, if your site has a lot of pages, a lot of your traffic will be in the long tail, and thus coming to different pages. So if you can optimize it to go from 32 to 16 queries, which I did, this is still a huge improvement for a big percentage of your visitors.

  2. RarstBy Rarst on 30 March, 2009

    I hadn’t played much with database. Last time I had terrible performance issues I thought that was DB-related. Turned out one “wise” plugin developer completely disabled Magpie cache for some obscure reason.

    Pulling four feeds on each page load shot performance dead. :)

    Going to try that queries plugin later this week.

  3. Mike SalwayBy Mike Salway on 30 March, 2009

    Thanks for the hints and tips. I also use YARPP and can see it being quite resource intensive, but it also does a great job.
    I’d be interested in hearing of any other “related posts” plugins you find that do an equally good job but use less resources.

    How many plugins are too many? No doubt the more plugins you use, the slower your performance will be.. and of course we can’t guarantee the code quality or optimisation of most of them.

    Cheers

  4. Kim WoodbridgeBy Kim Woodbridge on 30 March, 2009

    I’ve had trouble with YARPP and had to deactivate on a site with shared hosting. It was the cause of excessive CPU usage.

    • eddaiBy eddai on 2 April, 2009

      YARPP was the plugin that caused excessive CPU usage ?
      Gosh..i also had a headache of Excessive CPU usage but YARPP was one of the plugin i did not activate, as it was vital for my site. And later my site was down for 2 days..then i decided to move my site to another hosting..but everything same and my site now uses only %01 CPU. weird..i will keep this in my mind if i’m to face this problem again
      Thanks

    • RudyBy Rudy on 7 April, 2009

      I went through the same process and I even got VPS account because of excessive CPU usage. I did not know it was YARPP. I wish I had known before going through the ordeal switching servers and upgrading hosting accounts.

      However, I have learnt a great deal in the process and so something good came out of the incident. I am going to leave my THREE tips for optimizing WordPress below which I have not come across in other places just yet.

      • mitchoBy mitcho on 11 September, 2009

        Joost et al,

        I invite you to try YARPP again, perhaps first on a test site, as YARPP 3.0 (released just after this post and your comments in April of this year) introduced a caching feature which makes it so each relatedness calculation is only done once. YARPP 3.0+ is now much more efficient.

  5. RajuBy Raju on 30 March, 2009

    I am also using YARPP and would love to see a better alternative to it. I have previously used Contextually related Posts plugin and few others, but YARPP seemed more accurate than others.

  6. Harsh AgrawalBy Harsh Agrawal on 30 March, 2009

    Hey thanks for nice post…
    like mentioned by raju even I’m using YARPP, though I love it but there are two things which I don’t like about Yarpp

    One: The signature of YARPP
    two : 7 queries mentioned by you…

    Any Good alternative will be great which also show related posts in feeds.

    • mitchoBy mitcho on 11 September, 2009

      Harsh, please note that the “signature” you note can be turned off and should be off by default. As I also mentioned elsewhere, since you made this comment YARPP’s performance has been drastically improved. I invite you to give it a second look. :)

  7. KasparsBy Kaspars on 30 March, 2009

    Yoost, there are two other plugins that (I think) do the same job a bit better:

    WP Tuner for debugging database queries. The wealth of information this plugin provides is simply invaluable.

    Clean Options for listing and deleting unused entries from options table.

  8. Thaya KareesonBy Thaya Kareeson on 30 March, 2009

    Let me know what you end up using after YARPP. I use YARPP and I love it too, but I did notice that the only slow queries I have were from that plugin.

  9. John (Human3rror)By John (Human3rror) on 31 March, 2009

    let us know when you think of how to do this better than the yarpp…!

  10. Navin PoeranBy Navin Poeran on 31 March, 2009

    Thanks for the cool tips, for sure I’m going to try some out on my own blog :)

  11. AikidoBy Aikido on 31 March, 2009

    i’m using wp-optimize> plugin, looks quite cool, you can do revisions in your database, Clean marked Spam comments, Clean Unapproved comments, Optimize database tables and you can change your users nickname so easyly if you want.

  12. Geek HeavenBy Geek Heaven on 31 March, 2009

    thanks for the heads up – finally have a nice way of seeing why it takes some pages soo long to load.

  13. NirBy Nir on 31 March, 2009

    Thanks, mate, for the great article!

  14. JasonBy Jason on 31 March, 2009

    Thanks for the is_blog_installed tip, it all helps! I have recently been using the wp-optimize plug-in mentioned above and it seems to be doing the trick!

  15. AnthonyBy Anthony on 31 March, 2009

    Well,

    I’ve been using my own statistics plugin (ZdStatistics) as it includes a number of queries and time to generate pages as well as more general user info. In combination to that, I’ve been watching the code and tried to make as much info as possible into the memory when generating pages. For example on one of my other plugins, I try to put as much info with only one query in memory and then process this stuff.

  16. Shop NetworkBy Shop Network on 1 April, 2009

    I wrote a small little article, Reducing mySQL queries in WordPress. It describes how to reduce the number of mySQL queries when using APC php Accelerator. Take a look.

  17. Christian LandBy Christian Land on 2 April, 2009

    Thats an really interesting PlugIn. I downloaded it and added an “Auto-Explain” Feature to it to see how the queries perform (basically I just checked if the query started with SELECT and added “EXPLAIN” in front of it, called the db with that new query and displayed the results)

    Its really interesting to see what certain PlugIns (or WordPress itself) are doing.

    • Christian LandBy Christian Land on 2 April, 2009

      Just a little example for things that make you gonna go WOAAAAAH if you add that PlugIn: I’m playing around with the GD-Star Rating PlugIn on a local test-site. Only after using the DQ-PlugIn I realized that GDSR adds a lot of queries… my frontpage goes from 19 to a whooping 51 queries if I add the GDSR plugin (while displaying 10 articles). And a single article page from 24 to unbelievable 42 queries (without any comments – those would increase the query count even more if you add comment-rating, too). Compared to that, YARPP is harmless :-)

  18. IgorOsaBy IgorOsa on 2 April, 2009

    I’m using WP-Tuner and happy about it, but thanks for information on “is_blog_installed()”! It proved usefull!

    • trkyrdBy trkyrd on 26 November, 2009

      is this how it should be? i have the same problem but i am not sure how to do it.. please the check below..

      function is_blog_installed() {
      global $wpdb;

      // Check cache first. If options table goes away and we have true cached, oh well.
      if ( wp_cache_get( ‘is_blog_installed’ ) )
      return true;

  19. Aaron D. CampbellBy Aaron D. Campbell on 2 April, 2009

    First, you should also check out WPDB Profiling.

    Second, we used to use YARPP, but with an investor site that we did which had 6000+ posts and 1800+ tags, it completely choked. Even WP-Related-Posts would cause pages that displayed related posts to take 10+ seconds to load. We ended up having to write our own plugin (which is not released yet, but I hope to eventually). We called it “Related Posts -HV” (for high-volume). Basically, it stores related posts as post meta when a post is saved. It moves the heavy lifting to a one-time thing that affects the post writer, rather than affecting everyone that visits the site. However, you don’t want ONLY older posts to show as related, so you need to re-process a lot of posts when a new one is saved. We created some logic that gives a value of HOW RELATED two posts are. When a post is saved, that value is generated for all posts (comparing the new one to all existing posts). The top X (you can set this value) are saved as post meta. However, every post with a related number > 0 is checked. If it’s related value is 6, but the lowest value of related post currently saved for it is 8, we don’t re-process. However, if it’s 6 and the least-related post that’s stored is 5, we re-scan that post.

    The logic is too much to put in a comment here, but if you’re interested in tweaking with it, let me know.

  20. Tony KBy Tony K on 3 April, 2009

    YARPP is a hog. I switched to Rob Marsh, SJ. Similar Posts http://rmarsh.com/plugins/similar-posts/

  21. Widgett WallsBy Widgett Walls on 5 April, 2009

    When Similar Posts inexplicably stopped working for me, I tried out YARPP and others, but YARPP was crazy CPU expensive. I finally just figured out what the problem with Similar Posts was and went back to it (my settings for how it found the similarities stopped working, tweaking them like mad finally fixed it). I would love to see an alternative, so Aaron, your HV plugin sounds good to me.

    Will try some of the things mentioned in this article as well, starting with is_blog_installed(). Thanks.

  22. RamoonusBy Ramoonus on 5 April, 2009

    There`s a plugin called DB Cache
    Although it screws up the dashboard and admin part (updates are visible after the caching time expired)
    It improved my site`s loading time

    I also use Widget Cache, which cached widgets (doh!) which also saves a few queries.

    My biggest problem is the providers fileservers, not their databaseservers.

  23. RudyBy Rudy on 7 April, 2009

    After the whole ordeal of switching hosting companies and upgrading from shared hosting account to a VPS hosting account because of excessive CPU usage caused quite possibly by YARPP I learnt a three important things on how to optimized WordPress for speed and light server load.

    1. Use DB Cache to cache the queries made to the database. In some instances it can bring a normal WP blog with 40 or so db queries to less than 20 queries. Also, make sure you add the code to view the number of queries and time in seconds needed to generate the page at the footer of your website. Also, check the number of queries made to the database on the home page and the single page before you turn another plugin and check again after activating the plugin; do this three times to get some averages.

    2. Use Hyper Cache which is very simple to use and was designed specifically for blogs hosted at low resources and cheap hosting companies. Hyper Cache is the best caching plugin I have tried and I have tried them all. Hyper Cache can even cache pages for mobile devices and redirections.

    3. Use YARPP 3.0.b6 if you must have the capability of related posts in your blog. YARPP 3.0.b6 includes caching and has been redesigned to reduced the number of queries and to go easy on the server load. Furthermore, if you use YARPP 3.0.b6 or the previous version, you can tell it NOT to consider the Tags and Categories, which will greatly reduced the number of queries and the server load.

    I hope my experience can help somebody else and perhaps even save some money by not having to switch hosting companies or upgrading to more expensive hosting plan.

    My two centavos. :-)

    • Carlos GuevaraBy Carlos Guevara on 9 August, 2009

      I don’t Recommend using YARPP with any version because it’s very heavy CPU usage is very high when using it , I made a test on both shared hosting also on my local server .
      It eats up your server memory and CPU

  24. Aaron D. CampbellBy Aaron D. Campbell on 7 April, 2009

    Rudy…you can tell it NOT to consider the Tags and Categories, which will greatly reduced the number of queries and the server load.

    Unfortunately, matching tags and categories are often a great indicator of related posts!

  25. freekraiBy freekrai on 8 April, 2009

    Actually, what I found helped with YARPP was turning down title and body check and only using category and tags.

  26. MezanulBy Mezanul on 14 April, 2009

    I am also using YARPP but I also use Hypercache and WP Widget Cache for making my site load faster. And I do see the difference.
    @Rudy Thanks for telling about DB Cache, I will try that soon.
    @Kaspars Thanks, I will try out WP Tuner and Clean Options.

  27. gigiBy gigi on 14 April, 2009

    I’m trying debug queries plugin and at each refresh for my homepage i got a different Total query time: first time 2.63149523735046 for 97 queries; second time Total query time: 1.9686913490295 for 97 queries;
    Where is the problem?

  28. RamoonusBy Ramoonus on 15 April, 2009

    I personally dislike DB Cache since it also cached the dashboard and dashboard related features ….

  29. Uli - Cool Products DesignBy Uli - Cool Products Design on 17 April, 2009

    I’m a little bit scared of modifying the WordPress’ core files, since I’ve experienced two fatal error to my WordPress blog. I edited the file via FTP, and when I saved it back.. zapp.. my blog gone nowhere. it was just a white blank screen. I have no idea about the error. So I re-installed the WordPress.. twice.. with the same problem. Could you propose a safer way to edit the WordPress core files? Thanks.

  30. Decky RoorohBy Decky Rooroh on 20 April, 2009

    This is great article.
    :) Happy blogging !

  31. IlmondodeiSitiWebBy IlmondodeiSitiWeb on 21 April, 2009

    This is a beautiful article.
    I like these suggestions!

  32. feirBy feir on 22 April, 2009

    Great. Thanks for sharing.

  33. JulianBy Julian on 23 April, 2009

    I had lot of performance issues when I moved to a new VPS.

    I have compiled PHP in a CentOS with eaccelerator instead of xcache, also I optimized my.cnf for SQL cache in MyISAM tables.

    I have disabled YARPP plugin as well as other unused plugins.

    I compressed style files and tried to make the WordPress theme smaller as possible.

    I am not sure yet about what to do with Global Translator

  34. travelerBy traveler on 23 April, 2009

    Great post!
    I subscribed to your blog, this is a good source for WP users.

  35. Marc PettiferBy Marc Pettifer on 25 April, 2009

    Many thanks, this exactly what I needed, I’m building something other than a blog, but using wordpress as a framework, some of my pages weigh in at 90 queries right now and I need to cut that down as much as I can.

  36. FreewareMatterBy FreewareMatter on 5 June, 2009

    I’m using YARPP plugin and I love the way it works. But with the Debug queries plugin, I realize that it takes many queries, like that:

    (Before)
    Total query time: 0.07755s for 13 queries.
    Total num_query time: 0.470 for 16 num_queries.

    (After)
    Total query time: 1.95269s for 34 queries.
    Total num_query time: 2.714 for 37 num_queries.

    So I decide to turn it off now. But I need another related plugin, do you have some advices?

  37. PerfuBy Perfu on 12 June, 2009

    Great. Thanks for sharing.

  38. VladimirBy Vladimir on 18 June, 2009

    Those who are concerned with query optimization may find this plugin useful:

    http://wordpress.org/extend/plugins/sqlmon/

  39. Many thanks, this exactly what I needed, I’m building something other than a blog, but using wordpress as a framework, some of my pages weigh in at 90 queries right now and I need to cut that down as much as I can

  40. AlexBy Alex on 21 July, 2009

    What useful info about a useful plugin! Just what I was after! My host has been very patient with my blog burning too many cpu cycles. With this plugin, I’ve reduced the number of db queries to more acceptable levels.

    Excellent stuff!

    Cheers,

    Alex

  41. steveBy steve on 29 July, 2009

    Does the results of this plugin consider whether WP Super Cache is installed ?

    For example , I have Super Cache installed. Debug queries tells me I have 50 queries for a page. Have these queries been actually executed provided that Super Cache is on ?

  42. SeanBy Sean on 2 August, 2009

    YARPP is the prettiest but a total performance nightmare. We just had to can it on all of our WP sites as it was causing major problems. Efficient Related Posts is much better, more intelligent design, and not dynamic at post generation time (static meta data embed in posts done at post save time).

  43. MTGBy MTG on 2 August, 2009

    I had to stop using YARPP as it was causing high resource usage and my site was going down. I do use WP-Super-Cache, WP-Widgets-Cache and DB-Cache for some performance optimization of my WordPress blog. But I am still not fully satisfied.

    • mitchoBy mitcho on 11 September, 2009

      Sean, MTG, unfortunately this post and many of the comments were written right before my April release of YARPP 3.0 which was a major rewrite of YARPP for performance reasons. YARPP 3.0+ uses a very smart caching system to only run each “relatedness” computation once, so it is much more efficient now. In fact, Efficient Related Posts which was released more recently uses this same technique as well. I invite you to give YARPP a second look and let me know if you still have performance issues with it.

      • Aaron D. CampbellBy Aaron D. Campbell on 4 October, 2009

        I just thought that I’d post my findings. I still had quite a few problems with the most recent YARPP. When it first scans all the posts, it brought the server to a crawl. Then every time a post was saved it would take more than a minute to save. Switching to Efficient Related posts took that save time down to 2-3 seconds.

  44. Tammy CampBy Tammy Camp on 4 October, 2009

    Is there a definitive conclusion for a wordpress database optimizer after all of this? Seems like a lot of mixed opinions on all of them in these comments.

    • Joost de ValkBy Joost de Valk on 5 October, 2009

      Hey Tammy,

      while I didn’t know it at the time of writing (probably because it didn’t exist yet), W3 Total Cache is probably the best speed improving, database caching etc. plugin there is for WordPress out there at the moment.

  45. EastmobilesBy Eastmobiles on 13 November, 2009

    Thanks for this great post, I came to find that wordpress consumes too much resources, which is a pain in the neck.

  46. Harsh AgrawalBy Harsh Agrawal on 18 November, 2009

    I use Dreamhost as my server and they told me they dont support WP problem for plugins like YARPP because it creates lots of php queries and over load the server…

  47. BirdBy Bird on 28 November, 2009

    I have tried WP super cache but.. I found some problem that I have no idea what the problem is.

    So that I un-install it already

Trackbacks

  1. [...] also less efficient. Joost de Valk referred to it as a “heavy plugin” in his article on Optimizing WordPress database performance, and it definitely is. WordPress Related Posts is far more efficient, but offers you a little less [...]