disabling journalling of the database - side affects?

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

disabling journalling of the database - side affects?

Allan, Mark

Can anybody tell me if I will suffer any sideaffects if I disable the journalling of the database file with version 3.2.7?

We have changed the SQlite code locally to omit the journalling of the database file by supplying 1 as the value for omitJournal to calls to the function sqlite3BtreeFactory. We have done this as we do not want the performance overhead of doubling the amount of writes we make as we are using an NOR flash filing system and this is not particularly quick.

Can anybody help me?

Thanks


Mark


DISCLAIMER:
This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law.  If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.

Reply | Threaded
Open this post in threaded view
|

RE: disabling journalling of the database - side affects?

Allan, Mark

I definately have a problem when I disable journalling on version 3.2.7 of SQLite. I dont see the same problem with 3.2.1 of SQlite.
I disable the journalling of the database file as described in my earlier email.
With version 3.2.7 this causes SQlite to not sync the database file after some updates (i.e. sqlite3OsSync() is not called). This means that if power is lost before the database file is explicitly closed we are losing those changes.

I realise that the purpose of the journal file is to allow the database to return to its last valid state if power is lost during an update, however we believe that in our system we will not need this as our flash filing system is 100% power safe. i.e. it will return to the last flushed state of the file on power loss. So we believe that not only is the journalling of the database time consuming it is also unnecessary for us, which is a little annoying.

If this is a bug in 3.2.7 of SQlite can it be fixed? If I cannot disable the journal file safely by the mechanism described in my previous email then can somebody please indicate how I can disable journalling of the database safely.

Regards

Mark


> -----Original Message-----
> From: Allan, Mark [mailto:[hidden email]]
> Sent: 01 November 2005 09:52
> To: [hidden email]
> Subject: [sqlite] disabling journalling of the database -
> side affects?
>
>
>
> Can anybody tell me if I will suffer any sideaffects if I
> disable the journalling of the database file with version 3.2.7?
>
> We have changed the SQlite code locally to omit the
> journalling of the database file by supplying 1 as the value
> for omitJournal to calls to the function sqlite3BtreeFactory.
> We have done this as we do not want the performance overhead
> of doubling the amount of writes we make as we are using an
> NOR flash filing system and this is not particularly quick.
>
> Can anybody help me?
>
> Thanks
>
>
> Mark
>
>
> DISCLAIMER:
> This information and any attachments contained in this email
> message is intended only for the use of the individual or
> entity to which it is addressed and may contain information
> that is privileged, confidential, and exempt from disclosure
> under applicable law.  If the reader of this message is not
> the intended recipient, or the employee or agent responsible
> for delivering the message to the intended recipient, you are
> hereby notified that any dissemination, distribution,
> forwarding, or copying of this communication is strictly
> prohibited.  If you have received this communication in
> error, please notify the sender immediately by return email,
> and delete the original message immediately.
>
>
>


DISCLAIMER:
This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law.  If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.

Reply | Threaded
Open this post in threaded view
|

Re: disabling journalling of the database - side affects?

D. Richard Hipp
In reply to this post by Allan, Mark
"Allan, Mark" <[hidden email]> wrote:

> I definately have a problem when I disable journalling on version 3.2.7
> of SQLite. I dont see the same problem with 3.2.1 of SQlite.
> I disable the journalling of the database file as described in my
> earlier email.
> With version 3.2.7 this causes SQlite to not sync the database file
> after some updates (i.e. sqlite3OsSync() is not called). This means
> that if power is lost before the database file is explicitly closed
> we are losing those changes.
>
> I realise that the purpose of the journal file is to allow the database
> to return to its last valid state if power is lost during an update,
> however we believe that in our system we will not need this as our
> flash filing system is 100% power safe. i.e. it will return to the
> last flushed state of the file on power loss. So we believe that not
> only is the journalling of the database time consuming it is also
> unnecessary for us, which is a little annoying.
>
> If this is a bug in 3.2.7 of SQlite can it be fixed? If I cannot
> disable the journal file safely by the mechanism described in my
> previous email then can somebody please indicate how I can disable
> journalling of the database safely.
>

The ability to disable journalling is not a supported behavior of
SQLite.  If you can get it to work, that's great.  But if not, that
is not considered a bug.

The omitJournal flag on sqlite3BtreeFactory() is used for transient
tables that are never rolled back and which we do not care about if
there is a program crash or power failure.  The system should never
call sqlite3OsSync() on such files because sqlite3OsSync() is an
expensive operation (on most platforms) and for a transient file
it does not accomplish anything useful.  If version 3.2.1 was calling
sqlite3OsSync() on unjournalled files, then that was a performance
bug.  I have no specific memory of fixing that problem in 3.2.7, but
a lot of little problems were fixed in between those two releases,
so it seems plausible that this was one of them.

So my best guess is that if your journal-less use of SQLite worked
with version 3.2.1 then that was due to a bug in 3.2.1 that has
since been fixed - not a bug that has been introduced.

--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

RE: disabling journalling of the database - side affects?

Allan, Mark
In reply to this post by Allan, Mark
Thanks for your detailed reply. Would you mind answering my follow up queries?

> The ability to disable journaling is not a supported behaviour of
> SQLite.  If you can get it to work, that's great.  But if not, that
> is not considered a bug.

Ok so the ability to not be able to disable journaling is not a bug. However would you consider implementing this as a new feature in a future revision of SQLite? I am unsure how many other users would find the disabling of the journal file a useful feature? I know here we would really appreciate this. We have seen information on the web indicating that turning off journaling would help performance, See section 3.3 at
http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html. Although it does not state a mechanism for doing so. So maybe there are other users that would appeciate this as well as us?

Indeed not journaling the file did help performance a great deal, but of course we have to prioritise data integrity over speed. We do believe however that journaling the database for us is of no benefit as our filing system is 100% fail safe and will return to the last flushed state of the file on power loss.

Ideally we want to be able to use the latest versions of SQLite as they are released and as such don't want to stay with 3.2.1 especially as we may have been inadvertently benefiting from what was actually a bug anyhow.

Regards

Mark

> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]]
> Sent: 01 November 2005 11:49
> To: [hidden email]
> Subject: Re: [sqlite] disabling journalling of the database - side
> affects?
>
>
> "Allan, Mark" <[hidden email]> wrote:
> > I definately have a problem when I disable journalling on
> version 3.2.7
> > of SQLite. I dont see the same problem with 3.2.1 of SQlite.
> > I disable the journalling of the database file as described in my
> > earlier email.
> > With version 3.2.7 this causes SQlite to not sync the database file
> > after some updates (i.e. sqlite3OsSync() is not called). This means
> > that if power is lost before the database file is explicitly closed
> > we are losing those changes.
> >
> > I realise that the purpose of the journal file is to allow
> the database
> > to return to its last valid state if power is lost during an update,
> > however we believe that in our system we will not need this as our
> > flash filing system is 100% power safe. i.e. it will return to the
> > last flushed state of the file on power loss. So we believe that not
> > only is the journalling of the database time consuming it is also
> > unnecessary for us, which is a little annoying.
> >
> > If this is a bug in 3.2.7 of SQlite can it be fixed? If I cannot
> > disable the journal file safely by the mechanism described in my
> > previous email then can somebody please indicate how I can disable
> > journalling of the database safely.
> >
>
> The ability to disable journalling is not a supported behavior of
> SQLite.  If you can get it to work, that's great.  But if not, that
> is not considered a bug.
>
> The omitJournal flag on sqlite3BtreeFactory() is used for transient
> tables that are never rolled back and which we do not care about if
> there is a program crash or power failure.  The system should never
> call sqlite3OsSync() on such files because sqlite3OsSync() is an
> expensive operation (on most platforms) and for a transient file
> it does not accomplish anything useful.  If version 3.2.1 was calling
> sqlite3OsSync() on unjournalled files, then that was a performance
> bug.  I have no specific memory of fixing that problem in 3.2.7, but
> a lot of little problems were fixed in between those two releases,
> so it seems plausible that this was one of them.
>
> So my best guess is that if your journal-less use of SQLite worked
> with version 3.2.1 then that was due to a bug in 3.2.1 that has
> since been fixed - not a bug that has been introduced.
>
> --
> D. Richard Hipp <[hidden email]>
>
>
>


DISCLAIMER:
This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law.  If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.

Reply | Threaded
Open this post in threaded view
|

Re: disabling journalling of the database - side affects?

Gerry Snyder
Allan, Mark wrote:
>
>
> .... We do believe however that journaling the database for us
> is of no benefit as our filing system is 100% fail safe and will
> return to the last flushed state of the file on power loss.

Mark,

I am probably in over my head, as usual, but how do you recover if power
goes down during the write process for an update; that is, if some of an
update is written to the file and some of it is not?

I guess it must be a journalling fs. Double journalling would indeed be
inefficient.

Maybe it might be easier to turn off the fs journalling than that in sqlite?

Gerry

--
------------------
Gerry Snyder
American Iris Society Director, Symposium Chair
in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19

Reply | Threaded
Open this post in threaded view
|

RE: disabling journalling of the database - side affects?

Allan, Mark
In reply to this post by Allan, Mark
Gerry,

We are using a flash filesystem.

The changes are made to a separate area of the flash, when the file is flushed then the descriptor blocks are updated to point to the new block of flash. If the power is lost before the file is flushed then the descriptor is not updated and therefore the file is still in the same state as it was prior to any of the writes. The file is only changed when it is flushed or closed.

We believe based on this that we do not need to journal the database file. Indeed we had no problems with 3.2.1 albeit as we were benfiting from flushes that where not supposed to be happening.

Regards

Mark


> -----Original Message-----
> From: Gerry Snyder [mailto:[hidden email]]
> Sent: 01 November 2005 14:58
> To: [hidden email]
> Subject: Re: [sqlite] disabling journalling of the database - side
> affects?
>
>
> Allan, Mark wrote:
> >
> >
> > .... We do believe however that journaling the database for us
> > is of no benefit as our filing system is 100% fail safe and will
> > return to the last flushed state of the file on power loss.
>
> Mark,
>
> I am probably in over my head, as usual, but how do you
> recover if power
> goes down during the write process for an update; that is, if
> some of an
> update is written to the file and some of it is not?
>
> I guess it must be a journalling fs. Double journalling would
> indeed be
> inefficient.
>
> Maybe it might be easier to turn off the fs journalling than
> that in sqlite?
>
> Gerry
>
> --
> ------------------
> Gerry Snyder
> American Iris Society Director, Symposium Chair
> in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19
>
>
>


DISCLAIMER:
This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law.  If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.

Reply | Threaded
Open this post in threaded view
|

Re: disabling journalling of the database - side affects?

Christian Smith
In reply to this post by Gerry Snyder
On Tue, 1 Nov 2005, Gerry Snyder wrote:

>Allan, Mark wrote:
>>
>>
>> .... We do believe however that journaling the database for us
>> is of no benefit as our filing system is 100% fail safe and will
>> return to the last flushed state of the file on power loss.
>
>Mark,
>
>I am probably in over my head, as usual, but how do you recover if power
>goes down during the write process for an update; that is, if some of an
>update is written to the file and some of it is not?
>
>I guess it must be a journalling fs. Double journalling would indeed be
>inefficient.
>
>Maybe it might be easier to turn off the fs journalling than that in sqlite?


FLASH-based filesystems often have an append-only log behaviour, in order
to give wear levelling by spreading writes across all cells evenly.
Without this wear levelling, the FLASH device would suffer premature
failure as some cells would be written more often than others.

Info on JFFS can be found here.
http://sourceware.org/jffs2/

>
>Gerry
>

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