Support » Plugin: Toolset Types - Custom Post Types, Custom Fields and Taxonomies » [Plugin: Types – Custom Fields and Custom Post Types Management] Types causes db timeout?

  • I’m using Types 0.9.2.1 on WP 3.3 and whenever I add or update a post involving 2 custom taxonomies and 1 custom field group, all the db queries timeout. The issue is so severe that my hosting provider’s automated system has to kill of the queries because they tax the mysql server too much.

    Why do I believe types *might* be involved? Here’s one of the db queries that my hosting provider had to kill:
    SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id) INNER JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id) INNER JOIN wp_postmeta AS mt5 ON (wp_posts.ID = mt5.post_id) INNER JOIN wp_postmeta AS mt6 ON (wp_posts.ID = mt6.post_id) INNER JOIN wp_postmeta AS mt7 ON (wp_posts.ID = mt7.post_id) INNER JOIN wp_postmeta AS mt8 ON (wp_posts.ID = mt8.post_id) INNER JOIN wp_postmeta AS mt9 ON (wp_posts.ID = mt9.post_id) INNER JOIN wp_postmeta AS mt10 ON (wp_posts.ID = mt10.post_id) INNER JOIN wp_postmeta AS mt11 ON (wp_posts.ID = mt11.post_id) WHERE 1=1 AND wp_posts.post_type = 'wp-types-group' AND (wp_posts.post_status = 'publish') AND ( (wp_postmeta.meta_key = '_wp_types_group_terms' AND CAST(wp_postmeta.meta_value AS CHAR) = 'all') OR (mt1.meta_key = '_wp_types_group_terms' AND CAST(mt1.meta_value AS CHAR) LIKE '%,17,%') OR (mt2.meta_key = '_wp_types_group_terms' AND CAST(mt2.meta_value AS CHAR) LIKE '%,76,%') OR (mt3.meta_key = '_wp_types_group_terms' AND CAST(mt3.meta_value AS CHAR) LIKE '%,68,%') OR (mt4.meta_key = '_wp_types_group_terms' AND CAST(mt4.meta_value AS CHAR) LIKE '%,73,%') OR (mt5.meta_key = '_wp_types_group_terms' AND CAST(mt5.meta_value AS CHAR) LIKE '%,75,%') OR (mt6.meta_key = '_wp_types_group_terms' AND CAST(mt6.meta_value AS CHAR) LIKE '%,69,%') OR (mt7.meta_key = '_wp_types_group_terms' AND CAST(mt7.meta_value AS CHAR) LIKE '%,74,%') OR (mt8.meta_key = '_wp_types_group_terms' AND CAST(mt8.meta_value AS CHAR) LIKE '%,72,%') OR (mt9.meta_key = '_wp_types_group_terms' AND CAST(mt9.meta_value AS CHAR) LIKE '%,71,%') OR (mt10.meta_key = '_wp_types_group_terms' AND CAST(mt10.meta_value AS CHAR) LIKE '%,70,%') OR (mt11.meta_key = '_wp_types_group_terms' AND CAST(mt11.meta_value AS CHAR) LIKE '%,18,%') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

    http://wordpress.org/extend/plugins/types/

Viewing 15 replies - 1 through 15 (of 22 total)
  • I have the same problem.

    This is an event from the slow query log.
    Been looking in the plugin code, but I don’t find anything related to the JOINS and _wp_types_group_terms.

    [Code moderated as per the Forum Rules. Please use the pastebin]

    I’ve posted the slow query at pastebin (Didn’t know that wasn’t allowed here)
    http://pastebin.com/T1KhizFA

    Thread Starter kalleankaco

    (@kalleankaco)

    Interesting, coati. This seems like a random bug, though. Creating some posts work – albeit slow – others seem to consistently fail when you try to re-create them.

    coati, how does your wp installation look like? Here’s mine:

    • Akismet 2.5.3
    • Contact Form 7 3.0.2.1
    • Google Analytics for WordPress 4.2.2
    • Types – Complete Solution for Custom Fields and Types 0.9.2.1
    • W3 Total Cache 0.9.2.4
    • WordPress SEO 1.1.2

    I’m also using the official Swedish locale.

    As mentioned, my “post” content type is associated with 1 custom field group and 2 custom taxonomies (the standard category and tags taxonomies are also used).

    Hi kalleankaco,

    I’m not able to fully narrow it down yet. In my case it seems to depend on the ‘tags’ that an article has.
    Try to edit an article without tags => no problem.
    The more tags there are added => the slower the edit-page loads.
    If there are too much tags => the MySQL hangs at 100% CPU for more than a minute.

    I’m running WP v3.2.1 and Types 0.9.2.1, the same as you are.
    Other plugins don’t have impact as far as I can see.

    So, I think it has to do with the tags/terms and groups they are related to.

    One way to prevent the database problem is to edit the file:
    embedded/includes/fields-post.php

    Around line 495, just below the definition of the function wpcf_admin_post_get_post_groups_fields, add a return; without anything else, and the edit-page of problematic pages/posts just load as they should be.

    Of course, this will break other things, so it’s not a solution, only a test.

    When digging deeper:
    In the same function, wpcf_admin_get_groups_by_post_type gets called with a lot of distinct terms.

    $groups_by_post_type = wpcf_admin_get_groups_by_post_type($post_type, true, $distinct_terms);

    In that function (wpcf_admin_get_groups_by_post_type), there’s a loop over the (distinct) terms, which eventually causes the big query.

    Unfortunately I don’t know why and I don’t have a fix for it.
    Can you confirm that adding the return, makes the edit-posts pages render at normal speed again?

    Kind regards,

    Coati

    ps: I found your post by googling on ‘_wp_types_group_terms’, because that showed up in our slow-query log.
    The current post was the only result I got. I don’t know if it matters if it isn’t posted in the specific Types forum. http://wordpress.org/tags/types?forum_id=10
    I hope it gets picked up by the Types developers, that they can reproduce our problem and find a solution.

    Thread Starter kalleankaco

    (@kalleankaco)

    Yes, I can confirm that the number of tags seems to be the problem.

    I tested this by adding a post that I kept having problems with and which had 7 tags. This time I added the post as usual but omitted all tags – post was flawlessly created in an instant. I then tried to update the post by adding just 2 tags and noticed a slowness when submitting the change. I repeated this process up to 5 tags where the db query became very slow (but no timeout).

    I also tried the change you suggested to wp-content\plugins\types\embedded\includes\fields-post.php:

    function wpcf_admin_post_get_post_groups_fields($post = false) {
    return false;

    This modification broke a lot of things – including the custom fields – but yes, I could post the post with all 7 tags.

    The forum setup on WordPress.org is confusing but I thought this thread was posted in the official forum for Types?

    Thread Starter kalleankaco

    (@kalleankaco)

    I tried to add a notice in the Types forum and failed again. Maybe the developers have to add this post to the forum manually? Let’s see what happens.

    Hi there again kalleankaco,

    Thanks for checking.
    I _think_ I’ve found something. It solves the edit pages loading, but I’m not sure if it breaks other things.

    In embedded/includes/fields.php

    I’ve changed this part around line 285


    foreach ($terms as $term) {
    $args['meta_query'][] = array(
    'key' => '_wp_types_group_terms',
    'value' => ',' . $term . ',',
    'compare' => 'LIKE',
    );
    }

    With this:


    $args['meta_query'][] = array(
    'key' => '_wp_types_group_terms',
    'value' => ',' . join(',', $terms) . ',',
    'compare' => 'LIKE',
    );

    What do you think?

    Coati

    Considering my previous suggestion, and looking back at the earlier queries, it won’t work that way..

    Maybe

    ‘value’ => join(‘,’, $terms),
    ‘compare’ => ‘IN’,

    Somehow, a trick is needed to prevent the looped JOINS that kill the server.

    Thread Starter kalleankaco

    (@kalleankaco)

    Interesting, coati. I will try to look at the code later tonight, see if I can figure out something useful.

    I’m trying to get in touch with the developers but there’s no contact info to be found on their profile pages. They have their own support forum but access to it requires that you have purchased their product.

    Unfortunately my testfix doesn’t work either. But I’m sure there is the (or one of) bottleneck. I’ll have to debug the queries better and see what output it gives.
    I’m a bit afraid that in the database architecture there isn’t an easy way to get passed the “self joins” that are created. Maybe some functionallity can be removed, or an extra check added in a way that the joins aren’t used anymore on the edit pages.

    On the frontend or other admin pages, I didn’t see any problems at all.

    Plugin Author Amir Helzer

    (@amirhelzer)

    Very strange. This issue is not appearing in the plugin page.

    Srdjan (Types main developer) will join this thread tomorrow and work with you to get to the bottom of this.

    Thanks for debugging it.
    From here I can say that:
    ‘value’ => join(‘,’, $terms),
    won’t work.

    Cause joined $terms aren’t necessary in right order,
    and right result won’t be fetched.

    I’ll see if we can make distinct function more optimized.

    It’s optimized in dev version.
    Now it won’t do JOIN for each term post have.
    Customized query is used instead of ‘meta_query’ as argument for get_posts().
    No errors now and I hope that performance will be OK.

    Thread Starter kalleankaco

    (@kalleankaco)

    Excellent. Thank you. Will test and report as needed.

    Plugin Author Amir Helzer

    (@amirhelzer)

    This DEV version is not available to download from wp.org yet. We’ll have it here shortly.

Viewing 15 replies - 1 through 15 (of 22 total)
  • The topic ‘[Plugin: Types – Custom Fields and Custom Post Types Management] Types causes db timeout?’ is closed to new replies.