WordPress.org

1. matthijs
   Member
   Posted 3 years ago #

   Since a few weeks a wordpress site of mine has gotten very slow. On inspection I found out it runs about 2400 database queries for each page. What I did so far to try to find the problem:

   - Use default theme
   - Disable all plugins
   - Install on a different (local) webhost
   - Replace all wordpress files with fresh ones
   - Remove the code in the template which does the loop
   - Remove any function in the templates which might run a query

   So far without any result, the footer still reports > 2000 queries.

   I also installed the plugin debug queries, which reports an enormous amount of queries. Looking in phpmyadmin I can't see anything unusual in the database tables at first sight. A few thousand posts, 13 Mb for all tables combines.

2. matthijs
   Member
   Posted 3 years ago #

   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.

3. Otto
   Tech Ninja
   Posted 3 years ago #

   What are these queries? What do they look like?

4. matthijs
   Member
   Posted 3 years ago #

   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.

5. Otto
   Tech Ninja
   Posted 3 years ago #

   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.

6. matthijs
   Member
   Posted 3 years ago #

   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?

7. Otto
   Tech Ninja
   Posted 3 years ago #

   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.

8. matthijs
   Member
   Posted 3 years ago #

   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.

9. Otto
   Tech Ninja
   Posted 3 years ago #

   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.

10. matthijs
    Member
    Posted 3 years ago #

    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

11. matthijs
    Member
    Posted 3 years ago #

    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

12. matthijs
    Member
    Posted 3 years ago #

    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.

13. Otto
    Tech Ninja
    Posted 3 years ago #

    Do you have multiple categories per post?

    Generally speaking, putting %category% in the permalink string is a bad idea.

14. matthijs
    Member
    Posted 3 years ago #

    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.

15. Otto
    Tech Ninja
    Posted 3 years ago #

    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.

16. matthijs
    Member
    Posted 3 years ago #

    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

17. matthijs
    Member
    Posted 3 years ago #

    Ok I've taken a closer look at the rewrite rules and it's absolutely unclear why there are so many rules. If I just look at one Page, there are maybe a thousand rewrite rules for that single page, something like:

    s:53:\"vacancies/phd-studentship-wales/attachment/([^/]+)/?$\";
    s:32:\"index.php?attachment=$matches[1]\";
    s:63:\"vacancies/phd-studentship-wales/attachment/([^/]+)/trackback/?$\";
    s:37:\"index.php?attachment=$matches[1]&tb=1\";
    s:83:\"vacancies/phd-studentship-wales/attachment/([^/]+)/feed/(feed|rdf|rss|rss2|atom)/?$\";
    s:49:\"index.php?attachment=$matches[1]&feed=$matches[2]\";
    s:78:\"vacancies/phd-studentship-wales/attachment/([^/]+)/(feed|rdf|rss|rss2|atom)/?$\";
    s:49:\"index.php?attachment=$matches[1]&feed=$matches[2]\";
    s:78:\"vacancies/phd-studentship-wales/attachment/([^/]+)/comment-page-([0-9]{1,})/?$\";
    s:50:\"index.php?attachment=$matches[1]&cpage=$matches[2]\";
    s:46:\"(vacancies/phd-studentship-wales)/trackback/?$\";
    s:37:\"index.php?attachment=$matches[1]&tb=1\";
    s:66:\"(vacancies/phd-studentship-wales)/feed/(feed|rdf|rss|rss2|atom)/?$\";
    s:49:\"index.php?attachment=$matches[1]&feed=$matches[2]\";
    s:61:\"(vacancies/phd-studentship-wales)/(feed|rdf|rss|rss2|atom)/?$\";
    s:49:\"index.php?attachment=$matches[1]&feed=$matches[2]\";
    s:54:\"(vacancies/phd-studentship-wales)/page/?([0-9]{1,})/?$\";
    s:50:\"index.php?attachment=$matches[1]&paged=$matches[2]\";
    s:61:\"(vacancies/phd-studentship-wales)/comment-page-([0-9]{1,})/?$\";
    s:50:\"index.php?attachment=$matches[1]&cpage=$matches[2]\";
    s:46:\"(vacancies/phd-studentship-wales)(/[0-9]+)?/?$\";
    s:49:\"index.php?attachment=$matches[1]&page=$matches[2]\";
    s:35:\"vacancies/473/attachment/([^/]+)/?$\";
    s:32:\"index.php?attachment=$matches[1]\";
    s:45:\"vacancies/473/attachment/([^/]+)/trackback/?$\";
    s:37:\"index.php?attachment=$matches[1]&tb=1\";
    s:65:\"vacancies/473/attachment/([^/]+)/feed/(feed|rdf|rss|rss2|atom)/?$\";
    s:49:\"index.php?attachment=$matches[1]&feed=$matches[2]\";
    s:60:\"vacancies/473/attachment/([^/]+)/(feed|rdf|rss|rss2|atom)/?$\";
    s:49:\"index.php?attachment=$matches[1]&feed=$matches[2]\";
    s:60:\"vacancies/473/attachment/([^/]+)/comment-page-([0-9]{1,})/?$\";
    s:50:\"index.php?attachment=$matches[1]&cpage=$matches[2]\";
    s:28:\"(vacancies/473)/trackback/?$\";

    That specific page has currently around 100 words and maybe 2 attachments. Somehow it seems it "remembers" all page revisions or prior versions of that page?

    I'm also looking into the possibility that those pages which have the many rules, have had different attachments to them in their prior version. Looking at the rewrite rules, many have to do with attachments. The above page, "vacancies" currently has 26 attachments if you look in the add image > gallery. Only 2 of them are currently linked from the page.

    But in that case it seems like to be a bug in wordpress. Why is it creating those endless rules?

18. matthijs
    Member
    Posted 3 years ago #

    Another thing I discovered. The bug has appeared from the moment I upgraded the website to 2.7.

    I have backups of the database from all prior versions. If I import the 2.6.5 db file and run 2.6.5 it is fast (14-20 queries). As soon as I upgrade to 2.7 the problem starts. The upgrade process seems to run fine, just as always. I can go back and forth (all local) and repeat the process and see how the problem appears in 2.7

19. matthijs
    Member
    Posted 3 years ago #

    Update: the problem is caused by the combination of attachments of pages (and posts) and the permalink structure
    /%category%/%year%/%monthnum%/%day%/%postname%/

    Because of the nature of that permalink structure, wordpress writes about 10-20 rewrite_rules for each attachment and page. So with a site with a few hundred pages and a few hundred attachments, the amount of rewrite_rules soon explodes to many thousands. About 20,000 in my case. That doesn't scale.

    See this ticket:
    http://trac.wordpress.org/ticket/8958
    See this older ticket as well:
    http://trac.wordpress.org/ticket/3614
    and this forum thread:
    http://wordpress.org/support/topic/99953?replies=31

    The older ticket has a patch attached, but I'm not sure if an wp 2.0 patch is going to work on a 2.7 version. I'd rather see a real solution then a workaround patch (which might cause other problems).

20. greggo
    Member
    Posted 2 years ago #

    So I'm having the same problem. Basically, I can't use permalinks without turning my database to mud. Another weird thing is I can't find a 'rewrite_rules' entry in my wp-options table. Is there any way to simply reboot the rewrite rules? How do i just start over with this? The data I'm using was also imported using the WP Export/Import tools, so you'd think it'd be cleared up. Any ideas what I can do to get this working? I have been using /%postname%/ for my permalinks, but changing that to /blog/%postname% didn't work either.

    Help is greatly appreciated.

Topic Closed

This topic has been closed to new replies.