Re: [sqlite] sqlite on flash devices
We're using linux.
Data loss is not critical. If anything is lost due to unexpected power
downs, we can rebuild it and add it to the database at a later stage.
Apart from using :memory: another idea is to use linux ramfs and
periodically copy this to flash.
The other thing I'm assuming is to compile SQLITE with
-DSQLITE_TEMP_STORE=3 to keep sqlite temporary files in memory.
-
-Paul
On Thu, 2008-10-02 at 13:36 -0600, J Glassy wrote:
> Paul,
> here are a few odd thoughts on this:
> --Flash memory devices in general are subject to 'finite' numbers of
> I/O events, mostly affecting writes; more recent flash memory devices
> ease this constraint a bit, if only by supporting more I/O events
> before they hit their useful life-limit. I think you're asking the
> right questions though; can you model the likely no. of writes over
> the intended life-cycle based on anticipated activity etc?
> --Some of how you implement this depends on what embedded OS you're
> working with (is it a RTOS, a Linux hybrid, WinCE, Win Mobiles 5 or
> 6.x etc?. I'd want to make sure I could implement the software that
> manages the Sqlite as a daemon or background-service, for better
> flexibility.
> --Your idea of posting transactions from near-real-time event traffic
> to an in-memory definition (":memory:") may be good, certainly bears
> some study. It complicates how you might implement periodic handoffs
> from the ":memory:" instance to the non-volatile 'disk' instance of
> sqlite though, particularily when a serious exception (say, something
> really severe like a power-outage) might need to be trapped almost
> instantaneously. This is coincidentally (precisely) when you may need
> assurance that the most-recent data is safely stored on the
> non-volatile side of the embedded system. This in turn raises the
> question of your assessment on how much data can you afford to lose?
> (if any) a metric that governs how often you schedule your
> synchronization-transactions; I'm guessing you'll use a delta-oriented
> sync logic to avoid messy redundancy.
> --To try to address your question on how intimately you might need to
> know the insides of sqlite3, you can get away with knowing less in
> proportion to how closely your implementation strategy resembles a
> standard use-case (writing to 'disk'); the more multi-tier mechanism
> you decide to support, the more you'll benefit from knowing sqlite
> better on the inside (IMHO); at least you can study the code anytime
> etc.
> --Overall, always writing to the flash memory as a default strategy
> would be safer, simpler.. presuming you can afford the amount of time
> the I/O takes (some SD devices are notoriously slow at write-side I/O,
> so watch that). I'm assuming that each transaction is fairly small
> and atomic. I would prototype any of this using Python's binding to
> sqlite, to give yourself a good feel for the dynamics.
> --My own experience in this sort of implementation is using SyBASE
> Anywhere's mobile database,(along with MobiLink for device-to-host
> synchronization) on WinCE,Win Mobile 5 (not yet v6). I've used sqlite
> primarily on several non-embedded application area, but am looking
> hard myself at just the questions you are addressing.
>
> I don't if any of this helped.. but good luck.
>
> joe
>
> On Thu, Oct 2, 2008 at 2:20 AM, Paul McMahon <[EMAIL PROTECTED]>
> wrote:
> What's the consensus on using sqlite with flash based storage
> such as sd
> cards on embedded devices?
>
> Has anyone done this successfully yet with a real product
> (that needs to
> last a few years in the field)?
>
> How does one solve the problem of minimizing writes to flash?
> I've done
> a search through the user lists but there seems to be no
> generally
> agreed way to do this. Does it require intimate knowledge of
> the inner
> workings of sqlite?
>
> One scheme I thought of was to use an in memory sqlite
> database in
> conjunction with a flash based one.
>
> New entries go in the in memory database (using ":memory:" on
> filename).
> Searches search both the in memory and flash based database.
> Every now
> and then the in memory database entries get written to the
> flash based
> database.
>
> Any ideas would be greatly appreciated!
>
>
>
> ___
> 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] sqlite on flash devices
I was thinking of setting up similar experiments; nice idea hooking the MTD layer to count erase cycles.. I'm going to keep 1st implementation simple and just use sqlite direct to flash, no memory cache. Then I can measure the performance.. We should be using a flash controller that does wear levelling which of course will prolong life. - -Paul On Thu, 2008-10-02 at 17:44 -0400, Matthew L. Creech wrote: > On Thu, Oct 2, 2008 at 4:20 AM, Paul McMahon <[EMAIL PROTECTED]> wrote: > > What's the consensus on using sqlite with flash based storage such as sd > > cards on embedded devices? > > > > Has anyone done this successfully yet with a real product (that needs to > > last a few years in the field)? > > > > We've got several (Linux-based) embedded products using SQLite > databases. One has an M-Systems SoC with Ext3 on top of TFFS, another > uses CompactFlash with Ext3, and yet another uses a directly-addressed > NOR chip with JFFS2. None have had any problems related to SQLite. > > How long it lasts depends (obviously) on the amount of data you're > pushing through on average. For the NOR device, I implemented a test > system with SQLite, set up a simulator to model worst-case conditions > for the device so that it was writing to the database about as much as > it ever would, and hooked the MTD layer to count the number of erased > blocks over a pre-defined period of time. You can then use that rate > and the size of the partition to figure out how long it'll take to hit > e.g. 100k erase cycles over the whole device. For SD you don't know > how well it's doing wear-leveling behind the scenes, so throw in a > multiplier to account for that, and you'll at least get an > order-of-magnitude accurate idea of how long it'll last worst-case. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite on flash devices
I thikn you mean supplying a sqlite3_vfs to the sqlite3_open_v2 call; will look into it. Anyone know where there are examples of this? - -Paul On Fri, 2008-10-03 at 01:03 +0400, Alexey Pechnikov wrote: > Hello! > > I'm using SQLIte on a few dozens winmobile devices a few years. It's work > fine > with standart FS. If you want to try raw access to flash you can find > expiremental VFS code for flash and adopt this code for you. I don't remember > where I did see this code. > > Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite on flash devices
-- -Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite on flash devices
On Thu, Oct 2, 2008 at 4:20 AM, Paul McMahon <[EMAIL PROTECTED]> wrote: > What's the consensus on using sqlite with flash based storage such as sd > cards on embedded devices? > > Has anyone done this successfully yet with a real product (that needs to > last a few years in the field)? > We've got several (Linux-based) embedded products using SQLite databases. One has an M-Systems SoC with Ext3 on top of TFFS, another uses CompactFlash with Ext3, and yet another uses a directly-addressed NOR chip with JFFS2. None have had any problems related to SQLite. How long it lasts depends (obviously) on the amount of data you're pushing through on average. For the NOR device, I implemented a test system with SQLite, set up a simulator to model worst-case conditions for the device so that it was writing to the database about as much as it ever would, and hooked the MTD layer to count the number of erased blocks over a pre-defined period of time. You can then use that rate and the size of the partition to figure out how long it'll take to hit e.g. 100k erase cycles over the whole device. For SD you don't know how well it's doing wear-leveling behind the scenes, so throw in a multiplier to account for that, and you'll at least get an order-of-magnitude accurate idea of how long it'll last worst-case. -- Matthew L. Creech ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite on flash devices
Hello! I'm using SQLIte on a few dozens winmobile devices a few years. It's work fine with standart FS. If you want to try raw access to flash you can find expiremental VFS code for flash and adopt this code for you. I don't remember where I did see this code. Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite on flash devices
Paul,
here are a few odd thoughts on this:
--Flash memory devices in general are subject to 'finite' numbers of I/O
events, mostly affecting writes; more recent flash memory devices ease this
constraint a bit, if only by supporting more I/O events before they hit
their useful life-limit. I think you're asking the right questions though;
can you model the likely no. of writes over the intended life-cycle based on
anticipated activity etc?
--Some of how you implement this depends on what embedded OS you're working
with (is it a RTOS, a Linux hybrid, WinCE, Win Mobiles 5 or 6.x etc?. I'd
want to make sure I could implement the software that manages the Sqlite as
a daemon or background-service, for better flexibility.
--Your idea of posting transactions from near-real-time event traffic to an
in-memory definition (":memory:") may be good, certainly bears some study.
It complicates how you might implement periodic handoffs from the ":memory:"
instance to the non-volatile 'disk' instance of sqlite though, particularily
when a serious exception (say, something really severe like a power-outage)
might need to be trapped almost instantaneously. This is coincidentally
(precisely) when you may need assurance that the most-recent data is safely
stored on the non-volatile side of the embedded system. This in turn raises
the question of your assessment on how much data can you afford to lose? (if
any) a metric that governs how often you schedule your
synchronization-transactions; I'm guessing you'll use a delta-oriented sync
logic to avoid messy redundancy.
--To try to address your question on how intimately you might need to know
the insides of sqlite3, you can get away with knowing less in proportion to
how closely your implementation strategy resembles a standard use-case
(writing to 'disk'); the more multi-tier mechanism you decide to support,
the more you'll benefit from knowing sqlite better on the inside (IMHO); at
least you can study the code anytime etc.
--Overall, always writing to the flash memory as a default strategy would be
safer, simpler.. presuming you can afford the amount of time the I/O takes
(some SD devices are notoriously slow at write-side I/O, so watch that).
I'm assuming that each transaction is fairly small and atomic. I would
prototype any of this using Python's binding to sqlite, to give yourself a
good feel for the dynamics.
--My own experience in this sort of implementation is using SyBASE
Anywhere's mobile database,(along with MobiLink for device-to-host
synchronization) on WinCE,Win Mobile 5 (not yet v6). I've used sqlite
primarily on several non-embedded application area, but am looking hard
myself at just the questions you are addressing.
I don't if any of this helped.. but good luck.
joe
On Thu, Oct 2, 2008 at 2:20 AM, Paul McMahon <[EMAIL PROTECTED]> wrote:
> What's the consensus on using sqlite with flash based storage such as sd
> cards on embedded devices?
>
> Has anyone done this successfully yet with a real product (that needs to
> last a few years in the field)?
>
> How does one solve the problem of minimizing writes to flash? I've done
> a search through the user lists but there seems to be no generally
> agreed way to do this. Does it require intimate knowledge of the inner
> workings of sqlite?
>
> One scheme I thought of was to use an in memory sqlite database in
> conjunction with a flash based one.
>
> New entries go in the in memory database (using ":memory:" on filename).
> Searches search both the in memory and flash based database. Every now
> and then the in memory database entries get written to the flash based
> database.
>
> Any ideas would be greatly appreciated!
>
>
>
> ___
> 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
[sqlite] sqlite on flash devices
What's the consensus on using sqlite with flash based storage such as sd cards on embedded devices? Has anyone done this successfully yet with a real product (that needs to last a few years in the field)? How does one solve the problem of minimizing writes to flash? I've done a search through the user lists but there seems to be no generally agreed way to do this. Does it require intimate knowledge of the inner workings of sqlite? One scheme I thought of was to use an in memory sqlite database in conjunction with a flash based one. New entries go in the in memory database (using ":memory:" on filename). Searches search both the in memory and flash based database. Every now and then the in memory database entries get written to the flash based database. Any ideas would be greatly appreciated! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
