DB tuning & Sessions causing site to crash

Dedicated Server Details: CentOS latest php and mysql up to date. The physical server hardware is top of the line.

We run about 5 drupal CMS sites, 3 CS-CARTs sites and some simpler stuff on 3 other sites. they’re all on different domain names.

Most sites get on average 200 to 1000 visitors per day each

One of the CS-CART sites is huge, many pages and a lot of media content. It seems to use too many connections and a lot of memory. It sometimes gets 2000 visits in 12 hours and when more than 45 people are shopping at the same time the site will crash. actually the entire mysql server will crash and all sites will lose connection to the databases.

We have tuned our mysql settings to cope with requests and we no longer get the too many connection error. This is with memory_limit in php.ini @ 96M.

Now I read that you should not increase your php memory limit because u can get dossed by “hackers”

What happened now is:

I got the white screen with: “Error Occurred”.

Checked the logs and saw that the memory was exhausted. Checked my memory settings in the cs-cart install files and found that i actually had set 512M in local.config.php

@ini_set(‘memory_limit’, ‘512M’);

However in my php its set to 96M. Can someone tell me the difference between those two values and will they function if one is great than the other?

If my php.ini memory limit is 96M does that mean all sites on that server share this amount and if 2+ sites all have 96M 96M they can exhaust the mem limit??

i have now @ini_set(‘memory_limit’, ‘96M’); in local.config.php

and 96M in php.ini

these settings did not solve the “ERROR OCCURRED” page.

I checked the database and found SESSIONS to be needing repair, i then repaired it and site works again. I found this in the logs. Can someone translate that for me? i see root and password change and act as user…that looks king of suspicious.

Error: Duplicate entry ‘hb1s26suncusb8aq9hqcm6amg6-C’ for key ‘PRIMARY’ (1062)

Query: REPLACE INTO cscart_sessions (session_id, area, expiry, data) VALUES (‘hb1s26suncusb8aq9hqcm6amg6’, ‘C’, ‘1314117656’, ‘_validator_data|a:1:{s:2:"ua";s:83:"Mozilla/5.0 (Macintosh; Intel Mac OS X 10.5; rv:5.0.1) Gecko/20100101 Firefox/5.0.1";}settings|a:2:{s:14:"cart_languageC";a:2:{s:5:"value";s:2:"EN";s:6:"expiry";i:1314715256;}s:19:"secondary_currencyC";a:2:{s:5:"value";s:3:"EUR";s:6:"expiry";i:1314715256;}}auth|a:12:{s:4:"area";s:1:"C";s:7:"user_id";i:0;s:10:"tax_exempt";s:1:"N";s:10:"last_login";i:0;s:13:"usergroup_ids";a:2:{i:0;i:0;i:1;i:1;}s:9:"order_ids";a:0:{}s:11:"act_as_user";b:0;s:10:"this_login";i:1314110456;s:25:"password_change_timestamp";i:0;s:10:"company_id";i:0;s:7:"is_root";s:1:"N";s:7:"referer";s:64:"http://www.gxxx.xxx/shop/fxxxhtml\";}cart|a:0:{}access_domain|s:12:\"\";’)

Think of your php config as the default configuration. When you do an ini_set() call, you are overriding that default value. So you should be running at 96M of memory based on the changes you describe above.

If you run your site in Development mode then you will get a DB error dump rather than Error Occurred. This should be an intermittent solution while trying to find the problem. You can also enable “database errors” in the Logs/settings area. That will give you the DB errors seen by the system.

For regular maintenance, you should clear your “statistics” about once a month (maybe once a week if you're getting 2K hits a day). You should also clear your “Logs” at the same frequency, but usually on off cycle. I.e. if you clear things once a week, do Statistics on Sundays and Logs on Thursdays. Logic is to hold some history of what's happening on your site.

Your error is probably the result of the table needing to be repaired. There are 2 keys for the sessions table. The session ID and the area. So it saw that there was already the same session id being used for area (C - customer). But it's using a REPLACE so that doesn't make a lot of sense.

Anytime your site actually crashes, you have a high risk of the user_sessions table being in need of repair. It is something you should check afterward.

thanks for that. we have now tuned our database and php settings. cs-cart now runs great. Live HELP addon is a real killer though.