Jump to content
raiwa

Admin : categories.php slow loading

Recommended Posts

Posted (edited)

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

Edited by raiwa

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
Posted (edited)

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.

Edited by René H4

Share this post


Link to post
Share on other sites

By the way: I have imported this database into a Phoenix testshop, which shows the sam behaviour.

So it's not shop-version related, I think.

Share this post


Link to post
Share on other sites
Posted (edited)

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.

Edited by raiwa

Share this post


Link to post
Share on other sites
Posted (edited)

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?

Edited by raiwa

Share this post


Link to post
Share on other sites

Where exactly did you comment this out @raiwa? I would like to test this also, and check the impact.

Could moving categories cause the slowlyness also?

 

Share this post


Link to post
Share on other sites
Posted (edited)

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);

 

Edited by raiwa

Share this post


Link to post
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?

 

Share this post


Link to post
Share on other sites
Posted (edited)

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)];

 

Edited by raiwa

Share this post


Link to post
Share on other sites
28 minutes ago, René H4 said:

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

What I meant to ask is:

What is the function of the commented-out lines in the first place?

Share this post


Link to post
Share on other sites
Posted (edited)

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.

Edited by raiwa

Share this post


Link to post
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?


Help shape the future of Phoenix; join the Phoenix Club

Share this post


Link to post
Share on other sites
Posted (edited)
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.

Edited by raiwa

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
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.

Edited by raiwa

Share this post


Link to post
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.

Share this post


Link to post
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..... 

 

Edited by René H4

Share this post


Link to post
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.

Edited by raiwa

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×