MySQL bottlneck - getting mysql errors

[ksoup]

1.3.7 (latest) not 1.3.8

Our database appears to be 500 MiB... and we are having mysql errors by the boatload.

Is there someone who can help ... the store will continue to grow, so we need to learn what to do now with such a busy database.

Thank you in advance.

[ksoup]

Our loads are pretty high. How can we pinpoint what is using the resources?

1.3.7 (latest) not 1.3.8

Our database appears to be 500 MiB... and we are having mysql errors by the boatload.

Is there someone who can help ... the store will continue to grow, so we need to learn what to do now with such a busy database.

Thank you in advance.

[Kim]

Need to know more - exactly what are the errors? What version of MYSQL? Etc.

[ksoup]

Thank you, Kim....

The webhost tells us our server loads are high by a huge margin!
Database: MySQL 5.0.45
load average: 12.67, 11.41, 10.83
HTTP Server: Apache/1.3.39 (Unix) PHP/5.2.5

Here are some of the errors received (but mainly sluggggiiiisssshhhh store!!)

~ MySql Server has gone away

~ 1064 error

~ Common Customer Complaint
I am doing my VERY best to buy a ton of stuff from your
store while it is on sale....however....it is taking FOREVER to load.

~ 2006 MySQL server has gone away
in: [select code, title, symbol_left, symbol_right, decimal_point,
thousands_point, decimal_places, value from zen_currencies]

~ 1062 Duplicate entry 'zc_6ec65decb95a7741ada473af0fde76e1' for key 1
in: [db_cache table]

After clicking add item to cart

~ 2008 MySQL client ran out of memory


We have repaired our tables... the webhost has done a deeper level database repair and they are intact.

Any help you can give will be most appreciated! :)

Need to know more - exactly what are the errors? What version of MYSQL? Etc.

[DrByte]

Do all your tables have the correct indexes on them? (To find out, you'll need to export your table structures and compare them against the master mysql_zencart.sql file in the zc_install/sql folder)

[ksoup]

I have started comparing... and I see the following commonly... the _zen is missing from the keynames listed in phpmyadmin.... Is that a problem?


Master SQL
KEY idx_page_accessed_zen (page_accessed),
KEY idx_access_date_zen (access_date),
KEY idx_ip_zen (ip_address)



Index shown in PHPmyadmin
keyname type cardinality field
PRIMARY PRIMARY log_id
page_accessed INDEX page_accessed
access_date INDEX access_date
idx_ip_zen INDEX ip_address

Do all your tables have the correct indexes on them? (To find out, you'll need to export your table structures and compare them against the master mysql_zencart.sql file in the zc_install/sql folder)

[Website Rob]

Our database appears to be 500 MiB... and we are having mysql errors by the boatload.

The webhost tells us our server loads are high by a huge margin!
Database: MySQL 5.0.45
load average: 12.67, 11.41, 10.83
HTTP Server: Apache/1.3.39 (Unix) PHP/5.2.5

When you say "our Server loads" does this mean you have your own Server?

Yes - then more / better optimization needs to be done at the Server level

No - then probably time to get your own Server

A 500 MB database is HUGE and, if the above load stats are from a busy Shared Server and are continually at that level, your site will never get past the problems it has now.

[ksoup]

i certainly appreciate your responses and help!!

i understand we are on a dedicated server (does that share resources with the masses?). I do know, however, we share the resources with our forum and gallery.

When you say "our Server loads" does this mean you have your own Server?

Yes - then more / better optimization needs to be done at the Server level

No - then probably time to get your own Server

A 500 MB database is HUGE and, if the above load stats are from a busy Shared Server and are continually at that level, your site will never get past the problems it has now.

[ksoup]

Here is a common error we are getting... along with others... but i have seen this one several times or more

2006 MySQL server has gone away in:
[select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from zen_currencies]

Thoughts?

We are looking at a server upgrade and will do that soon. But, why do we continue to get errors??