Virtuemart mysql 0verload

[mathm00]

Joomla 3.9.6
VM 3.4.2
PHP 7.2

update: June 4th THis seems same problem as https://forum.virtuemart.net/index.php?topic=141470.0  Is there more recent information on this topic?
Post:
Since dec 2018 we incidental slow performance issues (webshop since 2006) . This is now getting worse. Sometimes saving changes in product information takes more then 20 seconds.
According to information of our provider this is caused by queries from Virtuemart.

The provider started server job recording every 5 minutes to make an inventory of the queries that are causing 100% cpu load.
Following query is executed variably from 0 up to 26 times per 5 minutes with the total of all products every single time.

SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id` \n\t\tFROM `kls_virtuemart_products` as p   \n LEFT JOIN `kls_virtuemart_product_shoppergroups` as ps ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id`  \n LEFT JOIN `kls_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id`  \n LEFT JOIN `kls_virtuemart_categories` as c ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` \n WHERE ( ((p.`product_parent_id` = "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` > "0")  AND  ((p.`product_parent_id` > "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` = "0")  AND  `c`.`published` = 1  AND  ( `ps`.`virtuemart_shoppergroup_id`= "1"  OR `ps`.`virtuemart_shoppergroup_id` IS NULL  )  AND  p.`published`="1"  AND  p.`virtuemart_product_id`!=2523 AND p.`virtuemart_product_id`!=2522 AND p.`virtuemart_product_id`!=2521 AND p.`virtuemart_product_id`!=2520 AND p.`virtuemart_product_id`!=2519 AND p.`virtuemart_product_id`!=2518 AND
Etc etc etc till the first (oldest) product in the database table is covered.
Then the last line of the query is:
\n group by p.`virtuemart_product_id` \n ORDER BY p.`created_on` DESC, p.`virtuemart_product_id` DESC LIMIT 0, 15[/size]

Needless to say, this is causing problems.
When searching the forum we came across a similar topic:  http://forum.virtuemart.net/index.php?topic=141515.0.
It said an upgrade to version 3.4.3. would be a solution and could be installed as a save option.
We thus downloaded and installed version 3.4.3 10031 on our test site. Unfortunately there we issues:
1. the product quantity update button (+ or -) does not function. You can only order 1 psc being the default. In the cart overview you can change quantity without a problem.
2. As test we placed several orders. One had an error: Customer and administrator both got an email with the order attachment as would be expected. But the order was not visible in the order section.
Order numbers are normally consecutive numbers. The former (not saved) order number was used (again) with a next test order. So apparently the order was not saved in the database. We don't know what caused this.

Resuming: we can't use this VM version yet . Also Virtuemart 3.4.5 10063 had the problem with this non responsive product quantity update button.
We urgently need to fix this query issue as the problem with the cpu load has escalated to unacceptable proportion.
Can someone help?

Marcel

[jenkinhill]

Just tried VM 3.6.0.10073  and  3.5.1.10059 (our latest test version) and both have no problems with quantity update button using default VM templates. So you either have a template problem (most likely) or a configuration problem there.

[mathm00]

yes maybe.  Only we do not have this issue with VM 3.4.2 and same template. Does that give another clue?

[jjk]

Do you have the 'Enable VirtueMart 404 error handling' activated in the Shop settings? If yes, uncheck it and check if your problem still exists.

[jenkinhill]

To test template issues select to use Protostar template - presumably you have a backup copy of the site to test with.

[Studio 42]

about the query, this is because the 'omit' option.
In the Virtuemart config > Template tab, set all Omit to "NO" and the query is then very lighter.
Of course, you can then have product displayed twice (in featured and latest for eg.), but your shop can be really faster.

[mathm00]

Thank for your reply's!
- The issue with the quantity update button seems indeed be caused by the template that apparently has a problem with newer VM versions.
- with respect to the query:  we set all the 'Omit' options to "No". This sadly has no effect on the load this query causes on the server of our provider. They just checked.
- the VirtueMart 404 error handling point also makes no difference.

So what to do? Any other ideas? It is not 100% clear for us if it certain that the 3.6.0.10073 VM version indeed stops this query?

[Studio 42]

This part of the query should be removed on using omit "no"

Code Select Expand

AND  p.`virtuemart_product_id`!=2523 AND p.`virtuemart_product_id`!=2522 AND p.`virtuemart_product_id`!=2521 AND p.`virtuemart_product_id`!=2520 AND p.`virtuemart_product_id`!=2519 AND p.`virtuemart_product_id`!=2518 AND ....

LEFT JOIN use all primary or index so it should not be slow.


A possible bug or mistake  ?
(p.`product_parent_id` = "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` > "0")
AND  ((p.`product_parent_id` > "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` = "0")  AND  `c`.`published` = 1  AND  ( `ps`.`virtuemart_shoppergroup_id`= "1"  OR `ps`.`virtuemart_shoppergroup_id` IS NULL  ) ...

I dont know why this is needed (p.`product_parent_id` = "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` > "0")
because whe have after
(p.`product_parent_id` > "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` = "0")
Perhaps a fix for some case ?

[derek webster]

Also,  I the menu item that defines the 1st instance of VM there are options on tabs - you could choose to NOT show all products within the selected category.

Not sure if that helps

[mathm00]

Hi Studio 42, setting Omit options to no, indeed helped. Our provider just now  gave notice that cpu load has dropped. We are very relieved. Very much obliged for help! Thanks all. Speed of our site is super again.
Annette

[trip]

Hi, @mathm00
could you explain what you did step by step to solve this?
And if you adjusted the sql query
Matthew

[trip]

Hi All
does anyone know how to kill the query, as we cannot seem to stop this. We have root access but whatever we try this query starts again by itself and brings the server to its knees.

Matthew

[Studio 42]

Have you try about the "omit" setting in the Vm config ?
This solved the slowdown in many cases.
Note that you have same setting in edit a virtuemart category. So set it to "global settings" here too.

[mathm00]

Hi Trip. We solved it like Studio 42 says. Go to the configuration of VM to the section 'template' and set omit options to 'no'. Annette