Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[Stephen Chrzanowski]

If you write your information to the cheap* USB key instead of the SD card,
from a mile-high view, you're looking at a bad data disk instead of a bad
OS disk.  You could backup daily your USB version of the database to the SD
card, or to a network drive (If available) so then you're only writing to
the SD card once per day.  If you put an hours worth of data in memory (Via
an array, or an in-memory database) you won't write data out anywhere,
unless the OS decides it has to (Swap), which would end up being out of
your control anyways.



* Cheap has two meanings here.  Both price, and ease of replacement.
Relatively speaking, its easier to put a new USB key in and get it
formatted, versus reloading the OS and getting everything running again.  I
don't own a Pi, so I don't know much much more work it is to load versus
putting a 'nix distro on a PC.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[Clemens Eisserer]

Hi Stephen,

> *First*, buy a bulk amount of cheap, inexpensive USB keys and start
> throwing your data at the key instead of your OS's card.

Don't see how that will change things: Once the SD card fails, data
logging will fail too - same is true for the usb key.

> *Second*, instead of writing to the SD card 8640 times a day, why not once
> every hour?

This is exactly what I am looking for by reducing the frequency of fsync.
This way the data will stay in the page cache without beeing flused to
the device, sans any ugly workarounds in my software.

Regards, Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[Stephen Chrzanowski]

There are a few reasons for the two-database method would be useful versus
a single database connection, depending on the volume of data in the pot.

1> Having a single hourly database will keep the database size minimal.
~8000 entries holding just temperature data, it'll be small so I can't see
a write taking longer than 10 seconds.
2> Backing up a large database that could potentially take longer than 10
seconds to backup, you might end up in a race condition.  If an insert at
the next 10 second mark causes the backup API to start over again, who's to
say that 20 seconds down the road the backup will restart again?
3> Depending on the kind of historical look up needed, picking a day/time
range would be as easy as attaching the required databases, in code,
(re)generating a temporary view to join the look ups would be simplistic.
The data could be considered "read only" and be safely read from a network
share on another machine as to not disrupt the Pi.

Depending on the structure of the code, if a two-database setup is daunting
to implement, the other option available, I suppose, is to do a bulk insert
from the memory database to the SD database in one transaction once an
hour, and when the bulk insert is completed, delete the in-memory contents
and continue on.  You should still be looking at a decent output times
(Just a few thousand small rows which probably amounts to only a small-few
hundred pages) so I think even writing to an old school 720k floppy
diskette should be quick enough.

Depending on the language, you could just entirely skip the in-memory
database and just remember the data recorded from the Pi by dumping it into
an array, then hourly dump the data into the database.

On Wed, Feb 12, 2014 at 4:03 AM, RSmith  wrote:

>
> Actually, the Backup-API is clever like that, it will automatically void
> and re-start the backup when data changes happen. Of course this might be
> equally detrimental if you update more frequently than the backup takes to
> complete, in which case the system would be in permanent backup and any
> gains voided.
>
> Maybe stop the updater till the backup is finished... it should however
> not take 10s, so it _should_ be safe, but I would put it in the
> precautionary wait state just to be safe.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[RSmith]

On 2014/02/12 10:09, Stephen Chrzanowski wrote:

The other thing I'd look into is that because of the varying speeds of SD,
the volume of information you could be writing, you may run into an issue
where you call the backup API but due to write speeds, something else
writes to the live in-memory database, and then your data becomes expunged
when the DELETE command is executed while the backup is happening.


Actually, the Backup-API is clever like that, it will automatically void and re-start the backup when data changes happen. Of course 
this might be equally detrimental if you update more frequently than the backup takes to complete, in which case the system would be 
in permanent backup and any gains voided.


Maybe stop the updater till the backup is finished... it should however not take 10s, so it _should_ be safe, but I would put it in 
the precautionary wait state just to be safe.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[Stephen Chrzanowski]

Three thoughts;

*First*, buy a bulk amount of cheap, inexpensive USB keys and start
throwing your data at the key instead of your OS's card.  I'm not 100%
clear on how a USB key handles itself as far as writing to certain parts of
its memory, but you could partition off chunks of space and just move the
mount point to a different partition periodically.  I picked up an
EXTREMELY small 16gig USB key (Smaller than a freak'n quarter) for like
$30.  This particular keys life span is going to be in a read-only state in
my car stereo.  I'm sure they sell smaller (Volume size, not physical) for
cheaper.

*Second*, instead of writing to the SD card 8640 times a day, why not once
every hour?  Since you losing an hour worth of data isn't significant, and
since the Pi has a decent chunk of memory (If you've got the B version),
you could just throw the data you're accumulating into memory, then at the
top of the hour, dump the data to the SD via the Backup API naming it with
a date/time stamp as the file name, dump the memory contents, and start
over again.  You'll save significant writes, pending swaps to cache by the
OS you'll be writing to the disk only 24 times a day instead of 8640 times.

The only changes you'd need to do to your code is to create the table in
memory, or, have a 'template' database sitting around that can use the
Backup API to put into memory.  Create the in-memory database with "
:memory: " as the file name (Excluding quotes, INCLUDING colons), and
you're off to the races.

The other thing I'd look into is that because of the varying speeds of SD,
the volume of information you could be writing, you may run into an issue
where you call the backup API but due to write speeds, something else
writes to the live in-memory database, and then your data becomes expunged
when the DELETE command is executed while the backup is happening.

So what I would do is;
- use a temporary variable to hold the memory location of the current
in-memory database, (Essentially NewTempDatabase = OldLiveDatabase)
- directly free-and-nill the OldLiveDatabase variable
- re-create a new :memory: database assigned to OldLiveDatabase,
- recreate/reload the template database against OldLiveDatabase,
- call the temporary variables backup API.
- I'm unsure if you'll get an event when the backup is complete, however,
once you call the backup, you should be able to use close the database,
then the object will free itself as usual.

So for a short period of time, you'll have two in-memory databases, one
containing the previous hours worth of data, and a new, fresh new
database.  The only issue that I can see coming up is if you're re-opening
the database each time you add a new record, which would be really bad to
begin with first off (Very expensive to do, but since you're doing one
thing every 10 seconds, not such a big deal I suppose), second, if you're
functions are aware of how to use a globally accessible database, or if
you're passing the database into your functions, etc, then using a memory
database is a total scrub.

*Third*, if you're thinking about using the second option, throw the backup
at a network drive, there by ELIMINATING writes to SD card by your
application.


On Mon, Feb 10, 2014 at 8:40 AM, Clemens Eisserer wrote:

> Hi,
>
> I would like to use sqlite for storing temperature data acquired every
> 10s running on my raspberry pi.
> As my first SD card died within a week with this workload, I am
> looking for opportunities to reduce write operations triggered by
> fsyncs to flash.
> For me loosing 1h of data at a power failure isn't an issue, however
> the DB shouldn't be corrupt afterwards.
>
> I found the pragma "synchronous", which when set to "NORMAL" does seem
> to do exactly what I am looking for - when sqlite is used in WAL mode.
> Am I right that with this configuration, fsync is only executed very
> seldomly?
>
> > In WAL mode when synchronous is NORMAL (1), the WAL file is synchronized
> before each checkpoint
> > and the database file is synchronized after each completed checkpoint
> and the WAL file header is synchronized
> > when a WAL file begins to be reused after a checkpoint, but no sync
> operations occur during most transactions.
>
> Thank you in advance, Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[Richard Hipp]

On Mon, Feb 10, 2014 at 9:07 AM, Clemens Eisserer wrote:

> Hi Richard,
>
> > In WAL mode, with synchronous=NORMAL (the default), fsync() only happens
> on
> > a checkpoint operation.  Whether or not checkpoint is "very seldom"
> depends
> > on a number of factors, but seems likely to be the case in your scenario.
>
> Thanks a lot for your answer.
> Just to make sure, with auto-checkpointing turned off, which other
> factors can cause a checkpoint to be created (if not manually)?
>

When the last open connection to the database file closes, it automatically
tries to checkpoint.  You cannot turn that off.

Checkpoints are good.  You do not want your WAL file to grow too large, and
the only way to reduce the size of the WAL file is to run a checkpoint.



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[Clemens Eisserer]

Hi Richard,

> In WAL mode, with synchronous=NORMAL (the default), fsync() only happens on
> a checkpoint operation.  Whether or not checkpoint is "very seldom" depends
> on a number of factors, but seems likely to be the case in your scenario.

Thanks a lot for your answer.
Just to make sure, with auto-checkpointing turned off, which other
factors can cause a checkpoint to be created (if not manually)?

Thanks, Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[Richard Hipp]

On Mon, Feb 10, 2014 at 8:40 AM, Clemens Eisserer wrote:

> Hi,
>
> I would like to use sqlite for storing temperature data acquired every
> 10s running on my raspberry pi.
> As my first SD card died within a week with this workload, I am
> looking for opportunities to reduce write operations triggered by
> fsyncs to flash.
> For me loosing 1h of data at a power failure isn't an issue, however
> the DB shouldn't be corrupt afterwards.
>
> I found the pragma "synchronous", which when set to "NORMAL" does seem
> to do exactly what I am looking for - when sqlite is used in WAL mode.
> Am I right that with this configuration, fsync is only executed very
> seldomly?
>

In WAL mode, with synchronous=NORMAL (the default), fsync() only happens on
a checkpoint operation.  Whether or not checkpoint is "very seldom" depends
on a number of factors, but seems likely to be the case in your scenario.


> > In WAL mode when synchronous is NORMAL (1), the WAL file is synchronized
> before each checkpoint
> > and the database file is synchronized after each completed checkpoint
> and the WAL file header is synchronized
> > when a WAL file begins to be reused after a checkpoint, but no sync
> operations occur during most transactions.
>
> Thank you in advance, Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[Clemens Eisserer]

Hi,

I would like to use sqlite for storing temperature data acquired every
10s running on my raspberry pi.
As my first SD card died within a week with this workload, I am
looking for opportunities to reduce write operations triggered by
fsyncs to flash.
For me loosing 1h of data at a power failure isn't an issue, however
the DB shouldn't be corrupt afterwards.

I found the pragma "synchronous", which when set to "NORMAL" does seem
to do exactly what I am looking for - when sqlite is used in WAL mode.
Am I right that with this configuration, fsync is only executed very seldomly?

> In WAL mode when synchronous is NORMAL (1), the WAL file is synchronized 
> before each checkpoint
> and the database file is synchronized after each completed checkpoint and the 
> WAL file header is synchronized
> when a WAL file begins to be reused after a checkpoint, but no sync 
> operations occur during most transactions.

Thank you in advance, Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users