How to speed up SQLite

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

How to speed up SQLite

pippi pohopper
I'm experimenting with SQLite and found there's a big difference in speed depending on the setting of PRAGMA SYNCHRONOUS.

I understand that settings other than"2" for this parameter don't guarantee that data is safely stored on disk when che COMMIT statement terminates.
 
I wonder if it is possible to mix various settings of PRAGMA SYNCHRONOUS in the same program, i.e. issue a "PRAGMA SYNCHRONOUS 2" before performing very important transactions and using a lower PRAGMA SYNCHRONOUS value before performing less important transactions.
 
If this is not possible because PRAGMA SYNCHRONOUS can only be issued once when attaching to the database, I'm considering dividing to workload between two processes or threads that write on the same database. One thread woud use "PRAGMA SYNCHRONOUS 2" and would be responsible for critical database updates and the other thread would use "PRAGMA SYNCHRONOUS 0" and would be responsible for less critical updates.
 
Anybody knows if either of this two solutions will work reliably ? Which one is the best (in terms of data safety) ?

Thank you, bye


               
---------------------------------
Yahoo! Messenger: chiamate gratuite in tutto il mondo
Reply | Threaded
Open this post in threaded view
|

RE: How to speed up SQLite

Thomas Briggs

   Given my understanding of the codebase (you get to decide what that's
worth), the value of the synchronous pragma determines decisions going
forward, so changing it mid-process should impact only transaction
handling from that point forward.  I do know, however, that there are
places in the code where special consideration is given to the
possibility of the synchronous pragma having been changed mid-process,
so it isn't necessarily a trouble-free approach, I don't think.
Changing its value only when no transaction is active would seem pretty
safe to me.

   -Tom

> -----Original Message-----
> From: pippi pohopper [mailto:[hidden email]]
> Sent: Saturday, October 01, 2005 12:31 PM
> To: [hidden email]
> Subject: [sqlite] How to speed up SQLite
>
> I'm experimenting with SQLite and found there's a big
> difference in speed depending on the setting of PRAGMA SYNCHRONOUS.
>
> I understand that settings other than"2" for this parameter
> don't guarantee that data is safely stored on disk when che
> COMMIT statement terminates.
>  
> I wonder if it is possible to mix various settings of PRAGMA
> SYNCHRONOUS in the same program, i.e. issue a "PRAGMA
> SYNCHRONOUS 2" before performing very important transactions
> and using a lower PRAGMA SYNCHRONOUS value before performing
> less important transactions.
>  
> If this is not possible because PRAGMA SYNCHRONOUS can only
> be issued once when attaching to the database, I'm
> considering dividing to workload between two processes or
> threads that write on the same database. One thread woud use
> "PRAGMA SYNCHRONOUS 2" and would be responsible for critical
> database updates and the other thread would use "PRAGMA
> SYNCHRONOUS 0" and would be responsible for less critical updates.
>  
> Anybody knows if either of this two solutions will work
> reliably ? Which one is the best (in terms of data safety) ?
>
> Thank you, bye
>
>
>
> ---------------------------------
> Yahoo! Messenger: chiamate gratuite in tutto il mondo
>
Reply | Threaded
Open this post in threaded view
|

RE: How to speed up SQLite

Christian Smith
On Mon, 3 Oct 2005, Thomas Briggs wrote:

>
>   Given my understanding of the codebase (you get to decide what that's
>worth), the value of the synchronous pragma determines decisions going
>forward, so changing it mid-process should impact only transaction
>handling from that point forward.  I do know, however, that there are
>places in the code where special consideration is given to the
>possibility of the synchronous pragma having been changed mid-process,
>so it isn't necessarily a trouble-free approach, I don't think.
>Changing its value only when no transaction is active would seem pretty
>safe to me.


However, as SQLite files are single files, a crash during a "non-important
transaction" could still hose the entire database. In this sense, there is
no such thing as a non-important transaction.

Perhaps, you could keep "non-important" data in a seperate database file,
and attach it to the main database. Have a connection to the main database
plus non-important database attached with synchronous updates, and a
seperate connection just to the non-important database with asynchronous
updates.

What, exactly, constitutes a non-important update?


>
>   -Tom
>
>> -----Original Message-----
>> From: pippi pohopper [mailto:[hidden email]]
>> Sent: Saturday, October 01, 2005 12:31 PM
>> To: [hidden email]
>> Subject: [sqlite] How to speed up SQLite
>>
>> I'm experimenting with SQLite and found there's a big
>> difference in speed depending on the setting of PRAGMA SYNCHRONOUS.
>>
>> I understand that settings other than"2" for this parameter
>> don't guarantee that data is safely stored on disk when che
>> COMMIT statement terminates.
>>
>> I wonder if it is possible to mix various settings of PRAGMA
>> SYNCHRONOUS in the same program, i.e. issue a "PRAGMA
>> SYNCHRONOUS 2" before performing very important transactions
>> and using a lower PRAGMA SYNCHRONOUS value before performing
>> less important transactions.
>>
>> If this is not possible because PRAGMA SYNCHRONOUS can only
>> be issued once when attaching to the database, I'm
>> considering dividing to workload between two processes or
>> threads that write on the same database. One thread woud use
>> "PRAGMA SYNCHRONOUS 2" and would be responsible for critical
>> database updates and the other thread would use "PRAGMA
>> SYNCHRONOUS 0" and would be responsible for less critical updates.
>>
>> Anybody knows if either of this two solutions will work
>> reliably ? Which one is the best (in terms of data safety) ?
>>
>> Thank you, bye
>>
>>
>>
>> ---------------------------------
>> Yahoo! Messenger: chiamate gratuite in tutto il mondo
>>
>

--
    /"\
    \ /    ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
     X                           - AGAINST MS ATTACHMENTS
    / \
Reply | Threaded
Open this post in threaded view
|

RE: How to speed up SQLite

Thomas Briggs
In reply to this post by pippi pohopper
 
> However, as SQLite files are single files, a crash during a
> "non-important
> transaction" could still hose the entire database. In this
> sense, there is
> no such thing as a non-important transaction.

   Hrm... A very good point.  Thanks for spotting the flaw in my
thinking. :)

   -Tom