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_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 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_2fbb5fe2e29a4d70aa5854ce7ce3e20b_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)"} []
CodePudding user response：
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.
CodePudding user response：
okay, I see the error message.
What can I do?
{"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_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}