[EMAIL PROTECTED] wrote: > "Brian Smith" <[EMAIL PROTECTED]> wrote: > > When SQLite writes to the log file, it > > > > (1) writes all the data, > > (2) fsyncs, then > > (3) updates the page count in the header, and finally > > (4) fsyncs again. > > > > Isn't it possible to change SQLite so that the steps 3 and 4 are > > unnecessary?
> But without steps 3 and 4 and on some filesystems, a power > failure that occurs while the journal is being written can > result in database corruption. > It is true that a sufficiently strong checksum might detect > the corruption in the middle. SQLite does do some > checksumming to try to detect this sort of problem when you > specify PRAGMA synchronous=NORMAL. But checksums are not 100%. Thanks for the quick reply. I agree that checksums are not 100%. But, some checksums are close enough that the advantage of doing a single fsync can outweigh the advantage of doing two fsyncs--not just from a performance standpoint, but from a reliability standpoint, because the failure to commit the data in the event of a power failure is still data loss, especially when the SQLite database is the primary storage. The two-sync process says "In the event of a power failure (or network interruption for NFS), I am (up to 30%) less likely to save your data, but if I *do* save it, you can be nearly 100% sure that it isn't corrupted." A mechanism based on checksums is saying "In the event of a power failure or network interruption (for NFS), I am (up to X%) less likely to save your data, but if I *do* save it, you can be Y% sure that it isn't corrupted." If X is small (say 1-3%) then how large does Y need to be before the tradeoff is worthwhile? > I suspect that most modern journalling filesystems are > SAFE_APPEND, but I'll continue to assume the worst > until I know for sure. > Marco Bambini <[EMAIL PROTECTED]> wrote: > > So, is safe so to set PRAGMA synchronous=NORMAL; under MacOS X? > > I am told probably not, though Apple has never issued a > definitive statement on the question. The SAFE_APPEND attribute or synchronous=NORMAL is not really helpful for anybody developing software for other people to use on their own systems, because we cannot reliably detect filesystem characteristics at runtime, and we cannot restrict people to running only on particular filesysetms. A mechanism that eliminates steps #3 and #4 without caring about the particular filesystem would be much more useful, assuming it can reliably detect errors. But, I recognize that such a change may improve performance of small transactions, but only at the expense of very large ones. Anyway, I actually don't care so much about raw performance of individual commits as much as the lock contention; reducing the commit time reduces the time that the exclusive lock is held. Regards, Brian _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
