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)

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