plugin causing high server resource consumption

[jarry23Participant]

Hi Ernest,

I am not 100% sure if its caused by your plugin but it seem it is. I use your pluging for few months and all was running fine, but yesterday I did a little sorting in categories – frontend search settings > categories & taxonomy terms. I added all categories manually and sorted them as needed. After that site started to consume high resources.

Here are some of the database queries that are slow and consume a lot of server resources:

1. Executed 4h 47m 46s ago for 188.767762 sec on Database –> xxx
Date: 2022-06-04 20:32:17 Query_time: 188.767762 Rows_examined: 963400: Rows_sent 0 Lock_time: 0.002398 Query_chars: 2359
SELECT staging_FGg_posts.post_title as title, staging_FGg_posts.post_title as post_title, staging_FGg_posts.ID as id, 1 as blogid, staging_FGg_posts.post_date as date, staging_FGg_posts.post_date as post_date, ” as content, staging_FGg_posts.post_
……
ish’) ) AND ( staging_FGg_posts.post_password = ” ) AND (1) AND ( (1) ) GROUP BY staging_FGg_posts.ID ORDER BY group_priority DESC, priority DESC, p_type_priority ASC, relevance DESC, staging_FGg_posts.post_date DESC, id DESC LIMIT 50000;
——————————————————————————————————————-
2. Executed 4h 47m 46s ago for 184.136921 sec on Database –> xxx
Date: 2022-06-04 20:32:17 Query_time: 184.136921 Rows_examined: 976917: Rows_sent 0 Lock_time: 0.002234 Query_chars: 2359
SELECT staging_FGg_posts.post_title as title, staging_FGg_posts.post_title as post_title, staging_FGg_posts.ID as id, 1 as blogid, staging_FGg_posts.post_date as date, staging_FGg_posts.post_date as post_date, ” as content, staging_FGg_posts.post_
……
ish’) ) AND ( staging_FGg_posts.post_password = ” ) AND (1) AND ( (1) ) GROUP BY staging_FGg_posts.ID ORDER BY group_priority DESC, priority DESC, p_type_priority ASC, relevance DESC, staging_FGg_posts.post_date DESC, id DESC LIMIT 50000;
——————————————————————————————————————-
3. Executed 4h 48m 5s ago for 182.76289 sec on Database –> xxx
Date: 2022-06-04 20:31:58 Query_time: 182.762890 Rows_examined: 1009246: Rows_sent 9546 Lock_time: 0.000497 Query_chars: 2369
SELECT staging_FGg_posts.post_title as title, staging_FGg_posts.post_title as post_title, staging_FGg_posts.ID as id, 1 as blogid, staging_FGg_posts.post_date as date, staging_FGg_posts.post_date as post_date, ” as content, staging_FGg_posts.post_
……
ish’) ) AND ( staging_FGg_posts.post_password = ” ) AND (1) AND ( (1) ) GROUP BY staging_FGg_posts.ID ORDER BY group_priority DESC, priority DESC, p_type_priority ASC, relevance DESC, staging_FGg_posts.post_date DESC, id DESC LIMIT 50000;
——————————————————————————————————————-
4. Executed 4h 47m 46s ago for 182.710029 sec on Database –> xxx
Date: 2022-06-04 20:32:17 Query_time: 182.710029 Rows_examined: 936829: Rows_sent 0 Lock_time: 0.001523 Query_chars: 2351
SELECT staging_FGg_posts.post_title as title, staging_FGg_posts.post_title as post_title, staging_FGg_posts.ID as id, 1 as blogid, staging_FGg_posts.post_date as date, staging_FGg_posts.post_date as post_date, ” as content, staging_FGg_posts.post_
……
ish’) ) AND ( staging_FGg_posts.post_password = ” ) AND (1) AND ( (1) ) GROUP BY staging_FGg_posts.ID ORDER BY group_priority DESC, priority DESC, p_type_priority ASC, relevance DESC, staging_FGg_posts.post_date DESC, id DESC LIMIT 50000;
——————————————————————————————————————-
5. Executed 4h 48m 6s ago for 181.05989 sec on Database –> xxx
Date: 2022-06-04 20:31:57 Query_time: 181.059890 Rows_examined: 1010337: Rows_sent 9604 Lock_time: 0.001971 Query_chars: 2361
SELECT staging_FGg_posts.post_title as title, staging_FGg_posts.post_title as post_title, staging_FGg_posts.ID as id, 1 as blogid, staging_FGg_posts.post_date as date, staging_FGg_posts.post_date as post_date, ” as content, staging_FGg_posts.post_
……
ish’) ) AND ( staging_FGg_posts.post_password = ” ) AND (1) AND ( (1) ) GROUP BY staging_FGg_posts.ID ORDER BY group_priority DESC, priority DESC, p_type_priority ASC, relevance DESC, staging_FGg_posts.post_date DESC, id DESC LIMIT 50000;

I changed it back to “use all from product cat” that I used all the time and I had no problem, but if possible, take a look at it. I would like to sort the categories.

[jarry23Participant]

You cannot access this content.

[jarry23Participant]

I can confirm that after I changed it back to “use all from product cat” the CPU usage dropped. So the issue is caused by this settings.

[Ernest MarcinkoKeymaster]

Hi,

Category filters can be excessive indeed, especially if you have a lot of categories (hundreds) and lot of posts to filter (thousands).
It is always recommended to use the least amount filters if possible, with the least amount of elements.On the server side this is optimized as much as possible, but some use cases can’t be optimized any further. The worst of all can be the checkbox type filters, when not all of the categories are used, or they are in a custom order. If you can’t avoid using filters, I recommend the following best use cases, to get the best possible performance:
– For checkbox layout, try having them unselected by default: https://i.imgur.com/NkK395W.png
– Also for checkboxes, reduce the number of terms visible in the filters
– Consider using a drop-down or a drop-down multiselect layout instead for a large number of visible terms, it is much more efficient in terms of performance: https://i.imgur.com/g7XkCzj.png
With the drop-down search the user can choose the exact categories, reducing the information sent to the server greatly.
– If can be avoided, do not use any taxonomy based exclusions nor inclusions in combination with the filters.
– Use the caching feature. Repetitive queries, including filtering will be cached, and no longer require table joins to get the items
These can radically affect the overally performance of the queries.

Best,
Ernest Marcinko
If you like my products, don't forget to rate them on codecanyon :)

[jarry23Participant]

Hi,

thank you for that info. I changed to drop-down multiselect layout. Just qestion. Can I set by CSS that categories will be visible on default without need to click on search filed (see attached screen).
Cahing will help a lot but my current hosting is limited by file usage and I am already almost in limit, so extra cached files for each queries are not possible for me now. Is there any chance to cache only filtering? For example by some another caching plugin?

Attachments:

You must be logged in to view attached files.

[Ernest MarcinkoKeymaster]

I have looked up the documentation of the library, which implements this feature, and it seems like that is not possible. A soon as the user clicks anywhere on the page, the container is fully deleted from the DOM tree, and reset when focused.
I checked if there are maybe options to somehow control the opening/closing, that is possible and very simple. However if the user clicks anywhere outside the selection container, it is automatically closed, and there is no option to prevent that.

You can try a custom script, something like this:

add_action('wp_footer', 'asp_add_footer_script', 9999);
function asp_add_footer_script() {
	?>
	<script>
	(function($){
		$('.asp_goselect2').asp_select2('open');
		setTimeout(function(){
			$('.asp_goselect2').asp_select2('open');
		}, 500);
		$('body').on('click', function(){
			$('.asp_goselect2').asp_select2('open');
		});
	}(jQuery));
	</script>
	<?php
}

This may keep it forcefully open, but it will not work for all of the cases.

Best,
Ernest Marcinko
If you like my products, don't forget to rate them on codecanyon :)

[Author]

Posts