Whoa it’s been nearly two weeks since I’ve posted on the blog. I’m up the walls busy at the moment working on developing some new WordPress themes for clients and our first public theme which should see the light of day before the middle of August. More on that a bit later in the month. We’ve also not forgotten about the High Performance WordPress post series – it’s just having to take a back seat for the moment – I promise to push out a few more on the series before the end of July! Anyway, last week we ran into some major performance problems with a clients WordPress powered website that I had to squeeze out a quick post to let you good folks know about the issue just in case it rears it’s ugly head.
Permalinks.
Wonderful little inventions that let us have nice clean search engine friendly urls. From a backend UI perspective, the WordPress permalink implementation is excellent. But, once you start to have a lot of posts and pages on your site you WILL hit performance issues if you simply use /%postname%/ as your preferred permalink structure. The website in question had over 10,000 records in the posts table. Now the interesting thing was that there was nowhere near that many posts/pages actually on the website – more like a couple of hundred blog posts and about 1,400 pages. The balance of records in the posts table were actually WordPress revisions. There’s a lot of updates happening on the site at the moment and the posts table had gotten out of control. Never fear, this little simple query cleared out any old revisions from the posts table:
NOTE: PLEASE PLEASE PLEASE BACKUP YOUR DB BEFORE RUNNING THIS QUERY! DON’T SAY YOU WERE NOT WARNED!!
[codesyntax lang=”sql”]
DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision'
[/codesyntax]
Credit to Lester for this nifty sql statement
As long as you maintain regular backups of your DB I see no reason to retain all your posts revisions on a long term basis. Some people might – I don’t. If performance is important to you (and it should be) then I advise keeping your posts table nice and lean by cleaning it up every few months. There’s also a nice little plugin for this if you’re that way inclined.
To keep the maintenance of your posts table low, you can also switch off revisions completely if they are overkill for your site. Just add the following to your wp-config.php:
[codesyntax lang=”php”]
define('WP_POST_REVISIONS', false );
[/codesyntax]
So why is this important?
So why are the number of records in your posts table important to your permalinks and performance? In a nutshell, the more records in your posts table, the longer WordPress takes to execute key queries to retrieve the data required to load every page and post on your site. If your site will only ever have 40-50 posts/pages you can probably stop reading now. Otherwise, you will want to read Otto’s very well explained post on why certain permalink structures are more harmful than others. Seriously read it now. No time to read it? Ok, basically WordPress keeps it’s rewrite rules nice, simple and elegant by using cascading specificity – a bit like CSS. When your permalink structure is something very general like /%postname%/ you are in effect overriding this cascade and WordPress handles it by switching to use more verbose page rewriting rules. The impact of this cannot be overstated on large WordPress websites. On a site with 1,000 pages, it basically kills a VPS server with 1GB of ram and a trickle of traffic. Get a sudden burst of traffic and you’re dead – that’s even with something like W3 Total Cache running. When I switched the permalink structure to something more specific like /%year%/%postname%/ (which was my preferred structure after lots of testing – and is Otto’s preferred structure too.) the impact was immediate. CPU and RAM usage fell back to what I would expect for a site like this. Switching back to /%postname%/ just to confirm this was definitely the problem took nearly 10 mins for WordPress to process the request – and an immediate CPU/RAM spike – not good.
I can’t believe I’ve never come across this before – it’s actually noted in the WordPress codex that starting a permalink structure with /%postname%/ is a bad idea from a performance perspective – it’s just kinda buried. Given that so many SEO’s stress the importance of using this exact permalink structure I think that WordPress should shout a bit more about this because it’s probably giving some people a very bad impression of WordPress performance for no reason. The really interesting thing is that this doesn’t impact on the permalink structure for pages which I was worried about from an SEO perspective. I thought I’d have to setup a crapload of 301 redirects for pages and I didn’t fancy telling the website owner that all their pages now had a year in the permalink structure. Thankfully the problem never manifested itself as pages don’t seem to be affected by the permalink rules you control via the WordPress backend. Sp, while the /%postname%/ structure will kill your pages load time, the rule is just for blog posts. Pages stay nice and friendly in the format “www.mydomain.com/page-title” In any event, I find that having a date in the permalink for blog posts is a really big help when trying to verify how useful any given page will be. I don’t do that here yet but will be switching this in the near future once I knock a few items off the existing todo list.
Leave a Reply