Hi, I am seeking technical information on the durability of transactions with journal_mode=WAL and synchronous=NORMAL.
Specifically, in the event of a power failure, can the following ever happen: (1) Loss of the last transaction completed. (2) Loss of some indeterminate number of recent transactions. (3) Loss of the entire WAL file. (4) Corrupt of the database making it unopenable. My use case is an embedded x86 running off a CFIDE (NAND flash). There is no shut down (just hard power-off), and a corrupt database will be a tragedy (best case: we wipe and reinit the database losing all data; worst case: device cannot boot and is rendered inoperable), but I can tolerate losing some recent transactions. Performance tests with synchronous=FULL yield 41 TPS which is slightly too slow for our purposes. I have scoured the SQLite docs (include http://www.sqlite.org/wal.html), the Wiki and the mailing list archives [1], and I can't find a definitive answer. It seems clear that (1) and (2) can happen, and likely that (3) can happen, but no clarity on (4). The following quotes would seem to indicate that the database should never become corrupted in WAL mode? Can someone confirm this? * DRH: "If you set synchronous=NORMAL, then the checkpoint thread/process is the only thread/process that ever calls fsync() so you can do queries and updates in a GUI thread with much less risk of freezing the system due to a slow disk. You pay for this by giving up durability following a power-loss/hard-reset. Please note that SQLite databases with WAL will continue to feature fully automatic recovery from power-loss or hard-reset; applications do not have to do anything special to recover from a crash." * http://www.sqlite.org/wal.html: "Further, syncing the content to the disk is not required, as long as the application is willing to sacrifice durability following a power loss or hard reboot. (Writers sync the WAL on every transaction commit if PRAGMA synchronous is set to FULL but omit this sync if PRAGMA synchronous is set to NORMAL.)" [1] Related mailing list threads * sqlite - WAL in SQLite, May 2010, http://www.pubbs.net/201005/sqlite/55971-sqlite-wal-in-sqlite.html * [sqlite] WAL - Performance/fsync, Jul 2010 http://www.mail-archive.com/sqlite-users@sqlite.org/msg53555.html * [sqlite] performance, transactions and wal checkpoints, Aug 2010, http://www.mail-archive.com/sqlite-users@sqlite.org/msg54512.html * [sqlite] Sqlite on NAND flash devices..., Aug 2010, http://www.mail-archive.com/sqlite-users@sqlite.org/msg54896.html PART 2 (A) My understanding is that the WAL is a ring-buffer that is a delta on the main database file. In the context of NAND flash and wear leveling, are there any portions of the database file or WAL that get written more regularly and may impact on the longevity of the flash device? (e.g. with journal_mode=DELETE there are parts of the database file header that seem to be modified on each transaction). (B) Will regular checkpointing cause uneven wear of the flash device? Specifically, does a checkpoint affect the "tail" of the WAL (causing it e.g. to reset to the start of the WAL file)? Conceptually, if I checkpointed after every transaction, would the disk write behaviour be similar to journal_mode=PERSIST? (C) If I am correct in assuming that WAL + synchronous=NORMAL can lose the entire WAL file but not corrupt the database, AND I don't want to checkpoint too regularly to prevent uneven wear of the flash device, what would be the best way to force an occasional fsync() of the WAL so that I limit losses to the last few transactions? (Implied sub-question: will an fsync() make the transactions durable, or can the entire WAL still be lost/corrupt even if an fsync() occurs?). Could I turn on synchronous=FULL to cause an immediate fsync()? Turn on synchronous=FULL for one transaction? Thanks in advance, Twylite _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
