Managing large number of customer groups and Products.

[apiuser]

Feature request from Taral-Patoliya, posted on GitHub Nov 03, 2016

I am working on a store where i need to import products on a daily basis and the number of products may go up to more then 1,90,000. The Store discount heavily depends on the customer groups which are around 8800 including the core ones in number.

The Products are imported seamlessly, but when trying to reindex the Catalog Product Price index, it inserts 130 million records in catalog_product_index_price_final_idx table which takes huge time to complete.

Also the customer groups does not have any alternative. because catalog price rules, and tier prices depend heavily on them.

130 million records are a very huge amount of data on a mysql installations.

Note We have 15000 products as of now, but it is expected to increase to 1,90,000. so the number of records in catalog_product_index_price_final_idx table may got up to 1.65 billion records.

Note: The infrastructure of the server is optimized for high performance, with SSD for storage and MySQL configured using percona db.

[apiuser]

Comment from rganin, posted on GitHub Nov 03, 2016

Thanks for reporting, the performance improvement work for this area is on it's way to implementation. Internal ticket: MAGETWO-54578.

[apiuser]

Comment from choukalos, posted on GitHub Nov 04, 2016

Hello @Taral-Patoliya

How many websites & store views do you have now and plan to expand to (that'll also have an impact on catalog index)? Do you expect the # of customer groups to grow over time as well? What would you expect them to grow to? What's the longest indexer run time you'd be okay with? Would you be okay having additional machines (aka workers) enabled to help run the indexer faster?

Would you be willing to do a Skype/video conference call to discuss? My work email is exposed in my profile - please email me if so with some dates/times and your timezone (I'm in US/CST).

Thanks! Chuck

[taralpatoliya]

We are expecting to increase the number of websites up to 5 and each has 2 store views for english and norwegian language, also we have now implemented an entirely new pricing mechanism using the m2's pricepool and also we have updated the code to use a customer attribute instead of the customer groups. so now instead of tier price and catalog cart rules (Which are higly inefficient to be implemented as it requires to reindex everytime we add a new rule) we are using a custom table which contains discounts. and it is working seamlessly.