Here is my my_cnf.txt

[mysqld]
#set-variable = max_connections=600
log-slow-queries
safe-show-database

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=3000
max_user_connections=600
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=256M
join_buffer_size=4M
max_allowed_packet=32M
table_cache=1024
sort_buffer_size=4M
read_buffer_size=4M
read_rnd_buffer_size=3072K
max_connect_errors=10
thread_concurrency=4
myisam_sort_buffer_size=128M
skip-locking
server-id=1

[isamchk]
key_buffer=256M
sort_buffer=256M
read_buffer=64M
write_buffer=64M

Generally those look good, assuming there's actually enough RAM allocated to the MySQL engine to support those aggregated values.
However, I'm certainly not the defacto expert, and there's a lot of information not available here in this discussion. Including VPS RAM, CPU speed/core allocation, and dozens of other items. This forum really isn't geared around teaching people how to configure the MySQL or other components of the webserver, since that's the job of the hosting company.

You may find it useful to analyze your server's slow-query-logs and pinpoint what exactly is problematic for your site.

Or, again, find another host whose performance doesn't croak on small query volumes.

DrByte:

Generally those look good, assuming there's actually enough RAM allocated to the MySQL engine to support those aggregated values.
However, I'm certainly not the defacto expert, and there's a lot of information not available here in this discussion. Including VPS RAM, CPU speed/core allocation, and dozens of other items. This forum really isn't geared around teaching people how to configure the MySQL or other components of the webserver, since that's the job of the hosting company.

You may find it useful to analyze your server's slow-query-logs and pinpoint what exactly is problematic for your site.

Or, again, find another host whose performance doesn't croak on small query volumes.

I do appreciate your responses. I moved server one time for this, (so now on 3rd server in a week, and that is painful.) Right now I have VPS Software SolusVM/OpenVZ
Processing Cores (upgradable) min 2 Cores
Guaranteed RAM 2GB
Burstable RAM 4GB
Disk Space 100 GB
Monthly Bandwidth 5,000 GB

How do I tell if there is enough RAM allocated to MySql server. Just ask them?

I would like either a server expert to step in if there is one available. I don't know the right questions to ask, and apparently they don't know what to do, even though they say they have the expertise. Don't want to move if possible again. or if need be move servers again. (don't think my wife can take that!)

Will try and look at slow-query-logs.

This is my response from host.

1. Can more RAM be allocated to the mysql database? would that help?

We haven't limited MySQL, you can check the configuration as we have already provided you the my.cnf file.

2. Can checking slow-query-logs analyze the issue?

I'm sorry but that won't help us, you need to have a developer look into those.

3. Do you have any other suggestions? At this point the product pages are too slow to be usable.

We've done as you requested, we've fine tuned everything, a developer would be able to suggest you something appropriate.

:yuck:

I have tried Leverage browser caching in .htaccess and it does not seem to show up when checking with http://gtmetrix.com

I edited the .htaccess in my zen cart folder with (got the last piece from a search. It does not seem to show up when I update it.

NOTE: Replace /shop/ with the relative web path of your catalog in the "Rewrite Base" line below:

Options +FollowSymLinks
RewriteEngine On
RewriteBase /mysite/

From Ultimate SEO URLs

RewriteRule ^(.)-p-(.).html$ index.php?main_page=product_info&products_id=$2&%{QUERY_STRING} [L]
RewriteRule ^(.)-c-(.).html$ index.php?main_page=index&cPath=$2&%{QUERY_STRING} [L]
RewriteRule ^(.)-m-([0-9]+).html$ index.php?main_page=index&manufacturers_id=$2&%{QUERY_STRING} [L]
RewriteRule ^(.)-pi-([0-9]+).html$ index.php?main_page=popup_image&pID=$2&%{QUERY_STRING} [L]
RewriteRule ^(.)-pr-([0-9]+).html$ index.php?main_page=product_reviews&products_id=$2&%{QUERY_STRING} [L]
RewriteRule ^(.)-pri-([0-9]+).html$ index.php?main_page=product_reviews_info&products_id=$2&%{QUERY_STRING} [L]
RewriteRule ^(.*)-ezp-([0-9]+).html$ index.php?main_page=page&id=$2&%{QUERY_STRING} [L]

For Open Operations Info Manager

RewriteRule ^(.*)-i-([0-9]+).html$ index.php?main_page=info_manager&pages_id=$2&%{QUERY_STRING} [L]

For dreamscape's News & Articles Manager

RewriteRule ^news/?$ index.php?main_page=news&%{QUERY_STRING} [L]
RewriteRule ^news/rss.xml$ index.php?main_page=news_rss&%{QUERY_STRING} [L]
RewriteRule ^news/archive/?$ index.php?main_page=news_archive&%{QUERY_STRING} [L]
RewriteRule ^news/([0-9]{4})-([0-9]{2})-([0-9]{2}).html$ index.php?main_page=news&date=$1-$2-$3&%{QUERY_STRING} [L]
RewriteRule ^news/archive/([0-9]{4})-([0-9]{2}).html$ index.php?main_page=news_archive&date=$1-$2&%{QUERY_STRING} [L]
RewriteRule ^news/(.)-a-([0-9]+)-comments.html$ index.php?main_page=news_comments&article_id=$2&%{QUERY_STRING} [L]
RewriteRule ^news/(.)-a-([0-9]+).html$ index.php?main_page=news_article&article_id=$2&%{QUERY_STRING} [L]

All other pages

Don't rewrite real files or directories

RewriteCond %{REQUEST_FILENAME} !-f [NC]
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*).html$ index.php?main_page=$1&%{QUERY_STRING} [L]

EXPIRES CACHING

ExpiresActive On ExpiresByType image/jpg “access 1 year"
ExpiresByType image/jpeg “access 1 year”
ExpiresByType image/gif “access 1 year”
ExpiresByType image/png “access 1 year”
ExpiresByType text/css “access 1 month”
ExpiresByType application/pdf “access 1 month”
ExpiresByType text/x-javascript “access 1 month”
ExpiresByType application/x-shockwave-flash “access 1 month”
ExpiresByType image/x-icon “access 1 year”
ExpiresDefault “access 2 days”

EXPIRES CACHING

Browser caching isn't going to help your database performance.

You could experiment with the query_cache addon if you're adventurous. It may or may not help your specific situation.

rliddle, if you've switched servers three separate times within the same hosting company, I'd say it's pretty clear that there's something wrong with that host.

I recommend you hit up our Hosting page for some good alternatives.

Will have to try query_cache

Blindside, should have said different hosts, not servers within the same host.

Make sure that each of your attributes are marked with "Apply Discounts Used by Product Special/Sale: No" (assuming that the options don't have cost associated with them); this will heavily reduce your parse times.

Excellent idea ... a fast way to do this for Attributes with 0 price that would not need the discount checked is to use:

UPDATE  products_attributes SET attributes_discounted = 0 WHERE options_values_price = 0;

Lat9, Can I just say, Thank You, Thank you!! I implemented that on one of my products and went from

Parse Time: 8.386 - Number of Queries: 1665 - Query Time: 7.37952516934

to

Parse Time: 0.671 - Number of Queries: 178 - Query Time: 0.194840832687

Amazing! I hope others who are in similar situations find this thread. I talked with "experts" in Seattle, Van Couver, Penn, Va, and switched hosts 2 times and this is the solution!

Thanks everyone for your help. Moving on!

lat9:

Make sure that each of your attributes are marked with "Apply Discounts Used by Product Special/Sale: No" (assuming that the options don't have cost associated with them); this will heavily reduce your parse times.

Will this make any difference to a site that has attributes with costs applied to them, but it not using any sales discounts on any products?

If it does, what sql patch would I need to run to turn every attribute I have to 'no' ?

If you have no Sales or Specials or do not offer those same Specials and Sales on Attributes then you can turn it off for all Attributes with:

UPDATE  products_attributes SET attributes_discounted = 0;

Ajeh, can you tell me specifically where this code would go? thanks.

Ajeh:

Excellent idea ... a fast way to do this for Attributes with 0 price that would not need the discount checked is to use:

UPDATE products_attributes SET attributes_discounted = 0 WHERE options_values_price = 0;

rliddle:

Lat9, Can I just say, Thank You, Thank you!! ...

Thanks everyone for your help. Moving on!

You are very welcome! Perhaps this pointer should be part of one of the attributes-related tutorials.

rliddle:

Ajeh, can you tell me specifically where this code would go? thanks.

You can run that in phpMyAdmin on the SQL or in the Tools ... Install SQL Patches ...

Gads - I completely missed those attribute things.
Grateful for the others who stepped in with the settings tweaks.

And ... the side-benefit is that you've now done a heckuvalotta tuning for server performance overall. :)

Hi all,

I am having a similar problem - products with attributes are loading very slowly. We sell invitations, which are very customized so attributes are a big part of our business.

You can check out my sample product here:
http://sweetletterpress.com/2013/index.php?main_page=index&cPath=104
with parse/query time shown at the bottom.
(I still have to add more attributes under "font 1" and "font 2" and I haven't added the additional product images yet either, all of which I'm assuming will slow it down even more)

Even slower than the product details page is the "quick view" (which should be called "very slow view"!). You can access it from the same link above by rolling over the thumbnail. Any thoughts on how to simplify/speed up this up?

I have an experienced ZC developer helping me but he says he's done all that he knows how for speed. We are using the latest version of zenCart on shared Hostmonster plan and a substantial number of customizations made to the install.

I am on hold with my hosting company right now to have them examine the things you all mentioned in this thread but I am hoping some of you Zen masters will take a look and see if anything looks amiss... extraneous, bloated code, errors etc.

Thanks very much.

UPDATE

Hostmonster says usage is all fine and normal. He upgraded me to php 5.3 (from 5.2) but not noticing any difference.

The times I'm seeing are:
http://sweetletterpress.com/2013/index.php?main_page=index&cPath=104
Parse Time: 4.625 - Number of Queries: 125 - Query Time: 3.1653004515839
Parse Time: 0.209 - Number of Queries: 114 - Query Time: 0.037671459594726
Parse Time: 0.255 - Number of Queries: 114 - Query Time: 0.083213655319214

Quick view:
Parse Time: 7.439 - Number of Queries: 184 - Query Time: 3.3798476817322
Parse Time: 4.684 - Number of Queries: 184 - Query Time: 1.4276787158661
Parse Time: 6.825 - Number of Queries: 184 - Query Time: 2.1082679089355
Parse Time: 5.672 - Number of Queries: 184 - Query Time: 2.7163698864136

http://sweetletterpress.com/2013/weddings/wedding-invitations/queen-anns-lace-wedding-invitation
Parse Time: 12.044 - Number of Queries: 245 - Query Time: 4.2742161156921
Parse Time: 9.282 - Number of Queries: 245 - Query Time: 3.6378543113098
Parse Time: 13.134 - Number of Queries: 245 - Query Time: 5.9804625470123
Parse Time: 8.659 - Number of Queries: 245 - Query Time: 3.6243100138245

So it looks like you've got slowness happening at both the database side and the CPU/PHP processing side.
With your many customizations and AJAX additions, I'm inclined to think those may be big culprits.

Some things for your developer to address:

1. You've got some assets that your template is trying to load, but which do not exist, including the main jQuery file and a .gif image, etc:
   GET http://sweetletterpress.com/2013/includes/templates/theme219/jscript/jquery-1.4.4.min.js 404 (Not Found)
   Uncaught TypeError: Object [object Object] has no method 'fancybox'
   GET http://sweetletterpress.com/2013/includes/templates/theme219/images/tpp/tableft28.gif 404 (Not Found)

2. You're loading 4 different versions of jQuery on your site. That's a HUGE performance problem, plus it also creates chaos for your page scripts to try to figure out what they're doing:
   /2013/includes/templates/theme219/jscript/jquery-1.4.4.min.js
   /2013/includes/templates/theme219/jscript/jscript_jquery-1.5.min.js
   /2013/includes/modules/pages/product_info/jscript_jquery-1.5.2.min.js
   /2013/includes/templates/theme219/jscript/jscript_jquery-1.8.2.min.js
   Each of those files take 0.7 to 1.3 seconds to load, and only the newest one is really needed. The others should be deleted.

3. Plus you're calling /2013/dpu_ajax.php to set the price on the page, and each time it fires it's taking 2.11 seconds to run. So whatever it's doing in the background needs some optimization too.

Fixing those issues will probably provide some reprive, and then maybe make other fixes more evident.