We have a dedicated server with a virtual instance running the application server with Apache, PHP and a virtual instance with DB on it. We are running Magento 1.7.0 version on the application server and MySql as the DB. For some reason the site appears to go down atleast 3-4 times a day.
During the time Magento is down with an error of "Can't connect to Mysql server", Apache seems ok, with it not being overloaded. The mysql server also seems to be in good health with no overloading and not many procesess. If you try to connect from the Magento instance to the DB server via the mysql command it takes a while for the command to be executed. We have checked all the network parameters and all seem fine.
Kindly let me know if you have any suggestions.
Best Answer
We have faced similar issues and the most common reasons are one of the following:
Reindexall is called from cron.php as per scheduling this on times will eat resources and get stuck or hold some queries( IMPORTANT ).
Core Url rewrite indexing is intensive so is product flat table populate process if you have Multistore created or lot many products.
Some third party extensions used to populate data externally.
Soltuions: 1. configure Mysql as per available resources and requirement. -- more memory, use of Mysql cache, number of connections allowed etc.
Make sure that Flat catalog product and category is on( IMPORTANT ).
Indexing not to be run frequently.
Improve your caching to reduce data base load.
Check for additional modules that might be using lots of resources.
Go into server mysql and check for processlist this will give some query list thats not getting executed completely.