[Plugin: Yet Another Related Posts Plugin] Broken in 2.9?

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 :
[email protected] 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();

https://www.creative-i.info

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.

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”

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.

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?

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