In the meantime I have come somewhat closer to the cause of the problem: it seems to be something with the permalinks. If I turn off the custom permalinks I have, the site runs fast again (with around 33 queries). If I turn back on the pretty links, it runs in the 2400 queries again.
My permalink structure is:
/%category%/%year%/%monthnum%/%day%/%postname%/
Nothing unusual I would think? I have had this structure on several sites for years, without any problem.
What are these queries? What do they look like?
Like this:
0.000209808349609 SELECT * FROM wp_posts WHERE ID = 2025 LIMIT 1
0.000149011611938 SELECT post_parent FROM wp_posts WHERE ID = 2023 LIMIT 1
0.000196933746338 SELECT * FROM wp_posts WHERE ID = 2049 LIMIT 1
0.00014591217041 SELECT post_parent FROM wp_posts WHERE ID = 2023 LIMIT 1
0.00019383430481 SELECT * FROM wp_posts WHERE ID = 2234 LIMIT 1
0.000221014022827 SELECT post_parent FROM wp_posts WHERE ID = 2023 LIMIT 1
0.000195026397705 SELECT * FROM wp_posts WHERE ID = 2235 LIMIT 1
0.000144004821777 SELECT post_parent FROM wp_posts WHERE ID = 2023 LIMIT 1
0.000261068344116 SELECT ID, post_name, post_parent FROM wp_posts WHERE post_type = ‘attachment’ AND post_parent = 1485
0.000207901000977 SELECT * FROM wp_posts WHERE ID = 1483 LIMIT 1
0.000236034393311 SELECT ID, post_name, post_parent FROM wp_posts WHERE post_type = ‘attachment’ AND post_parent = 1483
0.000221014022827 SELECT * FROM wp_posts WHERE ID = 1481 LIMIT 1
0.000219106674194 SELECT ID, post_name, post_parent FROM wp_posts WHERE post_type = ‘attachment’ AND post_parent = 1481
0.000232934951782 SELECT * FROM wp_posts WHERE ID = 1479 LIMIT 1
0.000274896621704 SELECT ID, post_name, post_parent FROM wp_posts WHERE post_type = ‘attachment’ AND post_parent = 1479
0.00022292137146 SELECT * FROM wp_posts WHERE ID = 1477 LIMIT 1
0.000221967697144 SELECT ID, post_name, post_parent FROM wp_posts WHERE post_type = ‘attachment’ AND post_parent = 1477
// etc etc etc
It goes on for a few hundred lines and then there’s a really big single one
0.00469207763672 INSERT INTO wp_options (option_name, option_value, autoload) VALUES (‘rewrite_rules’, ‘a:9592:{s:12:\”robots\\.txt$\”;s:18:\”index.php?robots=1\”;s:14:\”.*wp-atom.php$\”;s:19:\”i
which goes on like that for a few hundred lines. Really weird.
That looks like it’s regenerating the rewrite_rules option, but it shouldn’t have to do that on every page load. Only when the rewrite rules are modified. That’s what that last query does, it inserts the resulting rules into the DB so that it doesn’t have to recreate them later, it can just pull out the finished product from the options table.
Check the wp_options table, see if the rewrite_rules option is in there. Make sure there’s not two of them or something odd like that.
That’s interesting. The wp_options table does NOT contain a row with rewrite_rules
So it isn’t writing that rule away while it should?
Well, considering that you have that query right above:
It goes on for a few hundred lines and then there’s a really big single one
0.00469207763672 INSERT INTO wp_options (option_name, option_value, autoload) VALUES (‘rewrite_rules’, ‘a:9592:{s:12:\”robots\\.txt$\”;s:18:\”index.php?robots=1\”;s:14:\”.*wp-atom.php$\”;s:19:\”i
which goes on like that for a few hundred lines. Really weird.
Then I’d be asking why is that insert not happening? Something wrong with your database? Try repairing the options table.
Note that you have to have the pretty permalinks on for that to happen, if you’re still on default, then I wouldn’t expect it to be there.
Yes I do have the pretty permalinks on. So why isn’t the query executing? I don’t know. Could the field be too long to insert? I shall try to count the characters in that query. I saw the wp_options table has a longtext field for the value so it should be able to contain a pretty long one.
I will also check out what happens if I choose another permalink structure (say just %postname%).
How can I “repair” the options table?
By the way, thanks a lot for the support. Really appreciated.
How can I “repair” the options table?
1. Backup the table contents. Just in case.
2. REPAIR TABLE wp_options;
If you’re using phpMyAdmin, it has a repair option to do step 2 for you.
Ok thanks I’ll do that.
I tried something else just now: changing the permalink structure from
/%category%/%year%/%monthnum%/%day%/%postname%/
to
/%year%/%monthnum%/%day%/%postname%/
and then the rewrite_rules is written to the wp_options table.
So it seems the problem really is something with that first specific permalink structure. I’ll try the repair table first now.
[edit:] repair table did not help anything unfortunately
I just checked out the two queries and found a remarkable difference.
If I set the permalink structure to
/%year%/%monthnum%/%day%/%postname%/
the field rewrite_rules in wp_options contains about 7500 characters.
If I set the permalink structure to
/%category%/%year%/%monthnum%/%day%/%postname%/
the query which should be run but doesn’t contains about 1,202,783 characters. More then a million! A text file with that single query in it is 1.2 Mb alone. My browser crashed when I tried to manually run that query in PhpMyAdmin..
Is is normal for that query/field to be so large?
[EDIT:]I checked out a very similar website running on wordpress with the same permalink setting /%category%/%year%/%monthnum%/%day%/%postname%/, and that one has about 44,000 characters in the field rewrite_rules in wp_options
Another update to the problem: I managed to run the query from within a separate php script I made. I couldn’t use my normal texteditor because it crashes when I copy-paste the query in it. But with a different text editor I was able to run the query. And it did execute. After that, the site ran fast again, needing only about 20 queries. However, the permalinks don’t work then. But I could expect that, since I run that query in isolation, outside the wordpress code, which probably messes up some stuff.
So, at the moment the problem seems to be in the fact that that query is valid and runs but is way too large. Why is that? The site is not that large. It has about maybe a few hundred posts/pages, 150 users and a few hundred attachments. The table wp_options has 123 records, wp_postmeta 3200 records, wp_posts 900, wp_usermeta 800, the others only a few records.
Do you have multiple categories per post?
Generally speaking, putting %category% in the permalink string is a bad idea.
No, basically just one category, “news”.
What I have done is build a site mostly with pages. Also having a “static” front page. Now regular blog/news posts go into the category “news”.
But I can’t imagine it has anything to do with that. The site has been running like that for one and a half year with no problem. It’s only recently, since 6 weeks or so that the problems started. Maybe since the upgrade to 2.7.
I also have the same structure on many of my other wordpress sites (with no problems), and if I look around that structure is used else where as well.
Having said that, it does seem like wordpress is in this case exploding the amount of rewrite rules. The site does run. I have also ran a clean up on the db tables, and everything is fine there.
Well, the only thing I have left is to tell you to look through the rewrite rules and see what is being generated so many times. Something is clearly wrong there, knowing what the extra rules are might tell you where it’s coming from.
Ok I’ll do that. It will be a job, as it’s thousands of lines long. maybe I can write a script to automate the analysis
