Database queries causing overload on server

  • Resolved jamminjames

    (@jamminjames)


    9 years, 7 months ago

    We’ve been experiencing intermittent overload issues on our dedicated server, and a tech thinks he has isolated a possible cause, and it relates to queries done by NextCellent, apparently.

    He says:

    I think I have a good lead on what might be going on.

    There was a large increase in this particular query, which began taking more and more time as the the system became overloaded

    # User@Host: humortimes[humortimes] @ localhost []
    # Query_time: 46.554562 Lock_time: 0.025978 Rows_sent: 1344 Rows_examined: 6807
    SET timestamp=1429902157;
    SELECT option_name, option_value FROM siow8zcap_options WHERE autoload = ‘yes’;

    The options table is supposed to be relatively small, 6807 rows is a very large amount for this table. The table also takes up about 67MB which is huge for what this table is normally supposed to hold.

    The table is full of rows that contain option_name and option_value pairs where the name is something like _transient_0dec5811fde913014dc86375ff163bfc, and the value is the HTML code for an entire gallery page, equally about 37KB.

    The code begins with:
    <div class=”ngg-galleryoverview” id=”ngg-gallery-310bf18217ad768cb23bd2ad55081101-13396″>

    So it looks like the ngg gallery plugin is putting an excess of entries into the options table which is causing lookups of the options table to take a long time. The options table is queried frequently, so in periods of high traffic it becomes problematic and becomes overloaded.

    I have put a mysql index on the ‘autoload’ column which should hopefully speed up the queries a little bit, but ultimately the solution is to change the ngg setting that is causing these entries and then remove the existing ones.

    Does this make sense to anyone? Could NextCellent be causing this problem?

    https://www.ads-software.com/plugins/nextcellent-gallery-nextgen-legacy/

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

  • Hi there,

    I’m sorry to hear you’re having trouble.

    It seems to me that something is saving all the HTML output that NextCellent generates in the database as transients. This is of course not the way this is supposed to work.

    However, NextCellent doesn’t do that on it’s own. The HTML is generated every time someone visits the page.

    It could be that another plugin that you’re using is doing this. If you’re running some ‘optimization’ plugin somewhere, try disabling it and see if the issue persists.

    You can safely delete all entries starting with _transient_.
    Do make a backup before editing the database though.

    Thread Starter jamminjames

    (@jamminjames)

    I don’t have an optimization plugin, just w3t cache. However, the database cache for that is not activated, just object cache. Could that be doing it?

    Thread Starter jamminjames

    (@jamminjames)

    Could the NextGen Gallery Voting plugin have anything to do with this?

    I’ve just installed and tested with both plugins, and it’s not happening on my site.

    You could try 2 things:

    • Have your tech find out what is adding the transients to the database (fairly easy if you know php)
    • Disable all other plugins, and turn them back on one by one and check every time if the problem happens. If it starts again, you’ll know which plugin is responsible

    I wish I could be of more help, but I’ve checked again just to make sure, but NextCellent isn’t the one doing this.

    Thread Starter jamminjames

    (@jamminjames)

    Looking at this old thread (https://www.ads-software.com/support/topic/wp_options-to-many-records), it seems this same problem was resolved in an update for NGG.

    If this is the same issue, as it seems to be, then can NextCellent implement the same fix?

    Mmmh, interesting.

    Could it be that you have used NextGen before you switched to NextCellent?

    Thread Starter jamminjames

    (@jamminjames)

    Yes, definitely. Isn’t this a common occurrence?

    Is there some maintenance I should do after the switch that I missed?

    No, but it seems you switched to NextCellent before they fixed it. This is what happened, I think:

    • NextGen had a bug which put all those entries in the database
    • You switched to NextCellent before they published their fix

    So, no new entries will be added by NextCellent, and in the link you gave are some methods to clean up the database. If you do that,the problem should be fixed.

    Thread Starter jamminjames

    (@jamminjames)

    Thanks, seems to be fine now.

Viewing 9 replies - 1 through 9 (of 9 total)
  • The topic ‘Database queries causing overload on server’ is closed to new replies.