Transactions

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

Transactions

Martin Engelschalk
Hi all,

it may sound strange, but I do not need transactions.  Also i do not
care if the database is corrupted in case of a program or system crash.
So: is it possible to disable transactions in sqlite? Mr. Mark Allan
seems to have done this. Could i speed up my writes this way?

Thanks,
Martin
Reply | Threaded
Open this post in threaded view
|

Re: Transactions

Christian Smith
On Tue, 4 Oct 2005, Martin Engelschalk wrote:

>Hi all,
>
>it may sound strange, but I do not need transactions.  Also i do not
>care if the database is corrupted in case of a program or system crash.
>So: is it possible to disable transactions in sqlite? Mr. Mark Allan
>seems to have done this. Could i speed up my writes this way?


Turn off synchronous writes:
http://www.sqlite.org/pragma.html

Look for the synchronous pragma. Set it to OFF:
sqlite> PRAGMA synchronous = OFF;

Updates to transactions will no longer fsync() data to the disk, and
instead rely on OS write-back caching, which can significantly improve
speed at the cost of chances of corruption in case of system crash.


>
>Thanks,
>Martin
>

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

Re: Transactions

Martin Engelschalk
Hello Christian,

thank you, but synchronous is already off. What i aim to avoid is
writing the rollback - journal. In order to rollback, some additional
writing to disk is surely unaviodable.

Martin

Christian Smith schrieb:

>On Tue, 4 Oct 2005, Martin Engelschalk wrote:
>
>  
>
>>Hi all,
>>
>>it may sound strange, but I do not need transactions.  Also i do not
>>care if the database is corrupted in case of a program or system crash.
>>So: is it possible to disable transactions in sqlite? Mr. Mark Allan
>>seems to have done this. Could i speed up my writes this way?
>>    
>>
>
>
>Turn off synchronous writes:
>http://www.sqlite.org/pragma.html
>
>Look for the synchronous pragma. Set it to OFF:
>sqlite> PRAGMA synchronous = OFF;
>
>Updates to transactions will no longer fsync() data to the disk, and
>instead rely on OS write-back caching, which can significantly improve
>speed at the cost of chances of corruption in case of system crash.
>
>
>  
>
>>Thanks,
>>Martin
>>
>>    
>>
>
>  
>
Reply | Threaded
Open this post in threaded view
|

Re: Transactions

Christian Smith
On Tue, 4 Oct 2005, Martin Engelschalk wrote:

>Hello Christian,
>
>thank you, but synchronous is already off. What i aim to avoid is
>writing the rollback - journal. In order to rollback, some additional
>writing to disk is surely unaviodable.


You'll have to write your own pager layer, as there is currently no way to
disable the rollback journal.

Note, with no rollback journal, you'll not be able to rollback a
transaction. Any updates you make will be final even before a commit.

Another option might be to use a memory database, and write periodic
snapshots to disk in the form of a SQL text file. Look at shell.c in the
SQLite source for an example of how to do this (look for implementation of
the .dump command.) The SQL file then becomes your checkpoint, and can
also be compressed quite nicely which may save disk space. This is how the
likes of HSQL works.

Of course, if this is an embedded application, memory may be a problem
with a memory database. YMMV.


>
>Martin
>
>Christian Smith schrieb:
>
>>On Tue, 4 Oct 2005, Martin Engelschalk wrote:
>>
>>
>>
>>>Hi all,
>>>
>>>it may sound strange, but I do not need transactions.  Also i do not
>>>care if the database is corrupted in case of a program or system crash.
>>>So: is it possible to disable transactions in sqlite? Mr. Mark Allan
>>>seems to have done this. Could i speed up my writes this way?
>>>
>>>
>>
>>
>>Turn off synchronous writes:
>>http://www.sqlite.org/pragma.html
>>
>>Look for the synchronous pragma. Set it to OFF:
>>sqlite> PRAGMA synchronous = OFF;
>>
>>Updates to transactions will no longer fsync() data to the disk, and
>>instead rely on OS write-back caching, which can significantly improve
>>speed at the cost of chances of corruption in case of system crash.
>>
>>
>>
>>
>>>Thanks,
>>>Martin
>>>
>>>
>>>
>>
>>
>>
>

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

Re: Transactions

Jens Miltner

Am 05.10.2005 um 13:17 schrieb Christian Smith:

> On Tue, 4 Oct 2005, Martin Engelschalk wrote:
>
>
>> Hello Christian,
>>
>> thank you, but synchronous is already off. What i aim to avoid is
>> writing the rollback - journal. In order to rollback, some additional
>> writing to disk is surely unaviodable.
>>
>
>
> You'll have to write your own pager layer, as there is currently no  
> way to
> disable the rollback journal.
>

Hmmh, other issues with not having a journal file aside - pager.c has  
the following code in sqlite3pager_open():

       int useJournal = (flags & PAGER_OMIT_JOURNAL)==0;

Looks like you might be able to switch off the rollback journal by  
passing the proper flags to sqlite3pager_open().
Does anybody have more details/insight about this?

</jum>

Reply | Threaded
Open this post in threaded view
|

Re: Transactions

Christian Smith
On Thu, 6 Oct 2005, Jens Miltner wrote:

>
>Am 05.10.2005 um 13:17 schrieb Christian Smith:
>
>> On Tue, 4 Oct 2005, Martin Engelschalk wrote:
>>
>>
>>> Hello Christian,
>>>
>>> thank you, but synchronous is already off. What i aim to avoid is
>>> writing the rollback - journal. In order to rollback, some additional
>>> writing to disk is surely unaviodable.
>>>
>>
>>
>> You'll have to write your own pager layer, as there is currently no
>> way to
>> disable the rollback journal.
>>
>
>Hmmh, other issues with not having a journal file aside - pager.c has
>the following code in sqlite3pager_open():
>
>       int useJournal = (flags & PAGER_OMIT_JOURNAL)==0;
>
>Looks like you might be able to switch off the rollback journal by
>passing the proper flags to sqlite3pager_open().
>Does anybody have more details/insight about this?

This is only invoked when opening a transient or virtual table, using the
OpenVirtual VDBE opcode. These are used for sorting and unions in selects
or IN expressions. Such transient data is not required to live across
crashes, transactions or even statements, so no journalling is done.

However, it does offer the ability to simply modify the existing pager.c
to turn off journals unconditionally. The attached (untested) patch should
do that.  Just recompile pager.c with -DSQLITE3_DISABLE_JOURNALLING.


>
></jum>
>

Christian

PS. No, I don't recommend anyone does this, BTW:)

--
    /"\
    \ /    ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
     X                           - AGAINST MS ATTACHMENTS
    / \

pagerpatch (1K) Download Attachment