4 ms·

+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] o

+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 ago
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.
- kator 12y ago
  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.
  - falcolas 12y ago
    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.
    - kator 12y ago
      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.
      - falcolas 12y ago
        ++ to the moral of the story.
- yuubi 12y ago
  > 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.
  - falcolas 12y ago
    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.

falcolas 12y ago

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.

kator 12y ago
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.
- falcolas 12y ago
  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.
  - kator 12y ago
    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.
    - falcolas 12y ago
      ++ to the moral of the story.
yuubi 12y ago
> 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.
- falcolas 12y ago
  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.