Raise in the Wordpress number of queries in nested pages

[tiosolid]

Hi, a lot of people are complaining in the forums that since they updated from Wordpress 2.3.1 to 2.5 they pages started to use a huge ammount of queries and cpu time to be shown. This problems is happening mainly with nested pages (at least for me).

While using the debug-queries plugin, I get the following report:
4.29153442383E-05 SELECT post_parent FROM wp_posts WHERE ID= '286' LIMIT 1
4.19616699219E-05 SELECT post_parent FROM wp_posts WHERE ID= '286' LIMIT 1
4.6968460083E-05 SELECT post_parent FROM wp_posts WHERE ID= '286' LIMIT 1
4.19616699219E-05 SELECT post_parent FROM wp_posts WHERE ID= '287' LIMIT 1
4.19616699219E-05 SELECT post_parent FROM wp_posts WHERE ID= '286' LIMIT 1
4.41074371338E-05 SELECT post_parent FROM wp_posts WHERE ID= '287' LIMIT 1
4.29153442383E-05 SELECT post_parent FROM wp_posts WHERE ID= '286' LIMIT 1
4.2200088501E-05 SELECT post_parent FROM wp_posts WHERE ID= '286' LIMIT 1
(... repeated like 2000~3000 times)

querying my database I found that the posts with the 286 and 287 IDs are exaclty the pages above the problematic one. Like:

www.mysite.com/tutorials/psp/somepagehere

where:
tutorials: ID 286
PSP: ID 287
somepagehere: The page where the high queries usage is shown

I alread tried disabling all my plugins, but the problem still persists, so Im pretty sure its some kind of Wordpress bug. I Marked this ticket as highest priority because some people are losing their blogs because of High CPU usage account termination! If needed, you can check this post where many users are complaining about this:

​http://wordpress.org/support/topic/167000#post-732526

[DD32]

What theme are you using?

What widgets do you have loaded?

I just tried with /about-me/sub-about-me/sub-sub-about-me/ and i have 5 page-related queries:

      4 => 
        array
          0 => string 'SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name = 'sub-sub-about-me' AND (post_type = 'page' OR post_type = 'attachment')' (length=137)
          1 => float 0.000761032104492
          2 => string 'get_page_by_path' (length=16)
      5 => 
        array
          0 => string 'SELECT ID, post_name, post_parent FROM wp_posts WHERE ID = 74 and post_type='page'' (length=82)
          1 => float 0.000460863113403
          2 => string 'get_page_by_path' (length=16)
      6 => 
        array
          0 => string 'SELECT ID, post_name, post_parent FROM wp_posts WHERE ID = 7 and post_type='page'' (length=81)
          1 => float 0.000438928604126
          2 => string 'get_page_by_path' (length=16)
      7 => 
        array
          0 => string 'SELECT * FROM wp_posts WHERE ID = 75 LIMIT 1' (length=44)
          1 => float 0.000741004943848
          2 => string 'get_post' (length=8)
      8 => 
        array
          0 => string ' SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND (ID = '75') AND wp_posts.post_type = 'page'  ORDER BY wp_posts.post_date DESC ' (length=129)
          1 => float 0.000733137130737
          2 => string 'get_posts' (length=9)

(I'm not sure why both get_post() and get_posts() are making pretty similar queries

It'd be helpful to know where the queries are coming from. As you can see in that example, mine mainly come from 'get_page_by_path()'

while there's probably another plugin out there which probably allready does this, i'll attach a plugin which dumps the queries made into the footer of the page as a HTML comment, so you'll need to view the HTML source to get at them, Hopefully that'll include the source of the queries (I'm not too sure what the plugin you used is/does)

You'll also need to add

define('SAVEQUERIES', true);

to your wp-config.php file.

[tiosolid]

Im using the web-2.0 theme, but it happens even with my old theme (that worked fine before with 2.3).

Here's the widgets loaded in my sidebar:
5 text widgets;
Recent posts;
the TLA network widget (removing it or disabling it in the plugins page doesnt fix the problem);
Archives;
Links
And 2 more text widgets in the end;

Btw, I cant see your attached plugin, where is it? Afeter downloading it I can make a new reply here with the results. Thank You

[DD32]

Dumps the Queries made into the footer, Make sure to define SAVEQUERIES

[DD32]

Btw, I cant see your attached plugin

Sorry, Forgot to attach it :), Should be there now.

[tiosolid]

Thanks for the plugin. According to the plugin's dump (I took out the "normal" queries and started the dump from the strange ones):

    [20] => Array
        (
            [0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category')  AND ( t.term_id <> 58 ) ORDER BY t.name ASC 
            [1] => 9.10758972168E-05
            [2] => get_terms
        )

    [21] => Array
        (
            [0] => SELECT `post_parent` FROM wp_posts WHERE ID= '286' LIMIT 1
            [1] => 7.89165496826E-05
            [2] => _get_post_ancestors
        )

    [22] => Array
        (
            [0] => SELECT * FROM wp_posts  WHERE (post_type = 'page' AND post_status = 'publish')    ORDER BY menu_order, post_title ASC
            [1] => 0.00259494781494
            [2] => get_pages
        )

    [23] => Array
        (
            [0] => SELECT `post_parent` FROM wp_posts WHERE ID= '286' LIMIT 1
            [1] => 0.000121831893921
            [2] => _get_post_ancestors
        )

    [24] => Array
        (
            [0] => SELECT `post_parent` FROM wp_posts WHERE ID= '287' LIMIT 1
            [1] => 4.79221343994E-05
            [2] => _get_post_ancestors
        )

    [25] => Array
        (
            [0] => SELECT `post_parent` FROM wp_posts WHERE ID= '286' LIMIT 1
            [1] => 4.10079956055E-05
            [2] => _get_post_ancestors
        )

    [26] => Array
        (
            [0] => SELECT `post_parent` FROM wp_posts WHERE ID= '287' LIMIT 1
            [1] => 4.10079956055E-05
            [2] => _get_post_ancestors
        )

    [27] => Array
        (
            [0] => SELECT `post_parent` FROM wp_posts WHERE ID= '286' LIMIT 1
            [1] => 4.10079956055E-05
            [2] => _get_post_ancestors
        )

    [28] => Array
        (
            [0] => SELECT `post_parent` FROM wp_posts WHERE ID= '286' LIMIT 1
            [1] => 4.2200088501E-05
            [2] => _get_post_ancestors
        )

    [29] => Array
        (
            [0] => SELECT `post_parent` FROM wp_posts WHERE ID= '286' LIMIT 1
            [1] => 4.81605529785E-05
            [2] => _get_post_ancestors
        )

    [30] => Array
        (
            [0] => SELECT `post_parent` FROM wp_posts WHERE ID= '287' LIMIT 1
            [1] => 4.29153442383E-05
            [2] => _get_post_ancestors
        )
[the same for all the others queries]

On the other pages where the error occours the problematic function is the same (_get_post_ancestors)

[DD32]

Replying to tiosolid:

On the other pages where the error occours the problematic function is the same (_get_post_ancestors)

To me it looks like theres a logic error:
​http://trac.wordpress.org/browser/trunk/wp-includes/post.php#L2968

	if ( !isset($_post->ancestors) )
		return;

While that seems to say do not load ancestors if none are allready set to me, However, I'm not sure if thats quite correct in general.

Eitherway, The attached patch prevents those multiple queries for me, It limits it to finding the ancestors once per page request, I thought of wrapping it in a wp_cache_get/set however, this seems to do the job.

[DD32]

logic inverstion & s/<space>/<tab> in function

[DD32]

(Just a note, The change is line #2968)

[tiosolid]

Hi, i tried to change the (!isset) to (isset) but still the queries problem appeared. The unique way to solve my high mysql usage was to comment out the While statment from the get_post_ancestors function. Since this is probably going to break something somewhere, is there any other way to fix this?

[ryan]

[7601] looks like it is related.

[ryan]

But that is in 2.6 only.

[DD32]

[7601] looks like it is related.

That looks like its reversed the logic to me..
Not Empty = return if items are loaded
Not Isset = return if there are no items loaded

Idealy, I guess some caching needs to be added to the function to cache page ancestors maybe?

[ryan]

(In [7694]) Add post ancestors to the cache for the post object. see #6702 for trunk

[ryan]

(In [7695]) Add post ancestors to the cache for the post object. see #6702 for 2.5

[ryan]

That adds ancestors to the post cache and fixes cache clean so that all ancestors are cleaned. I don't know if that will be enough though. We might have to remove the _get_post_ancestors() call from get_post() and lose the feature that needs the ancestors in wp_list_pages().

[Denis-de-Bernardy]

fixed my bug, hopefully it fixes the one above as well.

D.

[tiosolid]

Didnt test the new milestone yet (2.5.1), but an user replied in the forum thread saying that removing a reference to wp_list_pages() from their blog made the high query usage end, even without applying the 6702 diff.

[ryan]

(In [7743]) Move cache cleaning of children to clean_post_cache(). see #6702

[ryan]

(In [7744]) Move cache cleaning of children to clean_post_cache(). see #6702

[ArneBrachhold]

Please make this optional, see #6860

[ArneBrachhold]

New argument $recursive (true by default)

[ryan]

Milestone 2.5.2 deleted

[Denis-de-Bernardy]

fixed?