VPS slow site

1.3.9h installed manually no upgrads.
Add ons:
Apsona
OSC Assist auto update runs every 8 hours.
Easy Populate4

We have a host (Lunarpages) that complains that we are using too many resources (they are famous for this) on a shared server so we went to another host (HOst For Web - I have always had good experiences with them until now) and obtained a VPS server with 1G RAM

Our site now on Host For web is now crashing because we run out of Memory. With just two users -(myself and another) navigating the site we can crash the site due to RAM spike .

Tech support has told us that we are out of RAM and that its that the database is so large and that we must upgrade to a larger VPS with more RAM. Our Database has 400K records and 99MB broken down basically like this:

<rounding the largest MB have .... in fornt>
banner history 900
categories 600
cat description 600
counter history 400
config 600
*misc feed tables from OSC autoupdate program 43,000 - not accessed when pages are served. Only when auto update is occurring.
manu 500
manu desc 500
meta tag product descriptions 21,000
order status hisory 800
orders total 600
products 35,000 .....8MB
product attributes 151,000 .....23MB
products description 35,000 .....30MB
products to categories 38,000

then a bunch of little tables.

So - does this appear to be a large database that could crash a 1G RAM VPS server with only a hand full of simultaneous users?

I dont really know if its the database or bad coding from one of the add ons that is causing the problem. Of course the hosts say "upgrade upgrade!" but I want to make sure thats really necessary to upgrade again after we just did (shared -> VPS)

Thanks for any input.

I have also noticed that over 60% of RAM is used for just basic functions of the VPS leaving us only 40% of the RAM for site/DB functions.

Is that normal?

When I hit the ZenCart home page, I see the RAM usage go from 180MB RAM available to 160MB available sometimes down to 140 depending on the page. When I run two browsers, the RAM goes down to 90 - 80 MB RAM available. That with 2 "people" hitting the home page.

Seems like alot of RAM to serve the ZC homepage. All we have active on it is New products (6) and Specials (6). We do have about 40 top categories to show headings .
.
Thank you

Hi,

The fact that you have 151,000 attributes is possibly going to cause you problems, although that depends on the way those attributes are spread across your 35,000 products.

Unfortunately, a lot of the code that handles attribute queries in 1.3.9 can be inefficient, and that is something we hope to address in new versions.

One of the things we did in v1.5.1 is install data_diggers queryCache by default. If you can't upgrade to 1.5.1, you might want to look at installing that plugin.

We are also looking at making the attribute queries themselves more efficient in upcoming versions (there is a lot of scope to reduce the loop nesting for some attribute queries) but again that's in the future and will require you to update code.

How are your attributes distributed? fairly evenly across many products? or a huge number associated with just a couple products?
What are your page-parse times?
http://www.zen-cart.com/content.php?63-how-do-i-speed-up-my-site

Any time I've ever hosted a large database driven site on anything other than a dedicated server, I have had problems. Even $50 hosting plans will run slow and crash.

Also, if your plan is using Plesk Parallels that is going to eat up half your 1 GB Ram before it even gets to your site.

Kyle,

Thanks for the replies. Ill check out parse times and post was on my list to do was well... will look into the querycache and 1.5.1. Have to step out for a while but will get back later tonite with numbers.

FYI the attributes are 9 color choices that apply to 18K products. Pretty evenly distributed over 50% of the products.

• Note, I see the parse times before images are loaded by browser. - maybe a hint to the over all problem?

Parse time:
-Home Page: Parse Time: 2.386 - Number of Queries: 455 - Query Time: 1.8560320601
-About us: Parse Time: 0.274 - Number of Queries: 140 - Query Time: 0.0368446787567
-Product info page with attributes: Parse Time: 0.634 - Number of Queries: 600 - Query Time: 0.132515612701 took about 7 seconds to load.
-Product info page with no attributes: Parse Time: 0.551 - Number of Queries: 263 - Query Time: 0.0541621955109
-Category page with many subcategory items to show: Parse Time: 0.630 - Number of Queries: 854 - Query Time: 0.146100096039 took about 25 seconds to load the page completely.
-Category page with only 3 sub cats to show: Parse Time: 3.123 - Number of Queries: 392 - Query Time: 2.06735750441 Took about 15 seconds to load the page completely
-To run a search on a keyword that returns 2500 items: Parse Time: 5.294 - Number of Queries: 1555 - Query Time: 4.44838138672 Took about 25 seconds to load the page completely.

What do you think? I just cant pinpoint the problem. Also, why do I get parse time before the images have even finished loading? Curious...

Thanks for your input.

The Page Parse Time is the time it takes your Zen Cart to get and build all the HTML for the page and send it to the browser. (and, the query time is how much of that page time is waiting on the database.)
It doesn't include anything related to how long it takes your browser or internet connection to download that HTML nor the linked assets (such as images, stylesheets, etc).

See: http://www.zen-cart.com/content.php?63-how-do-i-speed-up-my-site, and the links inside it, for more tips and info.

Just thought I would throw out there a suggestion regarding MySQL tuning.

Grab the script mysqltuner.pl and run that after your db has been running for a few days and use the suggestions as a guideline for some sql tweaks.

YMMV.

Thanks everyone....