[Solved]Slow Site - Categories? Products? I really need help

[rturner]

My slow site has been an ongoing problem. Without getting into all the helpful links I've followed here, I have made a lot of suggested improvements. I've uninstalled all but the most essential modules, etc, etc. I'm starting to wonder if I've been entering products to categories in the wrong way.

 

My host (hostgator vps) has logged that the database slowness is happening when clicking on products from thumbnails. They suggested I shorten my product names from an average of 80 characters to 10. I'm not sure this will work, but we've started changing product names down to 10 characters. We are about 1/4 of the way through 1100 products. (they also suggested that I move up to a level 5 vps - $$ - so that I can have the resources to run memcached).

 

Basically, I have around 10 main categories. Each one has about 10 subcategories. So far, I've been entering a product in it's main category and I also enter it in one or several subcategorie that it belongs to. Say if it's Nature related jewelry, I enter it in the main category Nature. I also add it under the subcategory of certain nature bracelets if it's a certain nature bracelet or its collection name under Nature (say, Nature / Florals). So I'll have one product in the main category Nature and also listed in several subcategories under Nature.

 

So far, the subcategories' products don't load great, but they're acceptable. Say, if a subcategory has 40 products, clicking a thumbnail takes 2 seconds to bring up the product. But in the larger main category, there could be 300 or 500 products. When I click on those product thumbnails, the product page takes 7-8 seconds to load. My customers will all leave if this happens.

 

I'm wondering if I should remove all products from Main Categories, or remove all but the most recent 50, and just leave them residing in their subcategories. That way I could end up with every category and subcategory having no more than 50 products in it.

 

The slowest category in question is http://www.ecg.com/new/33-nature

 

Does this sound like I'm on the right track? Does anyone have a similar experience? Thanks.

[Kisa_ua]

have you try to switch off Friendly URL?

[El Patron]

have you used tools to test your site externally from your browser? If not try this tool, http://www.webpagetest.org/ before you start moving or modifying your site content. And can you tell us your server configuration, i.e. shared hosting, vps, private...what are you using for caching from your back office, file system/memchache or none?

[rturner]

Thanks for the responses. I had tried switching off Friendly URL and it didn't seem to change anything. Trying the webpagetest tool shows a typical page in the Nature category waiting 9 seconds to the first byte. Which tells me that the database is having trouble finding the product (?).

 

I was using a VPS Level 3 on hostgator. Originally I had memcached installed, but Support told me that I didn't have enough resources to run memcached, so they took it off. Long story short, I upgraded to a Level 5 VPS with just under a 3ghz cpu and 1.5 gigs of RAM and I'm running memcached. The site is running faster and all the subcategories that contain under 50 products are very snappy. The Nature category has over 500 products. It's running faster, but still very slow. My initial solution, which I think will work, is to uncheck all the Nature products from the main category and just have them reside in the subcategories. So we've been unchecking them one at a time.

 

However, today I installed the latest version of the Layered Navigation block module hoping to index the products and speed things up. It did speed things up, but now all the products that we unchecked are back in the main categories again. I unchecked the Main categories when building the index, but they are still there. My eventual goal is to have this happen:

 

When someone clicks on a main category, like Nature, only thumbnail images of the Subcategories will show, and no products. Then when they click on the subcategory, it should run faster since there are only 30 to 50 products in there.

 

Think I should uninstall the Layered Navigation Block? Any idea how to remove products from main categories? Right now the subcategory thumbnails do now show, so I've been trying to figure that out as well. Thanks.

[El Patron]

The problem with prestasop, joomla, wordpress etc...is the same.

 

When doing pagination, the cms will read every item in that category before rendering the page rather than reading what is necessary to create the page and then having the next button, read the next lot. I suspect they do this because bot's can click the next button. for example on your site (nature):

http://screencast.com/t/wZUswDjW

 

ps caching or memcache will be of little to no use. IMHO you will need to either look at:

 

1. your existing mysql cache values, (use phpmyadmin for report)Here is an example of a hosting's vanilla query cache (typically no cache is setup)

http://screencast.com/t/8cqlQFuROOks

 

2. upgrade mysql to enterprise, as it supports 'real' caching. free releases of mysql will flush a cached entry when say view count is updated..argh

 

3.modify code find/create module that reads only what can be rendered, and obtains the next page render from pagination button. this can however cause issues for search engine bots

 

4. and of course just limiting the number of items in a category...which just seems limiting.

[rturner]

From your post I think I *finally* understand what is happening, and it's what I suspected. Prestashop is looking for every product in a category before it renders the one asked for.

 

1. I clicked around phpmyadmin, but I'm afraid I don't know enough syntax to execute a cache query without fear of flushing all my tables or something :-)

2. It looks like mysql enterprise runs about $5000 USP per year. I guess I can't afford that.

3. I'll look around for that type of module. I'm not really concerned with search bot issues as my site only has specific members, not the general public.

4. It's limiting, but at least I know I'm on the right path (thanks!). I plan to have no products in the main parent categories, just the subcategory thumbnails. Then I will probably have 10 subcategories, each with 50 products, and that should make load times at least acceptable.

 

I really appreciate your informative post! If you happen to know which .tpl file I have to edit to enable the category thumbnails to show when clicking on a main category, I would appreciate it. I've been looking over product-list.tpl and can't find it. I think the theme I'm using turned that option off.

[El Patron]

in your phpmyadmin, see if you can find this:

 

http://screencast.com/t/mxLAPNrt3VTA

 

Many people are not comfortable with setting up many facets of a vps/dedicated server and hosting companies do not automatically improve base installations, so you use more resources and upgrade. So one basically ends up with a shared hosting on a faster machine and leave it up to the customer to figure out how to enable features supported with their vps/dedicated server. Most folks don't get past cpanel/plesk, which don't have tools for this sort of thing anyway.

 

I suggest finding experienced linux person that can configure your run-time environment to take advantage of what you paid for. Else if you can get past setting up shell access to your server, installing putty, learning linux commands, then you are 80% there.

[rturner]

I found the query cache:

[El Patron]

I need to move to your hosting provider...my vps sucks in comparison if this config is out of the box...

 

maybe try flushing your cache, see bottom of your screen shot...then running your nature..then checking cache?

 

we lost plesk CLI interface on our linux box, spend hours yesterday and this morning and then my 'the provider'...says were not the provider...I think my hosting provider that is making me a bitter old man..

[rturner]

I don't think it's out of the box. I've been dealing with their linux administrators and they assured me that they would configure my container to optimize with Prestashop. It didn't cost anything, but now I'm paying around $100/mo. to Hostgator. At least I have a huge container with a lot of RAM so I'll be moving my other smaller sites there.

 

On the other hand, I tried flushing the cache and got this error:

#1227 - Access denied; you need the RELOAD privilege for this operatn

 

I could always try logging into the shell as root and running the MySQL code to flush the cache. Or I'm sure I could ask Support to do it. I think my bottom line is going to be trimming down the categories. Unfortunately I can't run dedicated servers, but this track seems to be working for my small company. Thanks!

[kikbo]

I tried reading this post to see if it would help my ttfb issues i'm having with prestashop on godaddy...and your website doesn't load at all http://www.ecg.com/new/en/

 

:-/

[Aswin C.]

rturner, did you try to enable smarty debug, and see with module is taking long time?

config -> smarty.config.inc.php -> $smarty->debugging = true;

 

also look at my post "slow blockcategories.tpl"

[Aswin C.]

Im 100% sure your problem has something to do with my post.

 

how much categories you have?

i'm working on a small fix... will improve your shop speed by 99% ! ( smarty cache will actually work for blockcategories )

Edited July 3, 2012 by AswinC (see edit history)

[powdermonkey1850]

Bizzarly, My instance was much quicker after turning OFF APC caching! ?