query INSERT INTO “search_tmp…” never ends after mass attribute update

[rsulym]

Preconditions

Magento 2.2.1, php-fpm 7.1, MariaDB 10.3.5

Steps to reproduce

Perform mass attribute values update (~30000 items)

Expected result

1) Everything works fine

Actual result

1) re-index process finished ok, but: 2) A lot of queries INSERT INTO “search_tmp…” started, until server reaches pm.max_children parameter value and PHP-fpm die, and they constantly resurrecting even after mysql restart and process kill.

[tgrantmartin]

I too am having this issue. It appears to stem from large layered navigation generated querystrings which the system treats as a search operation and thus attempts to record the terms. This is hugely problematic when crawlers are walking down the layered navigation links. I've used my webserver access logs to add deny rules to my firewall for all but the essential bots from major search engines in a temporary effort to prevent max_connection overrun issues.

[wbyrnetx]

We just launched a 2.2.3 project and got slammed by those insert queries. Appears to be this same exact issue.

Haven't done more digging that that because the site seems to have stabilized.

[ghost]

@rsulym, thank you for your report. We've acknowledged the issue and added to our backlog.

[tgrantmartin]

I found that this was occurring as a result of an SEO crawler trying to process layered navigation links in rapid succession. Some of the combinations didn't seem to make sense either. At any rate, my solution was to set no-follows on all layered navigation links, and ultimately to remove layered nav from our all products page which listed several thousand skus.

[bilalusean]

any updates? it is a serious issue, please release a patch ASAP. We experienced this issue in Magento 2.2.2.

[Mathiu]

Same here and causes server overload up to few times a day. Would love to get a fix for this.

I can confirm what @tgrantmartin said about crawlers and layered navigation.

Magento version: 2.2.5.

Any progress on this?

[Flowlance]

Same problem. Hundreds of inserts to searchtmp*. Production server goes down multiple times a day. Any progress here?

[Mathiu]

Not giving any priority this feels bad, not sure why there no fix for this yet as its a breaking bug and been up since about half a year.

I have updated Magento to latest 2.2.6 version which didn't fix the issue and ended up disabling Magento_Search and Magento_CatalogSearch. After disabling them, our site is running super smooth but there is no search...

[quisse]

Anyone having this issue, maybe try creating a PR? I know it can be hard but it's fastest way to solve this issue...

[koseduhemak]

same problem here. Magento 2.1.9. Every 3rd/4th day my server is killed by an INSERT INTO search_tmp...-Query. Every website on that server stops responding. This a an absolute no go.

If I kill the search_tmp-Query server starts acting normal for few days till it happens again. The problem did not occur for 6 months and then suddenly started. Magento was not touched in that time period so nothing changed here. Of course there were some server-software / mysql / kernel updates etc. but that should not be the problem here.

I saw however, that there were some bots accessing my online shop frequently... Maybe it is like @tgrantmartin described above - the layered navigation.

Maybe it is related to dead locks? I found this via SHOW ENGINE INNODB STATUS;:

*** (1) TRANSACTION:
TRANSACTION 422112337225568, ACTIVE 0 sec starting index read
mysql tables in use 6, locked 6
LOCK WAIT 16 lock struct(s), heap size 1136, 46 row lock(s)
MySQL thread id 191529, OS thread handle 140632223483648, query id 21039562 localhost myMysqlUser Sending data
INSERT INTO `search_tmp_5bf5904d2b9f17_29881944` SELECT `main_select`.`entity_id`, MAX(score) AS `relevance` FROM (SELECT `search_index`.`entity_id`, (((0) + (0)) * 1) AS `score` FROM `catalogsearch_fulltext_scope1` AS `search_index`
 LEFT JOIN `catalog_eav_attribute` AS `cea` ON search_index.attribute_id = cea.attribute_id
 INNER JOIN `catalog_category_product_index` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id
 LEFT JOIN `catalog_product_index_eav` AS `farbe_filter` ON search_index.entity_id = farbe_filter.entity_id AND farbe_filter.attribute_id = 163 AND farbe_filter.store_id = 1
 LEFT JOIN `cataloginventory_stock_status` AS `farbe_filter_stock` ON farbe_filter_stock.product_id = farbe_filter.source_id
 LEFT JOIN `cataloginventory_stock_status` AS `stock_index` ON search_index.entity_id = stock_index.product_id AND stock_index.website_id = 0 WHERE (stock_index.stock_
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10961 page no 20 n bits 368 index PRIMARY of table `myDatabase`.`catalog_category_product_index` trx id 422112337225568 lock mode S waiting
Record lock, heap no 209 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 4; hex 00000005; asc     ;;
 1: len 4; hex 00000b7b; asc    {;;
 2: len 2; hex 0001; asc   ;;
 3: len 6; hex 0000086f6e36; asc    on6;;
 4: len 7; hex c5000003ce24d4; asc      $ ;;
 5: len 4; hex 80002711; asc   ' ;;
 6: len 2; hex 0000; asc   ;;
 7: len 2; hex 0004; asc   ;;

*** (2) TRANSACTION:
TRANSACTION 141520438, ACTIVE 0 sec inserting
mysql tables in use 12, locked 12
516 lock struct(s), heap size 90320, 54189 row lock(s), undo log entries 1349
MySQL thread id 191515, OS thread handle 140632223082240, query id 21039512 localhost myMysqlUser Sending data
INSERT INTO `catalog_category_product_index` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, ccp.position + 10000 AS `position`, 0 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
 INNER JOIN `temp_catalog_category_tree_index_bf36ab68` AS `cc2` ON cc2.parent_id = cc.entity_id AND cc.entity_id NOT IN (1)
 INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc2.child_id
 INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
 INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
 INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.entity_id = cpe.entity_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 87
 LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.entity_id = c
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10961 page no 20 n bits 336 index PRIMARY of table `myDatabase`.`catalog_category_product_index` trx id 141520438 lock_mode X locks rec but not gap
Record lock, heap no 81 PHYSICAL RECORD: n_fields 8; compact format; info bits 0

I also asked for help at magento.stackexchange: https://magento.stackexchange.com/questions/250395/sql-query-kills-entire-server

[vetrivel163]

@Mathiu How you disabled "Magento_Search and Magento_CatalogSearch"? It has dependency.

root@44fffe3e7f6e:/var/www/html# bin/magento mo:dis Magento_Search Magento_CatalogSearch
Unable to change status of modules because of the following constraints:
Cannot disable Magento_Search because modules depend on it:
Magento_CatalogImportExportStaging: Magento_CatalogImportExportStaging->Magento_CatalogStaging->Magento_Search
Magento_AdvancedSearch: Magento_AdvancedSearch->Magento_Search
Magento_CatalogStaging: Magento_CatalogStaging->Magento_Search
Magento_ConfigurableProductStaging: Magento_ConfigurableProductStaging->Magento_CatalogStaging->Magento_Search
Magento_Elasticsearch: Magento_Elasticsearch->Magento_Search
Magento_Solr: Magento_Solr->Magento_Search
Magento_BundleStaging: Magento_BundleStaging->Magento_CatalogStaging->Magento_Search
Magento_DownloadableStaging: Magento_DownloadableStaging->Magento_CatalogStaging->Magento_Search
Cannot disable Magento_CatalogSearch because modules depend on it:
Magento_CatalogImportExportStaging: Magento_CatalogImportExportStaging->Magento_CatalogStaging->Magento_CatalogSearch
Magento_AdvancedSearch: Magento_AdvancedSearch->Magento_CatalogSearch
Magento_CatalogStaging: Magento_CatalogStaging->Magento_CatalogSearch
Magento_ConfigurableProductStaging: Magento_ConfigurableProductStaging->Magento_CatalogStaging->Magento_CatalogSearch
Magento_Elasticsearch: Magento_Elasticsearch->Magento_CatalogSearch
Magento_Solr: Magento_Solr->Magento_CatalogSearch
Magento_BundleStaging: Magento_BundleStaging->Magento_CatalogStaging->Magento_CatalogSearch
Magento_DownloadableStaging: Magento_DownloadableStaging->Magento_CatalogStaging->Magento_CatalogSearch

[Mathiu]

@vetrivel163 I don't have any of these modules installed.

[trantuyen7372]

any updates?

[lefte]

FYI in case anyone else ends up at this search result with the same symptoms we had, while running Magento 2.2.1 we saw dozens of temp tables being created that ended up crashing our database in production, similar to the following: CREATE TEMPORARY TABLE `search_tmp_5c5c5102169513_69975582` ( `entity_id` int UNSIGNED NOT NULL COMMENT 'Entity ID' , `score` decimal(32,16) UNSIGNED NOT NULL COMMENT 'Score' , PRIMARY KEY (`entity_id`) ) COMMENT='search_tmp_5c5c5102169513_69975582' ENGINE=memory charset=utf8 COLLATE=utf8_general_ci

We ended up solving the symptoms ourselves by overriding the quick order search auto completion to remove the fulltext search and only match on direct SKU results, as every character being typed was generating a new full text search temp table to get and score the results. Because we are a B2B store, we only needed SKU matching, not general text results, so this ended up resolving the symptoms for us.

[tzyganu]

@engcom-backlog-pb Can you explain in 50 words or less what exactly is causing this problem? Maybe we can bypass it until the official fix is released.
This is causing real problems and some people cannot afford to wait for the fix. Thanks in advance

[asebille]

HI,

We faced the same issue with query like:

INSERT INTO search_tmp_5c94f755eee4a1_99238049
SELECT search_index.entity_id, (((0) + (0) + (0) + (0)) * 1) AS score
FROM catalogsearch_fulltext_scope2 AS search_index
LEFT JOIN catalog_eav_attribute AS cea ON search_index.attribute_id = cea.attribute_id
INNER JOIN catalog_category_product_index AS category_ids_index ON search_index.entity_id = category_ids_index.product_id
LEFT JOIN catalog_product_index_eav AS zzmarque_filter ON search_index.entity_id = zzmarque_filter.entity_id AND zzmarque_filter.attribute_id = 244 AND zzmarque_filter.store_id = 1
LEFT JOIN cataloginventory_stock_status AS zzmarque_filter_stock ON zzmarque_filter_stock.product_id = zzmarque_filter.source_id
LEFT JOIN catalog_product_index_eav AS systeme_exploitation_filter ON search_index.entity_id = systeme_exploitation_filter.entity_id AND systeme_exploitation_filter.attribute_id = 534 AND systeme_exploitation_filter.store_id = 1
LEFT JOIN cataloginventory_stock_status AS systeme_exploitation_filter_stock ON systeme_exploitation_filter_stock.product_id = systeme_exploitation_filter.source_id
LEFT JOIN cataloginventory_stock_status AS stock_index ON search_index.entity_id = stock_index.product_id AND stock_index.website_id = 0
WHERE (stock_index.stock_status = 1) AND (category_ids_index.category_id = 4283) AND (zzmarque_filter.value IN ('818','4429') AND zzmarque_filter_stock.stock_status = 1) AND (search_index.entity_id IN (
SELECT entity_id
FROM 
(
SELECT e.entity_id, IFNULL(current_store.value, main_table.value) AS taille_ecran_pouces
FROM catalog_product_entity AS e
INNER JOIN catalog_product_entity_varchar AS main_table ON main_table.entity_id = e.entity_id
LEFT JOIN catalog_product_entity_varchar AS current_store ON current_store.attribute_id = main_table.attribute_id AND current_store.store_id = 2
WHERE (main_table.attribute_id = '265') AND (main_table.store_id = 0)
HAVING (taille_ecran_pouces IN ('13230'))) AS filter
)) AND (systeme_exploitation_filter.value IN ('9908') AND systeme_exploitation_filter_stock.stock_status = 1)

The most process burning part of this query type was the subquery:

SELECT e.entity_id, IFNULL(current_store.value, main_table.value) AS taille_ecran_pouces
FROM catalog_product_entity AS e
INNER JOIN catalog_product_entity_varchar AS main_table ON main_table.entity_id = e.entity_id
LEFT JOIN catalog_product_entity_varchar AS current_store ON current_store.attribute_id = main_table.attribute_id AND current_store.store_id = 2
WHERE (main_table.attribute_id = '265') AND (main_table.store_id = 0)
HAVING (taille_ecran_pouces IN ('13230')) AS filter

This subquery is build in

/vendor/magento/module-catalog-search/Model/Adapter/Mysql/Filter/Preprocessor.php (line 162 to 192)

private function processQueryWithField(FilterInterface $filter, $isNegation, $query)
    {
        /** @var Attribute $attribute */
        $attribute = $this->config->getAttribute(Product::ENTITY, $filter->getField());
        $linkIdField = $this->getMetadataPool()->getMetadata(ProductInterface::class)->getLinkField();
        if ($filter->getField() === 'price') {
            $resultQuery = str_replace(
                $this->connection->quoteIdentifier('price'),
                $this->connection->quoteIdentifier('price_index.min_price'),
                $query
            );
        } elseif ($filter->getField() === 'category_ids') {
            return 'category_ids_index.category_id = ' . (int) $filter->getValue();
        } elseif ($attribute->isStatic()) {
            $alias = $this->aliasResolver->getAlias($filter);
            $resultQuery = str_replace(
                $this->connection->quoteIdentifier($attribute->getAttributeCode()),
                $this->connection->quoteIdentifier($alias . '.' . $attribute->getAttributeCode()),
                $query
            );
        } elseif (
            $filter->getType() === FilterInterface::TYPE_TERM &&
            in_array($attribute->getFrontendInput(), ['select', 'multiselect'], true)
        ) {
            $resultQuery = $this->processTermSelect($filter, $isNegation);
        } elseif (
            $filter->getType() === FilterInterface::TYPE_RANGE &&
            in_array($attribute->getBackendType(), ['decimal', 'int'], true)
        ) {
            $resultQuery = $this->processRangeNumeric($filter, $query, $attribute);
        } else {
#######################FROM HERE
            $table = $attribute->getBackendTable();
            $select = $this->connection->select();
            $ifNullCondition = $this->connection->getIfNullSql('current_store.value', 'main_table.value');

            $currentStoreId = $this->scopeResolver->getScope()->getId();

            $select->from(['e' => $this->resource->getTableName('catalog_product_entity')], ['entity_id'])
                ->join(
                    ['main_table' => $table],
                    "main_table.{$linkIdField} = e.{$linkIdField}",
                    []
                )
                ->joinLeft(
                    ['current_store' => $table],
                    'current_store.attribute_id = main_table.attribute_id AND current_store.store_id = '
                    . $currentStoreId,
                    null
                )
                ->columns([$filter->getField() => $ifNullCondition])
                ->where(
                    'main_table.attribute_id = ?',
                    $attribute->getAttributeId()
                )
                ->where('main_table.store_id = ?', Store::DEFAULT_STORE_ID)
                ->having($query);

            $resultQuery = 'search_index.entity_id IN (
                select entity_id from  ' . $this->conditionManager->wrapBrackets($select) . ' as filter
            )';
#######################TOHERE
        }

        return $resultQuery; 
}

In our case, we were importing all attributes and in a short timelpase some of them where in frontend_type=text (instead of frontend_type=multiselect). Thoses attributes states could be cached then the condition in_array($attribute->getFrontendInput(), ['select', 'multiselect'], true) is not true and we build the sub-query as long as the cache isn't cleared.

Attributes frontend_type are cached in mage---d2b_EAV_ENTITY_ATTRIBUTESCATALOG_PRODUCT cache file.

We should never execute this query in our case, so we will bypass this subquery building everytime.

I hope this will help some of you.

[acceleratemedia]

Also having this issue on 2.3.1 where the server is going down daily due to these queries.

[joshbourke]

Also having this issue on 2.3.1 where the server is going down daily...

[raidoa]

Having that issue as well. Unfortunately we haven't yet found a clean solution. Yet currently we just kill the queries automatically after 30 seconds to keep the site running - so far it has been OK and has been the only way to keep the site up. For that we use "pt-kill".

Here's how we kill those INSERT searchtmp* queries, maybe helps someone: pt-kill -h hostname -u username -p password --busy-time 30 --match-state 'Sending data' --kill-busy-commands 'Query' --wait-after-kill 3 --print --kill

What to keep in mind - it's just a patch for the wound, and it might kill some other important long running queries. Just run it initially without the --kill parameter to see what queries would get killed...monitor the behaviour for some time, tweak if required. And when you are sure you understand what's going to happen, add the --kill to actually start killing the queries. Hopefully fix will be rolled out soon.

[tzyganu]

Additional info.
The problem (as @asebille says) it's not the actual insert, but the select to get the data. The large nubmer of joins with catalog_product_index_eav table is a real performance killer. There is one join for every filter applied on the collection. A solution that seems to work (actually hide the problem) was to use an integration with elasticsearch. So basically the product ids that need to go into the search_tmp table now come from elasticsearch and not a db query.

[raidoa]

@tzyganu in our case if we run any of those queries without the INSERT (so just the SELECT), they return results around 0.0460 seconds. Thanks for the idea regarding Elasticsearch, we'll try that too.

[pmcmanaman]

If you are seeing this issue, one thing that may help is to run an OPTIMIZE on the fulltext table. There are some extra steps to optimize fulltext tables which are :

• innodb_optimize_fulltext_only needs to be enabled before https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_optimize_fulltext_only

• The number of rows optimized per run is limited by innodb_ft_num_word_optimize, so you may need to run multiple OPTIMIZE commands to cover all the rows in the full text tables.

https://dev.mysql.com/doc/refman/5.6/en/fulltext-fine-tuning.html#fulltext-optimize

[matutetandil]

I know is not a solution and is not considered to be so. But maybe could be a workaround. Since the main issue is regarding the MySQL connector for the catalog search, what we are doing is to use Elasticsearch. As I said, this is not a solution, but for someone could be a workaround.

[ghost]

We have a number of stores one of which is a fairly large store containing some fairly lage configurable products (thousands of child product combination) and a number of filters. We have been experiencing problems with the above query never ending and eating up server resources when users are applying multiple filters.

Doing some digging I recorded an SQL query evaluating a large number of records

# Time: 190508 10:36:13
# User@Host: ***[****] @ localhost []
# Thread_id: 65002  Schema: ******_co_uk_m2  QC_hit: No
# Query_time: 656.043989  Lock_time: 0.000086  Rows_sent: 0  Rows_examined: 1019562170
SET timestamp=1557308173;
INSERT INTO `search_tmp_5cd2a07da8e2b4_83367814` SELECT `main_select`.`entity_id`, SUM(score) AS `relevance` FROM (SELECT DISTINCT  `search_index`.`entity_id`, (((0) + (0) + (0)) * 1) AS `score` FROM `catalog_product_index_eav` AS `search_index`
 INNER JOIN `catalog_product_index_eav` AS `brands_filter` ON `search_index`.`entity_id` = `brands_filter`.`entity_id` AND `brands_filter`.`attribute_id` = 222 AND `brands_filter`.`store_id` = 1
 INNER JOIN `catalog_product_index_eav` AS `fuel_filter_filter` ON `search_index`.`entity_id` = `fuel_filter_filter`.`entity_id` AND `fuel_filter_filter`.`attribute_id` = 469 AND `fuel_filter_filter`.`store_id` = 1
 INNER JOIN `cataloginventory_stock_status` AS `stock_index` ON stock_index.product_id = search_index.entity_id AND `stock_index`.`website_id` = 0 AND `stock_index`.`stock_id` = 1
 INNER JOIN `cataloginventory_stock_status` AS `sub_products_stock_index` ON sub_products_stock_index.product_id = search_index.source_id AND `sub_products_stock_index`.`website_id` = 0 AND `sub_products_stock_index`.`stock_id` = 1
 INNER JOIN `catalog_category_product_index_store1` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1' WHERE (search_index.store_id = '1') AND (category_ids_index.category_id = 105) AND (brands_filter.value = '563') AND (fuel_filter_filter.value = '893')) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC, `entity_id` DESC
 LIMIT 10000;

Inspecting the catalog_product_index_eav table we have multiple rows which contain the required data (due to individual child products). While i'm not sure this is the best fix for all it was a simple fix us at this time, insert a subquery into the Inner Join statements for catalog_product_index_eav reduces the number of rows and execution time drastically.

Old SQL

INNER JOIN `catalog_product_index_eav` AS `brands_filter` ON `search_index`.`entity_id` = `brands_filter`.`entity_id` AND `brands_filter`.`attribute_id` = 222 AND `brands_filter`.`store_id` = 1

New SQL

INNER JOIN (SELECT `entity_id`, `store_id`, `attribute_id`, `value` FROM `catalog_product_index_eav` WHERE `attribute_id` = 222 AND `store_id` = 1 GROUP BY CONCAT(`entity_id`,"-",`attribute_id`,"-",`store_id`,"-",`value`)) AS `brands_filter` ON `search_index`.`entity_id` = `brands_filter`.`entity_id` AND `brands_filter`.`attribute_id` = 222 AND `brands_filter`.`store_id` = 1

On my store this has cut the query time from 10min seconds to 0.09 seconds

Code changes are as follows;

Magento\CatalogSearch\Model\Search\FilterMapper\CustomAttributeFilter::apply

           $store_id = (int) $this->storeManager->getStore()->getId();
           $removeExtraChildProducts = new \Zend_Db_Expr('(SELECT `entity_id`, `store_id`, `attribute_id`, `value` FROM ' . $this->resourceConnection->getTableName('catalog_product_index_eav') . sprintf(' WHERE `attribute_id`=%s AND `store_id`=%s GROUP BY CONCAT(`entity_id`," ",`attribute_id`," ",`store_id`," ",`value`) )', $attributeId, $store_id));

            $select->joinInner(
                //[$filterJoinAlias => $this->resourceConnection->getTableName('catalog_product_index_eav')],
                [$filterJoinAlias =>  $removeExtraChildProducts],
                $this->conditionManager->combineQueries(
                    $this->getJoinConditions($attributeId, $mainTableAlias, $filterJoinAlias),
                    Select::SQL_AND
                ),
                []
);

I added this as a quick core hack for now as I dont have the time to create an override (and some methods in the parent class are marked as private!)

[social-assassin]

I've been struggling with this issue for a few weeks as well. But, good news, I come bearing a much easier fix.

This is a related issue: https://github.com/magento/magento2/issues/22587

Apply the following patch to

/Model/Search/FilterMapper/CustomAttributeFilter.php https://bitbucket.org/muntzamersfoort/magento2-patches/raw/0ac032f30a6ab8b3f56b63d06db4c1649923f499/layered-navigation-performance.patch

[joshbourke]

Update - can confirm configuring ElasticSearch fixed the issue. It's a relatively painless process.

[SeeniBorn]

We have Magento EE 2.2.8 version with Elastic search 6.4 Recently we have upgraded from EE2.2.2 to 2.2.8 and Elastic search 2.x to 6.4 Suddenly we are facing issue with Elastic search and leads to site down issue. NR says the following code part,

Once we switch back search to Mysql then load becomes normal. What is cause of this kind of issue? Whether we reached any index limit in Elastic search or connection issue?

[elioermini]

very worrying database overload from 2.2.7 to 2.2.8.

[elioermini]

I've readded sprintf('%s.source_id=%s.source_id', $mainTable, $joinTable), after line 143 of \Magento\CatalogSearch\Model\Search\FilterMapper\CustomAttributeFilter so like it was on 2.2.7, monitoring now the status.

[SeeniBorn]

@elioermini Whether the issue getting resolve when you add that line?

[spleen1334]

That fix creates problems for configurable attribute filtering. Returns 0 products on some combinations.

[elioermini]

@spleen1334 I'll check the configurable issues however having a website that goes down is more critical, I usually set the attributes available for filtering as follows: Use in Layered Navigation "Filtrable with Result" and Used in Product Listing "Yes" and don't recall issues but I could be wrong. @SeeniBorn it's currently better. It's interesting to know if your indexer for Product EAV if is set update on Schedule or on save, could be affecting as well.

[SeeniBorn]

@elioermini My setting has update on Schedule for catalog search and indexer for Product EAV is Yes. No clue how the Elastic search overloaded and site get affected.

[GardeEuro]

Anyone has news about this issue? Upgrading magento from 2.2.3 to 2.2.8 my installation crashes when customers browse categories!

Debugging I found terrible INSERT query execution time on "search_tmp_XXXXX tables"!

[cabunar]

These commands fix the problem..

php bin/magento cache:clean php bin/magento indexer:reindex

You can add a crontab ... 0 0 * /usr/bin/php /var/www/myproject/bin/magento cache:clean && /usr/bin/php /var/www/myproject/bin/magento indexer:reindex

The bin/magento setup:cron:run does not work properly..

[magento-engcom-team]

Hi @rsulym.

Thank you for your report and collaboration!

The related internal Jira ticket MAGETWO-94602 was closed as non-reproducible in 2.2.x. It means that Magento team either unable to reproduce this issue using provided Steps to Reproduce from the Description section on clean or the issue has been already fixed in the scope of other tasks.

But if you still run into this problem please update or provide additional information/steps/preconditions in the Description section and reopen this issue.

[pmathbliss]

This is still a problem. If the first select is changed to a distinct sub query, everything is fixed. I am working on fixing the select in vendor/magento/module-catalog-search/Model/Adapter/Mysql/BaseSelectStrategy/BaseSelectFullTextSearchStrategy.php

Otherwise the sub query on this site gets 350k records and then mysql sorts it and returns the top 10000(which is actually just 35.)

`

SELECT main_select.entity_id, SUM(score) AS relevance FROM (SELECT DISTINCT search_index.entity_id, (((0) + (0) + (0)) * 1) AS score FROM (select distinct entity_id, store_id, source_id FROM catalog_product_index_eav) AS search_index INNER JOIN catalog_product_index_eav AS size_group_filter ON search_index.entity_id = size_group_filter.entity_id AND size_group_filter.attribute_id = 203 AND size_group_filter.store_id = 1

AND (size_group_filter.value = '1546')
INNER JOIN catalog_product_index_eav AS web_color_filter 
  ON search_index.entity_id = web_color_filter.entity_id 
  AND web_color_filter.attribute_id = 215 
  AND web_color_filter.store_id = 1 
AND (web_color_filter.value = '1931')

INNER JOIN cataloginventory_stock_status AS stock_index 
  ON stock_index.product_id = search_index.entity_id 
  AND stock_index.website_id = 0 
  AND stock_index.stock_id = 1 
INNER JOIN cataloginventory_stock_status AS sub_products_stock_index 
  ON sub_products_stock_index.product_id = search_index.source_id 
  AND sub_products_stock_index.website_id = 0 
  AND sub_products_stock_index.stock_id = 1 
INNER JOIN catalog_category_product_index_store1 AS category_ids_index 
  ON search_index.entity_id = category_ids_index.product_id 
  AND category_ids_index.store_id = '1'
  AND (
  category_ids_index.category_id = 16
)

WHERE (search_index.store_id = '1') ) AS main_select GROUP BY entity_id ORDER BY relevance DESC, entity_id DESC LIMIT 10000

`

[pmathbliss]

The fix worked perfectly. I had the wrong file. This does not appear to fix 2.2 and can break a search. After the fix, I no longer see queries waiting to insert into the temp. I think this has been a performance issue for a while.

module-catalog-search/Model/Adapter/Mysql/BaseSelectStrategy/BaseSelectAttributesSearchStrategy.php

Replace the following method:

public function createBaseSelect(SelectContainer $selectContainer)
    {
        $select = $this->resource->getConnection()->select();
        $mainTableAlias = $selectContainer->isFullTextSearchRequired() ? 'eav_index' : 'search_index';

        $tableName = $this->resource->getTableName('catalog_product_index_eav');
        if($mainTableAlias != "search_index")
        {
            $tableName = new \Zend_Db_Expr("(SELECT DISTINCT  entity_id, store_id, source_id  FROM `{$tableName}`)");
        }

        $select->distinct()
            ->from(
                [$mainTableAlias => $tableName],
                ['entity_id' => 'entity_id']
            )->where(
                $this->resource->getConnection()->quoteInto(
                    sprintf('%s.store_id = ?', $mainTableAlias),
                    $this->storeManager->getStore()->getId()
                )
            );

        if ($selectContainer->isFullTextSearchRequired()) {
            $tableName = $this->scopeResolver->resolve(
                $selectContainer->getUsedIndex(),
                $selectContainer->getDimensions()
            );

            $select->joinInner(
                ['search_index' => $tableName],
                'eav_index.entity_id = search_index.entity_id',
                []
            )->joinInner(
                ['cea' => $this->resource->getTableName('catalog_eav_attribute')],
                'search_index.attribute_id = cea.attribute_id',
                []
            );
        }

        $selectContainer = $selectContainer->updateSelect($select);
        return $selectContainer;
    }

[dverkade]

The fix worked perfectly. I had the wrong file. This does not appear to fix 2.2 and can break a search. After the fix, I no longer see queries waiting to insert into the temp. I think this has been a performance issue for a while.

module-catalog-search/Model/Adapter/Mysql/BaseSelectStrategy/BaseSelectAttributesSearchStrategy.php

Replace the following method:

public function createBaseSelect(SelectContainer $selectContainer)
    {
        $select = $this->resource->getConnection()->select();
        $mainTableAlias = $selectContainer->isFullTextSearchRequired() ? 'eav_index' : 'search_index';

      $tableName = $this->resource->getTableName('catalog_product_index_eav');
      if($mainTableAlias != "search_index")
      {
          $tableName = new \Zend_Db_Expr("(SELECT DISTINCT  entity_id, store_id, source_id  FROM `{$tableName}`)");
      }

        $select->distinct()
            ->from(
                [$mainTableAlias => $tableName],
                ['entity_id' => 'entity_id']
            )->where(
                $this->resource->getConnection()->quoteInto(
                    sprintf('%s.store_id = ?', $mainTableAlias),
                    $this->storeManager->getStore()->getId()
                )
            );

        if ($selectContainer->isFullTextSearchRequired()) {
            $tableName = $this->scopeResolver->resolve(
                $selectContainer->getUsedIndex(),
                $selectContainer->getDimensions()
            );

            $select->joinInner(
                ['search_index' => $tableName],
                'eav_index.entity_id = search_index.entity_id',
                []
            )->joinInner(
                ['cea' => $this->resource->getTableName('catalog_eav_attribute')],
                'search_index.attribute_id = cea.attribute_id',
                []
            );
        }

        $selectContainer = $selectContainer->updateSelect($select);
        return $selectContainer;
    }

We're running a customer on Magento 2.3.1 which seems to have the same issue. The change above doens't resolve the issue and actually brakes the search function with the following error:

Column not found: 1054 Unknown column 'eav_index.attribute_id' in 'where clause'

[pmathbliss]

This only works if search is not needed. It could be adjusted to make work but it the source of the problem.

On Thu, Jul 18, 2019, 6:01 AM Danny Verkade - Cream < notifications@github.com> wrote:

The fix worked perfectly. I had the wrong file. This does not appear to fix 2.2 and can break a search. After the fix, I no longer see queries waiting to insert into the temp. I think this has been a performance issue for a while.

module-catalog-search/Model/Adapter/Mysql/BaseSelectStrategy/BaseSelectAttributesSearchStrategy.php

Replace the following method:

public function createBaseSelect(SelectContainer $selectContainer) { $select = $this->resource->getConnection()->select(); $mainTableAlias = $selectContainer->isFullTextSearchRequired() ? 'eav_index' : 'search_index';

  $tableName = $this->resource->getTableName('catalog_product_index_eav');
  if($mainTableAlias != "search_index")
  {
      $tableName = new \Zend_Db_Expr("(SELECT DISTINCT  entity_id, store_id, source_id  FROM `{$tableName}`)");
  }

    $select->distinct()
        ->from(
            [$mainTableAlias => $tableName],
            ['entity_id' => 'entity_id']
        )->where(
            $this->resource->getConnection()->quoteInto(
                sprintf('%s.store_id = ?', $mainTableAlias),
                $this->storeManager->getStore()->getId()
            )
        );

    if ($selectContainer->isFullTextSearchRequired()) {
        $tableName = $this->scopeResolver->resolve(
            $selectContainer->getUsedIndex(),
            $selectContainer->getDimensions()
        );

        $select->joinInner(
            ['search_index' => $tableName],
            'eav_index.entity_id = search_index.entity_id',
            []
        )->joinInner(
            ['cea' => $this->resource->getTableName('catalog_eav_attribute')],
            'search_index.attribute_id = cea.attribute_id',
            []
        );
    }

    $selectContainer = $selectContainer->updateSelect($select);
    return $selectContainer;
}

We're running a customer on Magento 2.3.1 which seems to have the same issue. The change above doens't resolve the issue and actually brakes the search function with the following error:

Column not found: 1054 Unknown column 'eav_index.attribute_id' in 'where clause'

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/magento/magento2/issues/15545?email_source=notifications&email_token=AAHIDOGRDRC7SCKAFAH73ADQABSTNA5CNFSM4FB4D7Y2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD2IMOZA#issuecomment-512804708, or mute the thread https://github.com/notifications/unsubscribe-auth/AAHIDODHAHP6BJUVG2ZSNPDQABSTNANCNFSM4FB4D7YQ .

[Adel-Magebinary]

This issue is still there in 2.3.2

[mahesh-rajawat]

@magento-engcom-team , its not fixed yet?

[pmathbliss]

There needs to be a better way to tune these. There isnt going to be a one fits all solution.

On Thu, Aug 8, 2019, 6:09 AM Mahesh Singh notifications@github.com wrote:

@magento-engcom-team https://github.com/magento-engcom-team , its not fixed yet?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/magento/magento2/issues/15545?email_source=notifications&email_token=AAHIDOCOEM6P4EUWHCNZYXTQDQLHZA5CNFSM4FB4D7Y2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD33RSVY#issuecomment-519510359, or mute the thread https://github.com/notifications/unsubscribe-auth/AAHIDOF5AOINPQORR7YCQNDQDQLHZANCNFSM4FB4D7YQ .

[Adel-Magebinary]

BTW, switched to elastic search brings more problems after the full index.

1. Nav breaks
2. Filter anything by attribute goes to full shutdown.
3. Performance for index.

[pradeepnew]

Hi, I have checked for slow queries and below query is found on slow query log. Please suggest any solution for this. I am having magento version 2.3.2

INSERT INTO search_tmp_5d50e7d779c7f7_98698615 SELECT main_select.entity_id, SUM(score) AS relevance FROM (SELECT DISTINCT search_index.entity_id, (((0) + (0) + (0)) * 1) AS score FROM catalog_product_index_eav AS search_index INNER JOIN catalog_product_index_eav AS brand_filter ON search_index.source_id = brand_filter.source_id AND brand_filter.attribute_id = 194 AND brand_filter.store_id = 1 INNER JOIN catalog_product_index_eav AS gender_filter ON search_index.source_id = gender_filter.source_id AND gender_filter.attribute_id = 139 AND gender_filter.store_id = 1 INNER JOIN cataloginventory_stock_status AS stock_index ON stock_index.product_id = search_index.entity_id AND stock_index.website_id = 0 AND stock_index.stock_status = 1 AND stock_index.stock_id = 1 INNER JOIN cataloginventory_stock_status AS sub_products_stock_index ON sub_products_stock_index.product_id = search_index.source_id AND sub_products_stock_index.website_id = 0 AND sub_products_stock_index.stock_status = 1 AND sub_products_stock_index.stock_id = 1 INNER JOIN catalog_category_product_index_store1 AS category_ids_index ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1' WHERE (search_index.store_id = '1') AND (category_ids_index.category_id IN (47)) AND (brand_filter.value = '490') AND (gender_filter.value = '84')) AS main_select GROUP BY entity_id ORDER BY relevance DESC, entity_id DESC LIMIT 10000;

[jaromirmuller]

We have same issue on 2.3.2 with sample data. It appears when you have 8 and more filters enabled.

[mertgokceimam]

We've been hit with this issue also today. We will apply following patch https://github.com/magento/magento2/issues/22587#issuecomment-505895318 and install use elastic search to make sure we are not effected with this in future

Magento team pushing changes that effect performance on Enterprise solution is not acceptable

[anatolij-p]

Klevu search is one viable alternative.

When active and setup properly it completely bypasses the Magento database for product listing page & search results. All data is coming from API (initial load, filtering, etc...)

[mertgokceimam]

@anatolij-p Klevu doesn't help resolving this issue we already use Klevu as front end search.

I received a patch from Magento team for this issue , we are going to test it on our staging.

[anatolij-p]

Did you configure it properly? It must use their template, then all data is via api. Also can you share that patch from magento here?