plugin causing high server resource consumption

Home Forums Product Support Forums Ajax Search Pro for WordPress Support plugin causing high server resource consumption

This topic contains 5 replies, has 2 voices, and was last updated by Ernest Marcinko Ernest Marcinko 2 months, 1 week ago.

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #37922
    jarry23
    jarry23
    Participant

    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.

    #37924
    jarry23
    jarry23
    Participant
    You cannot access this content.
    #37927
    jarry23
    jarry23
    Participant

    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.

    #37928
    Ernest Marcinko
    Ernest Marcinko
    Keymaster

    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 :)


    #37929
    jarry23
    jarry23
    Participant

    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.
    #37933
    Ernest Marcinko
    Ernest Marcinko
    Keymaster

    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 :)


Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.