WordPress.org

Ready to get started?Download WordPress

Forums

Types - Custom Fields and Custom Post Types Management
Types causes db timeout? (23 posts)

  1. kalleankaco
    Member
    Posted 2 years ago #

    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/

  2. coati
    Member
    Posted 2 years ago #

    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]

  3. coati
    Member
    Posted 2 years ago #

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

  4. kalleankaco
    Member
    Posted 2 years ago #

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

  5. coati
    Member
    Posted 2 years ago #

    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.

  6. kalleankaco
    Member
    Posted 2 years ago #

    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?

  7. kalleankaco
    Member
    Posted 2 years ago #

    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.

  8. coati
    Member
    Posted 2 years ago #

    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

  9. coati
    Member
    Posted 2 years ago #

    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.

  10. kalleankaco
    Member
    Posted 2 years ago #

    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.

  11. coati
    Member
    Posted 2 years ago #

    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.

  12. AmirHelzer
    Member
    Plugin Author

    Posted 2 years ago #

    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.

  13. Srdjan
    Member
    Plugin Author

    Posted 2 years ago #

    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.

  14. Srdjan
    Member
    Plugin Author

    Posted 2 years ago #

    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.

  15. kalleankaco
    Member
    Posted 2 years ago #

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

  16. AmirHelzer
    Member
    Plugin Author

    Posted 2 years ago #

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

  17. kalleankaco
    Member
    Posted 2 years ago #

    No worries. Again, thank you kindly for your prompt fix.

  18. coati
    Member
    Posted 2 years ago #

    Excellent. This new dev-version has fixed the performance issue completely.
    Thanks for your great work jozik, Amir and the WPML/Types team!
    I'm sure kalleankaco will be happy too.

  19. Srdjan
    Member
    Plugin Author

    Posted 2 years ago #

    Glad it worked.
    Thank you guys for debugging it.

  20. kalleankaco
    Member
    Posted 2 years ago #

    Great to see this resolved. Thank you everyone involved.

  21. Strong Island
    Member
    Posted 2 years ago #

    Hi, I am having performance issues, has this fix been rolled in to the current version???

  22. coati
    Member
    Posted 2 years ago #

    @Strong Island: Yes a fix was integrated pretty soon, I don't have performance issues anymore.
    Maybe your issue is caused by something else.

  23. Strong Island
    Member
    Posted 2 years ago #

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic