[sqlite] Is 'synchronous' pragma now being set on per database basis?

[Paul]

>   On 3/16/15, Paul  wrote:
> >  when the usage is
> >
> > (1) open database
> > (2) execute 'PRAGMA synchronous = OFF;'
> > (3) attach database X
> > (4) execute 'PRAGMA X.synchronous = OFF;'
> > (5) begin transaction
> > (6) do some inserts/deletes/updates
> > (7) commit transaction
> > (8) close database
> >
> > I observe one fsync() for directory where 'main' database is located.
> > Why attaching database is causing this nasty fsync() during commit, even
> > when both databases have fsync() disabled?
> 
> Problem fixed in https://www.sqlite.org/src/info/018d7671402a0f81
> 

Thank you very much.

[sqlite] Is 'synchronous' pragma now being set on per database basis?

[Paul]

>   On 3/16/15, Paul  wrote:
> 
> > Is  doc page is
> > outdated and 'synchronous' pragma is now set for each databse separately?
> 
> It has always been that way.  The documentation has recently been
> updated to clarify that point.  See
> http://www.sqlite.org/draft/pragma.html#pragma_synchronous for the
> documentation as it will appears in the next release.
> 

Thank you for clarification, Dr.Hipp, sadly I didn't know that.

But there is still one question left. Why when my usage is

(1) open database
(2) execute 'PRAGMA synchronous = OFF;'
(3) begin transaction
(4) do some inserts/deletes/updates
(5) commit transaction
(6) close database

not a single fsync() is being invoked. But when the usage is

(1) open database
(2) execute 'PRAGMA synchronous = OFF;'
(3) attach database X
(4) execute 'PRAGMA X.synchronous = OFF;'
(5) begin transaction
(6) do some inserts/deletes/updates
(7) commit transaction
(8) close database

I observe one fsync() for directory where 'main' database is located.
Why attaching database is causing this nasty fsync() during commit, even when 
both databases have fsync() disabled?
How can I avoid it, since even directory sync is an expensive operation?

Thanks,
Paul

[sqlite] Is 'synchronous' pragma now being set on per database basis?

[Paul]

Hello, fellow developers. 

Recently we performed a lot of test with ZFS and Sqlite and long story short: 
we came up with a conclusion 
that it is safe to disable fsync() to keep database consistent (though not 
durable), even in case of power failure. 
So we decided to stck with? 'PRAGMA synchronous = OFF' since durability does 
not concerns us very much. 

But we have one specific use-case whe 'master' database is first opened and 
then 'slave' database is being attached. 
When some updates? are made to 'slave' and 'master' we still see fsync() being 
called for 'slave', its journal and 
their directory but not for 'master' nor for 'master's journal. 

We perform these steps: 

(1) open 'master' 
(2) execute 'PRAGMA synchronous = OFF;' 
(3) attach 'slave' 
(4) do updates 

and see fsync()s. 

Even when we insert 'PRAGMA synchronous = OFF;' after (3): 

(1) open 'master' 
(2) execute 'PRAGMA synchronous = OFF;' 
(3) attach 'slave' 
(3.1) execute 'PRAGMA synchronous = OFF;' 
(4) do updates 

nothing changes: 'slave', its journal and thier directory are still fsync()-ed. 

On the other hand if we change our steps to 

(1) open 'master' 
(2) execute 'PRAGMA synchronous = OFF;' 
(3) attach 'slave' 
(3.1) execute 'PRAGMA slave.synchronous = OFF;' 
(4) do updates 

i.e. explicitly specify 'slave' before synchronous pragma, both 'slave' and its 
journal stop being fsync()-ed. 
However there is still fsync() for directory that we would really like to 
avoid. 

So the questions are: 

Is  doc page is outdated 
and 'synchronous' pragma is now set for each databse separately? 

How to avoid fsync() for directory? 
I know that it is 'not safe'. But same can be said about not syncing journal 
and/or database file when 'PRAGMA synchronous = OFF;' is set. 
Is there a specific reason for keeping fsync() for directory? 

Seqlite version that we use is: sqlite3-3.8.7_1 

Thanks, 
Paul 

[sqlite] Is 'synchronous' pragma now being set on per database basis?

[Richard Hipp]

On 3/16/15, Paul  wrote:
>  when the usage is
>
> (1) open database
> (2) execute 'PRAGMA synchronous = OFF;'
> (3) attach database X
> (4) execute 'PRAGMA X.synchronous = OFF;'
> (5) begin transaction
> (6) do some inserts/deletes/updates
> (7) commit transaction
> (8) close database
>
> I observe one fsync() for directory where 'main' database is located.
> Why attaching database is causing this nasty fsync() during commit, even
> when both databases have fsync() disabled?

Problem fixed in https://www.sqlite.org/src/info/018d7671402a0f81


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Is 'synchronous' pragma now being set on per database basis?

[Richard Hipp]

On 3/16/15, Paul  wrote:
>
>>   On 3/16/15, Paul  wrote:
>>
>> > Is  doc page is
>> > outdated and 'synchronous' pragma is now set for each databse
>> > separately?
>>
>> It has always been that way.  The documentation has recently been
>> updated to clarify that point.  See
>> http://www.sqlite.org/draft/pragma.html#pragma_synchronous for the
>> documentation as it will appears in the next release.
>>
>
> Thank you for clarification, Dr.Hipp, sadly I didn't know that.
>
> But there is still one question left
>

We are still working on that problem.  Patience, Grasshopper.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Is 'synchronous' pragma now being set on per database basis?

[Richard Hipp]

On 3/16/15, Paul  wrote:

> Is  doc page is
> outdated and 'synchronous' pragma is now set for each databse separately?

It has always been that way.  The documentation has recently been
updated to clarify that point.  See
http://www.sqlite.org/draft/pragma.html#pragma_synchronous for the
documentation as it will appears in the next release.


-- 
D. Richard Hipp
drh at sqlite.org