Sqlite on NAND flash devices...

[Pankaj Chawla]

Hi

I have a Sqlite db on an embedded device on which there are
inserts happening at a rate of 1 insert every 3 seconds. That
being the case if Sqlite does file close/sync every 3 seconds
it is going to wear off the NAND in no time. I tried to instead
put 2 mins worth of inserts inside a transaction/commit block so
that now data only gets committed every 2 mins. What I observe
though is that now a journal file gets created during the time
transaction/commit block is active and I would assume now all
my 3 second updates are going into the journal file. If that is true it
means I am still writing to the NAND every 3 seconds through the
journal file even though the main DB is only getting synced
with the journal every 2 mins.

Is this understanding correct? If yes, is there a way to actually
reduce the number of writes happening to the NAND flash such
that I can save it from quick wear out? If no, how is the writes
to the main DB different from the writes to the journal?

Thanks
Pankaj
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Black, Michael (IS)]

Can you keep your database in-memory and just copy it to your flash periodically?  That would give you the most control and minimize the # of writes as much as you want.  It's good you recognize that you'll burn your flash out quickly with what you're doing now.
 
There's also the -DSQLITE_TEMP_STORE option which keeps temporary files in-memory.
You probably want SQLITE_TEMP_STORE=3 during compilation.
 
** This function returns true if main-memory should be used instead of
** a temporary file for transient pager files and statement journals.
** The value returned depends on the value of db->temp_store (runtime
** parameter) and the compile time value of SQLITE_TEMP_STORE. The
** following table describes the relationship between these two values
** and this functions return value.
**
**   SQLITE_TEMP_STORE     db->temp_store     Location of temporary database
**   -----------------     --------------     ------------------------------
**   0                     any                file      (return 0)
**   1                     1                  file      (return 0)
**   1                     2                  memory    (return 1)
**   1                     0                  file      (return 0)
**   2                     1                  file      (return 0)
**   2                     2                  memory    (return 1)
**   2                     0                  memory    (return 1)
**   3                     any                memory    (return 1)

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 

________________________________

From: [hidden email] on behalf of Pankaj Chawla
Sent: Mon 8/30/2010 5:29 AM
To: [hidden email]
Subject: EXTERNAL:[sqlite] Sqlite on NAND flash devices...



Hi

I have a Sqlite db on an embedded device on which there are
inserts happening at a rate of 1 insert every 3 seconds. That
being the case if Sqlite does file close/sync every 3 seconds
it is going to wear off the NAND in no time. I tried to instead
put 2 mins worth of inserts inside a transaction/commit block so
that now data only gets committed every 2 mins. What I observe
though is that now a journal file gets created during the time
transaction/commit block is active and I would assume now all
my 3 second updates are going into the journal file. If that is true it
means I am still writing to the NAND every 3 seconds through the
journal file even though the main DB is only getting synced
with the journal every 2 mins.

Is this understanding correct? If yes, is there a way to actually
reduce the number of writes happening to the NAND flash such
that I can save it from quick wear out? If no, how is the writes
to the main DB different from the writes to the journal?

Thanks
Pankaj
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Richard Hipp-3]

Use WAL mode.  Set PRAGMA synchronous=NORMAL.  Do transactions that last 2
minutes each, starting a new transaction after each COMMIT.  Run checkpoints
in a background thread.

On Mon, Aug 30, 2010 at 6:29 AM, Pankaj Chawla <[hidden email]> wrote:

> Hi
>
> I have a Sqlite db on an embedded device on which there are
> inserts happening at a rate of 1 insert every 3 seconds. That
> being the case if Sqlite does file close/sync every 3 seconds
> it is going to wear off the NAND in no time. I tried to instead
> put 2 mins worth of inserts inside a transaction/commit block so
> that now data only gets committed every 2 mins. What I observe
> though is that now a journal file gets created during the time
> transaction/commit block is active and I would assume now all
> my 3 second updates are going into the journal file. If that is true it
> means I am still writing to the NAND every 3 seconds through the
> journal file even though the main DB is only getting synced
> with the journal every 2 mins.
>
> Is this understanding correct? If yes, is there a way to actually
> reduce the number of writes happening to the NAND flash such
> that I can save it from quick wear out? If no, how is the writes
> to the main DB different from the writes to the journal?
>
> Thanks
> Pankaj
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Pankaj Chawla]

HI Michael,

Thanks for the reply. Wont keeping things in memory lead to chances of
db getting corrupt especially in cases of power failure or device reboots.
I am not sure but since Sqlite is now used so frequently in embedded devices
and most devices use flash memories how are these situations mitigated.
Are there are papers/best practices available that can help. It seems to be
that if we try to reduce NAND writes by doing things in memory we lose on
reliability and the way to increase reliability is to do frequent writes. Is
that
a correct understanding?

Thanks
Pankaj

On Mon, Aug 30, 2010 at 5:15 PM, Black, Michael (IS) <[hidden email]
> wrote:

> Can you keep your database in-memory and just copy it to your flash
> periodically?  That would give you the most control and minimize the # of
> writes as much as you want.  It's good you recognize that you'll burn your
> flash out quickly with what you're doing now.
>
> There's also the -DSQLITE_TEMP_STORE option which keeps temporary files
> in-memory.
> You probably want SQLITE_TEMP_STORE=3 during compilation.
>
> ** This function returns true if main-memory should be used instead of
> ** a temporary file for transient pager files and statement journals.
> ** The value returned depends on the value of db->temp_store (runtime
> ** parameter) and the compile time value of SQLITE_TEMP_STORE. The
> ** following table describes the relationship between these two values
> ** and this functions return value.
> **
> **   SQLITE_TEMP_STORE     db->temp_store     Location of temporary
> database
> **   -----------------     --------------
> ------------------------------
> **   0                     any                file      (return 0)
> **   1                     1                  file      (return 0)
> **   1                     2                  memory    (return 1)
> **   1                     0                  file      (return 0)
> **   2                     1                  file      (return 0)
> **   2                     2                  memory    (return 1)
> **   2                     0                  memory    (return 1)
> **   3                     any                memory    (return 1)
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> ________________________________
>
> From: [hidden email] on behalf of Pankaj Chawla
> Sent: Mon 8/30/2010 5:29 AM
> To: [hidden email]
> Subject: EXTERNAL:[sqlite] Sqlite on NAND flash devices...
>
>
>
> Hi
>
> I have a Sqlite db on an embedded device on which there are
> inserts happening at a rate of 1 insert every 3 seconds. That
> being the case if Sqlite does file close/sync every 3 seconds
> it is going to wear off the NAND in no time. I tried to instead
> put 2 mins worth of inserts inside a transaction/commit block so
> that now data only gets committed every 2 mins. What I observe
> though is that now a journal file gets created during the time
> transaction/commit block is active and I would assume now all
> my 3 second updates are going into the journal file. If that is true it
> means I am still writing to the NAND every 3 seconds through the
> journal file even though the main DB is only getting synced
> with the journal every 2 mins.
>
> Is this understanding correct? If yes, is there a way to actually
> reduce the number of writes happening to the NAND flash such
> that I can save it from quick wear out? If no, how is the writes
> to the main DB different from the writes to the journal?
>
> Thanks
> Pankaj
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Black, Michael (IS)]

WAL mode still writes the journal to disk.
 
Perhaps combining this with SQLITE_TEMP_STORE=3 would be the best of all worlds?
 
Then his current situation of writing a 2-minute transaction should work just fine and minimize the # of writes.
 
 
 
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 

________________________________

From: [hidden email] on behalf of Richard Hipp
Sent: Mon 8/30/2010 7:20 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] Sqlite on NAND flash devices...



Use WAL mode.  Set PRAGMA synchronous=NORMAL.  Do transactions that last 2
minutes each, starting a new transaction after each COMMIT.  Run checkpoints
in a background thread.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Black, Michael (IS)]

Well...yeh...but you were complaining about the logs being written to your flash...you gotta' pick your poison.
 
If you keep only the temporary files in memory you should be OK.  That's what the compilation flag is for.
 
Keeping your entire database in memory is probably on an option if it's small.
 
Do you grow your database forever?  Or is there some limit to what you're doing I assume?
 
If you're happy with the 660 writes-per-day from your 2-minute transaction than the previous solutions (WAL, synchrnous, -DSQLITE_TEMP_STORE=3) should make you happy.  You'll only stand to lose the 2-minute transaction which is where you were when you started this thread.
 
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 

________________________________

From: [hidden email] on behalf of Pankaj Chawla
Sent: Mon 8/30/2010 7:21 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] Sqlite on NAND flash devices...



HI Michael,

Thanks for the reply. Wont keeping things in memory lead to chances of
db getting corrupt especially in cases of power failure or device reboots.
I am not sure but since Sqlite is now used so frequently in embedded devices
and most devices use flash memories how are these situations mitigated.
Are there are papers/best practices available that can help. It seems to be
that if we try to reduce NAND writes by doing things in memory we lose on
reliability and the way to increase reliability is to do frequent writes. Is
that
a correct understanding?

Thanks
Pankaj




_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Max Vlasov]

On Mon, Aug 30, 2010 at 4:21 PM, Pankaj Chawla <[hidden email]> wrote:

>
> Thanks for the reply. Wont keeping things in memory lead to chances of
> db getting corrupt especially in cases of power failure or device reboots.
>

You have to decide what you want. The problem is if you don't want to be
anything written to the storage until 2 minutes passed, you won't be able to
restore this 'anything' if the power is lost. Or you don't care about
restoring and just want to be sure the db won't be corrupted? If the latter
the temporal storage and appending it to persistent storage every 2 minutes
is ok since if it's a transaction, sqlite will take care of the power loss
consequences regardless of whether it's a single insert or multiply from a
temporal table

Max
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Pankaj Chawla]

Yes, I guess I am trying to figure out which poison to pick :-)
So here is what I need:

1. Lowest possible writes to the NAND.
2. Highest possible database reliability in situations of power
outage/reboots
    since the device is unmonitored.
3. Am ok with losing 2-5 minutes of data as long as 2 is not compromised.

I think keeping temp files in memory is a good idea but i was concerned
as I read at a few places that if  rollback journals are kept in memory then
on power outage
you not only lose the 2 minutes data but can have a corrupt DB in hand as
there
is no journal to lookup to for rollbacks. Is that correct?

It seems WAL with SYNC=NORMAL and SQLITE_TEMP_STORE=3 is the right way
to go as it will always maintain my DB in good state even if I lose 2
minutes of
data.

Thanks all for the quick help,
Pankaj



On Mon, Aug 30, 2010 at 6:20 PM, Black, Michael (IS) <[hidden email]
> wrote:

> Well...yeh...but you were complaining about the logs being written to your
> flash...you gotta' pick your poison.
>
> If you keep only the temporary files in memory you should be OK.  That's
> what the compilation flag is for.
>
> Keeping your entire database in memory is probably on an option if it's
> small.
>
> Do you grow your database forever?  Or is there some limit to what you're
> doing I assume?
>
> If you're happy with the 660 writes-per-day from your 2-minute transaction
> than the previous solutions (WAL, synchrnous, -DSQLITE_TEMP_STORE=3) should
> make you happy.  You'll only stand to lose the 2-minute transaction which is
> where you were when you started this thread.
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> ________________________________
>
> From: [hidden email] on behalf of Pankaj Chawla
> Sent: Mon 8/30/2010 7:21 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] Sqlite on NAND flash devices...
>
>
>
> HI Michael,
>
> Thanks for the reply. Wont keeping things in memory lead to chances of
> db getting corrupt especially in cases of power failure or device reboots.
> I am not sure but since Sqlite is now used so frequently in embedded
> devices
> and most devices use flash memories how are these situations mitigated.
> Are there are papers/best practices available that can help. It seems to be
> that if we try to reduce NAND writes by doing things in memory we lose on
> reliability and the way to increase reliability is to do frequent writes.
> Is
> that
> a correct understanding?
>
> Thanks
> Pankaj
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Max Vlasov]

On Mon, Aug 30, 2010 at 5:34 PM, Pankaj Chawla <[hidden email]> wrote:

> I think keeping temp files in memory is a good idea but i was concerned
> as I read at a few places that if  rollback journals are kept in memory
> then
> on power outage
> you not only lose the 2 minutes data but can have a corrupt DB in hand as
> there
> is no journal to lookup to for rollbacks. Is that correct?
>
>
I don't know what was the scenario Michael had in mind, but I'd create a
temporary table, fill it with data (there will be no single change to the
main db ) then after 2 minutues work perform INSERT INTO MyMainTable SELECT
* FROM MyTempTable. So this is the query that actually affects the contents
of the main db (and writes also to the journal file). This one can be
interrupted with a power failure and in this case on a next open sqlite will
try to restore the state that was before the transaction started

Max
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Pankaj Chawla]

Michael,

I just read through the Sqlite documentation and it seems rollback and
master journal
are always written to the disk. Below is the snippet from Section 3.0 on
http://www.sqlite.org/tempfiles.html#tempstore :

The rollback journal, master journal, and statement journal
files are always written to disk. But the other kinds of temporary
files might be stored in memory only and never written to disk.
Whether or not temporary files other than the rollback, master,
and statement journals are written to disk or stored only in
memory depends on the
SQLITE_TEMP_STORE<http://www.sqlite.org/compile.html#temp_store>
 compile-time
parameter, the temp_store
pragma<http://www.sqlite.org/pragma.html#pragma_temp_store>,
and on the size of the
temporary file.

Since it doesnt mention WAL files, I am not sure how they are managed. I
think this
leaves the only option of keeping all the 2 minute data in memory and then
create
a single transaction every 2 minutes to get it into the DB in one shot.

Thanks
Pankaj

On Mon, Aug 30, 2010 at 6:20 PM, Black, Michael (IS) <[hidden email]
> wrote:

> Well...yeh...but you were complaining about the logs being written to your
> flash...you gotta' pick your poison.
>
> If you keep only the temporary files in memory you should be OK.  That's
> what the compilation flag is for.
>
> Keeping your entire database in memory is probably on an option if it's
> small.
>
> Do you grow your database forever?  Or is there some limit to what you're
> doing I assume?
>
> If you're happy with the 660 writes-per-day from your 2-minute transaction
> than the previous solutions (WAL, synchrnous, -DSQLITE_TEMP_STORE=3) should
> make you happy.  You'll only stand to lose the 2-minute transaction which is
> where you were when you started this thread.
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> ________________________________
>
> From: [hidden email] on behalf of Pankaj Chawla
> Sent: Mon 8/30/2010 7:21 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] Sqlite on NAND flash devices...
>
>
>
> HI Michael,
>
> Thanks for the reply. Wont keeping things in memory lead to chances of
> db getting corrupt especially in cases of power failure or device reboots.
> I am not sure but since Sqlite is now used so frequently in embedded
> devices
> and most devices use flash memories how are these situations mitigated.
> Are there are papers/best practices available that can help. It seems to be
> that if we try to reduce NAND writes by doing things in memory we lose on
> reliability and the way to increase reliability is to do frequent writes.
> Is
> that
> a correct understanding?
>
> Thanks
> Pankaj
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Pankaj Chawla]

>
>
> I don't know what was the scenario Michael had in mind, but I'd create a
> temporary table, fill it with data (there will be no single change to the
> main db ) then after 2 minutues work perform INSERT INTO MyMainTable SELECT
> * FROM MyTempTable. So this is the query that actually affects the contents
> of the main db (and writes also to the journal file). This one can be
> interrupted with a power failure and in this case on a next open sqlite
> will
> try to restore the state that was before the transaction started
>
> Max
>
> Yes, it seems I should just leave Sqlite the way it is and instead store

the 2 min
data in temporary tables and then insert it in one go every 2 minutes.

Thanks
Pankaj
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin-3]

On 30 Aug 2010, at 2:34pm, Pankaj Chawla wrote:

> Yes, I guess I am trying to figure out which poison to pick :-)
> So here is what I need:
>
> 1. Lowest possible writes to the NAND.
> 2. Highest possible database reliability in situations of power
> outage/reboots
>    since the device is unmonitored.
> 3. Am ok with losing 2-5 minutes of data as long as 2 is not compromised.

Do not mess with any PRAGMAs or compilation options at all.
Do all your operations with a database held in :memory:

http://www.sqlite.org/inmemorydb.html

Every two minutes (background timer ?), update the database held in NAND, using one big transaction.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users