Hello,

A client, despite pointing out the dangers, wishes to run an embedded Linux device (featuring an SQLite3 database) from an SD card / USB memory stick. We're trying to gauge/ estimate the lifespan of the memory device in this scenario & if possible prolong it.

I had thought a possible strategy would be wait for say, 50 logging events, aggregate/ clump them & write to the database in one transaction. (I appreciate that a power loss would result in loss of uncommitted transactions.) Does this seem valid reasoning? Would a deferred transaction accomplish the same thing?

It appears that using 'PRAGMA temp_store = MEMORY' will help minimize reads & writes to disk. Are there other methods?

Is it possible to estimate/ detect/ inspect how many writes occur via a SQLite method? If not I'll try looking at command line disk monitoring tools

Many thanks,

Owen

Not an answer, just a data point:

I have a cheap 8GB USB memory stick plugged into a RaspberryPI on my desk. On that memory stick are 18 Fossil repositories, which are really just SQLite databases, totally a little over 1GB of storage. There is a cron job on the Pi that runs a "sync" on all 18 repos once an hour. It has been doing that, without issue, for a long time.

I too was wondering how long a dime-store USB stick would hold up under these circumstances, which is one reason why I'm running the experiment. The memory stick has so far held up better than I expected.

Is it possible to estimate/ detect/ inspect how many writes occur via a SQLite method?

This VFS shim is exactly or very close to what you need.

You'll have to get the shim inserted into the VFS stack to get the reporting you seek. There is no "SQLite method" per se to do so.

Thanks for the response.

Do you think my thoughts on aggregating writes to disk are valid?

From what I see, I think your thinking on this is progressing in the right direction. (I would not quite say "valid" until it reaches a more definitive and useful stage.)

The way I see it, to really minimize needless rewriting of the flash memory, you need to avoid "premature" "partial" writes of its pages. By "partial", I mean writes that change less than a whole page. By "premature", I mean those which are soon to be followed by a whole or "final partial" write of the same page.

That avoidance seems like a hard problem, one whose solution is unlikely to be demonstrably ideal and will depend upon your particular usage patterns. The trade between losing data not-yet-written and deferring writes must be made with those competing values weighed appropriately to your (or your customers') situation(s).

You may want to adjust the DB page size to match your device's intrinsic page size^a.

I find it hard to imagine how one might engineer a solution without some instrumentation to see what is happening without one or with a potentially suitable one.

a. Flash memory integrated circuits normally have a minimum block size for rewrite operations. They may be able write into block-erased cells in smaller chunks than what must be rewritten when a used block is modified, because per-cell erase is usually not available.

BTW, I did something similar years ago, not involving a DB though. It ate memory sticks at a rate of several per week until I learned to mount with the -noatime option. Richard may have gotten lucky if his long-lived memory stick actually came from the dime store (or its modern equivalent.)

Do you have enough memory to hold a complete copy of the database in memory while the program is running ? If so,

1. On startup open the database, set journal_mode for it to OFF, and copy it into memory. (Can one use VACUUM INTO for this ? What's a good method ?)
2. Make a zero-length string.
3. Use the memory copy of the database while the program is running.
4. Every time a change is made to the database (INSERT/UPDATE/DELETE) make the change as expected but also append the SQL command +';' to your string.
5. When you want to update the Flash copy of the database (obviously when your program quits but perhaps also every 5 minutes until then) use sqlite3_exec() on the string to update the Flash copy of the database, then blank out the string again.

Step 5 should flood the drive with a lot of writes. A modern Flash drive will take the opportunity to optimize all these writes, ignoring sector-writes which are later made obsolete, and reordering writes to minimise Flash wear.

Will this actually make any difference ? Is it a good solution to your problem ? It depends on your OS, FS, Flash Drive, and usage patterns. I couldn't possibly guess. But it is an efficient use of standard SQLite which won't require any recompilation, special settings, or programming of extra SQLite parts. And it will continue to work on future versions of SQLite without any attention.

Simon,

Many thanks for the suggestion.

Sounds like a great solution for some cases but for the greatest issue I see is that one table in the db will be used to record events.

Its planned for this table to hold multiple thousands of events (basically just a timestamp) and then periodically/ daily prune/ reduce the table back to a max size. I think to accomplish this I'll need to use vacuum which may be costly in terms of writes to the SD card/ USB memory stick.

Larry, your proposal to modify the db page size is also interesting but I fear that the customer may simply substitute their own card/ memory stick in time & so this efficiency will be lost..

You should "prune" old entries every time you do an insert, in the same transaction and commit the insert and delete at the same time (but perform the delete's before the insert's). The database will become a stable size.

Thanks for the suggestion

You should not routinely use VACUUM for a file stored in Flash. VACUUM does a lot of file reading, re-writing and deleting. I can't think of a faster or more efficient way to 'wear out' Flash memory than repeatedly copying than deleting data. VACUUM thrashes the storage medium for three benefits:

• it may 'uncorrupt' a corrupt database
• It rewrites rows in primary key order, which may speed up reading on storage subsystems which store files in sequential order
• It may shorten the file

If the first thing you're going to do after VACUUM is INSERT more data, then there's no point in doing the last of those. You will have performed two pointless operations: release filespace to the 'unused' pool, then grab space from the unused pool and add it to the database file.

SQLite is efficient at reusing unused filespace. Just use DELETE FROM and UPDATE on your data and let SQLite salvage reuse free space as it wants to. You might want to use VACUUM perhaps once a year or once a quarter. But I've had databases which have never been VACUUMed and they had tiny amounts of wasted space.

Simon, Thanks, yes I thought it would be costly in terms of reads & writes.