Performance Issue 1.5.1.1 : Clean Install + DB Populated Opencart

Please LogIn to Reply!

Post By: guest | 01-18-2013 07:57 Reply

Question

Hi,

My installation of 1.5.1.1 was working fine on my new development environment until I restructured my categories to add more tiers. Now I have massive performance degradation.

Summary Info:-

- New, clean install
- Shoppica Theme applied (http://www.shoppica.net)

All was working fine until I recently made some seemingly trivial changes.
Here is the deal:-

I have successfully imported a list of approximately 1500 products.
For testing purposes, I originally had all products in a simple 1 tier category layer.

The products are related to English Premiership Football teams so I will use actual examples.

Example of original TEST Category tree
> Browse by Team (top) > Manchester United > LIST OF PRODUCTS

Apart from too many products being in a single category, this worked absolutely fine and page load times were as expected.

Example of New (more organised) category tree
> Browse by Team (top) > English Premiership > Manchester Untied > > Executive Gifts > Ties > LIST PRODUCTS

I have only changed the category structure and yet page load times have massively degraded.

Total number of products 1426
Total number of categories 726 (most products are now located 4 tiers down)

I don't consider this to be a massive load, nor abnormal configuration.

I can only assume that this issue relates to MySQL queries / MySQL indexing surrounding categories and how they are handled from parent to child but maybe only when 3,4 or more layers exist.

I intend to add some debugging to disclose page load times and any problem MySQL queries but this may take some time. I will update thread once implemented if I find anything of interest.

Although I cannot be absolutely sure I believe the fault lies with Open Cart rather than the theme so any insight would be very helpful. Has anyone else noticed performance degradation on 1.5.1.1 when adding this many categories which are 3 or more tiers deep? I will also be posting this issue to support at themeburn.com in cast it is a theme problem.

Thanks in advance!

Gary

Answers

I just tested using the default theme rather than Shoppica and whilst there was a notable improvement, it was still a bit slow. I have also posted this observation to theme support website.

Based on results, it might be the case that degradation due to complex category structure is apportioned something like so:-

70% slow down from shoppica theme
30% slow down from open cart

I will continue to test.

Regards

Try
viewtopic.php?f=20&t=34851#p168642
and look out for Xsecrets' mod for category optimisation. There are other threads: a search should help.

Also try Q's 'Log & analyze DB queries' mod if you can't pinpoint the problem:
viewtopic.php?f=121&t=22048

Hi Moggin,

Thanks for the reply.

I've reduced the number of category tiers by removing the top tier which means that most products are now in a tier above.

Whilst this has improved things a little, it's still not fast enough and of concern.

I've installed vQmod and downloaded and installed 'Log & analyze DB queries' xml file but no logfile with queries is being created. Root dir is chmod 755. Unable to set as 777 so have modified the XML to output query_times.txt to a different location (within a folder of root) which is chmod 777 but still not working.

I also installed Xsecrets vQmod for category optimisation but did not notice any performance improvement.

Maybe I have a problem with vQmod? Any ideas? Is there any quick way of testing if vQmod is working?

Any ideas?

I got it :-)

Had vQmod 1.2.3 installed but xml files uploaded were all referencing a different version.

Also got query_time.txt outputting to root.

First inspection of query_time.txt for my home page confirms my fears...
Aggregate: 5.0192 secs

My website has 426 categories.

The output of query_time.txt is 844kb and >14,000 lines long!

There seems to be way, way too many queries going on in this output.

I'm no MySQL expert but I employ a few.
At first glance, there has to be a much more efficient way of compacting these queries and retrieving category / SEO info? Too many loops by the looks of it.

:-(

Also just noticed bugs in query_time.txt

Query Time is negative for this query below which is providing inaccurate aggregate.

Query Time: -0.9979 :: SELECT * FROM oc_category c LEFT JOIN oc_category_description cd ON (c.category_id = cd.category_id) LEFT JOIN oc_category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.parent_id = '113' AND cd.language_id = '1' AND c2s.store_id = '0' AND c.status = '1' ORDER BY c.sort_order, LCASE(cd.name)
Aggregate: -0.3228

Moggin wrote:Try
viewtopic.php?f=20&t=34851#p168642
and look out for Xsecrets' mod for category optimisation. There are other threads: a search should help.

Try the other version he did with caching: viewtopic.php?f=20&t=34851&start=20#p171445

rph wrote:
Moggin wrote:Try
viewtopic.php?f=20&t=34851#p168642
and look out for Xsecrets' mod for category optimisation. There are other threads: a search should help.

Try the other version he did with caching: viewtopic.php?f=20&t=34851&start=20#p171445

well be weary I have't even looked at that code since 1.5 .1 was released, and I think Daniel changed some things in that part of the code. If I ever get a spare second I might look back into that.

I also agree that there has to be a more efficient way to handle the SEO, but I'm just not sure what it is yet. Though generally those queries are very fast, but I suppose if you have that many categories the aggregate could add up.

Let me guess you are running some sort of store that has to do with automotive? I've really got to get the year make model mod done so people stop trying to run stores with ridiculous amounts of categories cause that's just really not the right way to handle it.

Xsecrets wrote:Let me guess you are running some sort of store that has to do with automotive? I've really got to get the year make model mod done so people stop trying to run stores with ridiculous amounts of categories cause that's just really not the right way to handle it.

Someone already created a mod for that though I don't know how good it is.

As suggested here, try to execute the following queries against your database and see if this changes something:

Code: Select all
ALTER TABLE `product` ADD INDEX ( `manufacturer_id` );
ALTER TABLE `product_to_category` ADD INDEX ( `product_id` );
ALTER TABLE `category` ADD INDEX ( `parent_id` );

Hi,

Thanks for the suggestions so far guys.

I've already added some more indexes to certain tables which I thought might help.
I am not using the manufacturer field at all.

I'm convinced that the issue relates to the volume of queries being generated by Open Cart when it is configured to have a large number of categories. Specifically, if these categories are 3 tiers deep, the number of queries can be enormous. I still don't believe my category configuration is an abnormal one though.

It really needs further inspection.
I'm guessing some optimization of queries is still required.

Xsecrets, this issue appears to be of interest to you so I hope you don't mind the fact that I just PM'd you with some information relating to my configuration to see if it highlights anything which might be of interest.

If anyone else closely involved with Open Cart would like a closer look at my configuration and how the number of queries seems to grow significantly under certain conditions, please feel free to ask for more information. My site is not yet public so I'm reluctant to post full details in the forum at this stage.

I currently have total query times of 5 seconds for a category in a 2nd tier and 7 seconds for a category in a 3rd tier.
This is obviously not good enough to go live with and I need a solution in the coming weeks.

I know I'm not set up on bullet proof hosting and an alternative would be to get a better host but this would defeat the object of making Open Cart more efficient for those on a budget. I would rather dedicate some of my time to helping improve the cart for others.

I recently learned a valuable lesson by moving a large website to a less expensive host to trim some costs. We moved from a dual processor dedicated server to a linode.com VPS. This immediately caused performance problems with certain pages and queries which were not an issue with extra CPU and RAM on the dedicated server but were only highlighted with less processing power. However, all of the issues were fixed within 48 hours with better programming and querying. Despite the slightly frustrating performance degradation over 48 hours it was still a very useful exercise to undertake. We were forced to optimize our website and improve the queries and programming. If anything, it now runs quicker on the new host due to the improvements.

I am not really a PHP / MySQL programmer. I understand the basics but the standard of my code would be too poor to put in to the public domain! However, I am happy to help with further testing of this issue if anyone would like to help in getting to the bottom of it.

Regards

More debugging info...

Default Template
2nd tier category: Aggregate queries = 0.8178 secs (query output, text file = 1,481 lines)
3rd tier category: Aggregate queries = 1.0678 secs (query output, text file = 1,469 lines)

Shoppica Template
2nd tier category: Aggregate queries = 5.6422 secs (query output, text file = 14,167 lines)
3rd tier category: Aggregate queries = 7.7303 secs (query output, text file = 28,221 lines)

It very much looks like Shoppica Template is causing a lot of problems.

The default could still be improved. Which queries are you timing?

We're taking the issues you describe into account and will update you with the result of the investigation. Thanks for the info.

rph wrote:The default could still be improved. Which queries are you timing?

he was timing all of them. He's just grabbing the aggregate time from the query log mod. And he has lots of products and categories.

I know.

Hi,

I really need some help with this guys please!

I think the problem lies with open cart itself but I cannot be sure.

Check out these query times from the default theme and the shoppica theme.

http://www.footystuff.co.uk/query_times/2011-09-27/
u: demo
p: demo123

Development site
http://www.footystuff.co.uk

BTW. I have vQmod v.2.0.1 installed but only 'query times' mod installed.

Regards

Well you're post above pretty much proved the problem was the shoppica theme.

Hi,

Clearly the Shoppica theme is aadding more queries and adding to the problem but aggregate query times of >5secs and >6secs on the default theme for 2nd and 3rd tier categories don't look too clever either though.

The big question is...
Is open cart simply not able to handle large category structures over 2-3 tiers in it's current form?
The volume of queries in my outputs certainly suggests so.

Regards

gcheers wrote:Hi,

Clearly the Shoppica theme is aadding more queries and adding to the problem but aggregate query times of >5secs and >6secs on the default theme for 2nd and 3rd tier categories don't look too clever either though.

The big question is...
Is open cart simply not able to handle large category structures over 2-3 tiers in it's current form?
The volume of queries in my outputs certainly suggests so.

Regards


I'm referring to this post

gcheers wrote:More debugging info...

Default Template
2nd tier category: Aggregate queries = 0.8178 secs (query output, text file = 1,481 lines)
3rd tier category: Aggregate queries = 1.0678 secs (query output, text file = 1,469 lines)

Shoppica Template
2nd tier category: Aggregate queries = 5.6422 secs (query output, text file = 14,167 lines)
3rd tier category: Aggregate queries = 7.7303 secs (query output, text file = 28,221 lines)

It very much looks like Shoppica Template is causing a lot of problems.


To me it looks like the longest time on the default template is just over 1 sec so where are you now comming up with over 5 seconds?

Tags:

opencart performance

Reply Or Comment!
Please LogIn to Reply!