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 |
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 |
In reply to this post by Pankaj Chawla
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 |
In reply to this post by Black, Michael (IS)
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 |
In reply to this post by Richard Hipp-3
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 |
In reply to this post by Pankaj Chawla
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 |
In reply to this post by Pankaj Chawla
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 |
In reply to this post by Black, Michael (IS)
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 |
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 |
In reply to this post by Black, Michael (IS)
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 |
In reply to this post by Max Vlasov
>
the 2 min
> > 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 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 |
In reply to this post by Pankaj Chawla
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 |
Free forum by Nabble | Edit this page |