Jump to content

 

VerDan

Member Since 13 Nov 2018
Offline Last Active Yesterday, 04:36 PM
-----

#310776 Multi-Vendor - Common Products For Vendors Performance

Posted by VerDan on 05 January 2019 - 01:42 PM

So things have actually changed.  We now have ~ 1.3M common products for roughly 80 vendors, plus additional vendors with their own private products.

 

This performance issue has been resolved ( an option to update "counters" was added to allow the counters to be updated at the end of the bulk import as opposed to after every insert.  This was a huge performance improvement in that it removed the full table scan after every insert. ); however, we do have some lag with a few of the queries that we see on the platform.   Once we get things stabilized from the import and identify some of the harder hitting queries based on our configuration, i will provide them.

 

Looking at your #1 above, we just went through the pain of doing this import.  It is not a fast process and we essentially had to break the import down into batches of 25K.   When going higher than that we had issues with PHP running out of memory (we had to kick the memory limit up to 3072M).   By keeping the batches at 25K, we were able to keep the memory consumption of the thread at just under ~ 1.2M  (all viewed via 'top', atop or other apps).     We were able to run 6 to 8 threads but the bottleneck was really at the DB level with the table locking.

 

There were several queries that really hammered and tended to have the biggest impact on the blocking.   

This first one was pretty bad for whatever reason.   One of the bad items (not shown below) is the categories.category_id IN clause -- we have 7800 categories, so every category was actually listed out in the IN clause as opposed to a correlated sub query.

  • SELECT products_categories.category_id as variant_id, products_categories.category_id as variant FROM cscart_products  as products 

The next also tended to have an impact and be pretty expensive

  • SELECT SQL_CALC_FOUND_ROWS products.product_id, descr1.product as product, companies.company as company_name

Lots more going on with essentially a simple multi-vendor implementation.   We have a lot of tuning to go.