J3.5.1 & Vm3.0.14 productdetails long load times....(caused by product table)

[Otto0815]

Hi,

i've just updated my livesite after my testsite hasn't had any Problems....
But now sometimes the load time for a single page is > 40 seconds....
I have absolutly no clue what could cause this....

Site: Kabello.de

Someone has a hint for me?
Greets

[Jörgen]

Me neither

http://forum.virtuemart.net/index.php?topic=79799.0

Jörgen @ Kreativ Fotografi

[Otto0815]

Me neither

http://forum.virtuemart.net/index.php?topic=79799.0

Jörgen @ Kreativ Fotografi

Thanks for the link....but the version is in the topic and the link to the site is in the thread?

It looks like that this error occurs if you click on "all productdetails" on the category browse page...
But even if i use the original productdetail template it changes nothing....
Firebug shows no errors

Update: My testpages now show the exact same error.....

[Jörgen]

Sorry missed that

I see what You mean
http://kabello.de/496/iphone-zubehoer/taschen.html

Alle Produktdetails...

Takes forever > 20 s

But pingdom.com sais 2.87 s ...

Jörgen @ Kreativ Fotografi

[Otto0815]

If you click for example: "Akkus->Audio-Akkus" on the left, then you get to the category browse page; till then everything is fine.
After that click on "alle Produktdetails" at any product and the site hangs.

On one of my testsite's i get the folloiwing in the debug modus:

Zeit: 38533.25 ms / 38572.01 ms Speicher: 7.472 MB / 12.43 MB Application: afterRenderComponent com_virtuemart

and this query takes 16 seconds:

Code Select Expand

SELECT p.`virtuemart_product_id`, `l`.`product_name`,`p`.product_sku
  FROM `k3rbu_virtuemart_products` as p
  INNER JOIN `k3rbu_virtuemart_products_de_de` as l using (`virtuemart_product_id`)
  LEFT JOIN `k3rbu_virtuemart_product_shoppergroups` as ps
  ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` 
  LEFT JOIN `k3rbu_virtuemart_product_categories` as pc
  ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` 
  LEFT JOIN `k3rbu_virtuemart_categories` as c
  ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` 
  LEFT
  OUTER JOIN `k3rbu_virtuemart_products` children
  ON p.`virtuemart_product_id` = children.`product_parent_id` 
  WHERE ( ( (p.`product_in_stock` - p.`product_ordered`) >"0" OR (children.`product_in_stock` - children.`product_ordered`) > "0") 
  AND  `pc`.`virtuemart_category_id` = 7
  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`!="5700" 
  AND `p`.product_sku <= "32003" 
  ORDER BY `p`.product_sku DESC, `virtuemart_product_id` DESC
  LIMIT 1

and this 21 seconds:

Code Select Expand

SELECT p.`virtuemart_product_id`, `l`.`product_name`,`p`.product_sku
  FROM `k3rbu_virtuemart_products` as p
  INNER JOIN `k3rbu_virtuemart_products_de_de` as l using (`virtuemart_product_id`)
  LEFT JOIN `k3rbu_virtuemart_product_shoppergroups` as ps
  ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` 
  LEFT JOIN `k3rbu_virtuemart_product_categories` as pc
  ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` 
  LEFT JOIN `k3rbu_virtuemart_categories` as c
  ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` 
  LEFT
  OUTER JOIN `k3rbu_virtuemart_products` children
  ON p.`virtuemart_product_id` = children.`product_parent_id` 
  WHERE ( ( (p.`product_in_stock` - p.`product_ordered`) >"0" OR (children.`product_in_stock` - children.`product_ordered`) > "0") 
  AND  `pc`.`virtuemart_category_id` = 7
  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`!="5700" 
  AND `p`.product_sku >= "32003" 
  ORDER BY `p`.product_sku ASC, `virtuemart_product_id` ASC
  LIMIT 1

[Jörgen]

Hello again

I edited the last reply, but You were Quick to reply.

It tales as You say long time to load.

I think it looks like some kind of SEO error
Have You tried with SEO off ?
Could this give a clue, pingdom gives this URL:
http://kabello.de/13033/7/akkus/audio-akkus/Audio-Akku%20für%20Sennheiser%20BA300%20%20%20%20%20%20%20%20%20%20140mAh%20,Li-Polymer-detail.html

There are a lot of blank spaces.

regards

Jörgen @ Kreativ Fotografi

[Otto0815]

Thx...

just tried with all soe off on my testsite and nothing changes....:-((

[Jörgen]

When You say testsite, Do You mean the live site? Do You have this issue locally too ?

regards

Jörgen @ Kreativ Fotografi

[Otto0815]

I have 2 testsites in 2 different subdomains...
One is an nearly clean install of j3.5.1 & vm3.0.14 and one is more or less a copy of my livesite

[Jörgen]

I would check memory for the live site if it clearly works locally. Maybe to little resources.

regards

Jörgen @ Kreativ Fotografi

[Otto0815]

Memory is set to 2048M (local value) and 512M (Master Value) tells me the joomla system information.
The 2048M is set by myself via htacces

[Otto0815]

It looks like this only happens if you call any of the productdetail pages....all other links are working as expected....
So i think it must have something to do with the code's i pasted above...

[Jörgen]

And on a local machine You don´t have this problem ?

regards

Jörgen @ Kreativ Fotografi

[Otto0815]

I have not tried to setup this on a local machine

This is the complete debug information:

Code Select Expand

Abfragezeit: 13727.45 ms Nach letzter Abfrage: 0.38 ms Abfragespeichernutzung: 0.026 MB Speichernutzung vor Abfrage: 9.546 MB Zeilen zurückgegeben: 1
SELECT p.`virtuemart_product_id`, `l`.`product_name`,`p`.product_sku
  FROM `k3rbu_virtuemart_products` as p
  INNER JOIN `k3rbu_virtuemart_products_de_de` as l using (`virtuemart_product_id`)
  LEFT JOIN `k3rbu_virtuemart_product_shoppergroups` as ps
  ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` 
  LEFT JOIN `k3rbu_virtuemart_product_categories` as pc
  ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` 
  LEFT JOIN `k3rbu_virtuemart_categories` as c
  ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` 
  LEFT
  OUTER JOIN `k3rbu_virtuemart_products` children
  ON p.`virtuemart_product_id` = children.`product_parent_id` 
  WHERE ( ( (p.`product_in_stock` - p.`product_ordered`) >"0" OR (children.`product_in_stock` - children.`product_ordered`) > "0") 
  AND  `pc`.`virtuemart_category_id` = 7
  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`!="13032" 
  AND `p`.product_sku <= "40654414" 
  ORDER BY `p`.product_sku DESC, `virtuemart_product_id` DESC
  LIMIT 1
Erklären
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE c const PRIMARY,published PRIMARY 4 const 1 Using temporary; filesort wurde genutzt
1 SIMPLE p range PRIMARY,published published 2 NULL 6153 Using where
1 SIMPLE ps ref virtuemart_product_id virtuemart_product_id 4 d021a0b7.p.virtuemart_product_id 1 Using where; Using index
1 SIMPLE children ALL product_parent_id ES KONNTE KEIN INDEX-SCHLÜSSEL GENUTZT WERDEN NULL NULL 6268 Using where
1 SIMPLE pc eq_ref virtuemart_product_id virtuemart_product_id 8 d021a0b7.p.virtuemart_product_id,const 1 Using where; Using index
1 SIMPLE l eq_ref PRIMARY PRIMARY 4 d021a0b7.pc.virtuemart_product_id 1 Using where
Profil (Profile)
Status Duration
starting 0.01 ms
Waiting for query cache lock 0.01 ms
checking query cache for query 0.11 ms
checking permissions 0.01 ms
checking permissions 0.00 ms
checking permissions 0.01 ms
checking permissions 0.01 ms
checking permissions 0.00 ms
checking permissions 0.01 ms
Opening tables 0.04 ms
System lock 0.01 ms
Waiting for query cache lock 0.05 ms
init 0.10 ms
optimizing 0.03 ms
statistics 0.15 ms
preparing 0.09 ms
Creating tmp table 0.03 ms
executing 0.01 ms
Copying to tmp table 13726.49 ms
Sorting result 0.02 ms
Sending data 0.01 ms
end 0.00 ms
removing tmp table 0.01 ms
end 0.01 ms
query end 0.00 ms
closing tables 0.01 ms
freeing items 0.01 ms
Waiting for query cache lock 0.00 ms
freeing items 0.11 ms
Waiting for query cache lock 0.02 ms
freeing items 0.00 ms
storing result in query cache 0.12 ms
logging slow query 0.00 ms
logging slow query 0.03 ms
cleaning up 0.02 ms
Aufrufstapel (Call Stack)
# Aufrufer Datei und Zeilennummer
12 JDatabaseDriverMysqli->execute() JROOT/libraries/joomla/database/driver.php:1497
11 JDatabaseDriver->loadAssocList() JROOT/administrator/components/com_virtuemart/models/product.php:1677
10 VirtueMartModelProduct->getNeighborProducts() JROOT/components/com_virtuemart/views/productdetails/view.html.php:161
9 VirtueMartViewProductdetails->display() JROOT/components/com_virtuemart/controllers/productdetails.php:55
8 VirtueMartControllerProductdetails->display() JROOT/libraries/legacy/controller/legacy.php:728
7 JControllerLegacy->execute() JROOT/components/com_virtuemart/virtuemart.php:125
6 require_once JROOT/components/com_virtuemart/virtuemart.php JROOT/libraries/cms/component/helper.php:405
5 JComponentHelper::executeComponent() JROOT/libraries/cms/component/helper.php:380
4 JComponentHelper::renderComponent() JROOT/libraries/cms/application/site.php:191
3 JApplicationSite->dispatch() JROOT/libraries/cms/application/site.php:230
2 JApplicationSite->doExecute() JROOT/libraries/cms/application/cms.php:257
1 JApplicationCms->execute() JROOT/index.php:49

And as i've just seen breaks php 7 all my scripts which where running as cronjob on my server.......i switch back with my livesite.

But would be nice if some has a solution for the described problem, i will try further on my testpage's

[Otto0815]

Updates:

No matter what php version i'm using (php 5.3 as modul, 5.6 as cgi, 7.04 as cgi) the productdetails page takes forever to load.
So i came to the conclusion that it MUST have something to do with VM 3.0.14 or J3.5.1.

With J3.4.8 & vm 3.0.12 everthing was fine.