You are browsing upcoming documentation for version 5.1 of OroCommerce, OroCRM, and OroPlatform, scheduled for release in March 2023. Read version 5.0 (the latest LTS version) of the Oro documentation to get up-to-date information.
See our Release Process documentation for more information on the currently supported and upcoming releases.
Configure Price List Sharding
Data sharding allows to improve OroCommerce operation and accelerate database performance when handling big volumes of data.
In terms of price lists, data sharding is used to split one big table of price lists into smaller tables, i.e., one table per price list.
By default, price list sharding is disabled.
However, it is recommended to enable price list sharding if you are experiencing product performance degradation, or for high load deployments with a large number of price lists and products within them.
For PostgreSQL, make sure you have PostgreSQL uuid-ossp extension loaded.
Combined prices cannot be sharded at the moment.
Configure Price List Sharding
To enable price list sharding, set the following parameter in config/config.yml:
parameters: enable_price_sharding: true
To reorganize storage and shard the price table, run the following command:
oro:price-lists:pl-storage-reorganize prices --strategy=sharding
Disable Price Sharding
To restore the default price storage structure:
To disable price list sharding, change config/config.yml to set
To reorganize storage and join sharded price tables into one, run the following command:
oro:price-lists:pl-storage-reorganize prices --strategy=base
In Oro applications (e.g., in PriceManager), sharding is handled via ShardManager, since Doctrine cannot work with dynamic tables for one entity. All operations with the price data, such as persist and remove, should be done via PriceManager which uses ShardManager.
To make you query sharding-aware (and use a proper table), add the following hints:
- $query->setHint(‘priceList’, $priceList->getId()); This hint is necessary to use correct query cache. If this hint is absent, the query cache should be disabled.
- $query->setHint(PriceShardWalker::ORO_PRICING_SHARD_MANAGER, $shardManager); This hint is used by PriceShardWalker to define the current table.
- $query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, PriceShardWalker::class); This hint is used to update the final SQL.
- Tables are divided by Price Lists, so one table contains prices only from one Price List. When prices from a different Price List are needed, we should use JOIN for each Price List.
- HINT_CUSTOM_OUTPUT_WALKER does not apply to delete queries. Delete queries must be executed via SQL, not DQL. You should manually manually replace the table names before running this query.
Insert Into Select Query
To create a correct insert-into-select query, please use InsertFromSelectShardQueryExecutor that defines the table and executes insert into it.
PriceShardWalker analyzes a query and tries to detect a proper table to use based on the query parameters.
To apply PriceShardWalker to grids, use the HINT_PRICE_SHARD hint. Oro QueryHintResolver applies the required hints automatically.
source: hints: - HINT_PRICE_SHARD count_hints: - HINT_PRICE_SHARD