Opencart extremely slow loading speed

Question

I'm running Opencart 1.5.2 on my server and after importing a large number of products I got a massive speed down. I tried installing a vq mod which had to speed up the site... it didn't.

Store

I know I have some elements on the site which are relatively big but before the import it was running fine.

Answer 1

The product count in the categories is a significant source of slow page loads in opencart. There are a few places this could be calculated and you will have to get rid of all of them to notice an improvement in page load time due to this factor.

If you edit the Catalog module you can disable the product count for the navigation menu (displayed in the left column by default) by setting Product Count: to Disabled.

The default theme also has a product count displayed in the main menu of the site. You can find this code in catalog/controller/common/header.php:

$product_total = $this->model_catalog_product->getTotalProducts($data);

$children_data[] = array(
  'name'  => $child['name'] . ' (' . $product_total . ')',
  'href'  => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id']) 
);

Remove or comment out any references to $product_total:

//$product_total = $this->model_catalog_product->getTotalProducts($data);

$children_data[] = array(
  'name'  => $child['name'],
  'href'  => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id']) 
);

That should take care of all the references in a default opencart install, but if you are using a custom theme or modules there could be more. More generally, you can search the entire catalog/ directory for references to model_catalog_product->getTotalProducts().

If you search for other references to getTotalProducts() Make sure you don't remove the references that use the product count for pagination, otherwise the pagination will not work properly. Here is an example from catalog/controller/product/search.php, a file that needs the product count to function properly.

$pagination->total = $product_total;

Removing these references has led to almost a 10x speedup in page load time on my development servers in an opencart install with ~2,000 products.

Answer 2

If you have seo urls enabled in your opencart, it can be a significant source of slowness.

I have oc v1.5.5.1 with about 3K categories and 40K products. I run it on shared hosting, and at first my loading times were about 40 seconds and higher. Then i got a little bit more into opencart, and realized that it is making a huge number of requests on oc_url_alias table in oc database. So my advice is:

1.Add index to query column in oc_url_alias table

With this my load times went down to about 7 seconds per page.

2.Add index to keyword column in oc_url_alias table

After both steps I got it down to about 1-3 seconds per page.

Now my oc is running in about 1 second per page, I achieved that by adding some more indexes in my mysql table, according to this guy's post http://bloke.org/php/opencart-is-slow-with-many-categories/, which are:

1. Index on parent_id column in category table
2. Index on category_id column in product_to_category table
3. Index on language_id column in category_description table
4. Index on store_id column in category_to_store table
5. Index on attribute_id AND language_id columns in product_attribute table
6. Index on manufacturer_id column in product table
7. Index on language_id column in product_description table
8. Index on store_id column in product_to_store table

Also, as aforementioned in other answers, I also removed product count in: catalog/controller/product/category.php and catalog/controller/module/categories.php. If still experiencing slowness you can completely disable categories module from sidebar.

At last, you can try using this extension: http://www.opencart.com/index.php?route=extension/extension/info&extension_id=10464, but it does not support seo urls as it is, so I had to tweak it a little (by decoding $_REQUEST['_route_'] parameter, which contains seo url request, in function run).

Answer 3

Got it sorted out. There seems to be a "new" approach in SQL queries discovered by OC team and it should be called "DDoS yourself 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 seconds to 20-25 seconds, hitting the SQL server hard:

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

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 - caching is not a solution. Server side caching is pretty enough. Anything beyond this means you have serious coding problems. So don't... I will repeat DON'T BUY caching modules. The are hiding the problems, not solving them. If a caching module can hide the problem on 40k products, it won't be able to do it in 140k products. You will need caching module for the caching 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 understand 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: Commenting 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 "caching 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.

Answer 4

In a store I work on which is Version 1.5.6.4_rc (i am pretty sure applies ot your version too) the issue was with the following:

foreach ($categories as $category) in catalog/controller/module/category.php around line 33

foreach ($categories as $category in catalog/controller/common/header.php around line 107

Because of that code we had over 900 db queries many from $this->url->link() along with various others.

We created a vqmod to address this issue by caching this category data so atleast it will only happen when the cache regenerates:

<modification>

    <id>Cache category data to speed up page load for store with many categories and sub categories.</id>
    <version>1.0.0</version>
    <vqmver>2.3.2</vqmver>
    <author>Weismann Web</author>

    <file name="catalog/controller/module/category.php">
        <operation>
            <search position="after"><![CDATA[
            foreach ($categories as $category) {
            ]]></search>
            <add><![CDATA[
            $category_data = $this->cache->get('vqmod_category_data_controller_module_category');

            if ($category_data) {
                $this->data['categories'] = $category_data;
                break;
            }
            ]]></add>
        </operation>
        <operation>
            <search position="before"><![CDATA[
            if (file_exists(DIR_TEMPLATE . $this->config->get('config_template') . '/template/module/category.tpl')) {
            ]]></search>
            <add><![CDATA[
            if (!$category_data) {
            $this->cache->set('vqmod_category_data_controller_module_category', $this->data['categories']);
            }
            ]]></add>
        </operation>
    </file>

 <file name="catalog/controller/common/header.php">
        <operation>
            <search position="after"><![CDATA[
            foreach ($categories as $category) {
            ]]></search>
            <add><![CDATA[
            $category_data = $this->cache->get('vqmod_category_data_controller_common_header');

            if ($category_data) {
                $this->data['categories'] = $category_data;
                break;
            }
            ]]></add>
        </operation>
        <operation>
            <search position="before"><![CDATA[
            $this->children = array(
            ]]></search>
            <add><![CDATA[
            if (!$category_data) {
            $this->cache->set('vqmod_category_data_controller_common_header', $this->data['categories']);
            }
            ]]></add>
        </operation>
    </file>


</modification>

Here is my post about it on the Opencart forums: Issue With Slow Opencart When Having Lots of Categories