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: [email protected] - [email protected]
GPG public key ID: FF5F32A8
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
