We have lots of Drupal sites installed on our server. Some of the sites do have lots of views and blocks configured on it. Occasionally, one of such Drupal site (with lots of views and contents) suddenly becomes unresponsive or very slow. But rest of the Drupal sites with less contents appear to work fine during that time. We have to restart MySQL database in order to bring unresponsive Drupal site back to normal. We were thinking of setting up a periodic MySQL restart using cron.
This periodic restart option do not appeal to me very much. I want to find the cause of this problem. I suspect that it is mostly because of MySQL, but still it is a guess.
I am not experienced in the server performance issues. What is the best way I should tackle this problem? How can I confirm that it is a MySQL problem and not because of other? If it is a MySQL problem, what basic steps I should take to solve it?