Clear Sitemap Cache – slow down site and deadlock on db

  • Resolved des79

    (@des79)


    8 years, 10 months ago

    Hi all,

    I’ve some problem with sitemap cache. I’ve deadlock on db when plugin try to clean transient and site crash. I put db proc stats to understand how many query trying to delete this transient:

    Query   | 35   | updating             | DELETE FROM wp_options WHERE option_name LIKE '\_transient\_%\_4tmlj' OR opt
ion_name LIKE '\_transie |
Query   | 24   | updating             | DELETE FROM wp_options WHERE option_name LIKE '\_transient\_%\_4tmlj' OR opt
ion_name LIKE '\_transie |
Query   | 3    | updating             | DELETE FROM wp_options WHERE option_name LIKE '\_transient\_%\_4tLIa' OR opt
ion_name LIKE '\_transie |
Query   | 35   | updating             | DELETE FROM wp_options WHERE option_name LIKE '\_transient\_%\_4tRzf' OR opt
ion_name LIKE '\_transie |
Query   | 24   | updating             | DELETE FROM wp_options WHERE option_name LIKE '\_transient\_%\_4tH1N' OR opt
ion_name LIKE '\_transie |
Query   | 13   | updating             | DELETE FROM wp_options WHERE option_name LIKE '\_transient\_%\_4tH1N' OR opt
ion_name LIKE '\_transie |
Query   | 13   | updating             | DELETE FROM wp_options WHERE option_name LIKE '\_transient\_%\_4tUGk' OR opt
ion_name LIKE '\_transie |
Query   | 53   | updating             | DELETE FROM wp_options WHERE option_name LIKE '\_transient\_%\_4u1lS' OR opt
ion_name LIKE '\_transie |

    and this queries cause this:

    mod_fcgid: stderr: WordPress errore sul database Deadlock found when trying to get lock; try restarting transaction per la query DELETE FROM wp_options WHERE option_name LIKE '\\_transient\\_%\\_4cBCd' OR option_name LIKE '\\_transient\\_timeout\\_%\\_4cBCd' fatta da require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('single-lesson.php'), do_action('start_lesson'), call_user_func_array, Lesson::setStarted, Core::addUserRelation, wp_insert_comment, wp_update_comment_count, wp_update_comment_count_now, clean_post_cache, clean_object_term_cache, do_action('clean_object_term_cache'), call_user_func_array, WPSEO_Sitemaps_Cache::invalidate_helper, WPSEO_Sitemaps_Cache::invalidate, WPSEO_Sitemaps_Cache::clear, WPSEO_Sitemaps_Cache_Validator::invalidate_storage, WPSEO_Sitemaps_Cache_Validator::cleanup_database, referer: <url>

    This brings the db cpu to 100% and with busy site this cause a queue like stats above and user get error 500

    I use wp seo ver. 3.2.1 but this also applies to earlier versions.

    If I can be useful for any other data/info tell me.

    P.s: now I had to comment line #175 class-sitemaps-cache-validator.php
    $wpdb->query( $query );

    Thank you

    https://www.ads-software.com/plugins/wordpress-seo/

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

  • I’m having a similar problem with Yoast’s slow queries (as measured by Query Monitor). The New Blog Post page normally takes 1.5 seconds to load. With Yoast SEO activated, it takes 12.5 seconds to load. The slow queries all look like this:

    DELETE
FROM wp_options
WHERE option_name LIKE '\_transient\_%\_6p6aw'
OR option_name LIKE '\_transient\_timeout\_%\_6p6aw'

DELETE
FROM wp_options
WHERE option_name LIKE '\_transient\_%\_6p6yG'
OR option_name LIKE '\_transient\_timeout\_%\_6p6yG'

... 

DELETE
FROM wp_options
WHERE option_name LIKE '\_transient\_%\_6tPNF'
OR option_name LIKE '\_transient\_timeout\_%\_6tPNF'

    There are several of these queries, and each one takes 1.3 to 1.4 seconds to execute. Most of the other queries execute in much less than 0.1 seconds.

    They’re all coming from WPSEO_Sitemaps_Cache_Validator::cleanup_database().

    I’m running the latest version of the plugin (3.2.3).

    How to fix these ultra-slow Yoast queries?

    I found out what was causing this problem and how to solve it. It was caused by a common issue of runaway transient entries in the wp_options table.

    I couldn’t believe those simple queries should take so long to run, so I looked at the wp_options table and was amazed that it had ballooned to more than 360,000 entries. All but about 500 of them were expired transients.

    So I ran the following SQL command:

    DELETE FROM wp_options WHERE option_name LIKE ('\_transient\_%');

    Then I defragmented the table, and after that my New Blog Post page loaded in 0.84 seconds instead of 12.5 seconds.

    I also verified with Query Monitor that the Yoast queries are now running in about 0.0003 seconds. Much better!

    For a long-term solution to clean up runaway transients every day, you can use the plugin Delete Expired Transients, which will run a query once a day like the one above to keep expired transients from getting out of control:
    https://www.ads-software.com/plugins/delete-expired-transients/

    Hope this solution helps others who are having the same problem.

    I had a similar issue with a site with a very large number of posts. I did not investigate things as much as I would of liked to but the above solution and plugin helped solve things.

    Thanks!

    @crobbinsdg: Great! ??

    Thread Starter des79

    (@des79)

    Hi Dexterity, crobbinsdg,

    Thank for your reply, I think it isn’t the best solutions. You use a plugin to remove transient that is the same task that WPSEO does.

    WPSEO create multi transient on db (categories, posts etc.) and on expire delete it and this redundant action on every page load bring site to multiple query on Db until crash (CPU overloading until 100% for deadlock).

    IMO it’s a temporary solution.

    Same issue here. delete-expired-transients did not solve the problem.

    Any ideas or workarounds?

    Plugin Support amboutwe

    (@amboutwe)

    Krzysztof Dryja (Aspexi) If you have a similar issue, please open a new request as this topic is about an old version.

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Clear Sitemap Cache – slow down site and deadlock on db’ is closed to new replies.