Performance Issues on site with many products

[totalsam]

Hi DrByte,

I spent a bit of time doing many of the items on the advice, spotted that a while back and tried it and in fairness it helped a little but not dramatically.

I just did the culprit page twice and got the following page/query parse times although I will say that both these attempts were considerably quicker than earlier in the day as you can see on this occasion taking 73-81 seconds whereas earlier my timer had got to 5mins before I gave up waiting!

Parse Time: 71.205 - Number of Queries: 33668 - Query Time: 10.3930005425
Parse Time: 68.176 - Number of Queries: 33668 - Query Time: 4.74085211569

Interestingly the home page has the following response:

Parse Time: 0.125 - Number of Queries: 72 - Query Time: 0.0332261366882

and the search:

Parse Time: 2.672 - Number of Queries: 590 - Query Time: 2.56255935788

Finally a product page:

Parse Time: 0.229 - Number of Queries: 210 - Query Time: 0.0661982624664

It seems the biggest problem has to be the sheer number of queries! Is there any reason that it should be doing 33k + queries just to open up a category?

[totalsam]

P.S. I have left the query times bugger active in case it helps anyone else in responding to this!

[DrByte]

You say it's the number of queries, but the actual query time is only 10 seconds, vs 71 seconds of overall page-processing time. Subtract the 10 seconds from the 71, and you're left with 61 seconds to receive all the data back from the database, process it, and then output all the required information to the browser.

Sure, reducing the number of queries would help, but I'm pointing out the fact that you're misreading the numbers.

[DrByte]

Do you have access to slow-query-log output from MySQL, specific to your site?

[totalsam]

Unfortunately I don't think so, something I have to ask the host about? 10 seconds still seems like a long time even though it is only small in comparison to the other 61 seconds, ideally every page should load within a second or two. Most people get bored of waiting after 10!

[DrByte]

Something appears to be looping through every product in your subcategories for some reason. Without knowing A LOT MORE about your store, all your present admin settings, all your installed addons, etc, I can't really help you with what settings to fix.

[totalsam]

So what would you recommend as the next step?

[DrByte]

slow-query-logs

And a list of all your addons, all the settings you've changed and why, what things you've NOT done from the articles I suggested earlier, etc.

[totalsam]

Have pmed you a list of add ons and details for you to look into it more.

If anything is found out about a possible cause I will add it onto here later.

[totalsam]

Problem now fixed, after DrBytes recommendation I turned off the following unused things and it reduced the queries to 78 and the problem blew away, virtually instantaneous now!

Parse Time: 0.137 - Number of Queries: 78 - Query Time: 0.0447775079193

- Show Featured Products on Main Page - Category with SubCategories

- Show Special Products on Main Page - Category with SubCategories

- Show Upcoming Products on Main Page - Category with SubCategories

- and maybe also: Show Featured Products on Main Page