We are having a big problem with our sites stability the last couple of weeks and after endless hours of troubleshooting I don't get anywhere. So I turn to you dear community.

Setup: 2 x VPS servers - Front end, 8 core, 8G RAM. - Database, 5 core, 3G RAM.

Both running Ubuntu. Ruby on Rails EE with Passenger 3 and Rails 2.3.11. MySQL 5.1.67.

The problem is that each night, at the exact same time (23:26) the SQL server suddenly shows a processlist full of COMMIT with an increasing Time. After 30-40 seconds (can go longer) a wave seems processed and the site responds for a few seconds before it repeats. During this hick up the database server load spikes while the front end is relaxing.

I have looked at slow queries, but is not finding any locks or other unusual queries ran at this time. I have looked at iotop at the time of the halt and there is no activity from mysql. I also tried turning off query_cache and messed around with the mysql configuration file without much change.

Any ideas?

  • Have you enabled the full query log, and captured all the queries happening during that time-frame? Yes logging all the queries will slow things down, but it might be worth doing at least once to see if you can isolate the cause. – Zoredache Oct 1 '12 at 21:58
  • Yes, but I just see a bunch of the regular queries that we run. I'm not sure where to look for the exact cause of the lockup though since the log doesn't specify the time the query took etc. – miccet Oct 1 '12 at 22:09
  • what does the sar output say. any clue from there. – Soham Chakraborty Oct 2 '12 at 0:34
  • 2
    Could this be the time that the VPS provider suspends the VMs to snapshot them? – Tom O'Connor Oct 2 '12 at 15:00
  • Good one, I'll check this. Thanks for thinking outside the box. – miccet Oct 2 '12 at 19:21

check for:

  • cron jobs
  • access/error log
  • mysql slow query log


I'd log-in to system around that time and would use (h)top and monitor system to see what exactly is causing it and then work from there to enable more logging on that daemon to figure it out.

  • There is no cron job at that time. Nothing in the error log. Like I said, nothing unusual in the slow query log. The most logged ones are the COMMITS, but it doesn't show exactly what query was ran. – miccet Oct 1 '12 at 21:55

We switched the DB server to a new cluster on our VPS provider's network and installed it fresh. We didn't notice any problems at all yesterday. Guess it's just one of those things that fall outside of our own configuration. Moral story is that you might need to check with your VPN provider if you're having these kind of troubles.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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