MySQL bottlneck - getting mysql errors

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

[kobra]

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?

Not better optimization of Zen Cart - more to optimizing your server LAMP settings and these are not something many will share as hours upon hours are spent in finetuning these settings.

Question: DB is 500MB?? How many products do you have?

[ksoup]

about 7400... mainly downloadables (which means at least one attribute on each one)

has anyone seen a zen cart database larger than this? how far can it be pushed!

Not better optimization of Zen Cart - more to optimizing your server LAMP settings and these are not something many will share as hours upon hours are spent in finetuning these settings.

Question: DB is 500MB?? How many products do you have?

[DrByte]

Have you enabled Email Archiving? If so, why? You should probably trim it down if you plan to keep it enabled.
Do you clear your Admin Activity Log regularly?
Naturally, make backups before you go deleting any information.


I've seen several databases larger than 500 MB, and they operate just fine.

Your performance issues are more likely related to your server's general configuration than to the size of the database. Having such high sustained load-averages is going to slow everything down.

Tuning your MySQL settings may help, but your load averages should be dealt with first.

[ksoup]

The email archive table is empty .. and we clean admin when the site flags us at the top in the admin area... so both good things.

Have you enabled Email Archiving? If so, why? You should probably trim it down if you plan to keep it enabled.
Do you clear your Admin Activity Log regularly?
Naturally, make backups before you go deleting any information.


I've seen several databases larger than 500 MB, and they operate just fine.

Your performance issues are more likely related to your server's general configuration than to the size of the database. Having such high sustained load-averages is going to slow everything down.

Tuning your MySQL settings may help, but your load averages should be dealt with first.

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

[DrByte]

2006 MySQL server has gone away

This is a result of several things:

1. A database connection was opened, but not closed, because there is intention to use it again.
2. Then the program goes off and does some processing or talks to other servers, etc.
3. Then it comes back and attempts to access the database again, but the processing in the previous step has taken longer than the timeout allowed by the server's configuration settings. Thus, since the server has closed the database connection, the program is not able to access it (since it had a reasonable expectation to be able to reach it again).

Some timeout problems can occur when attempting to open the Checkout-Shipping page and perhaps the real-time quote query from USPS takes to long (such as their servers are taking a long time to respond, are down, etc), and when the program attempts to process the rest of the page logic, the database is no longer disconnected.

But generally speaking, unless there is some sort of attempt to talk to an external resource or you have some sort of intense processing with custom code, there should be no reason for any database timeouts ... unless the problem is related to your server/database-server configuration.

[Website Rob]

Depending upon your Server Hardware specs, optimizing your current setup may get you better results.

For example, in the Server 'php.ini' file the following should be used:

[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent = OFF

That will help to prevent the situation as described by DrByte; which is also known as an "Orphan process".

[JoelMMCC]

Don’t overlook the possibility of just plain bad SQL coding, especially if you’ve customized anything. Zen Cart’s SQL through 1.3.x is pretty darned inefficient with its SQL anyway (they’re fixing much of that in 1.4.x, currently in beta — around 700 queries removed in the generation of the Home Page alone!). When customizing ad-hoc SQL queries pieced together from variables and named constants and string constants and expressions and the like, a typo can easily be overlooked.

We just had this problem with constantly getting “2008 MySQL client ran out of memory” errors on the products listings pages (“?main_page=index…”). After reading this thread and worrying about indices and MySQL and Apache config. parameters and our shared hosting service not being able to handle the load, etc., I finally simply pasted the query as shown on the error page into a phpMySQLadmin SQL window and executed it.

It returned well over seven million records, from one of the smaller categories! This, mind you, for a store with only about 1,600 items, only about 20 of which were in that category!

In analyzing the, I found some embarrassing typos on my part: in my customized “{store root}/includes/index_filters/default.filter.php” file, I had used the same alias on both sides of the “=” in my custom “LEFT JOIN … ON … =”s! I had, for instance (bad table alias capitalized here to call attention to it, but not of course capitalized in the original PHP code):

$listing_sql = "select … left join " . TABLE_PRODUCTS_MYTYPE_EXTRA . " x on p.products_id = x.products_id
    left join " . TABLE_MY_FILTER . " f on x.myfilter_id = X.myfilter_id," 

when it should’ve been

$listing_sql = "select … left join " . TABLE_PRODUCTS_MYTYPE_EXTRA . " x on p.products_id = x.products_id
    left join " . TABLE_MY_FILTER . " f on x.myfilter_id = F.myfilter_id," 

in all four times that that query appears (in various variants) in default_filter.php! Since I had more than one such custom filter, and all had that same typo (gah!), well, you can imagine why it returned so many records!

Fixing that brought the results down to a far more manageable mere several thousand, which no longer crashed, but which still returned way too many records and was showing duplicate products in the listing — better than an error message, perhaps, but still not good.

This time, the fault seemed to be in part of the original SQL query of the default_filter.php file! Maybe my custom product types and filters showed it up, but it seems to have been bad code all along, even in the default!

It has to do with the Manufacturers portion of the query (so if your store doesn’t use Manufacturers, this probably wouldn’t affect you, but you should still consider fixing it in case you do start using Manufacturers in the future).

Again, look for where the variable “$listing_sql” is assigned (it’s assigned four times, one of which will be executed based on various conditionals). In all four places, the table whose name is stored in the constant “TABLE_MANUFACTURERS” with alias “m” is listed as a cross join rather than a left or inner join! In all but the last one, this is mitigated by adding this conditional in the WHERE clause: “…and p.manufacturers_id = m.manufacturers.id” and so shouldn’t actually cause that problem unless the last one is executed.

It’s referenced right after “TABLE_PRODUCTS_DESCRIPTION” which is also listed as a cross join, but that one with good reason: the “languages_id” field of the “zen_products_description” table means that that is a many-to-one relation with the “zen_products” table and so needs to be filtered separately for current language.

I went ahead and reworded the query to make the TABLE_MANUFACTURERS referenced as a “LEFT JOIN ON p.manufacturers_id = m.manufacturers.id” in all four variants of the query, and removed the now-redundant “…and p.manufacturers_id = m.manufacturers.id” from the WHERE clause. To make the syntax cleaner, I moved it above the TABLE_PRODUCTS_DESCRIPTION reference.

Now my products index listing pages return no out-of-memory errors, and return just what they’re supposed to! And, they’re quite a bit faster, to boot!