Performance Issues on site with many products

Hi,

Currently running a store with 1.3.9h (planning to upgrade soon just haven't had a free moment yet!) with 50k products and 5,600 categories. I'm about to launch another 8k products and later an additional 35k products and probably an additional 3k categories will be needed for these products. This will bring totals to about 95k products and 9k categories. The site is already slow, admin runs as smooth as a babies bottom but customer side is not so slick.

The product search used to be very slow, but changing the default search to not search product descriptions sped this up dramatically (still not exactly brisk though!). Browsing by categories and loading product pages however is incredibly tediously slow.

The website address is www. e m i n e n c e g i f t s dotcodotuk (sorry don't want search engines to spot it on here!).

I currently am on a premium shared hosting plan from Camelot, is it an optimisation issue or should I really be looking towards going dedicated?

All comments would be much appreciated!

Doesnt seem very slow to me, each page loads within 2 seconds. You should use an image handler though, so all the thumbnails are actually thumbnails and not the full image.

The home page loads brilliantly, but try clicking on photo gifts and see how long that takes to load. I've read the email, opened the forum and replied to this message and its still not opened the page.

I'll look into the image handler when I have an opportunity, but I don't believe this is the problem, chrome loads text first then images, so in theory I should see the text and then a delay before the images catch up.

I did. It must be a server issue, because when I replied, I went through lots of categories, and products, and even went to the all_products page and everything loaded in about 1-2 seconds.

Now it won't even open most pages, and even if you do a view source of a page, it won't even load - and thats not loading any external files..

After a quick look the "All products" is pretty quick, its just the loading of individual pages which is now taking into the minutes to load instead of seconds.

Yes, but last time I went into individual products and it was fine.

Ok nothing obvious, everything seems fairly quick to me, perhaps if some one could give a link to a slow page that may help

1. Optimise your images, that may be part of it, image scaler might be a better option for you than image handler
   when I load one of your pages this is the image that is used for the thumbnail /yoursite/images/jewellery/WC009.jpg its 550px x 624px which is downloaded then resized by the browser to 90 by 70px.
2. get rid of stuff you aren't using, are you using css buttons? if not get rid of the stylesheet

Actually Have you run your site through a few of the testing sites out there

http://gtmetrix.com

this gives you both googles page speed and Yahoos ySlow

Here's a couple of examples

Not too bad now it doesn't search product descriptions by default, but a search:

https://www. e m i n e n c e g i f t s dot co dot uk/index.php?main_page=advanced_search_result&search_in_description=0&keyword=hello

The real killer - just opening a top level category - photo gifts:

http://www. e m i n e n c e g i f t s dot co dot uk/index.php?main_page=index&cPath=34

Have you considered this advice? https://www.zen-cart.com/tutorials/index.php?article=21

And what are your page/query parse times like? https://www.zen-cart.com/tutorials/index.php?article=105

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?

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

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.

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

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!

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.

So what would you recommend as the next step?

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.

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.

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