Make WordPress Core

Opened 16 years ago

Closed 15 years ago

#6702 closed defect (bug) (fixed)

Raise in the Wordpress number of queries in nested pages

Reported by: tiosolid's profile tiosolid Owned by: ryan's profile ryan
Milestone: 2.6 Priority: high
Severity: major Version: 2.5
Component: Optimization Keywords: has-patch needs-testing
Focuses: Cc:

Description

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

Attachments (3)

wpdbqueries.php (381 bytes) - added by DD32 16 years ago.
Dumps the Queries made into the footer, Make sure to define SAVEQUERIES
6702.diff (1.2 KB) - added by DD32 16 years ago.
logic inverstion & s/<space>/<tab> in function
clean_post_cache_recursive.patch (860 bytes) - added by ArneBrachhold 16 years ago.
New argument $recursive (true by default)

Download all attachments as: .zip

Change History (25)

#1 @DD32
16 years ago

  • Priority changed from highest omg bbq to high

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.

#2 @tiosolid
16 years ago

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
16 years ago

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

#3 @DD32
16 years ago

Btw, I cant see your attached plugin

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

#4 follow-up: @tiosolid
16 years ago

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)

#5 in reply to: ↑ 4 @DD32
16 years ago

  • Keywords has-patch needs-testing added
  • Milestone changed from 2.7 to 2.6

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
16 years ago

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

#6 @DD32
16 years ago

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

#7 @tiosolid
16 years ago

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?

#8 @ryan
16 years ago

[7601] looks like it is related.

#9 @ryan
16 years ago

But that is in 2.6 only.

#10 @DD32
16 years ago

[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?

#11 @ryan
16 years ago

  • Owner changed from anonymous to ryan

#12 @ryan
16 years ago

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

#13 @ryan
16 years ago

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

#14 @ryan
16 years ago

  • Milestone changed from 2.6 to 2.5.1

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().

#15 @Denis-de-Bernardy
16 years ago

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

D.

#16 @tiosolid
16 years ago

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.

#17 @ryan
16 years ago

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

#18 @ryan
16 years ago

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

#19 @ArneBrachhold
16 years ago

Please make this optional, see #6860

@ArneBrachhold
16 years ago

New argument $recursive (true by default)

#20 @ryan
16 years ago

  • Milestone changed from 2.5.2 to 2.9

Milestone 2.5.2 deleted

#22 @ryan
15 years ago

  • Milestone changed from 2.9 to 2.6
  • Resolution set to fixed
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.