Re: [sqlite] WAL, synchronous=1 and durability

2012-09-11 Thread Dan Kennedy
On 09/11/2012 11:03 PM, Daniel Frimerman wrote: In FULL mode, the above is sync'ed, although I don't know whether individual writes to the WAL file are sync'ed, or when all the data belonging to the commit is written the WAL is sync'ed. In NORMAL mode this is not done and that is why it is much f

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-11 Thread Daniel Frimerman
I fully agree - my tests (involving many hard resets) confirm this. If I could just expound on some of your statements for clarity, for others. This is my understanding gained from testing and reading the posts -- I did not see the code. > In both synchronous=NORMAL and synchronous=FULL the > *-w

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-11 Thread Simon Slavin
On 11 Sep 2012, at 7:21am, Keith Chew wrote: > In my ext3 file system, I have set barrier=1, disables write cache, > and also set commit=1 for per second disk sync from the kernel. In > your opinion, if I used NORMAL, would it mean: > (1) The most number of transactions I can lose in the WAL fil

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-11 Thread Dan Kennedy
On 09/11/2012 01:21 PM, Keith Chew wrote: Hi Dan In both NORMAL mode, we do the following: 1. Write a bunch of transactions into the WAL file. 2. fsync() the WAL file. 3. Copy the data from the WAL file into the database file. 4. fsync() the database file. If a power failure occur

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Keith Chew
Hi Dan > In both NORMAL mode, we do the following: > > 1. Write a bunch of transactions into the WAL file. > 2. fsync() the WAL file. > 3. Copy the data from the WAL file into the database file. > 4. fsync() the database file. > > If a power failure occurs at any time, the next process to

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Dan Kennedy
On 09/11/2012 11:12 AM, Keith Chew wrote: Hi Jay In WAL mode that's only half incorrect. Your description of NORMAL seems correct, but FULL should be fully durable. The WAL file may need to be checkpointed on startup, but the if the commit happens in WAL/FULL, you should have full

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Keith Chew
Hi Jay > > In WAL mode that's only half incorrect. Your description of NORMAL > seems correct, but FULL should be fully durable. The WAL file may > need to be checkpointed on startup, but the if the commit happens in > WAL/FULL, you should have full durability. > This is the reason for m

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Jay A. Kreibich
On Mon, Sep 10, 2012 at 09:50:58PM -0500, Jay A. Kreibich scratched on the wall: > On Tue, Sep 11, 2012 at 01:58:23AM +0100, Simon Slavin scratched on the wall: > > On 11 Sep 2012, at 12:55am, Keith Chew wrote: > > > > and I know FULL (1) will provide that. The question is why > > > NORMAL (1) c

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Jay A. Kreibich
On Tue, Sep 11, 2012 at 03:11:57PM +1200, Keith Chew scratched on the wall: > Hi Jay > > > http://www.sqlite.org/pragma.html#pragma_synchronous > > > > When synchronous is FULL (2), the SQLite database engine will use > > the xSync method of the VFS to ensure that all content is safe

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Keith Chew
Hi Jay > http://www.sqlite.org/pragma.html#pragma_synchronous > > When synchronous is FULL (2), the SQLite database engine will use > the xSync method of the VFS to ensure that all content is safely > written to the disk surface prior to continuing. This ensures > that an

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Jay A. Kreibich
On Tue, Sep 11, 2012 at 01:58:23AM +0100, Simon Slavin scratched on the wall: > On 11 Sep 2012, at 12:55am, Keith Chew wrote: > > and I know FULL (1) will provide that. The question is why > > NORMAL (1) cannot provide the same. > > Because NORMAL doesn't flush changes to disk after every singl

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Simon Slavin
On 11 Sep 2012, at 12:55am, Keith Chew wrote: > Durability means a transaction cannot be lost When is a transaction a transaction. Under the definition of ACID a transaction is a transaction as soon is your COMMIT returns with a non-error result code. > , and I know FULL (1) > will provide

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Keith Chew
Hi Simon Thank you for your reply. > > Probably worth us knowing whether you've read the 'Performance > Considerations' section of > > > Yes, I have read that, along with all the threads/posts I could find from the Internet. The thing that I cannot fully underst

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Simon Slavin
On 10 Sep 2012, at 9:00pm, Keith Chew wrote: > Using journal_mode=WAL and synchronous=2, it is my understanding (from > the docs and my own power loss testing) that sqlite will maintain > durability on a power loss. > > My question is, if sqlite can guarantee durability with synchronous=2, > wh

[sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Keith Chew
Hi Using journal_mode=WAL and synchronous=2, it is my understanding (from the docs and my own power loss testing) that sqlite will maintain durability on a power loss. My question is, if sqlite can guarantee durability with synchronous=2, why can't it (from reading the docs and other posts) do th