5 ms·
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
by kator 12y ago
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". :-)
- jeffdavis 12y ago+1. 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. [1] http://www.postgresql.org/docs/9.4/static/runtime-config-wal.html http://www.postgresql.org/docs/9.4/static/runtime-config-wal... [2] http://www.postgresql.org/docs/9.4/static/sql-createtable.html#SQL-CREATETABLE-UNLOGGED http://www.postgresql.org/docs/9.4/static/sql-createtable.ht...
- falcolas 12y agoYou, 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.
- kator 12y agoThis 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.