Jump to content

 

VerDan

Member Since 13 Nov 2018
Offline Last Active May 15 2019 09:16 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.




#309573 Multi-Vendor - Common Products For Vendors Performance

Posted by VerDan on 30 November 2018 - 12:49 AM

We are using Version 4.9.1 of Multi-Vendor, and have 80 vendors, with 30,000 total products.

When we enable "Common Products for Vendors[Beta]" addon that comes with the platform we notice an immediate 5x to 10x performance degradation at the user interface as we do a variety of things like switching between categories or trying to open any vendor's store. If we disable the addon, the performance returns to normal.

 

Has anyone else noticed a performance hit with this?   Is there a fix or something we can do to address this?

 

We are working with a 12 Core, 32GB dedicated host.    Our concern is we are getting ready to add about 1M products and we are already having issues.

 

Thanks!