Author Topic: virtuemart_product_customfields 1.3 million rows (optimize?)  (Read 463 times)

EvanGR

  • Jr. Member
  • **
  • Posts: 298
virtuemart_product_customfields 1.3 million rows (optimize?)
« on: October 18, 2019, 10:42:09 am »
Our website (50k products) has over 1.3 million rows in the virtuemart_product_customfields table.

We are having slow performance(*) when browsing products in general, is this something that could be contributing to it?
(* front end requests, CSS/JS cache and minifications etc... are already optimized)

The table format is MyISAM, and collation is utf8_general_ci.
Perhaps convert to InnoDB?
Should we create any indexes?

How can we completely stop custom_fields processing to quickly test performance impact?

Any other thoughts?

Thank you

(Latest Joomla 3.9, VM 3.6.3 10177, php 7.2)

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 4181
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: virtuemart_product_customfields 1.3 million rows (optimize?)
« Reply #1 on: October 18, 2019, 11:03:59 am »
It depend how you use the customfields, you should inspect the queries.
Maybe an index problem.
Is your server a shared one or a poor dedicate server ?
How much user you have per day ?
Do you use some cache ?
If you use a filter based on the customfields this can really slowdown your website.
Have you some DDOS attacks ?
You can have many cause. Hard to know why, without inspecting everything.

EvanGR

  • Jr. Member
  • **
  • Posts: 298
Re: virtuemart_product_customfields 1.3 million rows (optimize?)
« Reply #2 on: October 18, 2019, 11:19:54 am »
Thanks for your reply.

1) How do I inspect the generated queries? (vmDebug doesn't seem to show them)

2) It's a shared server, but we are looking into moving to VPS.

3) Traffic is almost non existent, it's a new site.

4) We use the Joomla cache functions.

5) We use Custom Filters by breakdesigns, and rely on them heavily. Each product has 3-5 minimum custom fields (we use VM custom fields for now)... and there are 50k+ products...
This is definitely something that we are looking to optimize (performance-wise), but we are still trying to figure out where the main bottlenecks are.

Thanks

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 4181
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: virtuemart_product_customfields 1.3 million rows (optimize?)
« Reply #3 on: October 18, 2019, 13:29:13 pm »
You can see the queries on activate debug in Joomla config.

PRO

  • Global Moderator
  • Super Hero
  • *
  • Posts: 10398
  • VirtueMart Version: 3+
Re: virtuemart_product_customfields 1.3 million rows (optimize?)
« Reply #4 on: October 18, 2019, 22:11:42 pm »

How can we completely stop custom_fields processing to quickly test performance impact?


1)Backup the site.

2) BACKUP THE DATABASE,
3) export the product customfields table. Make sure your options are right "truncate table before insert etc. "

4)empty the product customfields table.

test.

Then you can import them back.

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