Note: "permalinks" may not be as permanent as we would like,
direct links of old sources may well be a few messages off.
Hi, I don't know whether I dare add to this, but I do understand MySql and the way it writes data. If it helps the argument.... Using the InnoDB engine, it does write ahead, but only into a log file. The actual writing of the index's and row data follows later. (The data is held in memory, so no urgency to write to disk.) After a crash similar to a machine having power removed, a crash recovery takes place to write the missing data from the logs. This may be a slow process. I know from painful experience that this is not 100% guaranteed, you have the real risk of corrupting a table. If you use the MyIsam table, things are worse. This has no crash recovery and you stand more of a chance of corrupting data, eg, a row which has been written, but it's index which has not. Crash recovery is planned in version 5.2 I belive. However, you can lock MySql for writing, request it flushes all data, and then take a snapshot. Which is better than a full stop of the system, if handled well users will hardly notice... Ben H.D. wrote: > drbd at bobich.net wrote: >> Syncing the FS is sufficient for data safety on the level of >> journalling that keeps the FS consistent after a power failure. It is >> NOT sufficient for data consistency I would expect from a proper backup. > > The consistency of meta data is the job the file system itself, right. > The consistency of the actual data is in the _sole_ responsibility of > the application writing the data. If it cares about its data it has to > call fsync or not use buffered I/O at all. Every DBMS calls fsync on its > own write ahead log. Flushing data (not meta data) is not the job of the > file system at all, it is done either when pdflush feels like it, or the > application requests it by calling fsync. Every serious DBMS uses a WAL > and calls fsync when a transaction commits. So you are right that for > data consistency a file system snapshot if _not_ sufficient, but that > doesn't matter as the DBMS is able to reconstruct this exact state from > its own write ahead log. So DBMS are fine with file system snapshots. > You might have other (broken) applications that don't call fsync, those > sure might get in trouble. But they get in trouble after a power failure > as well, so better not use them at all -- they are broken. > >> Many file systems have data AND metadata journalling available, >> including ext3 (even if ext3 by default only journals metadata). > > Exactly, and in case of a DBMS it is absolutely useless to run with DATA > journaling, it doesn't help the DBMS in any way, it just costs > performance. So either use XFS -- which is a pure write back file system > by design or mount ext3 in writeback mode -- if it's a DBMS is what you > run on top of it. > >> is a much safer and more sensible option than snapshotting. > > It is in not safer in any way, and if you ever happend to use ZFS, it's > the most performant backup solution, as ZFS is a native COW-FS. On > Linux, with LVM, snapshots are still a big performance problem, agreed. > >> Even if snapshotting provided consistent images (which it doesn't) > > That is only a problem with broken applications, that do not care about > the data they have written hitting the actual disc. Sure a snapshot does > not cover the in memory image of the FS, with the exception of XFS, it > flushes the data as well, so it is consistent. > >> >> No, it never occured to me to run a DBMS on CopyFS. As you quite >> rightly point out, there is no point in doing it. As for DB backups, >> backup up a replicated server is a much safer and more sensible option >> than snapshotting. Even if snapshotting provided consistent images >> (which it doesn't), it still impairs the performance of a live server >> (it effectively does the same thing CopyFS does). Having a separate >> replicated DB server gives you both the guaranteed consistency and >> doesn't have a serious performance hit on the master server. > > Having a separate replicated DB server gives you both the guaranteed > consistency and doesn't have a serious performance hit on the master > server. > > You have the guaranteed consistency with FS snaps as well -- for a DBMS. > Replication overhead depends on the specific setup. Sending snapshots to > remote hosts on Solaris ZFS for example is way less overhead than any > sort of application based replication. > >> Hmm... I think we've drifted a tad far off the DRBD topic... > > I fear so ;) > > ************************************************************************* This e-mail is confidential and may be legally privileged. It is intended solely for the use of the individual(s) to whom it is addressed. Any content in this message is not necessarily a view or statement from Road Tech Computer Systems Limited but is that of the individual sender. If you are not the intended recipient, be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. We use reasonable endeavours to virus scan all e-mails leaving the company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our networks is reserved by us Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley, Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17 Registered in England No: 02017435, Registered Address: Charter Court, Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE. *************************************************************************