0

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.

3
  • Why are they restarting your VPS for?
    – qweet
    Mar 5, 2012 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.
    – phemmer
    Mar 5, 2012 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. Mar 5, 2012 at 17:28

3 Answers 3

1

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.

2
  • 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. Mar 5, 2012 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. Mar 6, 2012 at 6:21
4

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.

1
  • 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. Mar 5, 2012 at 17:31
-1

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
1
  • It is the default MySQL package that came with CentOS yes, what would the above commands do exactly? Mar 5, 2012 at 17:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .