Support » Plugin: Yet Another Related Posts Plugin (YARPP) » Shared Hosting Provider Overload- Violation notice caused by YARPP

  • Resolved vegandietblog


    Can someone please tell me if this has happened to them and how to fix it? I just upgraded to the latest version but cannot let this happen again or my hosting account may be shut down, according to GoDaddy.

    Your uch0914208053540 MySQL database caused an overload of shared resources for the server that it resides on. This overload threatened the health of the server and, in turn, threatened the connectivity of other customers utilizing it. This is, of course, unacceptable. Our database administrators had no choice but to take immediate action to protect our network and to prevent the possibility of a service interruption for other customers. Your database was disabled as a result.

    As we are unable to troubleshoot custom scripting, we are unable to provide a specific solution to you. However the following information was provided by our administrators:

    Problematic query:

    insert into wp_yarpp_related_cache (reference_ID,ID,score) SELECT 2748, ID, (0+ (MATCH (post_content) AGAINST (‘caption ???????? ????? ???????????????100 ???????? ???????????????????? ????????????? ??????????????? ??????????????? ??????? ????? ????? ?????????????????????????????????????? ???????????????????????????? ??? ?????????? ??? width 300 alignleft ‘)) * 3+ (MATCH (post_title) AGAINST (’23?? 21 ???????????????5 ‘)) * 1+ COUNT( DISTINCT tagtax.term_taxonomy_id ) * 1+ COUNT( DISTINCT cattax.term_taxonomy_id ) * 1) as score
    from wp_posts
    left join wp_term_relationships as blockrel on (wp_posts.ID = blockrel.object_id)
    left join wp_term_taxonomy as blocktax using (term_taxonomy_id)
    left join wp_terms as blockterm on (blocktax.term_id = blockterm.term_id and blockterm.term_id in (1,17,57))
    left JOIN wp_term_relationships AS thistag ON (thistag.object_id = 2748 )
    left JOIN wp_term_relationships AS tagrel on (tagrel.term_taxonomy_id = thistag.term_taxonomy_id
    AND tagrel.object_id = wp_posts.ID)
    left JOIN wp_term_taxonomy AS tagtax ON ( tagrel.term_taxonomy_id = tagtax.term_taxonomy_id
    AND tagtax.taxonomy = ‘post_tag’)
    left JOIN wp_term_relationships AS thiscat ON (thiscat.object_id = 2748 )
    left JOIN wp_term_relationships AS catrel on (catrel.term_taxonomy_id = thiscat.term_taxonomy_id
    AND catrel.object_id = wp_posts.ID)
    left JOIN wp_term_taxonomy AS cattax ON ( catrel.term_taxonomy_id = cattax.term_taxonomy_id
    AND cattax.taxonomy = ‘category’)
    where (post_status IN ( ‘publish’, ‘static’ ) and ID != ‘2748’) and post_password =” and post_type = ‘post’
    group by id
    having score >= 5.00 and count(blockterm.term_id) = 0 and COUNT( DISTINCT cattax.term_taxonomy_id ) >= 2 and COUNT( DISTINCT tagtax.term_taxonomy_id ) >= 2 order by score desc limit 5 on duplicate key update date = now()

    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE wp_posts range PRIMARY,type_status_date type_status_date 124 351 Using where; Using temporary; Using filesort
    1 SIMPLE blockrel ref PRIMARY PRIMARY 8 uch0914208053540.wp_posts.ID 2 Using index
    1 SIMPLE blocktax eq_ref PRIMARY PRIMARY 8 uch0914208053540.blockrel.term_taxonomy_id 1
    1 SIMPLE blockterm eq_ref PRIMARY PRIMARY 8 uch0914208053540.blocktax.term_id 1 Using index
    1 SIMPLE thistag ref PRIMARY PRIMARY 8 const 18 Using index
    1 SIMPLE tagrel eq_ref PRIMARY,term_taxonomy_id PRIMARY 16 uch0914208053540.wp_posts.ID,uch0914208053540.thistag.term_taxonomy_id 1 Using index
    1 SIMPLE tagtax eq_ref PRIMARY,taxonomy PRIMARY 8 uch0914208053540.tagrel.term_taxonomy_id 1
    1 SIMPLE thiscat ref PRIMARY PRIMARY 8 const 18 Using index
    1 SIMPLE catrel eq_ref PRIMARY,term_taxonomy_id PRIMARY 16 uch0914208053540.wp_posts.ID,uch0914208053540.thiscat.term_taxonomy_id 1 Using index
    1 SIMPLE cattax eq_ref PRIMARY,taxonomy PRIMARY 8 uch0914208053540.catrel.term_taxonomy_id 1

    This query examines 227,448 rows, which is unacceptable in shared hosting.

Viewing 2 replies - 1 through 2 (of 2 total)
  • Yeah I ran into the same problem with my Dreamhost account. It’d been going on for months. At first, I blamed Dreamhost for not being able to keep their lights on, then I started disabling my plugins one by one, this it turns out to be YARPP that was causing MySQL to grind things down to a halt.

    Dreamhost wouldn’t investigate to see which query was the culprit, but maybe your post will help the author optimize the plugin.

    In order for YARPP to calculate its results, it indeed needs to run some queries which are a little heavier than most. However, over time, these queries have been optimized and drastically simplified, joining only two tables instead of four, for example. YARPP also has its own aggressive caching built in.

    There are some settings which help make YARPP more performant. Information is in the FAQ in the readme.

    If you’re in touch with some technical folk at your hosting, I’m always happy to conversations with engineers as well. Feel free to send them my way. 🙂

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Shared Hosting Provider Overload- Violation notice caused by YARPP’ is closed to new replies.