Re: [sqlite] WAL, durability, and synchronous=NORMAL
Hi, >>> In WAL mode with synchronous=NORMAL, when the user commits >>> a transaction, it is written into the WAL file. No sync >>> until a checkpoint happens. So if the power fails, you might >>> lose all the transactions that have been written into the WAL >>> file. >> Ahha. That explains it. Thanks for that. This makes WAL mode less >> attractive to me. > If you use synchronous=FULL then it should not be possible to > lose a transaction once it has been committed (assuming the > hard disk is playing fair). Is there any way to force a sync to disk (e.g. that I could call every few seconds), other than PRAGMA wal_checkpoint ? Regards, Twylite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, durability, and synchronous=NORMAL
On 11/25/2010 09:04 PM, Simon Slavin wrote: > > On 25 Nov 2010, at 2:00pm, Dan Kennedy wrote: > >> In WAL mode with synchronous=NORMAL, when the user commits >> a transaction, it is written into the WAL file. No sync >> until a checkpoint happens. So if the power fails, you might >> lose all the transactions that have been written into the WAL >> file. > > Ahha. That explains it. Thanks for that. This makes WAL mode less > attractive to me. If you use synchronous=FULL then it should not be possible to lose a transaction once it has been committed (assuming the hard disk is playing fair). Of course, that means a sync at the end of each transaction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, durability, and synchronous=NORMAL
On 25 Nov 2010, at 2:00pm, Dan Kennedy wrote: > In WAL mode with synchronous=NORMAL, when the user commits > a transaction, it is written into the WAL file. No sync > until a checkpoint happens. So if the power fails, you might > lose all the transactions that have been written into the WAL > file. Ahha. That explains it. Thanks for that. This makes WAL mode less attractive to me. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, durability, and synchronous=NORMAL
On 11/25/2010 08:49 PM, Simon Slavin wrote: > > On 25 Nov 2010, at 8:36am, Dan Kennedy wrote: > >> On 11/25/2010 03:24 PM, Twylite wrote: >>> > > Argh. Trevor, I'm going to find Stephenie Meyer and do something she doesn't > like. > >>> 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. >> >> Possible. Possible. Possible. Not supposed to be possible. > > Is (2) really possible ? I thought that, unless the power failure caused > corruption at the hard disk level, a maximum of one transaction could be lost. > (2) and (3) are really the same question. In WAL mode with synchronous=NORMAL, when the user commits a transaction, it is written into the WAL file. No sync until a checkpoint happens. So if the power fails, you might lose all the transactions that have been written into the WAL file. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, durability, and synchronous=NORMAL
On 25 Nov 2010, at 8:36am, Dan Kennedy wrote: > On 11/25/2010 03:24 PM, Twylite wrote: >> Argh. Trevor, I'm going to find Stephenie Meyer and do something she doesn't like. >> 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. > > Possible. Possible. Possible. Not supposed to be possible. Is (2) really possible ? I thought that, unless the power failure caused corruption at the hard disk level, a maximum of one transaction could be lost. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, durability, and synchronous=NORMAL
On 11/25/2010 03:24 PM, Twylite wrote: > 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. Possible. Possible. Possible. Not supposed to be possible. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL, durability, and synchronous=NORMAL
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
