Slow performance due to custom fields (1,600,000+ entries)

[EvanGR]

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]

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

[balai]

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]

I confirm.

You must change virtuemart customfield to customfields4all - this customfileds use own DB table and are indexing by customfields ID not VALUE.

[PRO]

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.

[diri]

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]

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]

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]

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]

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]

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]

Patrick: You are 100% right

[dslove]

Hi all,

This is a very interesting subject, and currently a major pain for me.

We have prepared a new J! 3.10.11 / VM 4.0.6 bilingual website for a client migrating approximately 60.000 products and a total of ~80.000 custom field values.

We are using Custom Fields for All and the Custom Filters Pro extension by the same developer.

A very big issue is the back end product page (when editing a product via the admin panel), it seems to take forever to save any product changes when there's a custom field with 40.000 values (of which we try to select maybe 3-8 of them) added to the product, and quite often it doesn't work.

Should this really be an issue for our project?
Maybe it's related to the resources of the staging environment?

Judging from your examples, 40K values in one CFA field shouldn't be an insurmountable performance issue.

Thanks,
Alex

[jenkinhill]

I suspect you need to move to VM4.0.10   http://dev.virtuemart.net/projects/virtuemart/files

See https://virtuemart.net/news/509-virtuemart-4-0-8-release-news  - ignore the 4.0.8 in the title. It's really 4.0.10

[balai]

Part of the issue is that it loads all the custom field values of Custom Fields For All in the product page, to be able to search and select them in the product page.
We are planning a workaround on that issue, in one of the next releases.

Regarding the Custom Filters module, i suggest enabling "asynchronous loading", so that the page is loaded without being burdened by the filters.