Too many attributes? Store too slow

[rliddle]

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

[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.

[rliddle]

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.

[rliddle]

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.

[rliddle]

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 ##

[DrByte]

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.

[BlindSide]

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.

[rliddle]

Will have to try query_cache

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

[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.

[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;