Slow Search Queries - Possibly related to virtual categories

[harrigo]

I have been using this module for a few years now and have found it relatively quick however over time have noticed a bit of a slow down. I have been playing with this on lesser hardware recently and found queries to be quite slow... here is a section of my profiler:

magento | 1.741605 | 1.741605 | 1 | 28,880,656 | 48,234,496
-- | -- | -- | -- | -- | --
...
·  ·  ·  ·  ·  ·  ·  ·  TEMPLATE:/var/www/buzzdevb/public_html/var/view_preprocessed/pub/static/vendor/magento/module-catalog-search/view/frontend/templates/result.phtml | 1.354794 | 1.354794 | 1 | 5,564,800 | 27,262,976
·  ·  ·  ·  ·  ·  ·  ·  ·  EAV: Magento\Eav\Model\Config::getAttribute | 0.001753 | 0.000008 | 212 | 9,776 | 0
·  ·  ·  ·  ·  ·  ·  ·  ·  ·  EAV: Magento\Eav\Model\Config::getEntityType | 0.000604 | 0.000003 | 212 | 11,000 | 0
·  ·  ·  ·  ·  ·  ·  ·  ·  EVENT:catalog_block_product_list_collection | 0.000271 | 0.000271 | 1 | 85,608 | 0
·  ·  ·  ·  ·  ·  ·  ·  ·  ·  OBSERVER:review | 0.000255 | 0.000255 | 1 | 83,800 | 0
·  ·  ·  ·  ·  ·  ·  ·  ·  EAV:load_collection | 1.241051 | 1.241051 | 1 | 2,616,152 | 27,262,976
·  ·  ·  ·  ·  ·  ·  ·  ·  ·  ES:Execute Search Query | 1.107998 | 0.276999 | 4 | 1,628,528 | 10,485,760
·  ·  ·  ·  ·  ·  ·  ·  ·  ·  ES:Virtual Rule getSearchQueriesByChildren | 0.076594 | 0.038297 | 2 | 20,891,272 | 14,680,064

So the whole page is taking around 1.74 seconds which is pretty slow for my site with uncached product and category pages taking like 0,5-0,7s TTFB. The main issue I am having is certain search queries. My store has about 20,000 products and feel that should be relatively small but searching plates can take a while as have a few 1000 of them and I feel this shouldn't be the case.

The main slow down seems to be within the ES:Execute search query (taking 1.1s of the 1.7) and i thought that should be pretty lightning quick. I have looked at other "slow" examples on here where the issue was not the ES query time as that was clocking in at 0.001889 which would be magic if that was the case for me!

I have been finding reducing my usage of virtual categories has been helping this and the more that are created seems to be worsening but I do like the feature and have come to reply on it in some cases.

I am working on moving to having these "virtual categories" actually indexed based on rules so they are just normal categories refreshed every night due to this but just wanted to check it this was normal and if there was anything else that could be suggested that may be going wrong or if this is completely abnormal?

[romainruaud]

Hello @harrigo

Could you copy/paste an example of generated query ? You can enable logging in Stores > Configuration > Elasticsuite > Enable Debug Mode and the queries will be logged into the system.log

If we can see the query we'll be able to give you insights about the processing time.

[harrigo]

Have attached as was large, this was only one or two queries but looks quite crazy to me. I have not got that many optimisers or anything although have played with a few, (there are 5 active ones at present).

I have also reduced down i believe searchable attributes to just skus, mpn meta data title and a few others I believe via Magento attributes "use in search" config to try and speed it up. I also thought it might be to do with Use Category Name in product search so have disabled that on atleast all the virtual categories but unsure if it has made that query any lighter. Any more advice would be greatly appreciated.

system.log

[romainruaud]

Well there's a lot of virtual categories clauses combined in this query, indeed. The query itself has a size of 1.7Mo :/

What is the use case behind your virtual categories ? I see some of them based on product material, style, etc... Is this due to the fact that you do not have anything efficient to order your products before they are added to Magento ?

[harrigo]

Well most should be brands, as that allows products to be placed in one main category and then autofilled into brands so we don't have to worry about forgetting to add them there. When they have a colour / style that might be in cases where brand and range and that range is all blue like a certain range of crockery or we often give a range a style and so that is what builds the range section for that brand.

I have been asking to keep usage to minimum on these and removed around 100 virtual categories and made sure they have a root category that is not the main store root however it still keeps getting larger.

Yeah that query is very large does it need to include all the VCS in a search query or could I botch it out in my case, not sure why it needs to know about categories in initial query?

[harrigo]

Right so i'm just testing botching them out although i know probably not a great idea and no idea what i'm doing i'm just wondering if this may just help in my case...

Smile\ElasticsuiteVirtualCategory\Search\Request\Product\Attribute\Aggregation/Category;

    public function getAggregationData(\Magento\Catalog\Model\ResourceModel\Eav\Attribute $attribute)
    {
	//RETURN EMPTY ARRAY INSTEAD
        return $bucketConfig = [];

        $facetQueries = $this->getFacetQueries();
        if (!empty($facetQueries)) {
            $bucketConfig = ['type' => BucketInterface::TYPE_QUERY_GROUP, 'name' => 'categories', 'queries' => $facetQueries];
        }

        return $bucketConfig;
    }

Just to return an empty array I guess but not entirely sure what i've messsed with everything seems to work and rapid compared to before (ES:Execute Search Query | 0.022710) with much smaller queries.

What repercussions can i expect from doing that?

[frostblogNet]

Hello we have seen similar problems. The query that gives us the most trouble is the one in the backend when you go to the categories overview. Apparently all categories are fetched there with one ES query. We had Eleastic Search 7.12 in use so far after an update to 7.13 there were failures of Eleastic Search when someone in the backend accessed the category overview. Only a downgrade to ES 7.12 brought stability again.The store has about 20.000 simple products, 3000 config products and about 100 to 150 virtual categories.

[bmxmale]

Hi, we have same problem. but I can't say it's slow query, this is killer query.

I checked that problem are with this line:

$data = call_user_func_array([$virtualRule, $callback], [$category]);

   # Smile\ElasticsuiteVirtualCategory\Helper\Rule
    public function loadUsingCache(CategoryInterface $category, $callback)
    {
        \Magento\Framework\Profiler::start('ES:Virtual Rule ' . $callback);
        $cacheKey = implode('|', [$callback, $category->getStoreId(), $category->getId()]);

        $data = $this->cache->load($cacheKey);

        // Due to the fact we serialize/unserialize completely pre-built queries as object.
        // We cannot use any implementation of SerializerInterface.
        if ($data !== false) {
            $data = unserialize($data);
        }

        if ($data === false) {
            $virtualRule = $category->getVirtualRule();
            $data        = call_user_func_array([$virtualRule, $callback], [$category]);
            $cacheData   = serialize($data);
            $this->cache->save($cacheData, $cacheKey, $category->getCacheTags());
        }
        \Magento\Framework\Profiler::stop('ES:Virtual Rule ' . $callback);

        return $data;
    }

^ array:42 [▼
  "store_id" => "3"
  "entity_id" => "328"
  "attribute_set_id" => "3"
  "parent_id" => "1"
  "created_at" => "2016-12-07 08:15:56"
  "updated_at" => "2021-06-24 13:33:16"
  "path" => "1/328"
  "position" => "3"
  "level" => "1"
  "children_count" => "487"
  "name" => "Shop Category"
  "meta_title" => null
  "display_mode" => "PRODUCTS"
  "custom_design" => null
  "page_layout" => null
  "url_key" => "shop-category"
  "description" => null
  "meta_keywords" => null
  "meta_description" => null
  "redirect_priority" => "0"
  "searchindex_weight" => null
  "custom_layout_update" => null
  "available_sort_by" => null
  "category_menu_description" => null
  "breadcrumbs_priority" => null
  "is_active" => "1"
  "include_in_menu" => "1"
  "exclude_from_crosslinking" => "0"
  "exclude_from_sitemap" => "0"
  "exclude_from_html_sitemap" => "0"
  "landing_page" => null
  "is_anchor" => "1"
  "custom_use_parent_settings" => "0"
  "custom_apply_to_products" => "0"
  "single_column_layout" => "0"
  "products_column_count" => null
  "products_pic_aspect" => null
  "category_mmenu_style" => null
  "custom_design_from" => null
  "custom_design_to" => null
  "filter_price_range" => null
  "virtual_rule" => Smile\ElasticsuiteVirtualCategory\Model\Rule {#3066 ▼
    -queryFactory: Smile\ElasticsuiteCore\Search\Request\Query\QueryFactory {#1862 ▶}
    -productConditionsFactory: Smile\ElasticsuiteCatalogRule\Model\Rule\Condition\ProductFactory {#2070 ▶}
    -categoryFactory: Magento\Catalog\Model\CategoryFactory {#995 ▶}
    -categoryCollectionFactory: Smile\ElasticsuiteVirtualCategory\Model\ResourceModel\VirtualCategory\CollectionFactory {#2069 ▶}
    -queryBuilder: Smile\ElasticsuiteCatalogRule\Model\Rule\Condition\Product\QueryBuilder {#2349 ▶}
    -storeManager: Magento\Store\Model\StoreManager\Interceptor {#298 ▶}
    #conditionsFactory: Smile\ElasticsuiteCatalogRule\Model\Rule\Condition\CombineFactory {#2073 ▶}
    #elementName: null
    -conditionDataFactory: Smile\ElasticsuiteCatalogRule\Model\Data\ConditionFactory {#2072 ▶}
    #_conditions: null
    #_actions: null
    #_form: null
    #_isDeleteable: true
    #_isReadonly: false
    #serializer: Magento\Framework\Serialize\Serializer\Json {#146}
    #_formFactory: Magento\Framework\Data\FormFactory {#2074 ▶}
    #_localeDate: Magento\Framework\Stdlib\DateTime\Timezone {#615 ▶}
    #extensionAttributesFactory: Magento\Framework\Api\ExtensionAttributesFactory {#375 ▶}
    #extensionAttributes: null
    #customAttributeFactory: Magento\Framework\Api\AttributeValueFactory {#319 ▶}
    #customAttributesCodes: null
    #customAttributesChanged: true
    #_eventPrefix: "core_abstract"
    #_eventObject: "object"
    #_idFieldName: "id"
    #_hasDataChanges: true
    #_origData: null
    #_isDeleted: false
    #_resource: null
    #_resourceCollection: null
    #_resourceName: null
    #_collectionName: null
    #_cacheTag: false
    #_dataSaveAllowed: true
    #_isObjectNew: null
    #_validatorBeforeSave: null
    #_eventManager: Magento\Framework\Event\Manager\Proxy {#288 ▶}
    #_cacheManager: Magento\Framework\App\Cache\Proxy {#378 ▶}
    #_registry: Magento\Framework\Registry {#168 ▶}
    #_logger: Magento\Framework\Logger\LoggerProxy {#17 ▶}
    #_appState: Magento\Framework\App\State\Interceptor {#33 ▶}
    #_actionValidator: Magento\Framework\Model\ActionValidator\RemoveAction\Proxy {#377 ▶}
    #storedData: []
    #_data: array:1 [▼
      "store_id" => 3
    ]
  }
]

Adding line return $ bucketConfig = []; from @harrigo comments problem gone. catalogsearch and big categories works fine (before it was not working )

[Nuranto]

Same issue here, on a big category, with a lot of virtual children, there is two huge queries that took 1sec each. (I can send query log if needed)

I guess the only solution would be to use a mixed system :

• when loading product collection of a virtual category : use virtual category filters
• Assign products to virtual categories via an indexer, and when loading product collection from a parent category : use that index instead of rules.

EDIT:
Another potential solution (I didn't lookup into the code, it may be already like this) :

If the root category of the virtual child category is the current category, or one of its child, there's actually no need to add the rules to the query.

For instance :

• Cat A : static category
  • Cat B : virtual category, with root category = Cat A
  • Cat C : static category
    • Cat D : virtual category, with root category = Cat C
  • Cat E : virtual category, with root category = Cat F
• Cat F : static category

If I understand correctly what's going on currently, is that retrieving products for Cat A generates a request like this :
(Products of Cat A) AND (Products query from cat B) AND (Products query from cat D) AND (Products query from cat E)
But it would be the same, and faster to "simply" do :
(Products of Cat A) AND (Products query from cat E)

And I guess 99% of real-world virtual categories are in the case of Cat B/D (using a parent category as category root)

[Nuranto]

I made a PR for this : #2926

I tested on a category, the loading time was 3.43sec before, 1.3sec after.

ES http calls from 1.3sec to 167ms. I guess the rest of the gain is on query building.

I checked product count before/after on 10 categories, and it's the same. However, it would probably require more tests to be sure there's no regression. @harrigo @frostblogNet @bmxmale @romainruaud

[Nuranto]

Probably solves #1798 too

[romainruaud]

Hi @Nuranto

Thank you for contributing this.

As you said, this will require a bit of testing before getting merged, because there can be several different uses cases. But it looks definitely promising.

Best regards

[Nuranto]

Hi @romainruaud

Thanks. Do you know why the automated tests aren't starting on this PR ? Do I have to do something ?

[adamzero1]

Hi did this get anywhere?

[tim-breitenstein-it]

We have the same issue with around 400 virtual categories.

How is the status?

Has anyone tested the patch and can confirm success?

[romainruaud]

This was partially integrated in a recent rework of assembling virtual categories queries.

Which version are you using @tim-breitenstein-it ?

Regards

[tim-breitenstein-it]

Which version are you using

v2.10.15

We are currently working on an Update for Magento v2.4.6 with the latest Smile\Elasticsuite v2.11.6.1, but this take at least a month or longer so we need to fix this quite soon on our productive environment.