Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Admin : categories.php slow loading


raiwa

Recommended Posts

Hello,

I have a store installation (CE FROZEN, PHP 7.2) with very slow loading admin/categories.php, maybe 20-30 sec.

Characteristics for products/chategories:

  • products aprox. 1800
  • products_to categories aprox. 20000 (products are linked to several categories)
  • categories aprox. 6000

The big number of categories and product links may explain the slow loading.

Database tables have been analysed and optimized, they are all using MyISAM and the index settings in the tables are all correct (core).
But I observe the following difference between server and local develop installation under XAMPP:

Server:

  • always the same loading time

XAMPP:

  • slow loading on first time page load
  • rel. fast loading on second and following page loads (2-3 sec.)

So my conclusion is that it should be related to a database cache setting.
I checked the sqli settings in php.ini of the XAMPP installation and tried to lower mysqli.cache_size=2000 to a very low setting (10) in order to simulate the server problem but no difference.

 

Questions:

  • With the amount of products/categories/links does it sound reasonable this slow loading?
  • Which setting on the server/database can produce this different behaviour

 

Thank you in advance
Rainer

Link to comment
Share on other sites

MySQL also has cache settings in mysql.conf or whatever. 

Have you tried turning on query logging and testing the queries in phpMyAdmin?  The logging tells you what queries the page is generating.  Then run the queries in phpMyAdmin to see which are slow. 

With a large products_to_categories table, you might add a UNIQUE index on categories_id, products_id (in that order).  Because the categories page queries from a category usually.  So the primary key is backwards for its needs.  That usually won't matter, but it might in your circumstance. 

Always back up before making changes.

Link to comment
Share on other sites

2 hours ago, raiwa said:

I have a store installation (CE FROZEN, PHP 7.2) with very slow loading admin/categories.php, maybe 20-30 sec.

I've seen similar in many shops over the years and there have been many posts in these forums about it. I haven't seen one that slow but you have more categories than in the sites I've seen. The first thing I always try is to add this command to the top of the admins application_top file

  ob_start("ob_gzhandler");

The above should help a little but probably not enough to make it fast enough to live with. You can try the MySQL cache but I don't think that is a good solution. First, if this is a shared server, the host probably won't enable cache option. If it does get enabled, it is only useful if the data is not changed much and that is probably not the situation for products.

I tried a local cache years ago and it did help but it had the same problem. Each time something was changed the cache had to be rewritten and that slowed things down again. But on hindsight, I think this might work if the layout of the page was changed. So just the main categories could be cached at first. Then as the sub-categories are loaded, they could be cached and so on. That would allow individual caches to be re-built and not be too bad.

Last year I started a project to use ajax to load that section. I didn't get very far since it is a huge job and I didn't have the time to get back to it. But the tests I made with what I did showed a tremendous decrease In load time. 

I'm sure you have probably thought of these options already but maybe they will give you some ideas.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

For my shop it's around 5-6 seconds. It got worse after I had moved a number of categories to reduce the number of main-categories.

Maybe this helps - I would like to see it a little faster as well, but I can still live with it. if 20-30 seconds it needs attention indeed.

edit: my shop has some 1000 categories.

Link to comment
Share on other sites

Thanks all of you for the help.

6 hours ago, ecartz said:

MySQL also has cache settings in mysql.conf or whatever.

Found it in mysql/bin/my.ini: query_cache_size = 32M on my local XAMPP, no cache at all on the server

I could then simulate the problem lowering the XAMPP cache.

6 hours ago, ecartz said:

Have you tried turning on query logging and testing the queries in phpMyAdmin?  The logging tells you what queries the page is generating.  Then run the queries in phpMyAdmin to see which are slow.

It's the categories queries mainly and the products count /products query.
 

6 hours ago, ecartz said:

With a large products_to_categories table, you might add a UNIQUE index on categories_id, products_id (in that order).  Because the categories page queries from a category usually.  So the primary key is backwards for its needs.  That usually won't matter, but it might in your circumstance. 

0.2 sec for first time load (maybe 27.7sec =>27.5sec). 0.1 sec difference for the load with cache(2.3sec => 2.2sec). Not sure if this is real or not.

4 hours ago, Jack_mcs said:

 


  ob_start("ob_gzhandler");

 

0.2 sec on first time page load (maybe 27.5sec =>27.3sec). 0.1 sec difference for the load with cache (2.2 sec => 2.1 sec together with the above). Not sure if this is real or not.

 

It seems the only possibility is to go for a fast dedicated server with sql cache enabled.

Link to comment
Share on other sites

Just found that it's the subcategory and products count which produces the slowness.

Commenting out the info, speeds up the first time load to 3-3.5 sec. and 0.7 sec for the load with cache.

@ecartz, Matt, @burt Gary, wouldn't this be a reason to make the subcategories and products count an optional setting in core? Or just remove it?

Link to comment
Share on other sites

admin/categories.php around line 1018-1019 (FROZEN):

      if ((!isset($_GET['cID']) && !isset($_GET['pID']) || (isset($_GET['cID']) && ($_GET['cID'] == $categories['categories_id']))) && !isset($cInfo) && (substr($action, 0, 3) != 'new')) {
//        $category_childs = array('childs_count' => tep_childs_in_category_count($categories['categories_id']));
//        $category_products = array('products_count' => tep_products_in_category_count($categories['categories_id']));

        $cInfo_array = array_merge($categories); // $category_childs, $category_products removed
        $cInfo = new objectInfo($cInfo_array);
      }

and line 1239:

//            $contents[] = array('text' => '<br />' . TEXT_SUBCATEGORIES . ' ' . $cInfo->childs_count . '<br />' . TEXT_PRODUCTS . ' ' . $cInfo->products_count);

 

Link to comment
Share on other sites

7 minutes ago, raiwa said:

admin/categories.php around line 1018-1019 (FROZEN):

Confirmed!

 

7 minutes ago, raiwa said:

and line 1239:

Could not find this code.....so didn't change anything here.

What exactly is the impact of commenting out the 1018/1019?

 

Link to comment
Share on other sites

From aprox 27 sec to 3.5 sec on first time load (without sql cache) and from aprox 2.5 sec to 0.7 sec on following loads with sql cache (if used).

All depends on the amount of categories/subcategories/products.

Just checked in Phoenix 1.0.5.5 the output for default view line 1068:

            $contents[] = ['text' => '<br>' . TEXT_SUBCATEGORIES . ' ' . $cInfo->childs_count . '<br>' . TEXT_PRODUCTS . ' ' . $cInfo->products_count];

They are also outputted here in category delete alert in lines 999-1000:

        if ($cInfo->childs_count > 0) $contents[] = ['text' => '<br>' . sprintf(TEXT_DELETE_WARNING_CHILDS, $cInfo->childs_count)];
        if ($cInfo->products_count > 0) $contents[] = ['text' => '<br>' . sprintf(TEXT_DELETE_WARNING_PRODUCTS, $cInfo->products_count)];

 

Link to comment
Share on other sites

Just to retrieve the number of subcategories and products for to display them in the info panel for the selected category. Not really important information I think.

Although more important for the alert if a category will be deleted. But this could be revamped to execute the queries only for that action and only for the selected category.

Link to comment
Share on other sites

21 hours ago, raiwa said:

a reason to make the subcategories and products count an optional setting in core? Or just remove it?

It was removed shop side.  It can be removed admin side, there is no reason why not - other than the message reminding shopowner when deleting, but that can be done in another way as you state.
Push a PR to the dev branch?

Link to comment
Share on other sites

9 minutes ago, burt said:

It was removed shop side.  It can be removed admin side, there is no reason why not - other than the message reminding shopowner when deleting, but that can be done in another way as you state.
Push a PR to the dev branch?

O.K.

Link to comment
Share on other sites

  • 2 weeks later...
On 3/21/2020 at 6:37 PM, raiwa said:

From aprox 27 sec to 3.5 sec on first time load (without sql cache) and from aprox 2.5 sec to 0.7 sec on following loads with sql cache (if used).

All depends on the amount of categories/subcategories/products.

Just checked in Phoenix 1.0.5.5 the output for default view line 1068:


            $contents[] = ['text' => '<br>' . TEXT_SUBCATEGORIES . ' ' . $cInfo->childs_count . '<br>' . TEXT_PRODUCTS . ' ' . $cInfo->products_count];

They are also outputted here in category delete alert in lines 999-1000:


        if ($cInfo->childs_count > 0) $contents[] = ['text' => '<br>' . sprintf(TEXT_DELETE_WARNING_CHILDS, $cInfo->childs_count)];
        if ($cInfo->products_count > 0) $contents[] = ['text' => '<br>' . sprintf(TEXT_DELETE_WARNING_PRODUCTS, $cInfo->products_count)];

 

@raiwa I was about to change a catagorie image in my Frozen shop, but was unable to edit the sub-cat. I was due to this change. I changed back, all worked well. Did this a few times and it's confirmed solid. So I'll have to be more patient when loading the Categories.... 😞 It was worth the try.

Link to comment
Share on other sites

Check again your changes. It’s not possible that it’s due to this mod. It’s unrelated to categories images. It has been included in core Phoenix 1.0.5.6.

Link to comment
Share on other sites

You must have made an error which prevents the image to be loaded. The code change itself is unrelated to the categories image.

Link to comment
Share on other sites

Sorry what I meant to say is I cannot change the category at all. I usually click it once, then it highlights (sort of orange) and then I edit. But it does not highlight at all.

Again, I could be my shop, since the admin is bootstrapped by the person who set it up form me, so I suspect some more changes here and there..... 

 

Link to comment
Share on other sites

If you have other mods, possible. But it shouldn't be difficult to adapt.

If you wish, post the relevant snippet and I'll have a look.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...