Serious performance issue - over 100K queries

[OopsIbrickedIT]

ZC v1.5.0 PHP 5.6

I have added about 20000 products in category structure Main page-->subcategory1-->subcat2 8000 products
-->subcat3 5000 products
-->subcat4 7000 products

Now my category load time is 35 seconds at best. We are on the works to upgrade to 1.5.5f but it is still unfinished and our customers can't access to these new categories because of awfull page load times. I have done all the basic optimization tricks but they have had little help (-3s page load time)

There is the query cache plugin but it is designed for 1.3.9. Has anyone experiences has it helped on 1.5.0? Or is this template related?
Categories with few products loads under 0.8 seconds

Below is the sad truth how slowest categories load:

[barco57]

They Implement data-diggers querycache module in 1.5.1

[OopsIbrickedIT]

They Implement data-diggers querycache module in 1.5.1

Yes, there is an upgrade on the works to 1.5.5f coming but perhaps I will install 1.5.1 upgrade to live server.

[OopsIbrickedIT]

Yes, there is an upgrade on the works to 1.5.5f coming but perhaps I will install 1.5.1 upgrade to live server.

EDIT: I decided to upgrade 1.3.9 plugin to support 1.5.0 rather than upgrade whole site. Problem eased a little bit and query count halved but still largest category load time is 25 seconds. Now I am pretty clueless where to start find the problem and any help would be appreciated.

[mc12345678]

Yes, there is an upgrade on the works to 1.5.5f coming but perhaps I will install 1.5.1 upgrade to live server.

Testing the database against a vanilla install of 1.5.5f should be easy to do to see if the benefits of that out weigh the cost of trying to patch an old software set. Note that when you get to a Zen Cart version that supports php 7.x, there is expected to be a speed increase.

EDIT: I decided to upgrade 1.3.9 plugin to support 1.5.0 rather than upgrade whole site. Problem eased a little bit and query count halved but still largest category load time is 25 seconds. Now I am pretty clueless where to start find the problem and any help would be appreciated.

Earlier said had done all of the basic optimizations without any detail. What may be basic in one's experience may not include as much as what someone else considers basic. So, what was done? Also by providing that, others may have questions about the product configuration or other related questions.

[OopsIbrickedIT]

Testing the database against a vanilla install of 1.5.5f should be easy to do to see if the benefits of that out weigh the cost of trying to patch an old software set. Note that when you get to a Zen Cart version that supports php 7.x, there is expected to be a speed increase.

I would have done that but our hosting operator has messed up and cannot connect to new mysql5.7 server right now at all

Earlier said had done all of the basic optimizations without any detail. What may be basic in one's experience may not include as much as what someone else considers basic. So, what was done? Also by providing that, others may have questions about the product configuration or other related questions

.

Basic optimization I mean following:

https://www.zen-cart.com/content.php...eed-up-my-site except product pricing also imagehandler in use show images should be all ready optimized

So it boils down I think to that that first navigation category is slowest but subcategories load little faster, this must mean ZC queries the whole dir-structure to the bottom I suspect. Happens only when you press the navigation link on main category. The frontpage loads relatively fast ca. 0.8-1 s

Sorry if I was unclear but was bit of hurry.

I am aiming to run latest or zc 1.5.5.f and php 7x but there has been delays after delays

[mc12345678]

I would have done that but our hosting operator has messed up and cannot connect to new mysql5.7 server right now at all

.

Basic optimization I mean following:

https://www.zen-cart.com/content.php...eed-up-my-site except product pricing also imagehandler in use show images should be all ready optimized

So it boils down I think to that that first navigation category is slowest but subcategories load little faster, this must mean ZC queries the whole dir-structure to the bottom I suspect. Happens only when you press the navigation link on main category. The frontpage loads relatively fast ca. 0.8-1 s

Sorry if I was unclear but was bit of hurry.

I am aiming to run latest or zc 1.5.5.f and php 7x but there has been delays after delays

Umm, you did notice number 6 in the list, did you not?

6. If your categories contain a LOT of products, consider dividing them into subcategories for improved efficiency not only in performance but also in helping the customer find what they're looking for.
If you really can't make your categories more lean, consider turning off the previous/next buttons on the product-info page (Admin->Configuration->Product Info), or at least set the prev/next sort mode to NOT include the product name (ie: pick product Model or Price instead).

Further, when you have a LOT of products in large numbers of subcategories, you may find significant improvement by going to Admin->Configuration->Index Listing and turning off the first 8 settings there (setting them to 0). Adjust as necessary, depending on what content you wish to display.

Remember the time I said things are relative to one another. What may seem basic to one may be complex to another type statement? Splitting 20,000 items in three groups of about 5-8 thousand product is still relatively large groups. I mean, there is a post offered indicating that the number of page associated with viewing this many product in a category is even problematic because of how the page "tabs" are displayed.

While 20,000+ product is not unique to this one instance or store and there are others that have had that many or more total product in a store, but to have to look through nearly a third of that as a customer? Seems like too much in any one category. Not to mention the 20,000 that was added was in addition to whatever potentially large number of product were already there.

[OopsIbrickedIT]

Umm, you did notice number 6 in the list, did you not?


Remember the time I said things are relative to one another. What may seem basic to one may be complex to another type statement? Splitting 20,000 items in three groups of about 5-8 thousand product is still relatively large groups. I mean, there is a post offered indicating that the number of page associated with viewing this many product in a category is even problematic because of how the page "tabs" are displayed.

While 20,000+ product is not unique to this one instance or store and there are others that have had that many or more total product in a store, but to have to look through nearly a third of that as a customer? Seems like too much in any one category. Not to mention the 20,000 that was added was in addition to whatever potentially large number of product were already there.

Hi again,

I made a test environment with 1.5.5f and default template with php7.0 and did not see major performance improvements. Query count remained same as the live site (1.5.0 + Query cache).

We run a car parts web shop and categories are split by car make & model. As you might know to maintain logical stucture of that kind of part amounts you can not split everything to tens of thousands main level categories. We have currently 53K products (more coming at the rate of 1K-4K a day) and at best the problematic main category takes 7 seconds to load. There is not so much problems to load subcatecories but that category top hierarchy page when clicked from left side navigation seems to make queries all the way to the bottom (eg. all products) which seems to me totally stupid and unnecessary waste of queries & server resources. I wonder if there are any attempts made in last 20 years to solve this "little" problem. There must be. Or am I alone with this pretty large shop?

[mc12345678]

Number of queries between versions is not expected to change as I said before. The question is still asked and the fact that the question was asked is still logged, the difference is that if the exact question was previously asked to the database, it does not need to access the database a second time.

As far as "digging" down into all product, some of that depends on what options are made available such as menu selections; new, specials, etc groupings that pull from all product; and the like...

[OopsIbrickedIT]

Number of queries between versions is not expected to change as I said before. The question is still asked and the fact that the question was asked is still logged, the difference is that if the exact question was previously asked to the database, it does not need to access the database a second time.

As far as "digging" down into all product, some of that depends on what options are made available such as menu selections; new, specials, etc groupings that pull from all product; and the like...

Ok, thank you for clarification. I have not yet tested this simple category tree

But that could be possibly a solution to the problem. So the category loads only say 2 steps deep and user can dig further from there. Unfortunately the site is down.