WordPress.org

Ready to get started?Download WordPress

Forums

[Plugin: Yet Another Related Posts Plugin] Broken in 2.9? (52 posts)

  1. davidgustav
    Member
    Posted 4 years ago #

    I'm glad I'm not the only one. I upgraded to 2.9 several days ago and everything was fine until this morning. My hosting company had to deactivate my database because an overload of requests. Looking at the log I traced it back to YARP.

    It started with WordPress database error Server shutdown in progress for query select count(*) as count from yarpp_related_cache where reference_ID = 5621 and date > date_sub(now(),interval 600 minute) made by require, require_once, include, related_posts, yarpp_related, yarpp_cache_enforce

    The hosting company also said that something weird had happened to my .htaccess file, but I have no idea how that is related.

    Hoping that I can reactivate this plugin in the future, it was great.

  2. @davidgustav - it's possible that the settings you're using is just too computationally intensive for your server. There are some tips for this in the FAQ.

  3. davidgustav
    Member
    Posted 4 years ago #

    @mitchoyoshitaka Thanks, I'll take a look. It had been running fine for 6 months with no settings changed. The upgrade to 2.9 is the only variable I can think of right now. Unless the number of posts grew too fast I guess...

  4. @davidgustav - hopefully the caching will take care of it and keep it under control after a while too. Best of luck.

  5. Dani3l3
    Member
    Posted 4 years ago #

    I don't know... the plugin has been sitting there, with the same options, for many months now...
    only after upgrading to WP2.9 - and to YARPP 3.1.1 - on the same day (I know, I shouldn't have upgraded on the same day... but releases of EVERYTHING and anything are way too frequent these days... but I digress...:-)) I noticed the high load.
    look at this pic http://www.muscetta.com/images/WP29YARPP311load.png
    in that picture you can see the load until last sunday... the CPU getting a lot busier after the upgrade on sunday... being busy a couple of days...
    halfway tuesday the load decreases (I changed settings saying not to consider tags and categories anymore) but still being busy... then, ultimately, on saturday, the load getting back to its previous level after I deactivated the plugin.
    I am now giving the beta a try.

  6. hifa
    Member
    Posted 4 years ago #

    I have tried beta release also..but it still causes high load...

  7. Dani3l3
    Member
    Posted 4 years ago #

    FYI, in MY testing (3 hours...) the latest beta seems to be slightly less heavy.

    Anyhow, I think I found the culprit: the highEST load on my server was caused by the calculation of related posts when pulling the RSS feed... which was causing the recalculation of related posts for ALL posts in the feed (50 posts, in my case!).
    Disabling the feature from feeds it seems to go A LOT better... but I am not quote sure as WHY I had not seen this before, in previous versions - I don't remember changing any option...

  8. @Dani3l3 - the RSS forces all those posts' related posts to be calculated at once, for a single request (though it's cached afterwards) so it is indeed intensive, particuarly at the beginning. If turning it off works for you, though, I'm glad. :) Feel free to turn it back on after a while of caching... this action should not reset the cache so you shouldn't notice any spike then.

    @hifa, perhaps that would help for you as well.

  9. Dani3l3
    Member
    Posted 4 years ago #

    ok, I confirm that with the latest beta and with the option of the RSS disabled the CPU has been a lot quiter (more or less at the level it was earlier) for the last 12 hours.
    I just don't remember having ENABLED the option now - I don't actually remember having changed any option at all during of just after upgrading...
    might it be some change in core related to RSS the way feeds are handled, if the plugin's code did not change?

  10. @Dani3l3 that is possible, and unfortunately I don't know about those changes. :/

  11. sjgold
    Member
    Posted 4 years ago #

    Major issues... Absolutely major.

    After 3-4 days of problems with the database. My webhost finally shut us down with the following email:

    We have disabled your database to return the server to normal usage. To re-enable your database, you will need to correct the following query:

    INSERT INTO fgt_yarpp_related_cache (reference_ID,ID,score)
    (SELECT 842, ID, (0+ (MATCH (post_content) AGAINST ('enjoy share ')) *
    1+ (MATCH (post_title) AGAINST ('stones chimaera ')) * 1+ COUNT(
    DISTINCT tagtax.term_taxonomy_id ) * 1+ COUNT( DISTINCT
    cattax.term_taxonomy_id ) * 1) as score from fgt_posts
    LEFT join fgt_term_relationships as blockrel on (fgt_posts.ID = blockrel.object_id)
    LEFT join fgt_term_taxonomy as blocktax using (term_taxonomy_id)
    LEFT join fgt_terms as blockterm on (blocktax.term_id = blockterm.term_id and blockterm.term_id in (1))
    LEFT JOIN fgt_term_relationships AS thistag ON (thistag.object_id = 842 )
    LEFT JOIN fgt_term_relationships AS tagrel on (tagrel.term_taxonomy_id = thistag.term_taxonomy_id
    AND tagrel.object_id = fgt_posts.ID)
    LEFT JOIN fgt_term_taxonomy AS tagtax ON ( tagrel.term_taxonomy_id = tagtax.term_taxonomy_id
    AND tagtax.taxonomy = 'post_tag')
    LEFT JOIN fgt_term_relationships AS thiscat ON (thiscat.object_id = 842 )
    LEFT JOIN fgt_term_relationships AS catrel on (catrel.term_taxonomy_id = thiscat.term_taxonomy_id
    AND catrel.object_id = fgt_posts.ID)
    LEFT JOIN fgt_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 != '842') and post_password =''
    GROUP BY id
    HAVING score >= 1.00 and count(blockterm.term_id) = 0 order by score
    desc limit 5) union (SELECT 842, ID, (0+ (MATCH (post_content) AGAINST
    ('enjoy share ')) * 1+ (MATCH (post_title) AGAINST ('stones chimaera
    ')) * 1+ COUNT( DISTINCT tagtax.term_taxonomy_id ) * 1+ COUNT( DISTINCT
    cattax.term_taxonomy_id ) * 1) AS score FROM fgt_posts
    LEFT join fgt_term_relationships as blockrel on (fgt_posts.ID = blockrel.object_id)
    LEFT join fgt_term_taxonomy as blocktax using (term_taxonomy_id)
    LEFT join fgt_terms as blockterm on (blocktax.term_id = blockterm.term_id and blockterm.term_id in (1))
    LEFT JOIN fgt_term_relationships AS thistag ON (thistag.object_id = 842 )
    LEFT JOIN fgt_term_relationships AS tagrel on (tagrel.term_taxonomy_id = thistag.term_taxonomy_id
    AND tagrel.object_id = fgt_posts.ID)
    LEFT JOIN fgt_term_taxonomy AS tagtax ON ( tagrel.term_taxonomy_id = tagtax.term_taxonomy_id
    AND tagtax.taxonomy = 'post_tag')
    LEFT JOIN fgt_term_relationships AS thiscat ON (thiscat.object_id = 842 )
    LEFT JOIN fgt_term_relationships AS catrel on (catrel.term_taxonomy_id = thiscat.term_taxonomy_id
    AND catrel.object_id = fgt_posts.ID)
    LEFT JOIN fgt_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 != '842') and post_password =''
    GROUP BY id
    HAVING score >= 1.00 and count(blockterm.term_id) = 0 order by score desc limit 5) on duplicate key update date = now()

    Disabling the YARPP plugin will resolve this issue.

  12. @sjgold At a quick glance I can't see anything wrong with that query... if it's simply that it's too computationally intensive, turning off any disallowed tags or categories, and not considering tags and categories would both help performance.

    I assume you're using the latest version?

  13. hifa
    Member
    Posted 4 years ago #

    @sjgold thanks for reporting that..I was seeking solution for since 2.9 upgrade..
    and @mitchoyoshitaka taken ur recommendation and I unchecked tags, categories from relatedness..
    voila..no excessive load, no high cpu any more..

    using yarpp after a long time again..

    Thanks!

  14. @hifa great!

  15. danigirl
    Member
    Posted 4 years ago #

    First, thanks for a great plugin, I really like it and am amazed to see how responsive you are!

    In upgrading to WP 2.9 and then today 2.9.1, I noticed that some of my dashboard functionality disappeared (can't view recent comments or WP news for instance) and had lost the ability to edit some pages but not all. Very strange! I figured it was YARRP by deactivating and reactivating all the plug ins, and the problem is mostly fixed by upgrading to YARPP 3.1.3 but I have still lost a few items on the dashboard (recent comments, WP news.) I'd rather have YARPP activated than these features, but am worried about the feed problems some others have mentioned. I'm at http://danigirl.ca/blog. Thougths?

  16. vancebell
    Member
    Posted 4 years ago #

    Just to follow up on Danigirl's observations:

    After upgrading a couple blogs to 2.9.1 I've noticed similar problems with the Dashboard while using YARPP 3.1.3 (even as the only activated plug-in). The left nav and Right Now callout will be the only panels showing - the rest of the page being blank.

    Also, the ability to upload images via Media or Add an Image was simultaneiously affected. After selection and submission of a moderately sized file, the process fail to make it to the next step.

    Deactivating YARPP 3.1.3 returns functionality to normal.

  17. William Bowles
    Member
    Posted 4 years ago #

    Hi,
    YARPP was the only one of its kind that I've actually gotten to work properly. However I've discovered that it's causing havoc with MsQL. Here's the report from my (excellent) hosting co who brought it to my attention:

    Bill

    We've been seeing a lot of large, badly written MySQL queries coming in from your user of late. It looks to be something to do with a plugin, "yarpp_related_cache". Would you be able to disable the plugin involved?

    I've copied the query report below, if you're interested (or if you want to send it on to the plugin's creator :))

    Best regards,

    Johnathon

    Count : 85
    Time : 2899 s total, 34.105882 s avg, 11 s to 119 s max
    95% of Time : 2395 s total, 29.9375 s avg, 11 s to 85 s max
    Lock Time (s) : 0 total, 0 avg, 0 to 0 max
    95% of Lock : 0 total, 0 avg, 0 to 0 max
    Rows sent : 0 avg, 0 to 0 max (0.00%)
    Rows examined : 17.88M avg, 4.80M to 65.18M max (62.57%)
    Database : creative
    Users :
    creative@truth-int.positive-internet.com 192.168.254.1
    EXPLAIN : Not a SELECT statement

    Query abstract:
    SET timestamp=N; INSERT INTO wp_yarpp_related_cache
    (reference_id,id,score) (SELECT N, id, (N+ (MATCH (post_content) AGAINST
    ('S')) * N+ (MATCH (post_title) AGAINST ('S')) * N+ COUNT( DISTINCT
    tagtax.term_taxonomy_id ) * N+ COUNT( DISTINCT cattax.term_taxonomy_id )
    * N) AS score FROM wp_posts LEFT JOIN wp_term_relationships AS thistag
    ON (thistag.object_id = N ) 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 =
    'S') LEFT JOIN wp_term_relationships AS thiscat ON (thiscat.object_id =
    N ) 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 =
    'S') WHERE (post_status IN ( 'S', 'S' ) AND id != 'S') AND post_password
    ='S' GROUP BY id HAVING score >= N.N AND COUNT( DISTINCT
    cattax.term_taxonomy_id ) >= N AND COUNT( DISTINCT
    tagtax.term_taxonomy_id ) >= N ORDER BY score DESC LIMIT N) UNION
    (SELECT N, id, (N+ (MATCH (post_content) AGAINST ('S')) * N+ (MATCH
    (post_title) AGAINST ('S')) * N+ COUNT( DISTINCT tagtax.term_taxonomy_id
    ) * N+ COUNT( DISTINCT cattax.term_taxonomy_id ) * N) AS score FROM
    wp_posts LEFT JOIN wp_term_relationships AS thistag ON
    (thistag.object_id = N ) 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 =
    'S') LEFT JOIN wp_term_relationships AS thiscat ON (thiscat.object_id =
    N ) 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 =
    'S') WHERE (post_status IN ( 'S', 'S' ) AND id != 'S') AND post_password
    ='S' GROUP BY id HAVING score >= N.N AND COUNT( DISTINCT
    cattax.term_taxonomy_id ) >= N AND COUNT( DISTINCT
    tagtax.term_taxonomy_id ) >= N ORDER BY score DESC LIMIT N) ON duplicate
    KEY UPDATE date = now();

    Query sample:
    SET timestamp=1263882775;
    insert into wp_yarpp_related_cache (reference_ID,ID,score) (SELECT 300,
    ID, (0+ (MATCH (post_content) AGAINST ('percent his inflation growth
    eurozone year prices europe britain trichet level recession french bank
    economy european billion president may eu ')) * 3+ (MATCH (post_title)
    AGAINST ('walker martin unravel economies european ')) * 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 thistag ON (thistag.object_id = 300 )
    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 = 300 )
    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 != '300') and
    post_password =''
    group by id
    having score >= 2.50 and COUNT( DISTINCT cattax.term_taxonomy_id ) >= 1
    and COUNT( DISTINCT tagtax.term_taxonomy_id ) >= 1 order by score desc
    limit 10) union (SELECT 300, ID, (0+ (MATCH (post_content) AGAINST
    ('percent his inflation growth eurozone year prices europe britain
    trichet level recession french bank economy european billion president
    may eu ')) * 3+ (MATCH (post_title) AGAINST ('walker martin unravel
    economies european ')) * 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 thistag ON (thistag.object_id = 300 )
    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 = 300 )
    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 != '300') and
    post_password =''
    group by id
    having score >= 2.50 and COUNT( DISTINCT cattax.term_taxonomy_id ) >= 1
    and COUNT( DISTINCT tagtax.term_taxonomy_id ) >= 1 order by score desc
    limit 10) on duplicate key update date = now();

    http://www.creative-i.info

  18. jimsky7
    Member
    Posted 4 years ago #

    I love YARPP and have used and customized (templates) it extensively for myself and other NGOs.

    We found one of our servers running "extremely hot" today and MySQL was just running away using 100% of two CPUs every time a new page was being generated in WordPress. It was using 2 (fast) CPUs at 100% for about 15 seconds to generate each YARPP section for each post.

    I disabled YARPP and it went to normal performance.

    When I re-enabled, the CPUs went back to their cranking behavior.

    My guess is that the YARPP cache wasn't working perfectly - just a guess. Because we were fine with YARPP after you put the cache in place some time ago. And now, the CPUs were running away when just random visitors were looking at posts - every post.

    We're on WP 2.9.1, just for the last few days on this particular WP site, so perhaps there was some interaction with 2.9 or 2.9.1.

    I just installed the 3.1.4 beta of YARPP and so far things look good. Caches seem to be working. Just wanted to give you my first feedback on the beta.

  19. jimsky7
    Member
    Posted 4 years ago #

    Aha - I see what YARPP is doing - it is related to cache, but not exactly to this thread so I will open another thread to ask about it. No need to reply to what I asked above.

    I will call the new thread - "cache rules cause thrashing in 3.1.3"

  20. danigirl, vancebell, YARPP doesn't have any code that should affect the dashboard... is it possible that there's a PHP error that's stopping the dashboard from loading completely? You might see something like this if you "display source" in your browser.

    Creative-i, jimsky7, while the cache in 3.x has certainly helped, YARPP can make WP more database intensive. Making it "do not consider" tags and categories, as well as removing any excluded tags or categories would help.

  21. Hottospot
    Member
    Posted 3 years ago #

    I have started experiencing issues with the plugin which results in high CPU and high SQL memory which is causing problems with one of my sites that has 7000+ posts and 7 pages.

    Looking through my Slow Query logs the file is populated with numerous wp_yarpp_related_cache related queries.

    What is worrying is the number of records affected (running into tens of millions!) e.g on just one days slow query log file I have literally hundreds of instances of the

    insert into wp_yarpp_related_cache (reference_ID,ID,score)

    query in my log file with stats such as the following:

    # Query_time: 16 Lock_time: 0 Rows_sent: 0 Rows_examined: 12184905
    # Query_time: 26 Lock_time: 0 Rows_sent: 0 Rows_examined: 21898590
    # Query_time: 21 Lock_time: 0 Rows_sent: 0 Rows_examined: 17727610
    # Query_time: 9 Lock_time: 0 Rows_sent: 0 Rows_examined: 6533365
    # Query_time: 7 Lock_time: 0 Rows_sent: 0 Rows_examined: 4291965

    Also on further analysis of the query which is being logged I can see that you are doing a UNION statement with a number of LEFT JOINS.

    However both parts of the UNION are exactly the same!

    I am not sure why you need to do this as I have tested the query out myself and you can remove either half of the UNION to get the same results. Maybe there is a need for the UNION if the criteria differs on each side but in the case where its the same you should change the query as they are very performance intensive and doing each one twice when there is no need is causing a lot of problems.

    For example this is just one of the many queries from the log file I have found where both the top and bottom half of the UNION is the same

    insert into wp_yarpp_related_cache (reference_ID,ID,score)

    (SELECT 10309, ID, (0+ (MATCH (post_content) AGAINST ('euro global banks dollars world economic program debt greece crisis taxpayers bailout european governance financial currency dollar states money single ')) * 1+ (MATCH (post_title) AGAINST ('euro bail looted taxpayers american ')) * 3+ 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 (29,1497))
    left JOIN wp_term_relationships AS thistag ON (thistag.object_id = 10309 )
    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 = 10309 )
    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 != '10309') and post_password =''
    group by id
    having score >= 2.50 and count(blockterm.term_id) = 0 order by score desc limit 5)

    union

    (SELECT 10309, ID, (0+ (MATCH (post_content) AGAINST ('euro global banks dollars world economic program debt greece crisis taxpayers bailout european governance financial currency dollar states money single ')) * 1+ (MATCH (post_title) AGAINST ('euro bail looted taxpayers american ')) * 3+ 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 (29,1497))
    left JOIN wp_term_relationships AS thistag ON (thistag.object_id = 10309 )
    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 = 10309 )
    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 != '10309') and post_password =''
    group by id
    having score >= 2.50 and count(blockterm.term_id) = 0 order by score desc limit 5)

    on duplicate key update date = now();

    Maybe you can look into this?

  22. @Hottospot the FAQ includes some hints which would help you make the DB calls less computationally intensive.

Topic Closed

This topic has been closed to new replies.

About this Topic