Queries slowing site.

Vincec
(@vincec)

4 months, 4 weeks ago
With the recent update (current version as of 2/10) to SSP my MySQL server was spending up to 86% of its time looping through my 14000 terms we have on the site. My page speed when under load went from less than a second to over 12 seconds when the page could not be cached. There were well over 100 calls to the query. The ssp_get_tags function is new, and seems to be the issue. I can send full logs, but support asked I post it here.

From my server logs (via New Relic):
/SeriouslySimplePodcasting\Integrations\Blocks\Castos_Blocks::get_tags
Custom/ssp_get_tags
Custom/WP_Term_Query::populate_terms < 83% of the server resources were going to this and I have hundreds of calls to Custom/get_term

The function in question.
```
function ssp_get_tags( $hide_empty = false ) {
    $tags = get_terms( 'post_tag', array(
        'post_type' => ssp_post_types(),
        'hide_empty' => $hide_empty,
    ) );
    return is_array( $tags ) ? $tags : array();
}
```

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

Plugin Author Serhiy Zakharchenko
(@zahardoc)

4 months, 4 weeks ago

Hi @vincec,

Thank you for bringing this to our attention. We will fix the issue and enhance the caching in the next plugin update.

arypneta
(@arypneta)

4 months, 3 weeks ago

I’m experiencing this issue as well, so I’m glad to see that a fix is on the way!

Thread Starter Vincec
(@vincec)

4 months, 3 weeks ago

arypneta rolling back to Version 3.7.1 does not have the same issue while waiting for the fix.

Thread Starter Vincec
(@vincec)

4 months, 3 weeks ago

Version 3.7.1 does not have the issue while waiting for the fix.

arypneta
(@arypneta)

4 months, 2 weeks ago

@vincec Good to know, thanks!

Plugin Author Serhiy Zakharchenko
(@zahardoc)

4 months, 1 week ago

@vincec @arypneta Did version 3.9.0 resolved the issue?

arypneta
(@arypneta)

4 months, 1 week ago

I upgraded to 3.9.0 on Friday and when I checked today, I only saw one error message about a “KILLED QUERY” from over the weekend. That’s significantly better than what we were experiencing before.

When I previously reverted to 3.7.1, we stopped getting any of them, but one in three or four days is probably fine. I will keep checking it over the week and see if it increases when we have people in there actively working/posting articles.

arypneta
(@arypneta)

4 months, 1 week ago

@zahardoc Just to confirm, looks like it is about one error every couple days (which is a lot better than it was before). So, that should be fine for us, but if you were expecting no errors at all, we do still get them occasionally.

Plugin Author Serhiy Zakharchenko
(@zahardoc)

4 months, 1 week ago

Hi @arypneta,

Could you share what kind of error you are getting now?

arypneta
(@arypneta)

4 months, 1 week ago

Yeah, here you go. I think our hosting provider may be the one killing the query because it’s going on for too long:

KILLED QUERY (16800 characters long generated in …./wp-content/plugins/seriously-simple-podcasting/php/includes/ssp-functions.php:1753): SELECT t.*, tt.* FROM wp_2_terms AS t INNER JOIN wp_2_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE t.term_id IN (1949,1386,1028,1027,725,8013,1504,605,1446,849,8016,2805,15331,7093,1808,3197,1763,1854,4748,7234,10475,91,979,220,1507,2410,6038,1138,12286,12288,4138,5444,5772,1010,2877,4384,13545,4032,1940,14074,192,16043,1905,2281,13311,1450,9958,305,19715,16512,3252,1293,543,1956,366,8197,4196,13717,19498,13952,13406,1939,840,1896,9126,982,3864,15578,7715,3160,767,10834,10835,7358,1861,2073,2860,10318,14643,13154,10508,2706,16329,1735,9115,3489,17565,1053,4139,4489,1146,1075,4191,4006,13987,1131,17309,17904,2809,11583,10137,19545,449,15626,11172,3624,1004,3627,848,4786,13212,2373,1835,304,4751,4669,1601,8805,1384,10542,4383,8325,907,2422,10128,19674,13167,3641,6269,2862,7830,15581,6602,19632,4928,3940,17908,12603,1717,12847,3280,19635,547,8570,735,1263,752,4618,2412,6975,708,13662,2436,327,1833,4791,4192,13123,8463,12370,981,3708,7081,8581,1525,1906,4752,2345,6571,16330,2026,4211,14439,8983,7458,1147,78,994,8960,1133,1196,1823,12174,15114,715,13293,13148,7005,2054,1193,6951,7357,5411,5380,6430,6199,6336,13244,14154,628,9233,15569,6899,1044,903,7741,7758,7675,10292,15613,12862,10894,2083,10783,10786,11171,3030,12998,13460,6394,367,13281,19393,4358,4403,13583,1923,1262,7266,19049,1011,13225,529,6345,412,2747,10935,6931,4895,1694,8801,6045,8631,5106,48,844,843,395,3695,2208,178,2016,1238,11789,4676,7864,9041 …..

We had a lot of tags on this site, but when we started getting these errors, we cleaned out the list, so we’re down to about 4,500 tags, which doesn’t seem like a huge number to me given that we have more normal posts than that plus almost 350 posts of type podcast that also can have tags. But I don’t really know what a baseline normal number of tags is, so maybe that is way higher than most other sites using SSP.

Plugin Author Serhiy Zakharchenko
(@zahardoc)

3 months, 3 weeks ago

Hi @arypneta,

Thank you for the details. Unfortunately, I can’t help with this since we are simply using a WordPress function there, and the query is generated by WordPress. I recommend reducing the number of tags further or hiring a developer to write a custom query to override the current ssp_get_tags() function.

arypneta
(@arypneta)

3 months, 3 weeks ago

The really weird thing is that we have sites with many more tags (including one with 1,000 more tags that is also using SSP) that don’t give us this error message. So, perhaps there’s some other plugin conflict.

But anyway, the number of problems have been much reduced by the update, so this is probably fine for the time being.

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

You must be logged in to reply to this topic.