Queries overloading MySQL

  • Resolved MediaDC

    (@mediadc)


    10 years, 11 months ago

    After updating to WordPress 4.2.2 on WPEngine, my site started crashing with a 502 error. The logs showed that the query was timing out when calling to widget I had created using Query Wrangler and deployed to my landing pages.

    Here’s an example of the error

    WordPress database error Lost connection to MySQL server during query for query \n\t\t\tSELECT meta_key\n\t\t\tFROM wp_2_postmeta\n\t\t\tGROUP BY meta_key\n\t\t\tORDER BY meta_key /* From [redalertpolitics.com/2014/02/26/indiana-university-reports-potential-data-exposure/] in [**SERVER_PATH**/wp-content/plugins/query-wrangler/includes/query.inc:436] */ made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/rap2/single.php'), dynamic_sidebar, call_user_func_array, WP_Widget->display_callback, Query_Wrangler_Widget->widget, qw_execute_query, qw_template_query, qw_make_fields_rows, qw_all_fields, apply_filters('qw_fields'), call_user_func_array, qw_field_meta_value, qw_get_meta_keys, referer: http://redalertpolitics.com/author/associatedpress/?listpage=24

    This error would occur several times on the same page. Our theory was that each of the widgets was making a separate call to the meta_key table, and while each call was not very large in and of itself, multiple instances of that call were overloading the DB connection.

    My postmeta table for that site has about 151,000 rows in it, which I know is a lot, but this problem hadn’t been occurring or crashing my site until I updated to 4.2.2, so I thought this would be the right place to discuss how any recent updates to WordPress might have affected QW’s ability to execute these queries, and to see if anyone else has encountered a similar problem.

    I’ve had to disable the Query Wrangler plugins I had running on article pages until I can determine if this error can be fixed.

    Thanks!

    https://wordpress.org/plugins/query-wrangler/

Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Author Jonathan Daggerhart

    (@daggerhart)

    10 years, 11 months ago

    Hi MediaDC,

    That’s a tough one. The MySQL DB going away could be a number of things. If it is a memory issue, I can try to make some improvements around the way QW handles internal data that might save a bit of memory.

    Here is the list of 4.2.2 changes: https://codex.wordpress.org/Version_4.2.2 The one that stands out to me is “Fixes how WordPress checks for encoding when sending strings to MySQL”. I’ll see if I can find exactly what was done there, and if it might be problematic.

    Additionally, I’d be interested to know if the wpengine guys have any thoughts on the issue. Have you asked their support for insight yet? I’d imagine if it is actually WP 4.2.2 related (and not just a QW issue), they would be seeing more issues like this.

    Let me know if you figure out anything else, and I’ll do the same.

    Thanks,
    Jonathan

    Thread Starter MediaDC

    (@mediadc)

    10 years, 11 months ago

    The problem really seems to be here that there’s a query on line 432 of the includes/query.inc file that’s pulling all of the meta keys in my postmeta table, and there are a lot in my database. The problem is that this query is pulling them on every single page on the site.

    What’s the reason why the plugin needs to pull every single meta key on every single page?

    Plugin Author Jonathan Daggerhart

    (@daggerhart)

    10 years, 11 months ago

    Hi MediaDC,

    No, there isn’t a good reason why the plugin needs to get all the meta keys on each page.

    I’ve updated the plugin’s dev branch with a new approach to custom fields that mitigates this issue. Do you have a staging or development server where you could test these changes? https://github.com/daggerhart/query-wrangler/tree/dev (click “download zip” in sidebar) You’ll want to make sure you replace the old plugin with the dev one, so that the dev one has the same folder name as the old one used to have.

    The update doesn’t attempt to move any data around on your site, so it shouldn’t have any terrible consequences if you can try it out.

    You might not need to re-save any queries, as the new field attempts to automatically take over for the old field. Unfortunately, if you have meta fields whose key includes a space, their query fields will need to be corrected. You’ll have to edit a query, edit the field, and fix its Meta Key.

    The update will also change how you add fields to a query. Now there is a single generic “Custom Field” field in query wrangler. You have to add that to a query, then edit it and give it a meta_key.

    Let me know if you can test this somewhere. Again, if you don’t have any meta_keys with spaces in them, this should be a drop-in fix.

    And please let me know if you have any questions about testing the dev branch.

    Thanks,
    Jonathan

    Plugin Author Jonathan Daggerhart

    (@daggerhart)

    10 years, 11 months ago

    Hi MediaDC,

    I need to push some other updates out soon, so I made a branch specifically for the new meta_value field.

    https://github.com/daggerhart/query-wrangler/tree/new-meta-value-field

    If you get a chance to test this, or have any questions, please let me know.

    Thread Starter MediaDC

    (@mediadc)

    10 years, 10 months ago

    Hey Jonathan,

    I finally got the dev branch up on my prod site this evening, and everything looks to be ok so far. Pages are loading as expected and I’m not seeing any PHP errors right now, but traffic is also very low at this time of night on the East Coast. I’ll be watching it through the day tomorrow and will update to let you know how it’s working.

    Thanks!

    Plugin Author Jonathan Daggerhart

    (@daggerhart)

    10 years, 10 months ago

    Hi again MediaDC,

    In the most recent version of QW (1.5.36), there is a setting that lets you enable the new meta_value field handler. After you update to the newest version, go to Query Wrangler > Settings and set the Meta Value field handler to “New handler (beta)”. That should allow you to maintain upgrade path without any issue.

    The new field handler also includes an autocomplete to help find meta_key names.

    Let me know how it goes,
    Jonathan

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

The topic ‘Queries overloading MySQL’ is closed to new replies.