we use elastic search with shopware 6.4.6.1. Home page, listing page & product page are fast. The fuzzy / ajax live search is fast, too.
If you press ENTER or click to "show all search results", the page is very slow. You have to wait 30-40 seconds.
The shop has about 50.000 products with many properties. we debugged with tideways. Attached any informations.
Does anyone have an idea, how i can get the search page speed?
Screenshot: Tideways Screen
Controller: Shopware\Storefront\Controller\SearchController::search Stack:
#1 PDOStatement::execute
#2 Doctrine\DBAL\Driver\PDOStatement::execute
#3 Doctrine\DBAL\Connection::executeQuery
#4 Doctrine\DBAL\Query\QueryBuilder::execute
#5 Shopware\Core\Framework\DataAbstractionLayer\Dbal\EntityAggregator::fetchAggregation
#6 Shopware\Core\Framework\DataAbstractionLayer\Dbal\EntityAggregator::aggregate
#7 Shopware\Elasticsearch\Framework\DataAbstractionLayer\ElasticsearchEntityAggregator::aggregate
#8 Shopware\Core\System\SalesChannel\Entity\SalesChannelRepository::aggregate
#9 Shopware\Core\Content\Product\SalesChannel\Listing\ProductListingLoader::load
#10 Shopware\Core\Content\Product\SalesChannel\Search\ProductSearchRoute::load
#11 Shopware\Core\Content\Product\SalesChannel\Search\CachedProductSearchRoute::Shopware\Core\Content\Product\SalesChannel\Search\{closure}
#12 Shopware\Core\System\SystemConfig\SystemConfigService::trace
#13 Shopware\Core\Framework\Adapter\Cache\CacheTracer::Shopware\Core\Framework\Adapter\Cache\{closure}
#14 Shopware\Core\Framework\Adapter\Translation\Translator::trace
#15 Shopware\Core\Framework\Adapter\Cache\CacheTracer::Shopware\Core\Framework\Adapter\Cache\{closure}
#16 Shopware\Core\Framework\Adapter\Cache\CacheTagCollection::trace
#17 Shopware\Core\Framework\Adapter\Cache\CacheTracer::trace
#18 Shopware\Storefront\Framework\Cache\CacheTracer::Shopware\Storefront\Framework\Cache\{closure}
#19 Shopware\Storefront\Theme\ThemeConfigValueAccessor::trace
#20 Shopware\Storefront\Framework\Cache\CacheTracer::trace
Problem SQL:
# search*page::aggregation::price
SELECT
SUM(
IF(product.product_number = ?, ?, ?) + IF(product.product_number LIKE ?, ?, ?) + IF(
IFNULL(
product.manufacturer_number,
product.parent.manufacturer_number
) = ?,
?,
?
) + IF(
IFNULL(
product.manufacturer_number,
product.parent.manufacturer_number
) LIKE ?,
?,
?
) + IF(
IFNULL(product.ean, product.parent.ean) = ?,
?,
?
) + IF(
IFNULL(product.ean, product.parent.ean) LIKE ?,
?,
?
) + IF(
COALESCE(
product.translation.name,
product.parent.translation.name
) = ?,
?,
?
) + IF(
COALESCE(
product.translation.name,
product.parent.translation.name
) LIKE ?,
?,
?
) + IF(
COALESCE(product.categories.translation.name) = ?,
?,
?
) + IF(
COALESCE(product.categories.translation.name) LIKE ?,
?,
?
)
) as _score,
MIN(
IFNULL(
COALESCE(
(
ROUND(
(
ROUND(
CAST(
(
JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
) as DECIMAL(?, ?)
),
?
)
) * ?,
?
) / ?
),(
ROUND(
(
ROUND(
CAST(
(
JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
) as DECIMAL(?, ?)
),
?
)
) * ?,
?
) / ?
),(
ROUND(
(
ROUND(
CAST(
(
JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
) as DECIMAL(?, ?)
),
?
)
) * ?,
?
) / ?
),(
ROUND(
(
ROUND(
CAST(
(
JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
) as DECIMAL(?, ?)
),
?
)
) * ?,
?
) / ?
),(
ROUND(
(
ROUND(
CAST(
(
JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
) as DECIMAL(?, ?)
),
?
)
) * ?,
?
) / ?
),(
ROUND(
(
ROUND(
CAST(
(
JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
) as DECIMAL(?, ?)
),
?
)
) * ?,
?
) / ?
),(
ROUND(
(
ROUND(
CAST(
(
JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
) as DECIMAL(?, ?)
),
?
)
) * ?,
?
) / ?
),(
ROUND(
(
ROUND(
CAST(
(
JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
) as DECIMAL(?, ?)
),
?
)
) * ?,
?
) / ?
),(
ROUND(
(
ROUND(
CAST(
(
JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
) as DECIMAL(?, ?)
),
?
)
) * ?,
?
) / ?
),(
ROUND(
(
ROUND(
CAST(
(
JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
) as DECIMAL(?, ?)
),
?
)
) * ?,
?
) / ?
),(
ROUND(
(
ROUND(
CAST(
(
JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
) as DECIMAL(?, ?)
),
?
)
) * ?,
?
) / ?
),(
ROUND(
(
ROUND(
CAST(
(
JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
) as DECIMAL(?, ?)
),
?
)
) * ?,
?
) / ?
),(
ROUND(
(
ROUND(
CAST(
(
JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
) as DECIMAL(?, ?)
),
?
)
) * ?,
?
) / ?
),(
ROUND(
(
ROUND(
CAST(
(
JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
) as DECIMAL(?, ?)
),
?
)
) * ?,
?
) / ?
),(
ROUND(
(
ROUND(
CAST(
(
JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
) as DECIMAL(?, ?)
),
?
)
) * ?,
?
) / ?
),(
R
Edit: Error after dev mode activation.
request.CRITICAL: Uncaught PHP Exception
Elasticsearch\Common\Exceptions\BadRequest400Exception: "{"error":
{"root_cause":[{"type":"query_shard_exception","reason":"failed to
create query: [nested] failed to find nested object under path
[categories]","index_uuid":"w9uwGtZqTW-
Ri1BsVKJtnQ","index":"sw6_product_2fbb5fe2e29a4d70aa5854ce7ce3e20b_16
48715824"}],"type":"search_phase_execution_exception","reason":"all
shards failed","phase":"query","grouped":true,"failed_shards":
[{"shard":0,"index":"sw6_product_2fbb5fe2e29a4d70aa5854ce7ce3e20b_164
8715824","node":"K3_P7uQSRrOdbjjXdnhFTg","reason":
{"type":"query_shard_exception","reason":"failed to create query:
[nested] failed to find nested object under path
[categories]","index_uuid":"w9uwGtZqTW-
Ri1BsVKJtnQ","index":"sw6_product_2fbb5fe2e29a4d70aa5854ce7ce3e20b_16
48715824","caused_by":{"type":"illegal_state_exception","reason":"
[nested] failed to find nested object under path
[categories]"}}}]},"status":400}" at
/home/tonlqkde/www.tonerscout24.de/vendor/elasticsearch/elasticsearch
/src/Elasticsearch/Connections/Connection.php line 675 {"exception":"
[object]
(Elasticsearch\\Common\\Exceptions\\BadRequest400Exception(code:
400): {\"error\":{\"root_cause\":
[{\"type\":\"query_shard_exception\",\"reason\":\"failed to create
query: [nested] failed to find nested object under path
[categories]\",\"index_uuid\":\"w9uwGtZqTW-
Ri1BsVKJtnQ\",\"index\":\"sw6_product_2fbb5fe2e29a4d70aa5854ce7ce3e20
b_1648715824\"}],\"type\":\"search_phase_execution_exception\",\"reason\":\"all shards
failed\",\"phase\":\"query\",\"grouped\":true,\"failed_shards\":
[{\"shard\":0,\"index\":\"sw6_product_2fbb5fe2e29a4d70aa5854ce7ce3e20b_1648715824\",\"node\":\"K3_P7uQSRrOdbjjXdnhFTg\",\"reason\":
{\"type\":\"query_shard_exception\",\"reason\":\"failed to create
query: [nested] failed to find nested object under path
[categories]\",\"index_uuid\":\"w9uwGtZqTW-Ri1BsVKJtnQ\",\"index\":\"sw6_product_2fbb5fe2e29a4d70aa5854ce7ce3e20b_1648715824\",\"caused_by\":
{\"type\":\"illegal_state_exception\",\"reason\":\"[nested] failed to
find nested object under path [categories]\"}}}]},\"status\":400} at /home/tonlqkde/www.tonerscout24.de/vendor/elasticsearch/elasticsearch
/src/Elasticsearch/Connections/Connection.php:675)"} []
EDIT2: I have updated the system. 6.4.13. There is an old issus ticket: https://issues.shopware.com/issues/NEXT-10936 But the problem still occurs. Any idea's?
It looks like the SQL fallback is executed, due to ES query errors.
You can disable this behavior with SHOPWARE_ES_THROW_EXCEPTION=1 in your .env file to see the actual Elasticsearch Query error.