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

[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 faster (at least
15 times faster in my basic tests).

The steps below are part of a checkpoint that apply to NORMAL and FULL.
This is when the WAL file reached it's page limit, or called manually by
the user.  This step might be redundant for FULL mode, but that's not
important.

  2. fsync() the WAL file.
  3. Copy the data from the WAL file into the database file.
  4. fsync() the database file.



Synchronous=FULL mode is different. During step 1 above in FULL
mode, SQLite calls fsync() on the WAL file after writing each
transaction to it - before the users COMMIT command returns.

Since someone else kindly pointed out that COMMIT can require several
writes -- is each and every write required for a COMMIT synchronised to
prevent corruption? Or does SQLite utilise the same idea as with NORMAL
mode using checksums to prevent corruption? Perhaps that is why WAL+FULL is
faster than non-WAL+FULL? The more sequential writes in WAL mode (as
explained in the manual) must help here.  If something like checksums is
used to prevent corruption, maybe that also can be applied to non-WAL
journal modes, as an option to speed up their NORMAL mode?


One fsync() per transaction written to the WAL file.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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
> *-wal file may contain more than one transaction.
Because data is only written to the database file at "checkpoints" which
can be done manually or automatically by SQLite (current default set to
1000 pages).
So the -wal file will always contain up to 1000 pages (the default) before
getting flushed.
The difference between these 2 is that with NORMAL the writes are not
guaranteed, whereas with FULL the writes are guaranteed.

> The WAL file includes a running checksum so that if a power failure
> occurs, the next client to read the database can determine the prefix
> of the WAL file that can be considered trustworthy (as the power
> failure may have corrupted more recently written parts of the file).
To clarify if it is not obvious: this recover happens for OFF, NORMAL or
FULL when the DB is openned.

> In both NORMAL mode, we do the following:
>  1. Write a bunch of transactions into the WAL file.
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 faster (at least
15 times faster in my basic tests).

The steps below are part of a checkpoint that apply to NORMAL and FULL.
This is when the WAL file reached it's page limit, or called manually by
the user.  This step might be redundant for FULL mode, but that's not
important.
>  2. fsync() the WAL file.
>  3. Copy the data from the WAL file into the database file.
>  4. fsync() the database file.

> Synchronous=FULL mode is different. During step 1 above in FULL
> mode, SQLite calls fsync() on the WAL file after writing each
> transaction to it - before the users COMMIT command returns.
Since someone else kindly pointed out that COMMIT can require several
writes -- is each and every write required for a COMMIT synchronised to
prevent corruption? Or does SQLite utilise the same idea as with NORMAL
mode using checksums to prevent corruption? Perhaps that is why WAL+FULL is
faster than non-WAL+FULL? The more sequential writes in WAL mode (as
explained in the manual) must help here.  If something like checksums is
used to prevent corruption, maybe that also can be applied to non-WAL
journal modes, as an option to speed up their NORMAL mode?


@Simon Slavin
> Though if your OS and hardware does perform as documented then
> neither mode will actually lose data or cause database corruption.
This doesn't make sense; OS's do I/O caching, data corruption is always a
possibility then.
> FULL) Every COMMIT will take more time, but a power-failure can lose a
> maximum of one transaction.
Someone already corrected this; power-failure will result in no loss of
transaction, not even one.
I think I know why you said that.  In non-WAL and NORMAL, there are
intermediate writes related to 1 commit,
Also, as for network setups, docs on the WAL clearly state that the DB
should not be used over a network.
Other than that, I enjoyed the information about hard drives (not)
supporting ACID.  Good to know.

@Keith Chew (original poster)
> I am using EXT3 with barrier=1 and write-cache disabled from the HDD...
I believe there will be no difference in NORMAL or FULL modes, when
measuring performance, durability or consistency (data corruption).
SQLite will be probably be making pointless calls to sync the I/O buffers.
The steps above (1-4) show the difference between NORMAL and FULL: in FULL
every write to the WAL file is sync'ed, the rest is the same.  So in your
case of hardware cache being turned off, every write to the WAL file will
be physical anyway.
Well this excessive clearing up is giving me a callus :-)



In my WAL tests with NORMAL mode and caching enabled, the longer you waited
(we are talking up to ten seconds), the less data was lost after a hard
reboot, so by no means all data is lost, some will make it to the file, and
thanks to the checksums (as pointed out by Dan) the data will be recovered
to the best of SQLite's ability.

Well these are my 10 pence.

On Tue, Sep 11, 2012 at 5:52 AM, Dan Kennedy  wrote:

> 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 durability.
>>>
>>>
>> This is the reason for my question. As far as I can see, the
>> difference between NORMAL and FULL in WAL mode is that FULL has one
>> transaction in WAL to be checkpointed, and NORMAL has multiple. Since
>> both of them 

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

[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 file is 1
> second's worth?
> (2) If the WAL contained say 10 seconds worth of transactions (1
> transaction per second) before the power failure, and the 11th second
> the transaction failed to make it, ie stuck between your step 1 and 2,
> will the first 10 seconds worth of transactions still make it? I
> presume yes, since you have checksums in the WAL file?
> (3) If there is a power failure anywhere between steps 2, 3, and 4, I
> presume that will be OK, since the recovery will be done on next
> startup.
> 
> If the answer is true for the above 3 questions, then I think WAL with
> NORMAL is an extremely attractive option to use.

I'm posting this only because your questions suggest you need to know the 
actual way your system will perform in real life.  Dan has answered this in 
terms of how SQLite3 works, but I wanted to stress that SQLite3 isn't the only 
link in the chain.

SQLite3 working correctly depends on the SQLite3 calls writing directly to the 
disk, with the disk physically part of the computer that's executing the 
SQLite3 calls.  If you're mounting the disk using NFS or any other network FS, 
the answers are different.  Very few network setups correctly support fsync().

Also, your hard disk must support ACID, and must be in ACID support mode 
(probably jumper settings) which it probably isn't because that's almost never 
done except for hard disks used for servers.  It's more likely to be set up to 
do out-of-order saves, and say "That's saved." first but actually save things 
after the call has returned.  A good test is running an interactive program 
like Word or GIMP on that computer, or a 3D game.  If those apps run acceptably 
fast, then your hard disk isn't doing ACID.  This is the correct way to set up 
a non-server computer if you want it to feel nice and fast in use.

I have, as a demonstration, bought components and set up a computer in ACID 
mode, but then loaded mundane Windows XT and Office on it.  It was unusable, 
with Office pausing every 50 or so keystrokes for over a second so it could 
write temporary savefiles.  Even messing about with Windows Explorer, opening 
folders and moving and copying files was annoyingly slow.  Booted instead into 
a version of RedHat (I forget which version) every time Linux tried to add a 
line to a log file (which Unix does constantly) the GUI would freeze for a 
quarter second.  Not what a geek would want from his or her home computer.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 occurs at any time, the next process to read the
database scans the WAL file and attempts to read as many transactions
as possible. If the checksum fails at any point, it stops reading.

So you can lose data. Say a power failure occurs between steps 1
and 2 above. If your WAL had 5 unsynced transactions in it then
following recovery you may find that none, some or all of them have
survived, depending on how much of the WAL file actually made it
to disk before the power failed.



Thank you very much for the explanation, it is very clear! I have one
last question.

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 file is 1
second's worth?
(2) If the WAL contained say 10 seconds worth of transactions (1
transaction per second) before the power failure, and the 11th second
the transaction failed to make it, ie stuck between your step 1 and 2,
will the first 10 seconds worth of transactions still make it? I
presume yes, since you have checksums in the WAL file?


That sounds right to me. The first 10 seconds worth of transactions
and their checksums will have made it to disk so SQLite will be able
to recover them the next time the db is opened.


(3) If there is a power failure anywhere between steps 2, 3, and 4, I
presume that will be OK, since the recovery will be done on next
startup.


Right. Once step 2 has taken place your transactions have been synced
to disk. So even if a power failure occurs before they are copied into
the db file, they can't get lost.

Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 read the
> database scans the WAL file and attempts to read as many transactions
> as possible. If the checksum fails at any point, it stops reading.
>
> So you can lose data. Say a power failure occurs between steps 1
> and 2 above. If your WAL had 5 unsynced transactions in it then
> following recovery you may find that none, some or all of them have
> survived, depending on how much of the WAL file actually made it
> to disk before the power failed.
>

Thank you very much for the explanation, it is very clear! I have one
last question.

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 file is 1
second's worth?
(2) If the WAL contained say 10 seconds worth of transactions (1
transaction per second) before the power failure, and the 11th second
the transaction failed to make it, ie stuck between your step 1 and 2,
will the first 10 seconds worth of transactions still make it? I
presume yes, since you have checksums in the WAL file?
(3) If there is a power failure anywhere between steps 2, 3, and 4, I
presume that will be OK, since the recovery will be done on next
startup.

If the answer is true for the above 3 questions, then I think WAL with
NORMAL is an extremely attractive option to use.

Regards
Keith
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 durability.



This is the reason for my question. As far as I can see, the
difference between NORMAL and FULL in WAL mode is that FULL has one
transaction in WAL to be checkpointed, and NORMAL has multiple. Since
both of them need to checkpoint for durability, how is it that FULL
can guarantee durability for 1 transaction, but not NORMAL for
multiple? From a development point of view, I would imagine that both
will use the same checkpoint routine, but yet I cannot understand why
FULL has a guarantee and NORMAL does not..


In WAL mode, when a transaction is written to disk, the modified
pages are appended to the *-wal file. Later on, during a checkpoint, all 
the modified pages in the *-wal file are copied back into the

database file. In both synchronous=NORMAL and synchronous=FULL the
*-wal file may contain more than one transaction.

The WAL file includes a running checksum so that if a power failure
occurs, the next client to read the database can determine the prefix
of the WAL file that can be considered trustworthy (as the power
failure may have corrupted more recently written parts of the file).

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 read the
database scans the WAL file and attempts to read as many transactions
as possible. If the checksum fails at any point, it stops reading.

So you can lose data. Say a power failure occurs between steps 1
and 2 above. If your WAL had 5 unsynced transactions in it then
following recovery you may find that none, some or all of them have
survived, depending on how much of the WAL file actually made it
to disk before the power failed.

Synchronous=FULL mode is different. During step 1 above in FULL
mode, SQLite calls fsync() on the WAL file after writing each
transaction to it - before the users COMMIT command returns.

In this case if the same power failure occurs there is no chance
that any successfully committed transactions will be lost, as they
are guaranteed to have made it to disk.

So, in WAL mode, both sync=NORMAL and sync=FULL prevent database
corruption. However if a power failure occurs in NORMAL mode, some
recently committed transactions may have disappeared following
recovery.

Dan.








___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 my question. As far as I can see, the
difference between NORMAL and FULL in WAL mode is that FULL has one
transaction in WAL to be checkpointed, and NORMAL has multiple. Since
both of them need to checkpoint for durability, how is it that FULL
can guarantee durability for 1 transaction, but not NORMAL for
multiple? From a development point of view, I would imagine that both
will use the same checkpoint routine, but yet I cannot understand why
FULL has a guarantee and NORMAL does not..

Regards
Keith
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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) cannot provide the same.
> > 
> > Because NORMAL doesn't flush changes to disk after every single
> > transaction.  It queues a few of them up and flushes them all in one go. 
> 
>   That's not quite true.

  Oh wait, you're talking about WAL mode, aren't you?  Sorry,
  apparently I wasn't following the whole thread as closely as I should
  have been.

  In that case, yes... the WAL file will "save up" several transactions
  and only fully sync them when the checkpoint happens to clear the WAL
  file.

> > NORMAL) Faster but if you lose power more transactions are lost, but
> > the database is still not corrupt.
> >   FULL) Every COMMIT will take more time, but a power-failure can
> > lose a maximum of one transaction.
> 
>   That's also incorrect.

  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.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 safely
> >   written to the disk surface prior to continuing. This ensures
> >   that an operating system crash or power failure will not corrupt
> >   the database. FULL synchronous is very safe, but it is also
> >   slower. When synchronous is NORMAL (1), the SQLite database
> >   engine will still sync at the most critical moments, but less
> >   often than in FULL mode. There is a very small (though non-zero)
> >   chance that a power failure at just the wrong time could corrupt
> >   the database in NORMAL mode. But in practice, you are more likely
> >   to suffer a catastrophic disk failure or some other unrecoverable
> >   hardware fault.
> >
> 
> >From what I have read so far, my understanding is consistent with your
> explanation (except that I didn't realise corruption can happen in
> NORMAL, only lost of data).

  In the case of both NORMAL and FULL (in the case of non-WAL mode), if
  the commit is successful, there should never be data loss and the
  changes described by the transaction should become part of the
  database state.  If the commit did not finish, the changes described
  in the transaction were never part of the database state to start
  with and are not considered "lost".  In no case should NORMAL or
  FULL allow a commit to succeed, but not have the transaction be durable.

  ...except if the disks lie.  Which, as Simon has pointed out, is most
  of the time.

> Regardless, I would really like to hear
> from a developer that the above paragraph also applies to the WAL
> journal mode, and not just the older journal modes, since WAL was
> introduced later in 3.7 onwards.

  It does not.  The transaction model in WAL mode is totally different,
  so the journal modes are as well.

> Because of the architecture change in WAL, I was hoping that the
> durability can still be preserved while using NORMAL.

  WAL plays by a slightly different set of rules.  The docs describe
  this fairly well, but from the sound of it you need FULL for
  durability.  On the other hand, WAL requires fewer write to commit a
  transaction, so (if I'm reading this correctly) FULL in WAL mode is
  much faster than FULL in non-WAL mode.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 operating system crash or power failure will not corrupt
>   the database. FULL synchronous is very safe, but it is also
>   slower. When synchronous is NORMAL (1), the SQLite database
>   engine will still sync at the most critical moments, but less
>   often than in FULL mode. There is a very small (though non-zero)
>   chance that a power failure at just the wrong time could corrupt
>   the database in NORMAL mode. But in practice, you are more likely
>   to suffer a catastrophic disk failure or some other unrecoverable
>   hardware fault.
>

>From what I have read so far, my understanding is consistent with your
explanation (except that I didn't realise corruption can happen in
NORMAL, only lost of data). Regardless, I would really like to hear
from a developer that the above paragraph also applies to the WAL
journal mode, and not just the older journal modes, since WAL was
introduced later in 3.7 onwards. Because of the architecture change in
WAL, I was hoping that the durability can still be preserved while
using NORMAL.

Regards
Keith
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 single
> transaction.  It queues a few of them up and flushes them all in one go. 

  That's not quite true.

  Committing a transaction takes more than one disk write.  As I
  understand it, in FULL mode, the disk is sync'ed after each and
  every write.  In NORMAL mode, all of the writes required to commit a
  transaction are made in quick succession, but the disk is only
  sync'ed after the last write.  In most practical situations, my guess
  is that the sync takes longer than the writes (since the writes are
  mostly to the OS file-system buffers anyways).

  That means, for a very, very short time during the final commit
  process (microseconds, most likely), there are pending buffered
  writes.  If the system were to lose power between one of these
  writes and the final sync, there is a very-small-but-non-zero
  chance the database could become corrupt.

  But the disk is still fully sync'ed, to the best of SQLite's ability,
  after each and every commit.  In NORMAL mode, commits are still fully
  durable.

> NORMAL) Faster but if you lose power more transactions are lost, but
> the database is still not corrupt.
>   FULL) Every COMMIT will take more time, but a power-failure can
> lose a maximum of one transaction.

  That's also incorrect.  In both cases, if a transaction fully
  commits, you're golden.  Transactions are fully durable.  If COMMIT
  returned success, you should not lose the transaction.

  FULL mode, to the best of the SQLite developers' ability, protects
  against all corruption, but at a very high performance cost.  NORMAL
  mode opens up an extremely small chance of corruption for a
  significant performance increase.

  The PRAGMA docs explain much of this:

  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 operating system crash or power failure will not corrupt
  the database. FULL synchronous is very safe, but it is also
  slower. When synchronous is NORMAL (1), the SQLite database
  engine will still sync at the most critical moments, but less
  often than in FULL mode. There is a very small (though non-zero)
  chance that a power failure at just the wrong time could corrupt
  the database in NORMAL mode. But in practice, you are more likely
  to suffer a catastrophic disk failure or some other unrecoverable
  hardware fault.


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 that. The question is why NORMAL (1) cannot provide the
> same.

Because NORMAL doesn't flush changes to disk after every single transaction.  
It queues a few of them up and flushes them all in one go.  This makes things 
far faster because it's the flushing to disk which takes a lot of time.  So 
your payoff equation is this:

NORMAL) Faster but if you lose power more transactions are lost, but the 
database is still not corrupt.
  FULL) Every COMMIT will take more time, but a power-failure can lose a 
maximum of one transaction.

Just to remind you, all this depends on your hardware supporting ACID.  Which 
almost no hard disks do.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 understand is how can
FULL (2) guarantee durability, but NORMAL (1) cannot. Before making an
attempt to look into the code, I just wanted to see if anyone can help
provide some explanation.

> before anyone gives an in-depth answer.  The basic difference between the two 
> settings is that FULL (2) synchronises far more frequently than NORMAL (1), 
> meaning that in the even of power loss, fewer transactions will be lost.  
> Though if your OS and hardware does perform as documented then neither mode 
> will actually lose data or cause database corruption.

Durability means a transaction cannot be lost, and I know FULL (1)
will provide that. The question is why NORMAL (1) cannot provide the
same.

I am using EXT3 with barrier=1 and write-cache disabled from the HDD,
as far as I know this is as good as it gets for making the system
reliable. So, in this case, will the NORMAL (1) actually cause data
loss on power loss? I already know that corruption will not happen, I
am just interested in the "losing transactions" or "sacrifice
durability" as suggested in various threads/posts I have read.

Regards
Keith

>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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,
> why can't it (from reading the docs and other posts) do that with
> synchronous=1?

Probably worth us knowing whether you've read the 'Performance Considerations' 
section of



before anyone gives an in-depth answer.  The basic difference between the two 
settings is that FULL (2) synchronises far more frequently than NORMAL (1), 
meaning that in the even of power loss, fewer transactions will be lost.  
Though if your OS and hardware does perform as documented then neither mode 
will actually lose data or cause database corruption.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] WAL, synchronous=1 and durability

[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 that with
synchronous=1?

They both perform the checkpoint routine, except synchronous=1 does
the checkpoints with multiple transactions in the WAL file.

Regards
Keith
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users