There appear to be two possible reasons for this:
1) My permalink structure (I like %postname% or %category%/%postname%) sucks. Apparently having the category name in the permalinks will induce a performance hit when a WP site has a lot of content (and I’m over 3,000 posts and attachments).
2) Something to do with the recent change to Options +FollowSymLinks. My site went down a couple of weeks ago and was only restored when a my hosting rep commented out this line in my .htaccess. I read elsewhere that WP needs this to be turned on for permalinks to work correctly. I don’t know why permalinks would be working at all if this isn’t enabled though.
Some useful dialogue from wp-testers list:
http://comox.textdrive.com/pipermail/wp-testers/2009-January/011097.html
http://comox.textdrive.com/pipermail/wp-testers/2009-January/011099.html
http://comox.textdrive.com/pipermail/wp-testers/2009-January/011109.html
Also this trac ticket:
http://core.trac.wordpress.org/ticket/8958
And some metrics–permalink structure vs length of the rewrite field in wp_options:
/%category%/%postname%/ 1053253
/%post-id%/%postname%/ 10055
SQL statement to test this yourself (from the trac ticket): SELECT LENGTH(option_value) FROM wp_options WHERE option_name LIKE ‘rewrite%’
There are some competing assertions here… some say that large rewrite rules should be no problem as long as the max query size for database requests can accommodate. Might this be something that could be tweaked to resolve these issues?
Now here’s the kicker: when I switch to a clean permalinks style with a corresponding 100 fold decrease in rewrite value length… nothing happens. The query to wp_options still drags as much as before.
Any ideas? Maybe I’m barking up the wrong tree.
So the “fix” is to change your permalink structure?
I’ve actually used the category/postname combo for a while, didn’t know it was a performance issue. Good to know.
That seems to be the argument.
I should probably note that I later found that my heavy use of attachments certainly contributes to the problem. For every attachment several additional permalinks rules are created with the category/postname structure–10 or 11 apparently. It doesn’t seem like there is much that can be done aside from switching the permalink structure to something more deterministic, but it still doesn’t answer my performance issues…
Have a read of Otto’s post here.
http://ottopress.com/2010/category-in-permalinks-considered-harmful/
I’d guess your permalink structure is forcing verbose rules to enable, which tends to inflate the size of the rewrite array.
Otto’s post is the best i’ve seen for explaining when and why that happens.
Yes, I see it now.
Transitioning from such a permalink structure seems to be a bit tricky though… all outside links will break, for instance. Not good.
What I would be interested in is a way to formally break the linkage between attachments and pages… the bloat wouldn’t be so bad if I could do that.