Skip to:
Content
Pages
Categories
Search
Top
Bottom

bbPress 2.0.2 and 1.6MM posts = painfully slow

  • I was troubleshooting an issue with a rather large migration to bbPress (1.6MM posts) and bbPress 2.0.2 is very slow to load thread pages.

    It looks like bbPress (WP_Query) is trying to do an unindexed query lookup:

    CAST(wp_postmeta.meta_value AS CHAR) = '787852'

    The query takes quite some time and does a full table scan:

    # Time: 120518 19:23:53
    # User@Host: db[db] @ localhost []
    # Query_time: 9.688145 Lock_time: 0.000050 Rows_sent: 3 Rows_examined: 1623367
    SET timestamp=1337387033;
    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_type IN ('topic', 'reply') AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'closed') AND ( (wp_postmeta.meta_key = '_bbp_topic_id' AND CAST(wp_postmeta.meta_value AS CHAR) = '787852') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date ASC LIMIT 0, 25;

    Here’s a simple stack trace:

    /home/site/public_html/wp-includes/query.php   2498   get_sql ()   -- /home/site/public_html/wp-includes/
    /home/site/public_html/wp-includes/query.php 2913 get_posts () -- /home/site/public_html/wp-includes/
    /home/site/public_html/wp-includes/query.php 3000 query () -- /home/site/public_html/wp-includes/
    /home/site/public_html/wp-content/plugins/bbpress/bbp-includes/bbp-reply-template.php 129 __construct () -- /home/site/public_html/wp-content/plugins/bbpress/bbp-includes/
    /home/site/public_html/wp-content/themes/site/bbpress/content-single-topic.php 35 bbp_has_replies () -- /home/site/public_html/wp-content/themes/site/bbpress/
    /home/site/public_html/wp-includes/theme.php 1117 require () -- /home/site/public_html/wp-includes/
    /home/site/public_html/wp-includes/theme.php 1091 load_template () -- /home/site/public_html/wp-includes/
    /home/site/public_html/wp-includes/general-template.php 128 locate_template () -- /home/site/public_html/wp-includes/
    /home/site/public_html/wp-content/plugins/bbpress/bbp-includes/bbp-core-compatibility.php 144 get_template_part () -- /home/site/public_html/wp-content/plugins/bbpress/bbp-includes/
    /home/site/public_html/wp-content/themes/site/single-topic.php 33 bbp_get_template_part () -- /home/site/public_html/wp-content/themes/site/
    /home/site/public_html/wp-includes/template-loader.php 43 include () -- /home/site/public_html/wp-includes/
    /home/site/public_html/wp-blog-header.php 16 require_once () -- /home/site/public_html/
    /home/site/public_html/index.php 17 require () -- /home/site/public_html/

    Here’s the $bbp_r query passed into WP_Query:

    Array
    (
    [meta_query] => Array
    (
    [0] => Array
    (
    [key] => _bbp_topic_id
    [value] => 1623928
    [compare] => =
    )

    )

    [post_type] => Array
    (
    [0] => topic
    [1] => reply
    )

    [orderby] => date
    [order] => ASC
    [posts_per_page] => 25
    [paged] => 1
    =>
    [post_status] => publish,closed
    )

Viewing 7 replies - 1 through 7 (of 7 total)

  • Gautam Gupta
    Participant

    @gautamgupta

    #1823 should address this.

    This seems to have the right idea, but I’m not sure it covers WP_Query queries where the search criteria is against a meta key. The query has to cast an unindexed full-text field to char across N rows to find matching values.

    The generated query fragment that is problematic is this:

    wp_postmeta.meta_key = '_bbp_topic_id'
    AND CAST(wp_postmeta.meta_value AS CHAR) = '787852'

    It’s a core issue since Threads are used to group up Posts.

    This looks like an issue with WP_Meta_Query issuing a CAST for all rows in wp_postmeta during the query.

    Relevant LOCs in meta.php:

    $where[$k] = ' (' . $where[$k] . $wpdb->prepare("CAST($alias.meta_value AS {$meta_type}) {$meta_compare} {$meta_compare_string})", $meta_value);

    There are several options to pursue:

    * Store thread ids in a separate table and intercept get_meta_sql filter to update the query to look in the correct table for _bbp_topic_id lookups.

    * Update WP_Meta_Query to read relevant types from type-specific tables (i.e. wp_postmeta_unsigned) for topic_id lookups. Requires updating mechanisms to save metadata into type-specific tables as well.

    What are some other options?


    John James Jacoby
    Keymaster

    @johnjamesjacoby

    Yeah; the meta queries are really awful.

    I think I’ve resolved the issue. You’re one of the first people to report back using bbPress 2.x at that scale, so I’d love some feedback on the change.

    https://bbpress.trac.wordpress.org/changeset/4059


    vibol
    Member

    @vibol

    John, this is amazing! Thank you for fixing this.

    I’ll have to update my installation to your latest trunk before I can test.

    I will let you know how it works out.


    vibol
    Member

    @vibol

    John, I did some preliminary tests and the results aren’t looking good.

    I left my comments on trac: https://bbpress.trac.wordpress.org/ticket/1885#comment:3


    Daniel J. Lewis
    Participant

    @djosephdesign

    Any update to this? I, too, have a large bbPress forum and I see the following in my error log:

    
    SlowTimer [6357ms] at runtime/ext_mysql: slow query: SELECT SQL_CALC_FOUND_ROWS  wp_13_posts.ID FROM wp_13_posts  INNER JOIN wp_13_postmeta ON (wp_13_posts.ID = wp_13_postmeta.post_id)\nINNER JOIN wp_13_postmeta AS mt1 ON (wp_13_posts.ID = mt1.post_id) WHERE 1=1  AND wp_13_posts.post_type = 'topic' AND (wp_13_posts.post_status = 'publish' OR wp_13_posts.post_status = 'closed') AND (wp_13_postmeta.meta_key = '_bbp_last_active_time'\nAND  (mt1.meta_key = '_bbp_forum_id' AND CAST(mt1.meta_value AS SIGNED) != '133643') ) GROUP BY wp_13_posts.ID ORDER BY wp_13_postmeta.meta_value DESC LIMIT 0, 25
    
Viewing 7 replies - 1 through 7 (of 7 total)
  • You must be logged in to reply to this topic.
Skip to toolbar