Author Topic: Slow performance due to custom fields (1,600,000+ entries)  (Read 4691 times)

EvanGR

  • Jr. Member
  • **
  • Posts: 300
Slow performance due to custom fields (1,600,000+ entries)
« on: November 14, 2019, 12:37:13 pm »
Hello,

Let me start with the stats:

[VM 3.6.3, php 7.3.11, Dual Core VPS server 8GB RAM]
Number of products: ~ 60,000
Custom fields, per product: ~ 5 (of type 'string')
Related products, per product: ~ 20

The products have been batch imported via CSV Improved extension.

The virtuemart_product_customfields table already contains 1,600,000+ rows.
Most of the custom fields, stuff like "Car Manufacturer", "Car Model" appear multiple times, so this table contains a lot of repeating data.
(Can this be implemented in a more efficient way?)

We use and heavily rely on the breakdesigns Custom Filters extension (for searching based on the custom fields).

The performance of the website, is often (not always) very slow (measured up to 20 seconds for a page to finish loading).

The major performance bottleneck is the custom fields table. During testing, the speed of the website becomes very fast, once we empty the contents of this table.

We are looking for ways to drastically optimize performance, especially since we plan to import double the amount of products in the future.

Any thoughts on this appreciated.

Thanks

GJC Web Design

  • 3rd party VirtueMart Developer
  • Super Hero
  • *
  • Posts: 9396
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 3.6.8
Re: Slow performance due to custom fields (1,600,000+ entries)
« Reply #1 on: November 14, 2019, 12:52:19 pm »
I have found exactly the same problem but have no solution .. seems to be the nature of the beast

I assume u mean cat views ..  details were always acceptable even with CFs

Number of products per page has a huge influence .. almost 1 to 1 in our testing .. double the number of prods and it would be twice as slow

we have

32 GB RAM
8 cores
640 GB SSD

and cat pages are dog slow ... and due to custom fields and CF Filters -- switch them off / delete the CFs and lightening quick

What "solved" it for us is moving to a Litespeed server and using their caching .. the speed now is perfectly acceptable

https://www.escape-watersports.co.uk/clothing/shoes

It was complicated by doing stock and price updates every few minutes directly in the DB by scripts but LiteSpeed cache is the only one I found that allows you to programatically clear the individual cached product AND any related pages .. ( cat views etc)

As your doing carparts I did this site but no customfields .. it is all done with categories and speed isn't a problem

https://www.4wdpartshop.com.au/suzuki/suzuki-four-wheel-drives/grand-vitara/grand-vitara-jb416-m16a-2005-2008/brakes1827039135.html
GJC Web Design
VirtueMart and Joomla Developers - php developers http://www.gjcwebdesign.com
VM3 AusPost Shipping Plugin - e-go Shipping Plugin - VM3 Postcode Shipping Plugin - Radius Shipping Plugin - VM3 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
http://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

balai

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 1395
Re: Slow performance due to custom fields (1,600,000+ entries)
« Reply #2 on: November 14, 2019, 17:27:37 pm »
The problem with the native custom fields, is that the values in the table virtuemart_product_customfields are not indexed.
That means that for most queries (that include a WHERE customfield_value='something'), there is a row by row checkup, scanning the entire table.

Another issue is the size of the table. Unfortunately MySQL cannot scale horizontally (at least not in an easy and affordable way). Possibly it could be a good idea to use 3rd party custom field plugin (that use it's own tables) for your custom fields as well. Did not tested that but seem to have some basis.

Other solutions can include the use of a caching extension like JotCache or caching in the views or db level.

pinochico

  • 3rd party VirtueMart Developer
  • Jr. Member
  • *
  • Posts: 134
    • MiniJoomla
  • VirtueMart Version: 3
Re: Slow performance due to custom fields (1,600,000+ entries)
« Reply #3 on: December 15, 2019, 10:27:51 am »
I confirm.

You must change virtuemart customfield to customfields4all - this customfileds use own DB table and are indexing by customfields ID not VALUE.
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds from products, orders and database table
Virtuemart Email Manager - customs email templates

PRO

  • Global Moderator
  • Super Hero
  • *
  • Posts: 10399
  • VirtueMart Version: 3+
Re: Slow performance due to custom fields (1,600,000+ entries)
« Reply #4 on: December 16, 2019, 19:46:22 pm »
Also, i would think about using some standard fields as custom fields.

For example

Manufacturer is Make
Model Always is to same manufacturer.
So models can be manufacturer categories?

ALSO: Why dont you "prefilter" them?

As in, do not use product list to narrow them, until you have to?


The order you filter them can have an impact.



J3.9+ VM 3.4.2
Slowest Page Speed Score (88) (Category)
Fastest Page Speed Score (94-96) (productdetails)

diri

  • Jr. Member
  • **
  • Posts: 98
  • VirtueMart Version: most recent dev version (trunk)
Re: Slow performance due to custom fields (1,600,000+ entries)
« Reply #5 on: December 17, 2019, 09:25:36 am »
hm. How will you handle 50+ manufacturers with 200+ models per manufacturer with "only" about 50 attributes per model in a decent way?

Of course different manufacturers produce models in same product categories.

How should customer look at it at your site?

Views based on manufacturer only?

Take bicycles as an example:
Mountain bikes, eBikes, pedelecs, road bikes, trekking bikes, gravel bikes, city bikes, ... plus multiple sub categories. Take a stock of about 10.000 bikes as an example. Plus parts and accessories.

Manufacturer for itself is still limited to one category. So there seems to be no performant solution except hacking core.

You can have a look at a socalled standart for this branch: Bidex. Don't be astonished about number of product categories (AFAIR about 160), attributes and the like. And, this standard is not most current need of market (like almost any standart).

edit:
Be aware that most expensive bikes are currently available for around EUR 15.000 off the shelf while there are bikes for few hundred Euros in same product category.

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 4185
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: Slow performance due to custom fields (1,600,000+ entries)
« Reply #6 on: December 17, 2019, 15:42:00 pm »
Diri, you have 50 attributes per products ?!?
If you dont need the attributes in the category view, then you can disable custom fields in the list view and it should not be to slow.
Of course you should use customfields4all or my tag system in this case. Because the filter is get from an external table with indexes and not directly the values from virtuemart customfields that have no index(in all case text is slow for search and index need more memory for text fields)
Another advantage of my tag system and customfield for all is that you can rename it for all products in the plugin. In virtuemart you have to rename each text in the product.

diri

  • Jr. Member
  • **
  • Posts: 98
  • VirtueMart Version: most recent dev version (trunk)
Re: Slow performance due to custom fields (1,600,000+ entries)
« Reply #7 on: December 18, 2019, 09:02:14 am »
Diri, you have 50 attributes per products ?!?
Yep. Compare it with computers for gaming scene and computers for "normal" usage in IT.

The higher the price the more attributes become important. Bicycles are very special and often individual products - ie (upper) middle class and high end (let's say price starting above EUR 5.000) bikes are normally not bought "off the shelf". They are "feature complete" but, ...

Behaviour of customers is similar to car buyers: Why should I buy a normal Mercedes and not AMG with extras not being available for normal Mercedes?
Take VW as other example - lables are VW, Audi, Seat, Porsche, ..., - the attributes make the difference (and the price). The large SUV from VW has many parts from Porsche's SUV (or other way around?) but, there are real differences beside lable only.

Eg with "bio" bicycles there are about 40 important attributes to be able to distinguish and compare different models and lables based on technical facts.

When bikes have a motor real fun begins - legal obligations apply as well. Currently I count 54 important attributes at one manufacturer who offers 8 lables and parts and accessories. Mutliple manufacturers using one lable only offer products in every category as well and cover almost any price segment except very cheap low quality products (entry level to upper high end) and parts and accessories.
If you dont need the attributes in the category view, then you can disable custom fields in the list view and it should not be to slow.
In category view choosing manufacturer (if manufacturer modul is not VM standard) or sorting by price is sufficient. Details and comparison (similar products) are problematic areas.
Of course you should use customfields4all or my tag system in this case. Because the filter is get from an external table with indexes and not directly the values from virtuemart customfields that have no index(in all case text is slow for search and index need more memory for text fields)
Another advantage of my tag system and customfield for all is that you can rename it for all products in the plugin. In virtuemart you have to rename each text in the product.
I will have a closer look at it, maybe I change background tasks needing high performance to PHP as well than. Since many years I'm tired of PHP's incompatibilities from (sub) version to (sub) version. This is a plague since it's very beginning and has been reason for me to use Perl in background at least. High performance and ... it works. Spending time to PHP based frontend visualization to be compatible with recent versions is enough for me ATM.

Thank you for valuable advice ;-)

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 4185
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: Slow performance due to custom fields (1,600,000+ entries)
« Reply #8 on: December 18, 2019, 13:56:20 pm »
diri, using a solution based only on indexes is 10 to 100X more faster then native Vm fields, you dont have to use Perl, it's only a mysql database query optimisation problem.
If you have 100000 products with 50 different atributes and 50 manufacturers, it should render fast and not in 20 secondes.

diri

  • Jr. Member
  • **
  • Posts: 98
  • VirtueMart Version: most recent dev version (trunk)
Re: Slow performance due to custom fields (1,600,000+ entries)
« Reply #9 on: December 19, 2019, 09:03:00 am »
Even with MS Access based frontend and Acc backend somewhere on network it takes less than 1 second to get multiple different selections from about 500.000 active products in one dashboard on screen (criterias are numerical ones as well as text (!), something like product details and related products and related parts and related accessories with different dependancies (BOMs, comparisons and calculations, stock, ...)) on a trivial standard PC. Doing it wrong way this will take about 5 minutes at least (I got my lessons ... :( ).

Such things are nice exercises when normalizing data and testing structure of DB and SQL in use. In dependancy of front-/backend SQL can be optimized later on. But, when you are fast with standard SQL(92) on a slow combination like the one mentioned above it becomes really fast in better environment with platform dependant optimizations (DB2, mySQL, Oracle, Postgres, SQL Server, ...). When you have to change structure of DB to get performance something is wrong from very beginning ...

A really old rule comes to my mind: Don't develop a form as long as you don't have a proper DB.

Programming language being used is a matter of taste. Perl is my preferred language because of it's long time compatibility. Scripts written correct in 80s or 90s still work fine. It's worth to spend time to optimize those scripts for this - in 30+ years you get a fine library. Using PHP you have to watch compatibility at each upcoming version - it's not  really to my taste.

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 4185
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: Slow performance due to custom fields (1,600,000+ entries)
« Reply #10 on: December 19, 2019, 14:35:30 pm »
Diri, you're right, the custom fields table was not designed to do a search in values and for most case, it's not needed.
It's why i save the data in another table and customfields for all use a similar technique.

pinochico

  • 3rd party VirtueMart Developer
  • Jr. Member
  • *
  • Posts: 134
    • MiniJoomla
  • VirtueMart Version: 3
Re: Slow performance due to custom fields (1,600,000+ entries)
« Reply #11 on: December 19, 2019, 16:27:52 pm »
Patrick: You are 100% right :)
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds from products, orders and database table
Virtuemart Email Manager - customs email templates