Cart running very slow after 1.3.8a upgrade

I'm pulling my hair out, I recently upgraded my customer's cart from 1.3.7 to 1.3.8a and the cart is now running very slow. We have been running this site on Zen Cart for about three years and have never had performance problems until now. One unique thing about this cart is that we have approximately 50,000 categories.

The site is http://batterywarehouseco.com

I have looked through the forums and have not really found anything that has helped. I did however turn on display parse times. When displaying a master category that has 127 subcategories I get the following parse times.

Parse Time: 24.422 - Number of Queries: 66314 - Query Time: 16.174537784409

Why would it take 66,000 queries to display 127 subcategories? Could this be the issue?

To duplicate the issue click on "Camcorder" from the product menu. But the problem is not limited to just that category.

Displaying the same master category in admin results in no performance issue at all. Of course I have the Goto drop down box disabled. This issue that I'm have in the cart reminds me of the kind of long delays I experienced in admin with the goto box turned on. Could the cart be trying to query more than just the subcategories displayed?

Server information:
CENTOS 3.9 Linux
Apache 1.3.41
PHP 5.2.8
MySQL 4.1.22
WHM 11.24.2 / Cpanel 11.24.4
Fantastico 2.10.4 r27

Zen Cart was upgraded through Fantastico.

I am the owner of the server and have root access if needed to troubleshoot.

Any ideas, anyone? Please help.

I recently upgraded my customer's cart from 1.3.7 to 1.3.8a

Zen Cart was upgraded through Fantastico.

I am the owner of the server and have root access if needed to troubleshoot.
I have never seen a Fantastico upgrade that actually addresses ANY of the file incompatibilities that you should/might have in your overrides folders and as this is a client why are you using Fantastico at all???

This is not a native 1.3.7 install (been upgraded before) or at least based upon a template of earlier vintage so if you have been using Fantastico for these other upgrades no telling how out of date your custom template - battware - files are??

You need to look at each version upgrade's changelog and insure that your template files that have new versions are then merged.

Kobra,

I appreciate the advice, I had already checked for any file changes and made the necessary modifications. I even went as far as to change the template temporarily to the "classic" template and I stall have the issue.

I really think this is related to the high query count I'm experiencing. Any ideas why this would be so high?

Thanks in advance.

One unique thing about this cart is that we have approximately 50,000 categories.

I really think this is related to the high query count I'm experiencing. Any ideas why this would be so high?
If the site referenced is the one in question I do not see 50,000 cats at there are only about 20 top level cats and I am not seeing the parse times you referenced???
What must be selected to see this?

To see the query times and counts I was referring to, just click on the Camcorder category. The total category count I was referring to includes subcategories.

As an example Camcorder has 92 subcategories, one of which is Canon, the Canon subcategory has 723 models listed as subcategories. As you can see from this example the category count gets quite high. The categories table currently has 42852 rows in the database.

What is happening is only happening in the top level category, when I click on Camcorder, it takes about 66,000 queries to display the 92 subcategories and takes about 20 seconds to complete the queries, but when I click on Canon inside the Camcorder category, it displays the 723 subcategories in only 2.67 seconds with only 7600 queries.

Hope I'm explaining ok.

Thanks for your help.

Were you able to fix this? I did not see any excessive slowness briefly moving around your site ... :unsure:

Ajeh:

Were you able to fix this? I did not see any excessive slowness briefly moving around your site ... :unsure:

Still not fixed, I have been communicating through PM with Kobra. The last PM I sent is below:

I think I have made some progress on this issue. I started with a fresh install of 1.3.8a on another domain on my server and then imported the Battery Warehouse db. I got the exact same results, still around 66,000 queries to display the Camcorder category and still very slow.

Next, I decided to restore the installation prior to the upgrade on another domain and using 1.3.7, the results for the Camcorder display are

Parse Time: 0.340 - Number of Queries: 491 - Query Time: 0.074598350402832

If you would like to see the difference, the old version is up and running on http://lcmi.net/catalog

My priority now is to get the store back on 1.3.7, but that will take some working with the customer since they have taken orders. I will get you a backup of the database and a tarbal of the images directory before I downgrade. I should be able to complete this tomorrow.

When I have these, I'll send you links to download the files from my server. Thanks for all your help!

Best regards,

Mark
Four-Point

This morning I restored the cart to 1.3.7 and it is working fine. Just now I started with a fresh install of 1.3.8a on a different domain http://lcmi.net/catalog and I have loaded the saved database from the 1.3.8a install at http://batterywarehouseco.com on that domain. It is currently running very slow at the duplicate site, with 66,000 + queries to display the "Camcorder" category, while the 1.3.7 cart does it in less than 500 queries.

So for an easy performance comparison, just compare the two sites. The duplicate 1.3.8a site is using the default template with none of the product images loaded.

Kobra's reply to my last message (in green) was:

You should get DrByte involved in this as I am sure that he will have some causes...
1.3.8 code was designed for less querries and faster operation. So I would guess there is something amis

Do you agree? How do I get Dr. Byte involved, if needed?

Please turn on parse querry times in your main site for the comparison

Have you checked your indexes on your database tables?

Have you check that you are not running things that you do not use such as New Products, Featured, Specials, Upcoming centerboxes should not be run if you do not use them as they waste time ...

What is the server load on this under the Server Info for the dog site?

Ajeh:

Have you checked your indexes on your database tables?

Have you check that you are not running things that you do not use such as New Products, Featured, Specials, Upcoming centerboxes should not be run if you do not use them as they waste time ...

What is the server load on this under the Server Info for the dog site?

Not sure how I would check the indexes, I have run a repair on the db after copying it to the new domain.

As far as running things I don't use, none of the settings have changed between the 1.3.7 version and 1.3.8a version.

Both sites are on the same server, the server load is very low.

Server Load 0.18 (2 cpus)
Memory Used 46.4 %
Swap Used 18.56 %

I also turned on parse times display on http://batteryhouseco.com

Jumping in here:

On his main site and selecting the "camcorders" cat this is the parse result

Parse Time: 0.351 - Number of Queries: 490 - Query Time: 0.079982677459717

On the test 1.3.8a site selecting the same cat:

Parse Time: 13.712 - Number of Queries: 66327 - Query Time: 6.5049182998505

It seems to be the # of querries

four-point:

Not sure how I would check the indexes, I have run a repair on the db after copying it to the new domain.

As far as running things I don't use, none of the settings have changed between the 1.3.7 version and 1.3.8a version.

Both sites are on the same server, the server load is very low.

Server Load 0.18 (2 cpus)
Memory Used 46.4 %
Swap Used 18.56 %

I also turned on parse times display on http://batteryhouseco.com

In zencart server info:

Server Up Time: 22:44:45 up 3 days, 3:45, 2 users, load average: 0.03, 0.09, 0.10

In phpMyAdmin, it lists the indexes on the table structures ...

Open one window for the fast site and one for the slow site and make sure the slow site has its indexes ...

Ajeh:

In phpMyAdmin, it lists the indexes on the table structures ...

Open one window for the fast site and one for the slow site and make sure the slow site has its indexes ...

I check the indexes by comparing good and bad, they look the same on both.

On your v1.3.7 site ...

What are your settings on the Index Listing for the Centerboxes ... do you have them turned off?

On the Layout Settings do you have the Right Column turned off?

Turning off the right column and turning off the Centerboxes in the Index Listing for subcategories ...

I get the times of:
Parse Time: 0.181 - Number of Queries: 252 - Query Time: 0.0458592881012

I think I would need to see your settings in your v1.3.7 site ...

On the New, Featured, Specials and Upcoming ... depending on your settings ... you may need to tweak this to go back to the older style that did not go through all categories to obtain the products ...

This depends on what settings you are using on your site ...

Ajeh:

On your v1.3.7 site ...

What are your settings on the Index Listing for the Centerboxes ... do you have them turned off?

On the Layout Settings do you have the Right Column turned off?

I think I would need to see your settings in your v1.3.7 site ...

On the New, Featured, Specials and Upcoming ... depending on your settings ... you may need to tweak this to go back to the older style that did not go through all categories to obtain the products ...

Linda,

I have Right Column Global = 1 on the 1.3.7 site

The Index Listing settings are as follows:

Show New Products on Main Page 0
Show Featured Products on Main Page 2
Show Special Products on Main Page 3
Show Upcoming Products on Main Page 4
Show New Products on Main Page - Category with SubCategories 1
Show Featured Products on Main Page - Category with SubCategories 2
Show Special Products on Main Page - Category with SubCategories 3
Show Upcoming Products on Main Page - Category with SubCategories 4
Show New Products on Main Page - Errors and Missing Products Page 1
Show Featured Products on Main Page - Errors and Missing Products Page 2
Show Special Products on Main Page - Errors and Missing Products Page 3
Show Upcoming Products on Main Page - Errors and Missing Products Page 4
Show New Products - below Product Listing 1
Show Featured Products - below Product Listing 2
Show Special Products - below Product Listing 3
Show Upcoming Products - below Product Listing 4
New Products Columns per Row 3
Featured Products Columns per Row 3
Special Products Columns per Row 3
Filter Product Listing for Current Top Level Category When Enabled 1

I'm a little confused, has the way Featured Products is handled been changed in 1.3.8?

By the way I get similar results to you when I change the above settings to 0.

Yes it has ... it works down through the categories to get the products ...

For most, this is a great thing ... :smile:

For you with your bazillion categories this is slow ...

If you do not need these centerboxes on your site, then turn them off ...

If you do need them, then you can probably use the old centerboxes and they will work the old way in v1.3.7.1 ...

You can use the templates and overrides for this by putting those older centerboxes in:
/includes/modules/your_template_dir

With the v1.3.8 centerboxes, with all of your categories the time is:

Parse Time: 15.289 - Number of Queries: 88000 - Query Time: 11.2190559112

With the centerboxes from v1.3.7.1 with all your categories, the time is:

Parse Time: 0.185 - Number of Queries: 254 - Query Time: 0.0512316558228

Now I still need to test a few things to make sure all is happy and working but I think this would do it for you ...

Ajeh:

If you do not need these centerboxes on your site, then turn them off ...

Linda,

I now have my settings set to:
Show New Products on Main Page 0
Show Featured Products on Main Page 2
Show Special Products on Main Page 1
Show Upcoming Products on Main Page 0
Show New Products on Main Page - Category with SubCategories 0
Show Featured Products on Main Page - Category with SubCategories 0
Show Special Products on Main Page - Category with SubCategories 0
Show Upcoming Products on Main Page - Category with SubCategories 0
Show New Products on Main Page - Errors and Missing Products Page 0
Show Featured Products on Main Page - Errors and Missing Products Page 0
Show Special Products on Main Page - Errors and Missing Products Page 0
Show Upcoming Products on Main Page - Errors and Missing Products Page 0
Show New Products - below Product Listing 0
Show Featured Products - below Product Listing 2
Show Special Products - below Product Listing 1
Show Upcoming Products - below Product Listing 0
New Products Columns per Row 3
Featured Products Columns per Row 3
Special Products Columns per Row 3
Filter Product Listing for Current Top Level Category When Enabled 1

in both the 1.3.7 and 1.3.8a carts. Performance is fine in both now and I seem to be getting the centerboxes I need.

I did notice that the "Featured products" never displayed in the category lists on the 1.3.7 cart even though it was set to display. Perhaps that is why I never experienced slowness in the past.

No that I have the settings as they are, I should hopefully have no issues when I upgrade the production cart to 1.3.8a.

I know our cart is pretty unique with all the categories, I really appreciate you getting to the bottom of this!

Thanks again for all your help!

Best Regards,

Mark Galazka
four-point

Thanks for the update that you now have this working right and fast ... :smile: