maplegrovehosting

Forum Replies Created

Viewing 6 replies - 1 through 6 (of 6 total)
  • Forum: Plugins
    In reply to: [PowerPress Podcasting plugin by Blubrry] Custom Podcast Feed INNER JOIN Causes Large Volume of Temporary Tables
    Thread Starter maplegrovehosting

    (@maplegrovehosting)

    6 years, 3 months ago

    As an update while waiting for a developer response, we mitigated impact by leveraging the MySQL Rewriter Plugin to rewrite the queries as follows:

    SELECT wp_posts . * FROM wp_posts INNER JOIN wp_postmeta AS pp_wp_postmeta ON wp_posts . ID = pp_wp_postmeta . post_id WHERE ? = ? AND wp_posts . post_name = ? AND wp_posts . post_type = ? AND ( pp_wp_postmeta . meta_key = ? ) GROUP BY wp_posts . ID ORDER BY wp_posts . post_date DESC

    TO

    SELECT wp_posts . * FROM wp_posts INNER JOIN wp_postmeta AS pp_wp_postmeta ON wp_posts . ID = pp_wp_postmeta . post_id WHERE ? = ? AND wp_posts . post_name = ? AND wp_posts . post_type = ? AND ( pp_wp_postmeta . meta_key = ? )

    and

    SELECT SQL_CALC_FOUND_ROWS wp_posts . ID FROM wp_posts INNER JOIN wp_postmeta AS pp_wp_postmeta ON wp_posts . ID = pp_wp_postmeta . post_id WHERE ? = ? AND wp_posts . post_name = ? AND wp_posts . post_type = ? AND ( pp_wp_postmeta . meta_key = ? ) GROUP BY wp_posts . ID ORDER BY wp_posts . post_date DESC LIMIT ?,?

    TO

    SELECT SQL_CALC_FOUND_ROWS wp_posts . ID FROM wp_posts INNER JOIN wp_postmeta AS pp_wp_postmeta ON wp_posts . ID = pp_wp_postmeta . post_id WHERE ? = ? AND wp_posts . post_name = ? AND wp_posts . post_type = ? AND ( pp_wp_postmeta . meta_key = ? )

    Forum: Plugins
    In reply to: [PowerPress Podcasting plugin by Blubrry] Custom Podcast Feed INNER JOIN Causes Large Volume of Temporary Tables
    Thread Starter maplegrovehosting

    (@maplegrovehosting)

    6 years, 3 months ago

    “SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta AS pp_wp_postmeta ON wp_posts.ID = pp_wp_postmeta.post_id WHERE 1=1 AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’) AND ( pp_wp_postmeta.meta_key = ‘enclosure’ ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 100”

    Is another additional query that’s running very frequently in our environment when I debug via PHP vs MySQL, but it does not consume a temporary table to disk like the original post does.

    Forum: Plugins
    In reply to: [PowerPress Podcasting plugin by Blubrry] Custom Podcast Feed INNER JOIN Causes Large Volume of Temporary Tables
    Thread Starter maplegrovehosting

    (@maplegrovehosting)

    6 years, 3 months ago

    I now have a working root cause and a partial solution.

    Root cause: When a /feed/ is generated for an individual post, powerpress does not alter the ORDERBY clause (as there is no filter) to remove the default sort by post_date. Given that its a single post, we can be confident an order by is not required, and we can us the built-in wp API to check for this condition.

    
function powerpress_posts_orderby($orderby) {
        if(is_single())
        {
                $orderby = '';
        }
        return $orderby;
}
add_filter('posts_orderby', 'powerpress_posts_orderby');

    in powerpress.php, line 2188

    We’re still seeing the temporary tables getting logged with post_name = ? in the WHERE clause, so there is likely more conditional logic needed in the if to handle other cases that the plugin uses that I haven’t considered yet. The case above seemed representative of the issue at first glance.

    Forum: Plugins
    In reply to: [PowerPress Podcasting plugin by Blubrry] Custom Podcast Feed INNER JOIN Causes Large Volume of Temporary Tables
    Thread Starter maplegrovehosting

    (@maplegrovehosting)

    6 years, 3 months ago

    Did significant more digging into the code base and research of the issue. Looks like the powerpress.php uses WP query filters to build the requisite query. I still have not been able to reproduce/trace fully what action causes the query in the original post to be generated. I attempted adding the following patch as a hacky mitigation to test the theory:

    
function powerpress_posts_orderby($orderby) {
        global $wp_query;
        error_log($wp_query->request);
        if(strpos($wp_query->request, 'post_name ='))
        {
                $orderby = '';
        }

        return $orderby;
}
add_filter('posts_orderby', 'powerpress_posts_orderby');

    Still seeing the query getting run after this hotpatch. When I log out this filter I’ve added to powerpress.php it doesn’t ever log the query that mysql is noting in its statement analysis logs (where we originally discovered the issue).

    Hoping this jogs enough technical inspiration to help root cause when looked at as plugin dev team can probably shoot this one a bit quicker :–)

    Forum: Plugins
    In reply to: [PowerPress Podcasting plugin by Blubrry] Custom Podcast Feed INNER JOIN Causes Large Volume of Temporary Tables
    Thread Starter maplegrovehosting

    (@maplegrovehosting)

    6 years, 3 months ago

    Thank you. Just to add to this, confirming that the order by gets added starting line 179 of powerpress-playlist.php, and a lot of queries I see in the query log the result set is already filtered down to one row making the ORDER BY unnecessary in that instance.

    For example, if the build query contains: wp_posts.post_name = “my post title”
    you should always expect a single result, and there for not require the order by to be appended.

    Hoping we can review the conditionals of when the ORDER BY needs to be appended to the query to minimize its use accordingly to when its actually relevant to the result set.

    Forum: Plugins
    In reply to: [PowerPress Podcasting plugin by Blubrry] Custom Podcast Feed INNER JOIN Causes Large Volume of Temporary Tables
    Thread Starter maplegrovehosting

    (@maplegrovehosting)

    6 years, 3 months ago

    Here is a copy of the explain plan as well:

    https://maplegrovepartners.com/public/powerpress_query_explain.jpg

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