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:
- Temp tables still go out to files, which means writes, which means we can still hit painful fsync fun on ext3 and similar file systems. This can be fixed by moving to in memory temporary tables.
- Adds significant complexity to queries. However, this can be mitigated greatly by using VIEWs that join the temporary table with the permanent one. VIEWs cannot be written to though, so we'll either have to figure out which table to update (which can cause writes if it is the permanent table) or copy the data into the temp table. For the latter solution, when we sync the temporary table we would have to use INSERT OR REPLACE to update the permanent table. I'm not sure how this will effect UNION statements however (ordering may matter here - temp table first? UNION drops duplicated data as far as I know).
- Have to track primary key max index ourself for inserts, since the temp table won't be able to AUTOINCREMENT like the main table. We can mitigate this by having sqlite start at a certain value. See this e-mail thread and this page for more details.
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: