Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Angus March]

Shane Harrelson wrote:
> To the original question though, with PRAGMA synchronous=OFF, SQLite will
> NOT do explicit fsync()'s.  A exception to this occurs with attached DB's
> and a transaction; when the transaction is committed and the master journal
> is deleted, SQLite fsyncs the directory that contained the journal to ensure
> the delete is "flushed".   The only way around this fsync() is to compile
> with SQLITE_DISABLE_DIRSYNC.  This might be changed in the future.
>   

Right. If the only time fsync would be called is... whatever that
jargon is up there, then there should be a problem, since I don't think
I'm using that. Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Shane Harrelson]

On Mon, Aug 17, 2009 at 11:53 AM, D. Richard Hipp  wrote:

>
> On Aug 17, 2009, at 11:41 AM, Matt Sergeant wrote:
> >
> > Kernels will fflush when a file handle is closed
>
> Not according to Ted Ts'o (creator of the Ext2/3/4 filesystems).  See,
> for example, the extensive discussions of this at
>
>
> http://thunk.org/tytso/blog/2009/03/12/delayed-allocation-and-the-zero-length-file-problem/
> http://thunk.org/tytso/blog/2009/03/15/dont-fear-the-fsync/
>
> Ted says that it is widely believed among programmers that close()
> will sync a file, but in fact nothing in POSIX requires this and in
> fact Linux does not do it.  Some hacks were added to ext4 in the
> 2.6.30 kernel release to mitigate the damage following a power loss
> when programs fail to fsync() prior to close().  But everybody agrees
> those changes are an ugly hack.
>
> In POSIX, the bottom line is this:  The *only* way to force data to
> oxide is to call sync() or fsync().  Some kernels and/or some
> filesystems might sync at other times, but it is not something that
> you can rely on.
>
> D. Richard Hipp
> d...@hwaci.com
>


To the original question though, with PRAGMA synchronous=OFF, SQLite will
NOT do explicit fsync()'s.  A exception to this occurs with attached DB's
and a transaction; when the transaction is committed and the master journal
is deleted, SQLite fsyncs the directory that contained the journal to ensure
the delete is "flushed".   The only way around this fsync() is to compile
with SQLITE_DISABLE_DIRSYNC.  This might be changed in the future.

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

Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Simon Slavin]

On 17 Aug 2009, at 3:47pm, Angus March wrote:

> I was concerned
> that the documentation might be playing fast and loose, saying that
> fflush (or fsync, or fdatasync) won't be called, when it really means
> that it won't be called during any call to step() or finalize(), while
> it would be called when the session is closed. I wasn't sure, so I
> thought I'd ask, because it'll matter to my app.


Although the SQLite code might not be syncing, it is still issuing the  
commands to write data to the database file (at least I assume it  
does).  Various events can send a synchronisation event all the way up  
(or down) the chain of command.  For instance, a hard disk which goes  
to sleep due to inactivity will trigger a sync before they sleep; a  
laptop which is about to sleep due to low battery will often flush  
queued writes; in some operating systems the OS will issue a sync for  
files handled by a process about to be swapped out of active memory.

So no, you cannot depend on no writing to disk just because you've  
told SQLite not to synchronise every transaction.  If you don't want  
your record written to disk, don't issue the INSERT command.  Perhaps  
you could use a virtual table or something.

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

Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Angus March]

Matt Sergeant wrote:
> On Mon, 17 Aug 2009 10:47:23 -0400, Angus March wrote:
>   
>>> Because yes, that's what synchronous=OFF means. It stops SQLite from 
>>> issuing fflush calls (effectively).
>>>   
>>>   
>> Right, and this is implied by the documentation, but I was concerned
>> that the documentation might be playing fast and loose, saying that
>> fflush (or fsync, or fdatasync) won't be called, when it really means
>> that it won't be called during any call to step() or finalize(), while
>> it would be called when the session is closed. I wasn't sure, so I
>> thought I'd ask, because it'll matter to my app.
>> 
>
> Kernels will fflush when a file handle is closed, which will happen 
> when you close the database handle.
>   

Actually, looking at the man pages for fflush just the user-space
buffers are flushed, and not the write-behind buffer in the kernel. If
that's all SQLite does, that's ok.
So again, if anyone knows that synchronous=OFF means that SQLite
will not *deliberately* flush the kernel's write-behind cache *at any
time* for the *rest of the session*, please let me know.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Matt Sergeant]

On Mon, 17 Aug 2009 10:47:23 -0400, Angus March wrote:
>> Because yes, that's what synchronous=OFF means. It stops SQLite from 
>> issuing fflush calls (effectively).
>>   
> Right, and this is implied by the documentation, but I was concerned
> that the documentation might be playing fast and loose, saying that
> fflush (or fsync, or fdatasync) won't be called, when it really means
> that it won't be called during any call to step() or finalize(), while
> it would be called when the session is closed. I wasn't sure, so I
> thought I'd ask, because it'll matter to my app.

Kernels will fflush when a file handle is closed, which will happen 
when you close the database handle.

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Angus March]

Matt Sergeant wrote:
> On Fri, 14 Aug 2009 12:33:30 -0400, Angus March wrote:
>   
>> I want my INSERT done right away, I just don't want it to be flushed
>> from the filesystem's write-behind cache until the kernel decides, not
>> when SQLite decides.
>> 
>
> Did you mean you do "want it to be flushed from the filesystem's 
> write-behind cache when the kernel decides (rather than when SQLite 
> decides)"?
>   

That is one implication, yes.

> Because yes, that's what synchronous=OFF means. It stops SQLite from 
> issuing fflush calls (effectively).
>   
Right, and this is implied by the documentation, but I was concerned
that the documentation might be playing fast and loose, saying that
fflush (or fsync, or fdatasync) won't be called, when it really means
that it won't be called during any call to step() or finalize(), while
it would be called when the session is closed. I wasn't sure, so I
thought I'd ask, because it'll matter to my app.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Matt Sergeant]

On Fri, 14 Aug 2009 12:33:30 -0400, Angus March wrote:
> I want my INSERT done right away, I just don't want it to be flushed
> from the filesystem's write-behind cache until the kernel decides, not
> when SQLite decides.

Did you mean you do "want it to be flushed from the filesystem's 
write-behind cache when the kernel decides (rather than when SQLite 
decides)"?

Because yes, that's what synchronous=OFF means. It stops SQLite from 
issuing fflush calls (effectively).

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Simon Slavin]

On 14 Aug 2009, at 5:33pm, Angus March wrote:

> I want my INSERT done right away,

Then do not turn off synchronous !

> I just don't want it to be flushed
> from the filesystem's write-behind cache until the kernel decides, not
> when SQLite decides.

SQLite cannot control how your operating system handles its files.   
There are all sorts of reasons it might decide to flush that nothing  
else can control.  The results should not matter to you anyway.

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

Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Angus March]

Simon Slavin wrote:
> On 14 Aug 2009, at 5:25pm, Angus March wrote:
>
>   
>> I need to know that if I turn of the synchronous that no synching will
>> be done, up to, and including, when the session is closed. I'm asking,
>> because my program just INSERTs once per session, so if a synch gets
>> done when the session closes, that's pretty useless.
>> 
>
> You cannot trust this.  If you do not want your INSERT done until the  
> session is about to finish, do not do your INSERT until the session is  
> about to finish.
>   

I want my INSERT done right away, I just don't want it to be flushed
from the filesystem's write-behind cache until the kernel decides, not
when SQLite decides.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Simon Slavin]

On 14 Aug 2009, at 5:25pm, Angus March wrote:

> I need to know that if I turn of the synchronous that no synching will
> be done, up to, and including, when the session is closed. I'm asking,
> because my program just INSERTs once per session, so if a synch gets
> done when the session closes, that's pretty useless.

You cannot trust this.  If you do not want your INSERT done until the  
session is about to finish, do not do your INSERT until the session is  
about to finish.

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

[sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Angus March]

I need to know that if I turn of the synchronous that no synching will
be done, up to, and including, when the session is closed. I'm asking,
because my program just INSERTs once per session, so if a synch gets
done when the session closes, that's pretty useless.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users