4 ms·

> making MySQL live outside its means (i.e. overcommitting memory) opens the door to dangerous crashes and potential data corruption, so this is not recommended

> making MySQL live outside its means (i.e. overcommitting memory) opens the door to dangerous crashes and potential data corruption, so this is not recommended. data corruption? how? I'm no mySQL fan but is this FUD or referring to a real issue?

chomp 4y ago
If you OOM, it's gonna crash.
- Volundr 4y ago
  Sure but data corruption? Data integrity on powerloss or other crash is a core feature of pretty much any RDMS, and OOM is actually probably one of the safer ways a database can crash (any disk caches still get flushed so no unpredictability around what is actually persistented)
  - lizztheblizz 4y ago
    Article author here. I fully endorse Aaron's correction and appreciate the call-out. For context: I initially wrote this paragraph to include more flavor and history around crash recovery challenges with relational databases, implying that while your data might be safe, it is still 100% preferable, even today, to avoid crashes by accurately sizing AND limiting the database to live within its means. Crash recovery can still take a certain amount of time, and when people are weighing whether to bring their app up faster or maintain their data integrity, taking a shortcut in a high pressure situation is sadly not unheard of. Alas, in my editing, I opted to spend less time in the weeds there, and without the proper context, the use of the term "data corruption" lost all meaning, and no longer belonged in that sentence. Totally fair correction.
samlambert 4y ago
Every server crash risks data loss. That said MySQL is exceptionally good at avoiding corruption and data loss.
- paulryanrogers 4y ago
  I wouldn't say exceptionally. InnoDB is reasonably robust on a modern FS with sane mount options. Yet I'll never forget the MySQL 8 table rename bug that crashes the server and corrupts/truncates the table. Appeared in a GA release and took several patch releases before it was fixed.
aarondf 4y ago
This is a good call out. Data corruption is always possible due to software, firmware, hardware bugs and failures... but that’s not specific to OOMs. You could have non-crash safe settings like sync_binlog != 1 or innodb_flush_log_at_trx_commit != 1, but with the default settings of MySQL 8.0 it’s entirely crash safe in every way (binlog events etc). I think that part of the post needs to be updated. It was a bit of an off-handed comment but it's not as clear and accurate as it should be. I’m going to replace `potential data corruption` with `potential downtime` as it could initiate a failover and the process will have to restart and go through crash recovery which can take some time.

chomp 4y ago

If you OOM, it's gonna crash.

Volundr 4y ago
Sure but data corruption? Data integrity on powerloss or other crash is a core feature of pretty much any RDMS, and OOM is actually probably one of the safer ways a database can crash (any disk caches still get flushed so no unpredictability around what is actually persistented)
- lizztheblizz 4y ago
  Article author here. I fully endorse Aaron's correction and appreciate the call-out. For context: I initially wrote this paragraph to include more flavor and history around crash recovery challenges with relational databases, implying that while your data might be safe, it is still 100% preferable, even today, to avoid crashes by accurately sizing AND limiting the database to live within its means. Crash recovery can still take a certain amount of time, and when people are weighing whether to bring their app up faster or maintain their data integrity, taking a shortcut in a high pressure situation is sadly not unheard of. Alas, in my editing, I opted to spend less time in the weeds there, and without the proper context, the use of the term "data corruption" lost all meaning, and no longer belonged in that sentence. Totally fair correction.

samlambert 4y ago

Every server crash risks data loss. That said MySQL is exceptionally good at avoiding corruption and data loss.

paulryanrogers 4y ago
I wouldn't say exceptionally. InnoDB is reasonably robust on a modern FS with sane mount options. Yet I'll never forget the MySQL 8 table rename bug that crashes the server and corrupts/truncates the table. Appeared in a GA release and took several patch releases before it was fixed.

aarondf 4y ago

This is a good call out. Data corruption is always possible due to software, firmware, hardware bugs and failures... but that’s not specific to OOMs. You could have non-crash safe settings like sync_binlog != 1 or innodb_flush_log_at_trx_commit != 1, but with the default settings of MySQL 8.0 it’s entirely crash safe in every way (binlog events etc). I think that part of the post needs to be updated. It was a bit of an off-handed comment but it's not as clear and accurate as it should be. I’m going to replace `potential data corruption` with `potential downtime` as it could initiate a failover and the process will have to restart and go through crash recovery which can take some time.