MySQL bottlneck - getting mysql errors

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

[DrByte]

The "name" of the indexes is not important.
The existence of the indexes, along with having the correct fields defined to them, is the important part.

[ksoup]

I am still checking the database...

The host wants to upgrade apache... Can you tell us if that is a good idea? We experienced issues with it the last time they tried due to incompatibilities within PHP and MySQL

Currently we are at ...
Kernel version 2.6.9-55.0.9.ELsmp
Apache version 1.3.39 (Unix)
PHP version 5.2.5
MySQL version 5.0.45-community

The "name" of the indexes is not important.
The existence of the indexes, along with having the correct fields defined to them, is the important part.

[DrByte]

I don't see that upgrading apache would "help" your specific situation.

However, Zen Cart itself doesn't care whether you're using Apache 2.x or 1.x.

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

crud... forgot to ask this, too...

Do you have suggestions for better optimization for ZenCart.

And, are there any versions of mySQL and/or PHP that they recommend NOT using?