[1.5.5.1]Performance Issues after Upgrade

I was running 1.5.4.1 on my site with no performance issues at all. I have over 11,000 products on our store and everything is zippy and I would not say there was a performance issue. In testing for the upgrade, I am having a HUGE performance issue with 1.5.5.1.

I am going back to a plain install minus all additions to continue testing (setting up now). I've had no other issues with 1.5.5.1 except that a page takes between 10 and 20 seconds to load. I've uninstalled everything with no changes to the poor performance.

I am curious if others have seen a performance hit with stores that have 10,000+ products from the 1.5.5.1 install or if I should continue on my ways trying to figure out what mod is causing the issue.

Thanks in advance,
Bob

I have installed a fresh copy of 1.5.5.1, installed vqmod and csv product importer 3.2 only. Imported my 11,000+ products (no images).

With the sample data from the initial install, everything was zippy. But after my import, OC 1.5.5.1 is now not usable as it is too slow on page loads. So for now, I'm holding off on upgrading the 1.5.5.1 until the performance issues can be worked out.

If you'd like to see it in action, shop.603sports.com (don't order, this is a dev site). If anyone has any suggestions on what might be causing the issues please let me know.

Thanks,
Bob

If interested... Here is a link to the 1.5.4.1 install

www.603sports.com/store/

There is definitely something up with 1.5.5.1 and php/mysql performance. I do not have the technical ability to figure it out but I can tell you that on a clean install with 11,000+ products, your server will choke. I'm not sure what's going on but everytime a page is loaded I can see that a PHP process is being spawned that takes up lots of CPU cycles.

The Process does not go away and if another page is loaded now you have lots of CPU cycles x2 (or some factor). After about 3 page loads, my Bluehost server is basically non-responsive until I kill 1.5.5.1 PHP processes than it is fine again until more 1.5.5.1 pages load.

If anyone in the project is interested in seeing this I'm more than willing to give access to my backend. For now though. I need to stop the 1.5.5.1 server as it is killing our production site (on the same shared server) and I can't have that. Very easy to duplicate so If you are interested in debugging, please PM me.

I have the same problem with just under 10K products... Horrible performance. I downloaded the latest "version" from github 02/15/2013 and still same issues. I have a dedicated server with lots of RAM and access to 100% CPU and it still can't handle 1.5.5.1 but works just fine with 1.5.4.1... I can't believe this is considered a usable release by any but the smallest stores. Hopefully it will be addressed in the next "point" release.

I am having the same issue with my newly installed Opencart to replace our ageing oscommerce. The performance is very sluggish and takes several seconds to load, even with less than 300 products.

This is unusable on 1.5.5.1. I have category count disabled in settings but it is acting like it used to with it enabled..

That's because it still runs no matter what, it just doesn't display the results. Sorta useless as far as optimization goes.

It turned off in the old versions when turning it off. Right now my server completely locks down if I try to use new version with products in.

Demon5 wrote:It turned off in the old versions when turning it off.

Only in 1.5.3.x and only in the header menu. All the other versions the database queries are always run, the counts are just not displayed.

The reason it drags really bad now is because the database query was rewritten for filters and there's no longer database query caching.

We had the same thoughts in first place, but when we analysed the queries we found out that the bought theme was the problem. It has an 3rd leve menue vqmod, that has an error and counts the products on every category though it is disabled in the settings. You should check that.
We raised the query-cache too to 80MB - no it's really fast! I would like to say: Blazingly fast.
So it was not opencart. You can analyse that if you edit the db.php and log all queries.

Good luck
Karsten

It is OpenCart. You just worked around it.

Store with 11k products = 26 second load time. True disabling of getTotalProducts() = 6 second load time. Major improvement but still unacceptably slow.

well in my 1.5.3.1 it works nicely. using stock theme with modified css. anyone have a good fix for this? My product list is slightly larger....

Showing 1 to 20 of 113412 (5671 Pages)

Seems like I have the same problem here. Using aorund 60 categories(only 6 main cats) and aorund 18k products list. As the product is expected to grow to around 40k, i am not trying to fix it now, but it will be nice to have some information ahead.

Also using stock theme(nothing modded yet) on a VPS hosting(1GHz/1GB/40GBHDD/10Gbps). The performance is like a dead cat would perform - loading of the front page(or any page) takes 8-12s with disabled(from AP) categories count.

If you don`t have any solution can you please post the filenames of the files involved in loading and sorting categories? A wild guess is that sort order(ORDER BY {tableprefix}_category.sort_order) is one of the elements that is making everything slow down. The hell, I can live without sort order...

PP. I am helping a friend to get up an online store and I ma working on it in my free time, so this is why i ask you to point me the files responsible for this. Really don`t have much time to spedn on this problem, but i will be happy if i fix it fast. And also will post the fix here, so others could use it too

Got it sorted out. There seems to be a "new" approach in SQL queries discovered by OC team and it should be called "DDoS urself to death".

Product base has now grown to 37k products in 129 categories(from 18k over night, lol... shouldn`t have writed that automated import scripts and give it to a lamer...) and load times have grown from 6-12 secodns to 20-25 seconds, hitting the SQL server hard:

Вопросов начиная с запуска: 514,064,911(ques since startup)
ø в час: 1,301,788(ques avg for hour)
ø в минуту: 21,696(ques avg for minute)
ø в секунду: 362 (ques avg for second)

This is NOT normal, since there are 2 users on the system -the automated script(limited - 30 ques per second, then sleep(1)) and me(362-30 = 332 queries per second? by a human? WTF devs?). Based on this statistics OC in this way will need a serious server farm to serve 500+ users at the same time. Not gonna happen. Not in this life.

I do maintain various websites and have rewritten almost all of them. My most visited site(200k visits per day) is generating "only" 2.5Mil ques per day. And it is has heavy(content), believe me. If OC was loaded equally(200k views) this would mean there were going to be 100-120Mil ques per day.

ALSO the queries are not-so-wise, giving the server hard times with ORDER BY(as i suspected) and SELECT DISTINCT(pain!!!).

ALSO there are numerous options set to each query, no matter if they are set by the user or not(sort, order, etc). This makes ques like 4-5x longer than expected, even if the user don`t want any sorting order(ASC, DESC, etc.)

ALSO there are ques written in such a bad way, that amuses me. How could you pull total numbers for *anything* by using 5 phps and 3 queries, as long as you can do simple 1 line "SELECT COUNT(*) FROM ..."? OC team seems to not care about execution times and server loads.

I would like to apologize if somebody is offended by what I`ve written, but in my case I am right: the whole approach is wrong for achieving the goals(fast execution on 37k products/129 cats). OC could be good for someone with 2 categories and 50 products(lol?). Dunno. And i probably won`t find out.

As an INFO - chaching is not a solution. Server side cahching is pretty enough. Anything boyond this means you have serios coding problems. So don`t... I wil repeat DON`T BUY chaching modules. The are hiding the problems, not solving them. If a chaching module can hide the problem on 40k products, it won`t be able to do it in 140k products. You will need chaching module for the chaching module, lol.

Now, to the solution. Easy way. We will modify only the main problems. I will not explain the que modifications I made on my version, because they are in many files and are critical if you don`t uderstand what are you doing(you might loose OC options you would like to keep, while I don`t care about options as long as the site is loading for half a minute). So - minor modifications ONLY.

Wil say - explained for version 1.5.5.1 stock, stock theme. Means - no mods. After modification you will loose left side block with categories, but your site will load REALLY FAST(37k products/129 cats -> 0.137 seconds avg in sumulated 5 loads, server distance is ~200mi)

0) BACKUP your site. We will be modifying files. You could make a horrible mess. And cry afterwards.

1) Get /catalog/controller/product/category.php
Find line: 184
Must contain: $product_total = $this->model_catalog_product->getTotalProducts($data);
Replace with: //$product_total = $this->model_catalog_product->getTotalProducts($data);
Description: Comemnting out categories count as it takes pretty damd much to count products in 129 categories.(129 queries? WTF?)

2) Get /catalog/controller/product/category.php
Find line: 187
Must contain: 'name' => $result['name'] . ($this->config->get('config_product_count') ? ' (' . $product_total . ')' : ''),
Replace with: 'name' => $result['name'],
Description: Cleaning up - there is no count to be shown in categories, as we don`t count them anymore.

3) Get /catalog/controller/product/category.php
Find line: 388
Must contain: 'common/column_left',
Replace with: // 'common/column_left',
Description: Skippng generation of the left column in the category view.

4) Get /catalog/controller/product/product.php
Find line: 463
Must contain: 'common/column_left',
Replace with: // 'common/column_left',
Description: Skippng generation of the left column in product view.

5) Get /store/catalog/view/theme/default/template/product/product.tpl
Find line: 1
Must contain: <?php echo $header; ?><?php echo $column_left; ?><?php echo $column_right; ?>
Replace with: <?php echo $header; ?><?php echo $column_right; ?>
Description: Removing left column from theme - products view.

6) Get /store/catalog/view/theme/default/template/product/category.tpl
Find line: 1
Must contain: <?php echo $header; ?><?php echo $column_left; ?><?php echo $column_right; ?>
Replace with: <?php echo $header; ?><?php echo $column_right; ?>
Description: Removing left column from theme - catalog view.

DONE. Test your load speed. Should be pretty amazing, if your problem was like mine.

NOTE: Please note, that I am not familiar with nay version of OC and have never used it before. As we have solved part of the problem, it is not fully resolved. This is a temporary fix. Deleting the parts that cause slow load is a solution until you write them again, this time hopefully better. I am willing to rewrite it, if someone wants to outbid my boss. I can take vacation and work for you : ) My payment is currently 4700€ per week. Understanding and rewriting this left column in the right way shouldn`t take more than 1-2 working days.

PP. Will post this on several places, because I don`t think OC dev team will like what they`ve read, no matter I don`t mean to offend them - just to point the critical mistakes they`ve made(25.31 avg load time for each page in tests - no customer will wait more than 3-4 seconds before going away to another site! Dafuq?). And by not letting me post this info, people don`t know how to work their way out of the problem and go buy a "chaching module" that is actually crapping files around on the HDD like wild. Waste of money, waste of hard drive resources, waste of electricity... and all this - for creating an illusion everything runs fine, while it doesn`t.

I've decided to release the code for fully disabling product count. It's not remarkably different than what's been posted but it is an easy-to-use vqmod script. I found one further area of optimization and got the database queries time to go from 45 seconds to 2 seconds on a store with 11k products and 400 categories.

This isn't perfect as the product count query itself is still very slow but it is a valid workaround for people who don't mind disabling it for a major speed boost.

http://www.opencart.com/index.php?route ... n_id=10999

Well I decided to revisit OpenCart 1.5.5.1 to see if this issue had been fixed yet. Much to my amazement such a big issue has not been fixed (really). Thanks for the patch as it at least this makes the 1.5.5.1 OC use possible. (but still using 1.5.4 in production).

The crazy thing is that on 1.5.4.x my performance on a site with 300 cats and 26,000 products is so fast it's impressive. move the system to 1.5.5.1 with the vqmod to fix product counts it's faster than before original install but there it's now where near as responsive as the 1.5.4 version (and that's running plain jane 1.5.5.1 against a heavily mod'd and theme'd 1.5.4).

There has got to be something that the OC team did to the queries on a category page to kill the SQL performance. If I had the time I'd love to diff the 1.5.4 category display php files verses 1.5.5.1 and see what's going on with the SQL queries.

If the OC team is serious about growing the site list this issue has got to get corrected at the core of the software by the development team. While the product count was obviously a big part of the issue, there's more that needs to get done.

After looking in to this - 1.5.4.1 (running on my production server) utilises caching in getTotalProducts() . However, this was removed in 1.5.5.1 (running on my development server), when product filters were added - no explanation for why, though.

getTotalProducts() is required to be called at least once on the listing pages, since the total number is used for paginating the results. So, adding this caching back in, along with some modifications to when getTotalProducts() is called in the Core (1.5.5.1 is probably going to be my last 1.* upgrade - so what the hell), appears to have brought the speed back for me.

I have category counts disabled and I must note that i'm only currently running 1k products too, not 140k!