I think caution is still key, if you don't know what's going on Slow is Fast here. Several years ago while I was on an airplane flying to spend a nice vacation break with my family my admin partner tried to shutdown a MySQL db the "right way". He logged in and ran a mysqladmin shutdown and waited for a while. Not sure how long he waited but he claimed it was a "long time". Since it felt like there was no response to the command he assumed the database was hung and issued a kill -9 on all the mysql processes.
Sadly, what he failed to check was disk IO stats, this MySQL setup had heavy innodb table usage and settings that where deliberately set for more performance then reliability (large buffers, delayed commits etc.). What was going on was normal, MySQL was flushing everything to disk and to logs and was most likely going to stop without a problem.
He didn't look at the facts at hand, the disk IO was still going, MySQL was mostly writing to the log files, users where not being let in so the db was doing an orderly shutdown. Instead with the adrenalin pumping he felt he had “waited a long time” and issued the kill -9 and corrupted the InnoDB logs and tables beyond all recognition.
I landed at the airport to five frantic voicemails because this db was the core of a bunch of high profile sites and he was up to his ears in phone calls from the client. I had to spend the first 9 hours of my vacation with my kids playing in the background while I sweated it out on a laptop over a crappy connection that kept dropping me.
Yes I know, MySQL should have been able to handle the "power out" but this event was made worse because he started the shutdown, we had a deliberately fragile implementation, he didn't check the slaves so we didn't have a clean fall back and meanwhile he "waited a long time" but never checked the process to see what it was doing.
I use kill -9 (-KILL) all the time, but I do it where I know it's needed. Most of the time kill just works and if it doesn't that should give you pause to think carefully about what you'll do next. Slow is Fast and Fast is Slow, if you quickly do something radical like kill -9 or init 6 or 10 second power button crash then you may be spending the rest of your day cleaning up. Slow down a bit, look, listen and gather facts about the situation then make an informed decision. At least if you do all that and the rest of your day is still ruined you won't have that nagging feeling you shot yourself in the foot and you can talk intelligently to your client or boss about steps you took to avoid the situation.
My failure I suppose was that I hadn't explained to him that it was typical for the db to take upwards of 5 to 8 minutes to cleanly shutdown. Which gets to a second topic, documentation for production systems is essential, when the fire is on too many mistakes can be made because of "knowledge gaps" between team members. Needless to say after this incident I wrote extensive documentation for the team so the next time I was "on vacation" I could actually be "on vacation". :-)
Postgres is designed to be resilient to "kill -9" as well as hard power offs. Even if you use durability-sacrificing features like asynchronous commit[1] or unlogged tables[2], the risks are very well-defined and contained to recent transactions and data in that unlogged table, respectively.
But even for postgres, you have to be a bit careful. For instance, many disk drives lie about completing the writes and really have them in a volatile cache, so a hard power off can still cause corruption. You need to disable the write cache on the disks using hdparm (or similar) to be safe. And "kill -9" is quite annoying, because the child processes don't have a good way to know that the parent is exiting, so then you will be unable to start the new parent process until the children have all exited as well.
EDIT: There's still no excuse for MySQL completely corrupting the system on a kill -9. That's just a misdesign -- consider that the out-of-memory (OOM) killer on linux sends a -9.
You, and the OP, are likely mistaken about the cause of the corruption. MySQL, running InnoDB, is ACID compliant. InnoDB takes that durability seriously, and even by hand-tuning the performance factors, it's very hard to put InnoDB in a state where a simple process death, even during shutdown, will corrupt the files on disk.
If the database truly corrupted only due to improper shutdown, it was because the double write buffers were disabled on a non-atomic FS in which case you're intentionally risking DB corruption. More likely, there was bit-rot which was undetected during the normal running state of MySQL, and recovery couldn't get around it.
On the anecdote side, I've yet to have MySQL corrupt a database from a kill -9, and as I do a lot of failover development and testing, I'm issuing kill -9 to running databases frequently.
This was years ago and trust me it was corrupt. I think there may have been some issues with InnoDB in those days that made it a bit fragile in that situation.
My apologies if it sounded like I was doubting that there was corruption - I was not. I was doubting that merely sending kill -9 to the process was the sole cause of the corruption. More than likely it uncovered the corruption due to having to do recovery against corrupted data.
In the postmortem it seemed that by issuing the shutdown but not waiting for it to complete the database ended up in a strange state that we could never fully recover from.
I guess the moral here is tread lightly and take the time to be informed before you just smack something with a club.
> double write buffers were disabled on a non-atomic FS
Surely just sending SIGKILL will allow the writes already issued to complete, regardless of the FS type/options? Nobody kicked the plug out of the wall.
Double write buffers help to validate that the pages were written to disk correctly. Otherwise, there's no way to tell if a complete page was written, since writing 16k pages to disk is not an atomic operation.
InnoDB writes to the doublewrite buffer (an internal tablespace on disk), then the record in place. If the two do not match on recovery, the record is considered not written, and rebuilt from the transaction log.
If the doublewrite buffer is disabled, then InnoDB has no way of telling if the write to the page on disk was started, completed, or partially completed. This causes corruption.
You never were more than a power failure away from that disaster anyway by the looks of it. It wouldn't matter much whether or not the shutdown had started. In cases like these you're essentially playing Russian Roulette with your data, only you're using 5 bullets instead of 1.
Well in all fairness we did have replicas and sadly something was broken there.. Not pointing fingers but I checked it all before going on "vacation".. :-)
The advice about Slow is Fast in such situations is spot on.
Reminds me of a Unix incident at an automobile component manufacturer's plant. They had a multi-user Unix box supplied by the company I worked for at the time - a large Unix hardware and software vendor. A colleague and I had gone there for some system maintenance. In order to do something, my colleague, before I could stop him, gave the command "init 0" on the root console. ("init 0" shuts the system down without confirmation, for those who don't know, if run as superuser.) Within seconds the phone in the computer room was ringing madly, with calls from different intercoms on the shop floor, inventory store, etc. He had to apologize to many of them before they calmed down ...
I'm aware it may be an irritating question to answer, and I'd understand if you don't bother, but I have to ask it because I did never understand why people do such things...
This was a core cluster db for about 150 web sites that had high profiles and user traffic. It was a long time ago and the client didn't want to pay for 20 more boxes to do something more durable. These things are always a balance between durability and speed. In this case the client wanted cheap speed.
Sadly it worked very well except when people used a big hammer on it. We had replication slaves but in this incident the salves were not replicating and somehow the script checking them wasn't alerting. But even so the admin didn't check before bashing the keyboard and thus we were left with manual reconstruction from backups and other sundry sources of information.
The only reason I can think of to shutdown a production SQL database would be to do maintenance. In which case any sane admin should be taking a backup in advance. That should be the first step.
If the database is not a production server, does it matter if data was lost?
If the production database crashed/bugged out and therefore needed to be halted, was it really ready for production in the first place?
I don't mean to offend, but it sounds like your partner is a bit of a doofus.
No offense taken, the reason he went this path is the web sites involved seemed to be non-responsive.
The core issue was actually a deadlock but he wasn't trained in detecting that and in the past just restarting the db "fixed it". Because of course all the sessions dropped and dead locks were resolved.
This was very much a high visibility production environment and yes we all make mistakes sometimes.. How else can we learn.. Trust me he never did that again! :-)
Remember, all of use were a doofus at one time and even worse many of us are every day.
It matters very little to me how people screw up, it matters how they recover, learn and avoid doing it again in the future.
My favorite saying "Originality in mistakes" if you're going to screw up make it big, creative and interesting. And please, don't repeat it.
resolving deadlocks, other locks: log on via mysql, show processlist and kill the few long standing queries. It's an ugly solution and should not be used freely, yet a lot better than database restart.
Exactly, but my partner wasn't the most patient person and in all fairness it was typical to 3,000 or more connections active to the database. With that many queries outstanding and the dead locks it was always a bit hairy trying to find them.. Meanwhile every teenager on the planet was hitting their refresh button trying to figure out why the site was down.. :-)
Sadly, what he failed to check was disk IO stats, this MySQL setup had heavy innodb table usage and settings that where deliberately set for more performance then reliability (large buffers, delayed commits etc.). What was going on was normal, MySQL was flushing everything to disk and to logs and was most likely going to stop without a problem.
He didn't look at the facts at hand, the disk IO was still going, MySQL was mostly writing to the log files, users where not being let in so the db was doing an orderly shutdown. Instead with the adrenalin pumping he felt he had “waited a long time” and issued the kill -9 and corrupted the InnoDB logs and tables beyond all recognition.
I landed at the airport to five frantic voicemails because this db was the core of a bunch of high profile sites and he was up to his ears in phone calls from the client. I had to spend the first 9 hours of my vacation with my kids playing in the background while I sweated it out on a laptop over a crappy connection that kept dropping me.
Yes I know, MySQL should have been able to handle the "power out" but this event was made worse because he started the shutdown, we had a deliberately fragile implementation, he didn't check the slaves so we didn't have a clean fall back and meanwhile he "waited a long time" but never checked the process to see what it was doing.
I use kill -9 (-KILL) all the time, but I do it where I know it's needed. Most of the time kill just works and if it doesn't that should give you pause to think carefully about what you'll do next. Slow is Fast and Fast is Slow, if you quickly do something radical like kill -9 or init 6 or 10 second power button crash then you may be spending the rest of your day cleaning up. Slow down a bit, look, listen and gather facts about the situation then make an informed decision. At least if you do all that and the rest of your day is still ruined you won't have that nagging feeling you shot yourself in the foot and you can talk intelligently to your client or boss about steps you took to avoid the situation.
My failure I suppose was that I hadn't explained to him that it was typical for the db to take upwards of 5 to 8 minutes to cleanly shutdown. Which gets to a second topic, documentation for production systems is essential, when the fire is on too many mistakes can be made because of "knowledge gaps" between team members. Needless to say after this incident I wrote extensive documentation for the team so the next time I was "on vacation" I could actually be "on vacation". :-)