For one reason or another at random times, my webhost reboots the sever that hosts my CentOS VPS, when this happens it often causes one of my MySQL tables to crash. The table handles the sites PHP sessions (a Joomla site powered by PHP).

The site gets a fair amount of traffic so when the VPS is rebooted, the session table is often being written to.

Is there anything I can do to stop this happening? Perhaps some sort of soft MySQL shutdown when the server gets the reboot command?

The only thing I am doing now is auto repairing the table when the server comes back up.

  • Why are they restarting your VPS for? – qweet Mar 5 '12 at 13:05
  • First what storage engine are you using (InnoDB or MyISAM)? Second is the system being hard-powered-off, or is it a graceful shutdown (are the hypervisor interface tools telling the OS its shutting down)? Might also help to know what hypervisor it is. – Patrick Mar 5 '12 at 13:08
  • The tables are all MyISAM. I don't think the host will say exactly how it's being restarted but as the other answer said it looks like a force/kill. – ServerBloke Mar 5 '12 at 17:28

You could switch to InnoDB tables on your database, or use a database system that supports full ACID-compliant transactions; PostgreSQL does that and can crash without clobbering your database (it just has to replay its journal before it finishes its startup process).

MySQL's default tables are not crash-safe. I'm not even sure that I would call InnoDB crash safe, but it's as close as you're going to realistically get without switching to another product, and I assume that is not an option for you.

  • Would switching the tables type likely make any difference? I'm not sure MySQL is crashing, rather the host seems to be doing a forced shutdown. As you say switching to another product isn't an option unfortunately. – ServerBloke Mar 5 '12 at 17:33
  • You can consider the sudden loss of power a crash in this instance. If you cannot change systems, then you'll just have to do the best you can with what you have. InnoDB tables is a start, but make sure that you're binlogging and have regular manual checkpoint backups, of course, as well. Switching the table type to InnoDB will give you the ability to use (limited) transactions, and also honor things like ref integrity checks. It can, in certain environments, have performance issues, though. – Michael Trausch Mar 6 '12 at 6:21

Change webhost, if you are renting a VPS there should be no reason your host would be randomly rebooting your server. Normally when a system halt is called, MySQL shuts down properly. I suspect your provider just gives a kill command rather than a proper shutdown.

  • From what I can tell it's only happening every 2 or 3 weeks, they only give a general reason 'performance issue' etc. I agree it would be ideal if we could change hosts but it's not an option at the moment. I think you're right about the kill command rather than a proper shutdown. – ServerBloke Mar 5 '12 at 17:31

Are you using the MySQL package that CentOS ships? If you are, the mysqld should be shut down via the /etc/init.d/mysqld script. What does the following command return?

sudo chkconfig --list mysqld

If it's "off" for each runlevel, try running

sudo chkconfig on mysqld
  • It is the default MySQL package that came with CentOS yes, what would the above commands do exactly? – ServerBloke Mar 5 '12 at 17:34

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.