Performance issues with large category

[cablesimple]

Hi, I am in the process of building a store from an enormous amount of product data. I created an import script which inserted all the products into Zencart from this other data source. In all there are 118,919 products. Most of these products are in one category called Misc, 117,831 to be exact. There are 18 other categories (besides Misc) in total ranging from 11-337 products. When you browse to any of the categories they run relatively quickly with the exception of Misc. Here's a sample:
Parse Time: 9.008 - Number of Queries: 625 - Query Time: 8.7948297677002

I have query caching on and PHP_APC, so that actually reflects the speed increase with those turned on. I have seen times up to 13 and 14.

When you browse to a product in that category it is also very slow:
Parse Time: 7.415 - Number of Queries: 418 - Query Time: 2.5871367702332
However if I throw a different cpath on that same product:
Parse Time: 1.505 - Number of Queries: 415 - Query Time: 0.078100791870117

And the absolute worst performance issue is in the admin section. When I go to Catalog->categories/products it takes approximately 40 seconds to load that initial page!

The server this is running on is a Windows 2003 development server with absolutely nothing else running on it. It has a 3ghz proc with 4gb ram, so no issue there.

Now, I am going to be disbursing those products into different categories, but I don't know how many products will end up being in a single category when all is said and done. So I guess my questions are:
1) Is this normal performance when you have a single category this big
2) Is there anything I can do about it? (caching isn't helping all that much)
3) How many products in a single category before you start noticing this severe performance issue...I know that's a pretty fuzzy question, but is there a general rule of thumb for number of products in a category for zencart?

[DrByte]

The server this is running on is a Windows 2003 development server with absolutely nothing else running on it. It has a 3ghz proc with 4gb ram, so no issue there.

Your hardware may not be the issue.
But what about how you've configured the software?
What version of MySQL? and PHP?
What memory model for MySQL? How much of your 4GB RAM have you allocated to MySQL?
How have you distributed RAM within your MySQL memory model? What's your my.cnf got in it? (without passwords), esp for buffer settings.

1) Is this normal performance when you have a single category this big

Unless the server is tuned for it, yes. But I've seen categories with 20,000 products in them display in 0.20 seconds.

2) Is there anything I can do about it? (caching isn't helping all that much)

MySQL config changes as suggested earlier.

3) How many products in a single category before you start noticing this severe performance issue...I know that's a pretty fuzzy question, but is there a general rule of thumb for number of products in a category for zencart?

No general rule of thumb as far as Zen Cart's requirements are concerned.

[cablesimple]

Thanks for the quick reply. I'm running PHP 5.2.5 (Non-thread safe) using FastCGI on IIS (unfortunately). Mysql is 5.0.45 and besides adjusting a few things, my.ini is largely untouched from the install. I did try the my-huge.ini just for a test. It didn't provide much of a performance increase, not that I was expecting it to but figured I'd give it a shot. I've never really had the need to do much tuning, so I'll have to do some research on the subject.

I'm happy to hear that you've see a 20K category perform at such levels, at least I know I'm working towards something achievable.

[cablesimple]

Ok, spent a very long time trying to tune my MySQL server with no luck in speeding up that large category. I even fragmented the large category into a few smaller categories at 28,000 products or less but still experienced page load times of 5-6s without caching. Then I figured I would try to turn off some of the features of ZenCart that are turned on by default in a new install. Turns out that the "new products in month" feature was what was slowing me down. Because I used a script to add all the products at the same time, just about every product was included in the "new products in month" query. Now my page loads in < 2s for the large categories and much quicker with the smaller categories. I'm pretty happy with that.

The admin section is still very slow however, have to do some more troubleshooting to figure that one out I guess.

[yellow1912]

"but still experienced page load times of 5-6s without caching"
But you do use caching right? As it can greatly increase to loading speed. For most online stores with the content not being changed all the time, caching can be really helpful.

[cablesimple]

Oh yeah, I use it, but had it disabled for troubleshooting purposes.

[RodG]

G'day cablesimple,

> The admin section is still very slow however, have to do some more > troubleshooting to figure that one out I guess.

I've experienced the exact same problems as you, namely, when there are many 1000's of items in any given category things can really slow down.

I know exactly where and why these slowdowns occur, but I haven't found enough spare time to develop a fix.

The problem is actually caused by some very bad/inefficient code related to the category/product filters. Essentially these filters work by reading in all products into one array, the filtered products into another array, and the final output is derived from the difference of these two arrays (A far more efficient way of doing it would be to apply the filters before reading the data from the database and then directly outputting it).

Until this code gets re-written the only 'fix' I know of is to keep the number of items in any given category to something less than 10,000

Cheers
Rod.

ps. The comments above apply to V1.3.7. I haven't checked the code in 1.3.8 yet.

[cablesimple]

Hi RodG,
If you have them, would you care to share the file(s)/ line number to start at and save me a bit of research time? I need to do something to fix this issue, and I'd rather do it the right way assuming it's not going to take too long. Assuming I have the time to implement a proper fix I'd be happy to pass my code along.

[RodG]

G'day C,

> Hi RodG,
> If you have them, would you care to share the file(s)/ line number
> to start at and save me a bit of research time?

Sure. The 'offending' file is
/admin/includes/functions/general.php

The offending function is:
zen_draw_products_pull_down() it is located around line 243

It seems that I DID get to implement a very crude fix, BUT I never actually got to deploy it for some reason (this was over 6 months ago).

Looking at the code, what I did was to add a check on the number of products read from the database, and if it was over 1000 it re-ran the same query but this time with a LIMIT 1000 restriction.
I can't remember if there was a reason why I didn't simply limit the query to 1000 items in the first place.

Hope this is of some little help.

Cheers
Rod