Store regularly crashes until I optimize database

[kuroi]

Has your apache error log recorded the reason for the server errors?

[Vger]

I switched all tables except products_description to InnoDB from MyISAM in order to prevent max user connection lockups, but that hasn't been a problem before

Exceeding max user connections for MySQL should not be a problem unless:

1. You have a very poor host. Check with them what the max user connections allowance is.
2. You have an exceptionally busy website. This seems unlikely if it is happening overnight.
3. You have reset the default setting in the configuration.php files to allow persistent MySQL connections.

correct setting:

define('USE_PCONNECT', 'false');

Vger

[Cheepnis]

Exceeding max user connections for MySQL should not be a problem unless:

1. You have a very poor host. Check with them what the max user connections allowance is.
2. You have an exceptionally busy website. This seems unlikely if it is happening overnight.
3. You have reset the default setting in the configuration.php files to allow persistent MySQL connections.

correct setting:

define('USE_PCONNECT', 'false');

Vger

1. My host has the max user connection set to 10 and they won't budge unless I buy dedicated server, claiming 10 is more than enough. They are actually pretty good, but aren't interested in debugging my store scripts, naturally.
2. Nothing too bad, (10-15 at a time, including spiders).
3. the configure setting you described hasn't changed and is at the default you posted.

The logs don't indicate anything unusual.

[DrByte]

I find that the customer side of the store has crashed (500 Server Error).

Has your apache error log recorded the reason for the server errors?

As kuroi indicated, your server holds the key to the actual cause of the "500" error.
https://www.zen-cart.com/tutorials/index.php?article=63