Places:FsyncApproach
Problem
Below are potential solutions that were discussed on an email thread between Mozilla, Linux and SQLite developers.
Partitioning Approach
Summary from Dr. Hipp:
Presumably you have some large tables in your main database file that you consult frequently and update occasionally. I'm suggesting splitting each table in two. The long-term persistent data is stored in the persistent database. Recent changes are stored in a separate TEMP tables. As I pointed out earlier, TEMP tables do not use fsync. So you can write to temp tables as much as you want without getting into fsync problems. But if you put your changes in TEMP tables, that means whenever you query the database, you have to do a more complex query that pulls in information from the main table and also overlays any changes or additions from TEMP tables. [sic] Then you have a separate thread that every minute or so transfers all of the accumulated information in the TEMP tables over to the main persistent database as a single transaction. That gives you a total of 2 or 3 fsyncs per minute, which even the most broken file system should be able to handle with ease.
Pros:
Cons:
Tasks:
- determine which table(s) need to be partitioned
- initialize temp table(s)
- modify all queries that interact w/ the partitioned table(s)
- periodic flush of temp to permanent table (ensuring pk sync)
Long Open Transaction Approach
Summary from Shaver:
the long-open-transaction with forced commit for a bookmark addition or other thing needing durability
From Shaver's friend:
... don't have sqlite do a sync transaction each time, instead use batching of transactions at the sqlite level. I suspect you aren't SO desparate
that every last page visit is saved in case of a crash, but rather just want to avoid corrupting the database. This can make a huge difference for sqlite performance, and can amortize the sync overhead over many operations. This will still allow sqlite to be ACID but only periodically doing flushes to disk (you would probably close and re-open transactions
for N bytes/pages/seconds).
Pros:
Cons:
Split Database Approach
From Shaver:
We could also just use two databases, one for history (with sync=off) and one for bookmarks (with sync=normal) and query against them both. Then you would get an fsync/s_f_r only when you updated a bookmark, and not during normal browser operation, and we would not be risking loss of bookmark data during crash.
Pros:
Cons: