On 20/08/2018 15:10, Reinis Rozitis wrote:
Hi all, anyone with some suggestion/insight on the matter?
While I can't comment on the intricacies or internals of MySQL being (un)able to recover after a crash without the doublewrite buffer, if you skim through the changelog between versions (be that upstream Oracle or downstream in Maria/Percona), nearly every second (even minor) version has some sort of dataloss/corruption/segfault type of bug. Just for example a recent comes into mind https://jira.mariadb.org/browse/MDEV-15764
D'oh! [1]
From my experience I've been switching off doublewrite on MySQL (even on XFS and now on ZFS (because of compression)) for years and even in the few accidental powerloss/total crash cases I haven't seen a corruption caused by an unexpected reboot (possible write lost midflight). Most times mysql hasn't been unable to start just because of internal issues (which you solve by having slaves and backups).
Uhm, powerloss and segfault/segkill (ie: process crash) are quite different. The first means *any* activity is stopped (ie: filesystem has no means to write anything), while the latter means *mysqld* stops writing but the filesystem can write the partial data received.
My point being - zfs in principle is the same as the "atomic write hardware" (eg either the block writes succeed fully or not at all) so if you turn off doublewrite on those fancy Fusionio cards, I don't see a reason why you can't do the same on zfs.
What I means is that while a ZFS write is an all-or-nothing affair, it can write partial data from the application (mysqld) point of view. What it needs is a partial data from the application itself (ie: a crashing mysqld) - garbage in, garbage out. Doublewrite *shoult* catch that (ie: at restarting, mysqld would read the double buffer, detect it as corrupt and discard it while not touching at all any previous data on main database files). My understanding (which *can* be wrong) is that MariaDB "atomic write support" is a mean to inform the underlying device of the entire write process and to keep new data on "spare" location until the application itself (mysqld) commits the *entire*, verified write, enabling the hardware device to atomically swap/remap the affected data locations. In this case, a failed mysqld process will never reach the "commit phase", leaving the old data untouched.
Even if there are some edge cases where it could become "unsafe" most of the time you still run with better performance and considering the SSD wear level the hardware could fail (reach end of life) ~two times sooner ;)
Good point, this surely is a factor to evaluate.
p.s. sorry for the mail not being about the particular technical aspects rather than general thoughts
They are greatly appreciated! Thanks. [1] https://en.wikipedia.org/wiki/D%27oh! -- Danti Gionatan Supporto Tecnico Assyoma S.r.l. - www.assyoma.it email: g.danti@assyoma.it - info@assyoma.it GPG public key ID: FF5F32A8