Large Slow site on VPS

[monkeytown]

We have a site with about 50k products. The number grows each quarter by several hundred also. The site runs painfully slow. We are on a VPS from servint and it seems to me the site should run much faster. I don't have much (any?) knowledge on how to try and optimize the server which I think should be running the site much much faster ( mymonkeytown.com/shop ). Any ideas?

[CJPinder]

Hi,

Yes, the site should certainly be running a lot faster than it is. To help diagnose the problem we'll need to know a bit more information...

1) What version of Zen Cart are you using?
2) What add-ons do you have installed?
3) Have you made any other changes to the Zen Cart files?
4) If you switch back to the Classic template does the speed increase?
5) What is the spec. of your VPS?
6) Has the site been slow since it was first installed or has it just started happening?
7) Mas MySQL been optimized since it was installed?
8) Have you asked Servint to look at the problem?

Regards,
Christian.

[CJPinder]

Two more things...

9) Does the Admin also run slow or is it just the store front?
10) In Admin under the Configuration menu, if you select Index Listing are all the 'Show...' options set to 0?

Sorry for all the questions.

Regards,
Christian.

[DrByte]

At first glance the site appears to run fine ... until you click to view a particular product's details ... in which case the database is causing the slowdown:
Parse Time: 560.270 - Number of Queries: 192 - Query Time: 560.196580541

Product listings, add-to-cart, etc are all speedy. The main slowdown is loading the product page. This could be a problem with a bad addon, a damaged database table, missing database index, etc.

As Christian has indicated above, lots more information is needed.

[monkeytown]

Hey guys, thanks for the replies. I got super swamped and was out of the office for a bit. I'll see if I can address your questions to my question :)

Servint has tweaked what they can on their end. The problem is fairly recent. We added another site to the VPS this winter and the sites seemed to run fine together but occasionally they slow down (together obviously). Host says that the zen DB is what is bogging down the server. I thought it was maybe due to the number of products since some of the add ons (like easy populate) started acting funny around the 25k mark.

The site has a few add ons from this site and several tweaks and new functionality that we have added but nothing was added around the time that the site really started to slow down.

I think the problem is a resource issue because I can be refreshing on the product details page and getting parse times of 1 second and then suddenly the same page takes 50 seconds to load. I hate to throw money at the host if that isn't the problem but to me the site seems to be fine until what I guess is a bottle neck in the mysql server. Does that sound reasonable?

[monkeytown]

1) What version of Zen Cart are you using?
1.3.8a


2) What add-ons do you have installed?
easyPopulate
Quick Order
Local Sales Tax
Tax Exempt
Pay by Invoice (account number)

Custom Add Ons
FREE Shipping per account
AJAX Stock Check (for Quick Order)


3) Have you made any other changes to the Zen Cart files?
Yes, but nothing recently compared to the speed decrease


4) If you switch back to the Classic template does the speed increase?


5) What is the spec. of your VPS?
Shared between two sites

75 GB Storage UPGRADED
45 GB Storage
1.5 TB Monthly Transfer
1.5 GB Guaranteed (3 GB Burst RAM)

Hardware RAID 10
CentOS 5 Operating System
4 IP Addresses



6) Has the site been slow since it was first installed or has it just started happening?
Site ran for 2 years ok with little to no issues. Started acting up recently shortly after (not immediately) we started hosting another site on the server. Since then we have boosted the ram to PHP and upgraded packages with the host (doubling the available ram) on the VPS with some benefit but not eliminating the problem.


7) Mas MySQL been optimized since it was installed?
They said it is fully optimized.


8) Have you asked Servint to look at the problem?
Yes, slight performance increases but not solved. The problem has been especially bad this week.


9) Does the Admin also run slow or is it just the store front?
Anything pulling up categories/products seems to be slow. Sometimes much slower than others.


10) In Admin under the Configuration menu, if you select Index Listing are all the 'Show...' options set to 0?
Yes

[Ajeh]

More than 1-2 seconds is a problem ...

[Ajeh]

Could you look in phpMyAdmin at your database tables such as products, products_description etc.

and see if their indexes exist?

Do you use Specials on your Products?

Do you use Featured Products?

Do you use SaleMaker on your Categories?

[monkeytown]

We use specials occasionally, Specials table has 664 entries.

We don't use Featured Products or Sale maker.

Products has indexes on
products_id PRIMARY
products_date_added
products_status
products_date_available
products_ordered
products_model
products_price_sorter
master_categories_id
products_sort_order
manufacturers_id

Products_description has indexes on
products_id PRIMARY
language_id PRIMARY (also?)
products_name

[Ajeh]

You are missing numerous indexes ...

What version of Zen Cart is this?