Changing default PRAGMA SYNCHRONOUS at compile time

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Changing default PRAGMA SYNCHRONOUS at compile time

nickg-3
Hi all,

I'm on a Mac, OX 10.4.1, and Sqlite works great, except.... it seems  
to be defaulting to PRAGMA SYNCHRONOUS=FULL (3) instead of  
"NORMAL" (2).  This is painfully slow.
While I can send 'set pragma synchronous=normal' at every write, this  
seems strange.  How do i change this at the compile/configure stage?  
I attempted look at the source code but couldn't find figure it out.  
The installation guide didn't seem to have this parameter either.  
Any clues?

thanks,

--nickg

Reply | Threaded
Open this post in threaded view
|

Re: Changing default PRAGMA SYNCHRONOUS at compile time

Dan Kennedy

--- nickg <[hidden email]> wrote:

> Hi all,
>
> I'm on a Mac, OX 10.4.1, and Sqlite works great, except.... it seems  
> to be defaulting to PRAGMA SYNCHRONOUS=FULL (3) instead of  
> "NORMAL" (2).  This is painfully slow.
> While I can send 'set pragma synchronous=normal' at every write, this  
> seems strange.  How do i change this at the compile/configure stage?  
> I attempted look at the source code but couldn't find figure it out.  
> The installation guide didn't seem to have this parameter either.  
> Any clues?
>
> thanks,
>
> --nickg
>
>

I don't there exists such an option. If you're desperate to change it,
modify the value function sqlite3pager_open() sets variable pPager->fullSync
to. For "NORMAL", you want it initialized to 0.

Dan.






               
__________________________________
Yahoo! Mail
Stay connected, organized, and protected. Take the tour:
http://tour.mail.yahoo.com/mailtour.html 

Reply | Threaded
Open this post in threaded view
|

Re: Changing default PRAGMA SYNCHRONOUS at compile time

Kurt Welgehausen
> While I can send 'set pragma synchronous=normal' at every write ...

You don't have to execute the pragma for every write.
Once you've set synchronous=normal, it stays normal
until you close and reopen the database.

Regards
Reply | Threaded
Open this post in threaded view
|

RE: Changing default PRAGMA SYNCHRONOUS at compile time

cronos-2
>> While I can send 'set pragma synchronous=normal' at every write ...
>
> You don't have to execute the pragma for every write.
> Once you've set synchronous=normal, it stays normal
> until you close and reopen the database.

Not strictly true, in 3.2.1 it gets reset if you change the cache_size - see
ticket 1260, just been fixed I think.

Reply | Threaded
Open this post in threaded view
|

Re: Changing default PRAGMA SYNCHRONOUS at compile time

nickg-3

Hi everyone,

thanks for the replies, but I'm a bit confused.  It appears "full" is  
the new "normal"?

To me, "normal" just by it's name implies it's the default.  And  
http://sqlite.org/pragma.html says "When synchronous is NORMAL (1,  
the default)," and it continues with "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. So NORMAL is the default mode."  This  
makes it sounds like FULL is for obsessive-compulsive applications.  
Finally, on my journaled filesystem, an insert takes about 0.8  
seconds out of the box with FULL, which isn't so great.

Did something change?  Is this a documentation bug?  Is my system  
wacky (well, no mac comments please)?  What am I missing?

I'm happy to write a patch to set the default value at compile time,  
if anyone thinks it would be useful.

thanks all.

--nickg





--
On Jun 1, 2005, at 12:48 PM, Cronos wrote:

>>> While I can send 'set pragma synchronous=normal' at every write ...
>>>
>>
>> You don't have to execute the pragma for every write.
>> Once you've set synchronous=normal, it stays normal
>> until you close and reopen the database.
>>
>
> Not strictly true, in 3.2.1 it gets reset if you change the  
> cache_size - see
> ticket 1260, just been fixed I think.
>
>

Reply | Threaded
Open this post in threaded view
|

Re: Changing default PRAGMA SYNCHRONOUS at compile time

Kurt Welgehausen
The docs have not caught up with the code. As you guessed,
the default for synchronous used to be normal, but it has
been changed to full. If you want normal, all you have to
do is execute the synchronous pragma after you open the db.

As another fellow pointed out, there was an unintended
interaction between the synchronous setting and the
cache_size pragma. If you're changing the cache size, you
may need to do it before you change the synchronous value.

You may also be interested in this thread:

   <http://www.mail-archive.com/sqlite-users@.../msg06478.html>

Regards