5

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?

Improve this question

6 Answers 6

Reset to default
4

I second the recommendations of the DBTuner and Devel modules. I also agree that you should avoid restarting Mysql whenever possible. It builds up a bunch of caches in RAM and will have to start all over if you restart. You should make sure your mysql is using at least my-huge.cnf or better. This thread at Drupal.org http://drupal.org/node/85768 has some info about tuning Mysql for better performance. Always test your my.cnf changes on a dev server because Mysql config changes often break the server without warning or explanation.

Also, make sure PHP has a good amount of RAM. PHP delays can give the appearance of slow db server if you're only looking at the site in a web browser. You want it to have memory_limit = 32M or greater (I usually do 64-126M). You don't want it crazy high though because that can start to have a negative impact on Apache's performance.

There is also a High Performance group here http://groups.drupal.org/high-performance . This is a good place to poke around.

Improve this answer
4

First tool, that can be used, is Devel - it will show there's long calls have been made.
And there's many tools for perfomancing and tuning mysql in internet.

Improve this answer
3

I think if you ever need to restart MySQL regularly, especially scheduled, then there is a problem.

How often does this unresponsiveness occur? Do you have ssh access to the machine? Can you access the MySQL prompt and do 'show processlist;' ?

I suspect it is a locked table issue if a query is taking a long time.

Improve this answer
2
  • There is no as such time that when this unresponsiveness occurs. It happens even if there is only 2-3 users accessing this site, but after the MySQL restart, site come backs to normal. I need to get together with system-admin to run the 'show processlist;' command.
    – Ajinkya Kulkarni
    Commented Mar 12, 2011 at 2:01
  • Can you get the system admin to enable the slow query log? After restarting you could consult that to see if anything pops up.
    – Christian
    Commented Mar 13, 2011 at 23:40
2

I found the dbtuner module to be very helpful. It even suggests and provides an easy way of indexing specific fields. Give it a shot.

Improve this answer
2

Next time this happens do a SHOW FULL PROCESSLIST in phpmyadmin/mysql. It will show what queries are running; what tables are locked and for how many seconds the query has been running for. Once you know the query, it's a lot easier to figure out whats wrong.

I'm slightly biased, but dbtuer is a pretty sweet tool for finding slow queries in views. Devel rocks for finding all slow queries.

Improve this answer
2

If you're using version 7, some performance tips can be found at this post.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.