embedded linux, jffs2, SQLite, lock and backup strategy

[junping2000]

Hi, there,

This is going to be a bit long ...

We are thinking about making a product with embedded linux and
database capability. And for persistant data on the board, we want
to save the database on the flash (our board will not have moving
parts, i.e. IDE hard disk).

We also decided on using JFFS2 (Journal Flash File System 2), which
is quickly improving and gaining acceptance in embedded circle.

I started to play with SQLite a while back, and was very impressed
by the whole package: speed, simplicity, SQL, trigger, transaction
and good footprint, and endianess free...

But due to the limitation of JFFS2 (or any other flash file system),
I can't sit SQLite directly on top of it. I ran my test with SQLite
on PowerPC embedded linux, sometimes I got blocking time over a min ..

So my solution is to run SQLite on a ramdisk and periodically sync
the data to JFFS2. Now the question is "what's the most efficient
way of backing up data". The biggest concern here is the speed & wear
when using the flash file system. Here is what I come up with: I will
use a seperate process for this backup scheme. It will do a full copy
of SQLite first, then use shared memory between SQLite engine and my
backup process to intercept the "modifying" SQLs (update/insert/delete
statements) every 10 secs. That is, if there is any change to the DB
in this 10 secs, the backup process will write it to a file on JFFS2.
And if there are too many of this small files, it will start a full
backup and continue the 10-sec incrementals ...

Now if system has a power failure, I can use the full backup and all
the "incremental" backups from JFFS2 in SQL format to get to the DB
state within 10 secs of the crash. Keep in mind that SQLite's
transaction & journal won't help on a ramdisk, which is gone when
power is out.

This model will work for us because our system won't have a lot of
insert/update/delete ops (the estimate is 5 ops per minute). With
this, I don't have to do the full backup that often, which will speed
up the flash wear and slow down the system. And I found "intercepting"
SQL a much better way for backup than trying to figure out the DB
changes or non-change every 10 secs (using triggers?).

Here is my simplified intercept logic in SQLite engine:
1. create a piece of shared mem at the end of sqlite_open() and
detach/delete them at the end of sqlite_close()
2. at the beginning of sqlite_exec(), I save the zSql string to
a global var, say cur_sql (or in threaded version to a
thread-specific value).
3. at the end of sqlitepager_commit(), I copy cur_sql to shm. If
it gets here, it must be "insert" or "update" or "delete".
4. My backup process will use the same file lock, which SQLite
uses for transaction lock, for shm. It takes out SQLs (if there is
any) every 10 secs.

I just implemented this on linux, and it should work for all unices,
both for threaded and non-threaded applications. Even with THREADSAFE,
it's quite a small change.

My problem is hardly unique, so in the spirit of sharing ideas and
initiate a good discussion, I want to know if I am re-inventing the
wheels here or there are any better solutions. And if anyone is
interested in the patch and test program, just send me an email.

Take care

Junping Zhang
jzhang-***@public.gmane.org


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Get 128 Bit SSL Encryption!
http://us.click.yahoo.com/FpY02D/vN2EAA/xGHJAA/EbFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
sqlite-unsubscribe-***@public.gmane.org



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

[Jim Lyon]

Post by junping2000
Hi, there,
This is going to be a bit long ...
[snip]
So my solution is to run SQLite on a ramdisk and periodically sync
the data to JFFS2. Now the question is "what's the most efficient
way of backing up data". The biggest concern here is the speed & wear
when using the flash file system. Here is what I come up with: I will
use a seperate process for this backup scheme. It will do a full copy
of SQLite first, then use shared memory between SQLite engine and my
backup process to intercept the "modifying" SQLs

...

(update/insert/delete

Post by junping2000
statements) every 10 secs. That is, if there is any change to the DB
in this 10 secs, the backup process will write it to a file on

JFFS2.

Post by junping2000
And if there are too many of this small files, it will start a full
backup and continue the 10-sec incrementals ...
Now if system has a power failure, I can use the full backup and all
the "incremental" backups from JFFS2 in SQL format to get to the DB
state within 10 secs of the crash. Keep in mind that SQLite's
transaction & journal won't help on a ramdisk, which is gone when
power is out.

For what it's worth, and without looking at the code again, I would
have done this by working at the pager level. SQLite always knows
what pages in memory are dirty, and it always writes a page to memory
at one time.

There is a bitmask of dirty pages in the Pager struct, so you could
force saving at a finer granularity than 10s if you wanted to.

You can use pager_get_all_dirty_pages() to find out if there are any
pages that haven't been written to memory, for instance. And I would
have tried intercepting pager_write_pagelist(), or whatever is
responsible for saving changes to the database. This will also tell
you what pages have changed on the ramdisk version. You can then have
these spooled out to the flash on a separate thread.

How you would want to fix this to be safe in terms of power loss, I
don't know.

How efficient this is for you depends on the size of your updates.
Pages are 1024 bytes in SQLite, and if you are only updating a few of
them for each page changed, this would be less efficient. But storing
the SQL used to generate the changes can be expensive in terms of
space and writing.

Jim Lyon


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Get 128 Bit SSL Encryption!
http://us.click.yahoo.com/FpY02D/vN2EAA/xGHJAA/EbFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
sqlite-unsubscribe-***@public.gmane.org



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

[junping2000]

Post by junping2000

Post by junping2000
Hi, there,
This is going to be a bit long ...
[snip]
So my solution is to run SQLite on a ramdisk and periodically sync
the data to JFFS2. Now the question is "what's the most efficient
way of backing up data". The biggest concern here is the speed &

wear

Post by junping2000
when using the flash file system. Here is what I come up with: I

will

Post by junping2000
use a seperate process for this backup scheme. It will do a full

copy

Post by junping2000
of SQLite first, then use shared memory between SQLite engine and my
backup process to intercept the "modifying" SQLs

(update/insert/delete

Post by junping2000
statements) every 10 secs. That is, if there is any change to the

DB

Post by junping2000
in this 10 secs, the backup process will write it to a file on

JFFS2.

Post by junping2000
And if there are too many of this small files, it will start a full
backup and continue the 10-sec incrementals ...
Now if system has a power failure, I can use the full backup and

all

Post by junping2000
the "incremental" backups from JFFS2 in SQL format to get to the DB
state within 10 secs of the crash. Keep in mind that SQLite's
transaction & journal won't help on a ramdisk, which is gone when
power is out.

For what it's worth, and without looking at the code again, I would
have done this by working at the pager level. SQLite always knows
what pages in memory are dirty, and it always writes a page to memory
at one time.
There is a bitmask of dirty pages in the Pager struct, so you could
force saving at a finer granularity than 10s if you wanted to.

...

Thanks for the input.

Yeh, 10s is just my example. I could have finer granularity with
a semaphore in this case to trigger the backup for each change, but
the cost of each file structure in JFFS2 leads me to group changes
to a file instead of create a lot of small files. Another solution
is for the engine side check the number of SQLs in shm and wake up
the backup process if it reaches certain size, but we still have to
use an interval so we can tell the customer that they only lose at
most 10s of transactions.

Post by junping2000
You can use pager_get_all_dirty_pages() to find out if there are any
pages that haven't been written to memory, for instance. And I would
have tried intercepting pager_write_pagelist(), or whatever is
responsible for saving changes to the database. This will also tell
you what pages have changed on the ramdisk version. You can then have
these spooled out to the flash on a separate thread.

My SQLite version is from last Nov, I couldn't find those functions,
I will check out the new versions tonight.

The reason I used a seperate backup process and shared memory is that
it keeps the REAL happening sequence of SQLs. Considering I use the
same file lock to guard the shm, it should be fast too. Using a backup
thread to live in every process that uses SQlite engine, and thinking
about multiple process, critical sections, it's a bit more
complicated.

Post by junping2000
How you would want to fix this to be safe in terms of power loss, I
don't know.

JFFS2 is on flash and journaled, so FS will come back after a power
loss, guranteed. The worst case I lose a half-written backup file,
then I will resort the the previous one.

Post by junping2000
How efficient this is for you depends on the size of your updates.
Pages are 1024 bytes in SQLite, and if you are only updating a few of
them for each page changed, this would be less efficient. But storing
the SQL used to generate the changes can be expensive in terms of
space and writing.

You are right. I designed this for our current update pattern. If
the requirement changes, I have to find what's more efficient then.
Storing only the changes in binary form is the best, but it's too
costly to "pick" out the changes, we have to backup "pages" or SQLs.
One SQL can cause changes in multiple pages, I guess, so there is
no clear winner here. I probably have to read the code more ... :)


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Get 128 Bit SSL Encryption!
http://us.click.yahoo.com/FpY02D/vN2EAA/xGHJAA/EbFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
sqlite-unsubscribe-***@public.gmane.org



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/