Magento very high connections

Question

Magento is creating 700 + connections leading to database breakdown whenever cache is flushed or indexing is triggered. Production site remain down for 20 mins till all connections clears. All connections firing same query. And remain in state creating sort index. Using very high database configuration. DB on Amazon rds.Any help is appreciated. This is breaking our production site

Answer 1

The usually happens when we have large number of products or using multiple websites setup in Magento system.

I don't think caching is making issues. There is one indexing called Catalog URL Rewrite that makes problem most of cases.

Below things you can try:

1. Do in indexing from command line(putty) instead backend admin.
2. Do indexing one by one don't use reindexall.
3. Avoid Catalog URL Rewrite Indexing in peak time (Write a cron script to execute in night time)

4. Use this command to reindex from command line

   php shell/indexer.php --reindex <--Your Indexer Name-->

Answer 2

We had the same experience with one of the projects, several things need to be considered for trouble-shooting such issues for example - what DB cluster you are using, is there a query caching in place etc . Clearing the cache will result in increase of connection to serve the request from DB freshly . Avoid running indexes during peak hours ( specifically search and url rewrite index ) .