SQLite and ext3 journalling mode
 

Hi,

Are SQLite users that are worried about losing data that has been
committed (fsynced) better off setting data=journal than
data=ordered (or even data=writeback)?

The context is trying to reduce the number of writes to a flash
file-system without sacrificing data integrity in the event of a
power failure or OS crash.

Thanks,
Dan Kennedy.

_______________________________________________
Ext3-users mailing list
Ext3-users@redhat.com
https://www.redhat.com/mailman/listinfo/ext3-users

SQLite and ext3 journalling mode
 

On Tue, Dec 07, 2010 at 01:42:08AM +0700, Dan Kennedy wrote:
>
> Are SQLite users that are worried about losing data that has been
> committed (fsynced) better off setting data=journal than
> data=ordered (or even data=writeback)?

Well, they won't be better off a data integrity point of view.
Depending on how SQLite is configured, and how many fsync's are issued
by SQLite in response to application queries, and depending on your
background workload by other applications, using data=journal *might*
be a performance win.

In general, though, if you have background workloads that are
downloading torrents, data=journal is going to hurt a lot. So I don't
recommend it except for fairly specialized deployments where there's
only one primary user of the file system.

- Ted

_______________________________________________
Ext3-users mailing list
Ext3-users@redhat.com
https://www.redhat.com/mailman/listinfo/ext3-users

SQLite and ext3 journalling mode
 

On 12/07/2010 04:31 AM, Ted Ts'o wrote:

On Tue, Dec 07, 2010 at 01:42:08AM +0700, Dan Kennedy wrote:


Are SQLite users that are worried about losing data that has been
committed (fsynced) better off setting data=journal than
data=ordered (or even data=writeback)?


Well, they won't be better off a data integrity point of view.
Depending on how SQLite is configured, and how many fsync's are issued
by SQLite in response to application queries, and depending on your
background workload by other applications, using data=journal *might*
be a performance win.

In general, though, if you have background workloads that are
downloading torrents, data=journal is going to hurt a lot. So I don't
recommend it except for fairly specialized deployments where there's
only one primary user of the file system.


Thanks. But to be clear, is data=ordered better than data=writeback
wrt. data integrity following a power failure?

Regards,
Dan.

_______________________________________________
Ext3-users mailing list
Ext3-users@redhat.com
https://www.redhat.com/mailman/listinfo/ext3-users

SQLite and ext3 journalling mode
 

On 12/08/2010 02:02 PM, Miller, Mike (OS Dev) wrote:



-----Original Message-----
From: ext3-users-bounces@redhat.com [mailto:ext3-users-
bounces@redhat.com] On Behalf Of Ric Wheeler
Sent: Wednesday, December 08, 2010 11:08 AM
To: Richard Hipp
Cc: ext3-users@redhat.com
Subject: Re: SQLite and ext3 journalling mode

On 12/08/2010 11:56 AM, Richard Hipp wrote:


On Wed, Dec 8, 2010 at 11:25 AM, Ric Wheeler<ricwheeler@gmail.com
<mailto:ricwheeler@gmail.com>> wrote:

On 12/08/2010 06:52 AM, Dan Kennedy wrote:


Thanks. But to be clear, is data=ordered better than

data=writeback

wrt. data integrity following a power failure?

Regards,
Dan.


Data integrity can mean a couple of different things.

If you are file system meta-data centric (i.e., a file system

developer or

just worried about having to run fsck after a crash to repair the

file

system), then both options *should* be equivalent.

If you are one of those annoying users who define data integrity

to

include those annoying details like will my file have garbage in

it after

a crash that will make my DB or other app puke, then data ordered

is

clearly more robust.


Thanks, Ric. Yes, we are numbered among the "annoying users". Based

on what

you are telling us, we'll recommend that people use data=ordered,

barrier=1

Just as an FYI, not all HW vendors enable the drive write cache especially on array controllers. In those cases barriers do nothing.

-- mikem




Right - upstream has been working to make sure that we can default to barriers
on and not see a performance hit for devices like arrays that don't need them ...


Ric

_______________________________________________
Ext3-users mailing list
Ext3-users@redhat.com
https://www.redhat.com/mailman/listinfo/ext3-users

SQLite and ext3 journalling mode
 

On 12/08/2010 06:52 AM, Dan Kennedy wrote:

On 12/07/2010 04:31 AM, Ted Ts'o wrote:

On Tue, Dec 07, 2010 at 01:42:08AM +0700, Dan Kennedy wrote:


Are SQLite users that are worried about losing data that has been
committed (fsynced) better off setting data=journal than
data=ordered (or even data=writeback)?


Well, they won't be better off a data integrity point of view.
Depending on how SQLite is configured, and how many fsync's are issued
by SQLite in response to application queries, and depending on your
background workload by other applications, using data=journal *might*
be a performance win.

In general, though, if you have background workloads that are
downloading torrents, data=journal is going to hurt a lot. So I don't
recommend it except for fairly specialized deployments where there's
only one primary user of the file system.


Thanks. But to be clear, is data=ordered better than data=writeback
wrt. data integrity following a power failure?

Regards,
Dan.



Data integrity can mean a couple of different things.

If you are file system meta-data centric (i.e., a file system developer or just
worried about having to run fsck after a crash to repair the file system), then
both options *should* be equivalent.


If you are one of those annoying users who define data integrity to include
those annoying details like will my file have garbage in it after a crash that
will make my DB or other app puke, then data ordered is clearly more robust.


Note that most distributions (including RHEL) support & focus testing only
ordered mode....


Hope this helps :)

Ric

_______________________________________________
Ext3-users mailing list
Ext3-users@redhat.com
https://www.redhat.com/mailman/listinfo/ext3-users

SQLite and ext3 journalling mode
 

On Wed, Dec 8, 2010 at 11:25 AM, Ric Wheeler <ricwheeler@gmail.com> wrote:

On 12/08/2010 06:52 AM, Dan Kennedy wrote:




Thanks. But to be clear, is data="" better than data="">
wrt. data integrity following a power failure?



Regards,

Dan.






Data integrity can mean a couple of different things.



If you are file system meta-data centric (i.e., a file system developer or just worried about having to run fsck after a crash to repair the file system), then both options *should* be equivalent.



If you are one of those annoying users who define data integrity to include those annoying details like will my file have garbage in it after a crash that will make my DB or other app puke, then data ordered is clearly more robust.


Thanks, Ric.* Yes, we are numbered among the "annoying users".* Based on what you are telling us, we'll recommend that people use data="" barrier=1 for maximum data reliability in the face of power loss.

*


Note that most distributions (including RHEL) support & focus testing only ordered mode....



Hope this helps :)



Ric



_______________________________________________

Ext3-users mailing list

Ext3-users@redhat.com

https://www.redhat.com/mailman/listinfo/ext3-users



--
D. Richard Hipp
drh@sqlite.org

_______________________________________________
Ext3-users mailing list
Ext3-users@redhat.com
https://www.redhat.com/mailman/listinfo/ext3-users

SQLite and ext3 journalling mode
 

On 12/08/2010 11:56 AM, Richard Hipp wrote:



On Wed, Dec 8, 2010 at 11:25 AM, Ric Wheeler <ricwheeler@gmail.com
<mailto:ricwheeler@gmail.com>> wrote:


On 12/08/2010 06:52 AM, Dan Kennedy wrote:


Thanks. But to be clear, is data=ordered better than data=writeback
wrt. data integrity following a power failure?

Regards,
Dan.


Data integrity can mean a couple of different things.

If you are file system meta-data centric (i.e., a file system developer or
just worried about having to run fsck after a crash to repair the file
system), then both options *should* be equivalent.

If you are one of those annoying users who define data integrity to
include those annoying details like will my file have garbage in it after
a crash that will make my DB or other app puke, then data ordered is
clearly more robust.


Thanks, Ric. Yes, we are numbered among the "annoying users". Based on what
you are telling us, we'll recommend that people use data=ordered, barrier=1
for maximum data reliability in the face of power loss.


That is what I do as well - there are use cases and users that prefer the lower
latency and can accept the trade offs that come with data writeback or
non-barrier use, but I certainly think most users would be better using the
settings you have above.


Good luck!

Ric

_______________________________________________
Ext3-users mailing list
Ext3-users@redhat.com
https://www.redhat.com/mailman/listinfo/ext3-users

SQLite and ext3 journalling mode
 

> -----Original Message-----
> From: ext3-users-bounces@redhat.com [mailto:ext3-users-
> bounces@redhat.com] On Behalf Of Ric Wheeler
> Sent: Wednesday, December 08, 2010 11:08 AM
> To: Richard Hipp
> Cc: ext3-users@redhat.com
> Subject: Re: SQLite and ext3 journalling mode
>
> On 12/08/2010 11:56 AM, Richard Hipp wrote:
> >
> >
> > On Wed, Dec 8, 2010 at 11:25 AM, Ric Wheeler <ricwheeler@gmail.com
> > <mailto:ricwheeler@gmail.com>> wrote:
> >
> > On 12/08/2010 06:52 AM, Dan Kennedy wrote:
> >
> >
> > Thanks. But to be clear, is data=ordered better than
> data=writeback
> > wrt. data integrity following a power failure?
> >
> > Regards,
> > Dan.
> >
> >
> > Data integrity can mean a couple of different things.
> >
> > If you are file system meta-data centric (i.e., a file system
> developer or
> > just worried about having to run fsck after a crash to repair the
> file
> > system), then both options *should* be equivalent.
> >
> > If you are one of those annoying users who define data integrity
> to
> > include those annoying details like will my file have garbage in
> it after
> > a crash that will make my DB or other app puke, then data ordered
> is
> > clearly more robust.
> >
> >
> > Thanks, Ric. Yes, we are numbered among the "annoying users". Based
> on what
> > you are telling us, we'll recommend that people use data=ordered,
> barrier=1

Just as an FYI, not all HW vendors enable the drive write cache especially on array controllers. In those cases barriers do nothing.

-- mikem

> > for maximum data reliability in the face of power loss.
>
> That is what I do as well - there are use cases and users that prefer
> the lower
> latency and can accept the trade offs that come with data writeback or
> non-barrier use, but I certainly think most users would be better using
> the
> settings you have above.
>
> Good luck!
>
> Ric
>
> _______________________________________________
> Ext3-users mailing list
> Ext3-users@redhat.com
> https://www.redhat.com/mailman/listinfo/ext3-users

_______________________________________________
Ext3-users mailing list
Ext3-users@redhat.com
https://www.redhat.com/mailman/listinfo/ext3-users

SQLite and ext3 journalling mode
 

On Wed, Dec 08, 2010 at 11:56:21AM -0500, Richard Hipp wrote:
>
> Thanks, Ric. Yes, we are numbered among the "annoying users". Based on
> what you are telling us, we'll recommend that people use data=ordered,
> barrier=1 for maximum data reliability in the face of power loss.

Note that given that SQLite uses fsync() properly, data=ordered
shouldn't hurt or help --- with respect to the very narrow issue of
data integrity in the SQLite database.

What data=ordered protects against is the possibility that stale data
(from a deleted file, possibly from some other user's) might show up
as "garbage" in a file whose newly allocated blocks didn't get written
out to disk before a system crash or other unclean shutdown.

To the extent that SQLite properly uses fsync() and doesn't pay
attention to data in parts of the db file that hasn't been committed
yet, this won't matter. From the perspective of users who care about
stale data showing up in a file being a potential security issue, they
should definitely use data=ordered in ext3.

However, if you have a specific use case where the only thing that
matters is SQLite, then assuming that fsync() is being used properly,
SQLite itself should be OK.

I hope this helps,

- Ted

_______________________________________________
Ext3-users mailing list
Ext3-users@redhat.com
https://www.redhat.com/mailman/listinfo/ext3-users