URGENT PROBLEM - Extremely Slow Performance with increase in product volumes

[AussieGirl]

I am using the simple template system and have made a few other minor alterations to the standard osCommerce shopping cart, but am having huge performance problems. The number of products in the system is now over 40,000 and there are often around 40 people online at any one time. Performance wasn't a problem last month when there were only around 7,000 products.

 

Can anyone suggest anywhere to improve performance? Things I have found so far:

1. even though I'm not using the "new products" module in my template, it was still processing in the background - have now commented it out (plus other items in column left & right that I'm not using)
   
2. in the admin module - the categories.php was taking forever to load, so I have modified it so that the number of products in each category isn't calculated (unles doing a delete) - this means it doesn't have to count all 40,000 products every time I am at the top
   

I have read on the forums previously that people out there have osCommerce shopping carts with over 1,000,000 products. Can anyone with a large data volume give me any ideas about improving performance? The website is on a dedicated server now, but I am still having problems. I am at my wits end.

 

I am also getting an internal server error intermittantly when I upload new products using a custom designed upload process. The upload process itself is fairly simple - it lists all the files in the upload directory (limit of 500 at a time), then for each file does all the standard processing for inserting a new product - the shopping cart is for photos, so the prices are standard for all of the products in each batch and there is no description etc, the filename is used for the image and the description of the product. Any suggestions?

[Guest]

Even on a dedicated server, if there's a shared database, then this will slow things down. Is your database on the same server, or a shared one?

 

If it's on a dedicated server then i can't help.

[AussieGirl]

I'm not sure if it's a shared database or if the database is on the same server? That is something I can follow up with the hosting company.

 

Any other suggestions?

[Guest]

Do you have installed a cache related contribution? You can speed up sql queries. Also you can optimize the bandwidth of images by checking the 'if-modified-since' through a headers check. Turn off categories counters and optimize the sql queries for products/categories.

[Guest]

Also you can optimize the bandwidth of images by checking the 'if-modified-since' through a headers check.

is that a part of chemo's page cache?

optimize the sql queries for products/categories.

is that done through the database, not oscommerce?

[Guest]

no thats for images, for instance some of the thumbnailer contributions -like otf- deploy something like this. The page cache I think it stores an entire page if I remember correctly But even that would help with many products assuming you do a sensible splitting of product pages.

 

optimizing the sql queries can be done through the various script files of the osc. Instead of the standard single table column retrieval you could merge queries using union or join commands. Another thing will be to eliminate unused columns. For instance say you only have english pages that makes the language column redundant. By default you have 3 languages for every product/category description. So you could eliminate the unused languages to optimize the description tables. Same with currencies etc. The shorter the tables they are the better the performance. Many shops go live and they forget about these unused entries.

[Guest]

where do i check in otf thumbnailer that i have that setting active?

 

 

or instance say you only have english pages that makes the language column redundant. By default you have 3 languages for every product/category description.

will deleting them suffice? or is there a call to the database somewhere for them?

 

i do not use "model" anywhere on my product pages, admin or overall website. where could i eliminate the call to the db for that?

[AussieGirl]

Thanks for your reply, but I need some more help.

 

Do you have installed a cache related contribution?

I don't have a cache related contribution as I am not using the Manufacturers box or the Also purchased box and the categories box I am only getting it to retrieve the 1st level of the categories tree. The website visitors have to click through to get to lower levels. Would caching this small categories box do much in terms of performance? If yes, which cache contribution should I use and how do they update when you add more categories?

 

You can speed up sql queries. ... and optimize the sql queries for products/categories.

Any suggestions on speeding up the sql queries?

 

 

Also you can optimize the bandwidth of images by checking the 'if-modified-since' through a headers check.

How do I go about checking the 'if-modified-since' through the headers check? I don't understand this one.

 

Turn off categories counters.

SHOW_COUNTS is set to false. Is this where you mean?

[Guest]

You can check the later versions of otf they have a switch to turn the 'if-modified-since' on/off its a cache switch

 

http://www.oscommerce.com/community/contributions,2226

 

And yes removing unused languages, currencies helps. Of course taking out the language column completely will help more and even merging the tables is even better. However the later is not a trivial work. It involves lots of lines with sql queries and a lot of php scripts to see the results. A cache approach brings good results with minimum effort.

 

I was not refering to the cache boxes. I was refering to a different approach like

http://www.oscommerce.com/community/contributions,2561

http://www.oscommerce.com/community/contributions,1862

http://www.oscommerce.com/community/contributions,2880

 

There are many more you can simply search for cache the contributions section.

 

and yes the show counts helps performance when its set to false.

[AussieGirl]

Hi Guys,

 

Thanks so much for you help. I ended up doing a number of things to significantly improve performance. These were:

1. http://www.oscommerce.com/community/contributions,1862
   
2. using the standard cache of the categories box - but since I had modified the categories box to only display the 1st level I also modified the cache file to generate a filename without the cPath
   
3. used the logging functions to find any unnecessary sql calls - there were quite a few with the introduction of the simple template system
   

I also found out that I had a problem with my caching of thumbnail images, which meant although my bulk upload process had already provided the thumbnail files, the tep_image() function was re-creating them (adding to the load on the server).

 

Thank you, thank you, thank you!