Post by m3scott » Mon Jun 27, 2011 10:40 am

I have a site with about 17,000 products currently on oscommerce. I tested 1.4.9.5, which runs quite well, but would rather only have to migrate once and go straight to 1.5.0.5.

However, the load times with the same data and images are drastically different.

From http://www.selfseo.com...
live is osc, dev is 1.4.9.5 and dev2 is 1.5.0.5

Domain name Size Load Time Average Speed per KB
1 http://www.aspencreekoutdoors.com 44.51 KB 0.52 seconds 0.01 seconds
2 dev.aspencreekoutdoors.com 96.46 KB 1.66 seconds 0.02 seconds
3 dev2.aspencreekoutdoors.com 78.71 KB 20.45 seconds 0.26 seconds

so that won't work as it is with a +20 sec load time.

Can anyone verify this is a problem with 1.5.0?
I did see someone else had a similar issue here...http://forum.opencart.com/viewtopic.php ... 2&start=20

Newbie

Posts

Joined
Fri May 27, 2011 4:34 am

Post by Xsecrets » Mon Jun 27, 2011 11:09 am

Just for grins can you disable the category menu on the homepage and see if it speeds it up. I know that's not a solution, but it would confirm that the slowness is in the category module. Which is where I'm suspecting it is coming from.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by rph » Mon Jun 27, 2011 11:09 am

Yeah, this issue's been a tough nut to crack.

http://www.webpagetest.org/result/11062 ... 1/details/

There are two problems here: 1) file size, and 2) something going on in the back end.

File size is easy. Your problem files are:

Javascript
jquery-ui-1.8.9.custom.min.js - 202.6 KB (enabling gzip compression in Admin should help this)
Nivo Slider images
ammo3b-980x280.png - 1,074.3 KB
ammo2b-980x280.png - 1,074.3 KB
ammo1-980x280.png - 1,074.3 KB

Now the initial 11 second delay is harder to diagnose. Do you have any performance logging on your server which could help pinpoint the issue like mysql_slow_query?

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by rph » Mon Jun 27, 2011 11:14 am

Xsecrets wrote:Just for grins can you disable the category menu on the homepage and see if it speeds it up. I know that's not a solution, but it would confirm that the slowness is in the category module. Which is where I'm suspecting it is coming from.
It's worth checking if the category cache is being created and loaded properly. Creating the cache was a big source of slow downs in 1.4.x.

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by Xsecrets » Mon Jun 27, 2011 11:36 am

rph wrote:
Xsecrets wrote:Just for grins can you disable the category menu on the homepage and see if it speeds it up. I know that's not a solution, but it would confirm that the slowness is in the category module. Which is where I'm suspecting it is coming from.
It's worth checking if the category cache is being created and loaded properly. Creating the cache was a big source of slow downs in 1.4.x.
Well I think it's a bigger issue than that now, and mysql_slow_queries won't help you, because I don't think it's necessarily slow queries that are the problem just the volume of queries. Now that he has added the qty of products next to the categories he runs a getproducttotal call with every category which I think is actually the root of the problem. If disabling the categories makes the speed increase greatly then I can instruct him on how to comment out the get total line since he's not displaying it on his site anyways, and that should confirm if my suspicions are correct on what is causing the problem, though you also have a separate call to the database for every link to get the seo slug. I'm still trying to think of a way to improve that. There has to be a more efficient way to handle the seo.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by rph » Mon Jun 27, 2011 11:57 am

Ha. Daniel got completely rid of the category cache.

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by Xsecrets » Mon Jun 27, 2011 2:25 pm

yes, but I know from work on my layered navigation module that caching the lookups doesn't do much good if you still have to go back and do a product count call. I am actually rewriting all my db queries to remove that requirement right now in my conversion of the layered navigation to 1.5, so hopefully the speed will be acceptable on large stores.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by m3scott » Mon Jun 27, 2011 8:36 pm

Good call on the categories, Xsecrets.
Disabling categories on the homepage resulted in this....

Domain name Size Load Time Average Speed per KB
1 dev2.aspencreekoutdoors.com 10.9 KB 0.29 seconds 0.03 seconds

Similar results for product view...

Categories enabled
Domain name Size Load Time Average Speed per KB
1 dev2.aspencreekoutdoors.com/index.php?route=product/product&product_id=3795 83.18 KB 20.53 seconds 0.25 seconds

Categories disabled
Domain name Size Load Time Average Speed per KB
1 dev2.aspencreekoutdoors.com/index.php?route=product/product&product_id=3795 15.34 KB 1.85 seconds 0.12 seconds

Newbie

Posts

Joined
Fri May 27, 2011 4:34 am

Post by Xsecrets » Mon Jun 27, 2011 9:34 pm

m3scott wrote:Good call on the categories, Xsecrets.
Disabling categories on the homepage resulted in this....

Domain name Size Load Time Average Speed per KB
1 dev2.aspencreekoutdoors.com 10.9 KB 0.29 seconds 0.03 seconds

Similar results for product view...

Categories enabled
Domain name Size Load Time Average Speed per KB
1 dev2.aspencreekoutdoors.com/index.php?route=product/product&product_id=3795 83.18 KB 20.53 seconds 0.25 seconds

Categories disabled
Domain name Size Load Time Average Speed per KB
1 dev2.aspencreekoutdoors.com/index.php?route=product/product&product_id=3795 15.34 KB 1.85 seconds 0.12 seconds
ok well you or someone has obviously already modified your category module to remove the category counts.
edit catalog/controller/module/category.php and look for code something like this (you've probably already changed it a bit)

Code: Select all

        foreach ($categories as $category) {
            $children_data = array();
            
            $children = $this->model_catalog_category->getCategories($category['category_id']);
            
            foreach ($children as $child) {
                $data = array(
                    'filter_category_id'  => $child['category_id'],
                    'filter_sub_category' => true
                );        
                    
                $product_total = $this->model_catalog_product->getTotalProducts($data);
                            
                $children_data[] = array(
                    'category_id' => $child['category_id'],
                    'name'        => $child['name'] . ' (' . $product_total . ')',
                    'href'        => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id'])    
                );                    
            }
            
            $data = array(
                'filter_category_id'  => $category['category_id'],
                'filter_sub_category' => true    
            );        
                
            $product_total = $this->model_catalog_product->getTotalProducts($data);
                        
            $this->data['categories'][] = array(
                'category_id' => $category['category_id'],
                'name'        => $category['name'] . ' (' . $product_total . ')',
                'children'    => $children_data,
                'href'        => $this->url->link('product/category', 'path=' . $category['category_id'])
            );
        }

 
Change it to look like this

Code: Select all

        foreach ($categories as $category) {
            $children_data = array();
            
            $children = $this->model_catalog_category->getCategories($category['category_id']);
            
            foreach ($children as $child) {
                $data = array(
                    'filter_category_id'  => $child['category_id'],
                    'filter_sub_category' => true
                );        
                    
                //$product_total = $this->model_catalog_product->getTotalProducts($data);
                            
                $children_data[] = array(
                    'category_id' => $child['category_id'],
                    'name'        => $child['name'],
                    'href'        => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id'])    
                );                    
            }
            
            $data = array(
                'filter_category_id'  => $category['category_id'],
                'filter_sub_category' => true    
            );        
                
            //$product_total = $this->model_catalog_product->getTotalProducts($data);
                        
            $this->data['categories'][] = array(
                'category_id' => $category['category_id'],
                'name'        => $category['name'],
                'children'    => $children_data,
                'href'        => $this->url->link('product/category', 'path=' . $category['category_id'])
            );
        }

 
the total next to the name is probably already removed, but make sure that you comment out the product total lines and see if that makes a difference in speed with the category module turned on.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by m3scott » Tue Jun 28, 2011 12:31 am

That did it! Huge difference.

Domain name Size Load Time Average Speed per KB
1 dev2.aspencreekoutdoors.com/ 78.71 KB 0.59 seconds 0.01 seconds

So since we are not using the totalproducts info, we do not need to bother loading it into an array.
Makes sense, except for those who do want to use it. I'd call this a bug.

Thanks so much!!

Newbie

Posts

Joined
Fri May 27, 2011 4:34 am

Post by Xsecrets » Tue Jun 28, 2011 12:56 am

well it's not the fact that you load it into an array the problem is that you run through a loop of all the categories and you do a db query each time through the loop for the product count, so if you have say 1000 categories you are running 1000 queries against the database. Even if those queries take 0.05 sec then that's 5 seconds to do all the queries not to mention all the memory alloc file writes etc that happens when you run each query, so it adds up. I think I've solved this issue for my Layerd Navigation mod. Once I've completed the conversion and fully tested I may recommend a patch to the core to improve the situation.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by tuba » Tue Jun 28, 2011 6:45 am

Cant wait. I have 6000 categories and my site takes forever to load.. I wonder if anyone tested Opencart with as many categories...

Newbie

Posts

Joined
Sat Sep 04, 2010 7:11 am

Post by onlinephilately » Tue Jun 28, 2011 1:44 pm

Finally it looks like we are on to the problem.

I have also had the problem with slow loading of the site and managed to find that the problem is the loading of the categories by disable it.

Alos, just tested what Xsecret proposed above and the site is now running fast...

I would like to have the count functionallity though and am looking forwards to Xsecrets mod. When do you think we can expect it?

New member

Posts

Joined
Thu Jan 27, 2011 3:14 am

Post by dony_b » Tue Jun 28, 2011 11:37 pm

I feel like you have to give up some features in order to make it load faster and I think being on a decent server with at least 512MB of RAM helps a lot.

Its those damn DB queries and javascripts that will kill load time but at the same time there is no easy way around it.

After some tweaks found in the forums my site is loading average .05 at the moment

User avatar
Active Member

Posts

Joined
Wed Aug 18, 2010 9:56 pm
Location - Boston, MA

Post by Xsecrets » Thu Jun 30, 2011 4:48 am

ok I've made up a quick vqmod for people who have lots of products to test with. It does not change any .tpl files, so it should work with a custom template as all data is returned in identical arrays to the default.

It does have one difference some may say it's good some may say it's bad, but it does not return any categories that don't have any products, so you won't ever see Category1 (0).

It has not been thoroughly tested, so I wouldn't go throwing it on a live site just yet, but would love to hear some feedback from people who have dev environments with large number of categories. This could probably be improved a bit more with caching, but for now it's just straight queries.

Attachments

Hopefully faster category queries.


OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by onlinephilately » Thu Jun 30, 2011 4:07 pm

OK, I just uploaded the xml file into the vqmod/xml folder (it's the first time I use vQmod).

The site is returning a blank page...

New member

Posts

Joined
Thu Jan 27, 2011 3:14 am

Post by Xsecrets » Thu Jun 30, 2011 9:28 pm

onlinephilately wrote:OK, I just uploaded the xml file into the vqmod/xml folder (it's the first time I use vQmod).

The site is returning a blank page...
so you installed vqmod and tested and everything was working fine, but then when you put the vqmod file from this post in the vqmod/xml folder you got a blank page? Have you made any other modifications to your files? If you changed any of the stuff I'm searching on it could cause problems.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by onlinephilately » Thu Jun 30, 2011 9:34 pm

I made changes to certain files but not to catalog/controller/module/category.php

Is the xml-file changing other files too?

OK, I see you change in other files. I did upload them as originals and now it loads "super fast"...

But, the order of the categories is now a mess. It shows all categories (i.e. also all sub categories at once).

New member

Posts

Joined
Thu Jan 27, 2011 3:14 am

Post by Xsecrets » Fri Jul 01, 2011 12:19 am

yeah I don't think the idea is going to work. It's adapted from my layered navigation where that is not a problem. I think the only way to get the product counts and keep the structure is going to be to use the slow way built into the system. I don't think there is going to be much that can be done about it. I really think the whole idea of putting the product counts on there needs to simply be scrapped and they need to be removed, because it is simply not scaleable.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by onlinephilately » Fri Jul 01, 2011 12:25 am

It now works with the products count but the category structure is gone.

I thought that the product count was the slowing factor. So why isn't the category structure work now you think?

New member

Posts

Joined
Thu Jan 27, 2011 3:14 am
Who is online

Users browsing this forum: No registered users and 56 guests