590
edits
Places:FsyncApproach: Difference between revisions
Jump to navigation
Jump to search
→Long Open Transaction Approach: Updated cons and swag
No edit summary |
Comrade693 (talk | contribs) (→Long Open Transaction Approach: Updated cons and swag) |
||
| (15 intermediate revisions by 3 users not shown) | |||
| Line 11: | Line 11: | ||
Pros: | Pros: | ||
* Temp tables are in-memory, so there are no filesystem writes when inserting into the temp table. | |||
Cons: | Cons: | ||
* 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 [http://www.mail-archive.com/sqlite-users@sqlite.org/msg35131.html this e-mail thread] and [http://sqlite.org/autoinc.html this page] for more details. | |||
* How do we update existing data? [http://www.mail-archive.com/sqlite-users@sqlite.org/msg35184.html discussion list question] | |||
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) | |||
swag: 3+ weeks | |||
=Long Open Transaction Approach= | =Long Open Transaction Approach= | ||
| Line 29: | Line 41: | ||
Pros: | Pros: | ||
* Less overall fsycns | |||
* Easy (almost painless) | |||
Cons: | Cons: | ||
* Can lose data if we crash with an open transaction | |||
* Still involves writes to disk | |||
* If anyone else (non-places) fsyncs, we still lose | |||
* Can no longer make a group of operations atomic with transactions (no nested transaction support). | |||
swag: 1-2 weeks | |||
=Split Database Approach= | =Split Database Approach= | ||
| Line 38: | Line 58: | ||
Pros: | Pros: | ||
* The common task of adding a history visit is cheaper | |||
Cons: | Cons: | ||
* Likely false assumption that history is less important than bookmarks (smart location bar changed this) | |||
* Tight coupling between the two databases | |||
* New history visits require a write to both (likely) | |||
swag: 1-2+ months | |||
=Grow File in Large Hunks Approach= | |||
Summary from Andrew Morton: | |||
<blockquote> | |||
If the file does need to grow during regular operation then I'd suggest | |||
that it be grown in "large" hunks - say, extend it (with write()) by a | |||
megabyte at a time. Then fsync it to get the metadata written. Then | |||
proceed to use sync_file_range() for the now-non-extending small writes. | |||
So the large-write-plus-fsync is "rare". | |||
</blockquote> | |||
Pros: | |||
* Can reasonably easily be implemented in vfs sqlite3_io_methods. | |||
Cons: | |||
* Only a solution for Linux. | |||
* Still waits on ([https://bugzilla.mozilla.org/show_bug.cgi?id=421482#c152 less significant disk access]) regularly, and so may need to be combined with async IO or another approach for optimal behavior. | |||
swag: 2 weeks | |||
=Journaling Virtual Filesystem Approach= | |||
Summary from Karl: | |||
<blockquote> | |||
By implementing a virtual filesystem with data journaling, the vfs can sync | |||
and empty its journal when it chooses. The filesystem would satisfy the | |||
SQLITE_IOCAP_SEQUENTIAL characteristics and so the database would remain | |||
consistent even when it has synchronous == OFF. | |||
</blockquote> | |||
<blockquote> | |||
Such a virtual filesystem implemented using sqlite3_io_methods could log | |||
changes immediately to its own replay journal file on the underlying | |||
filesystem, and maintain a lookup table of the pages that have not yet been | |||
written to their specific real files. | |||
</blockquote> | |||
<blockquote> | |||
Pages in the journal would need to be occasionally synced | |||
(possibly with sync_file_range), and then copied to their real files. The | |||
pages in the real files would need to be synced before their corresponding | |||
journal pages become free to be reused. In a single-thread implementation, | |||
this could be done when the lookup table is too large to maintain | |||
efficiently and then the journal can be emptied. In a background-thread | |||
implementation the journal could be a ring buffer and all syncing would | |||
take place on the background thread. | |||
</blockquote> | |||
<blockquote> | |||
(This is related to a | |||
[http://www.mail-archive.com/sqlite-users@sqlite.org/msg35212.html replay journal discussion].) | |||
</blockquote> | |||
Pros | |||
* All databases could benefit without requiring individual code changes. | |||
Cons | |||
* More complicated than Grow File in Large Hunks Approach | |||
* Data is written twice and so there is twice as much IO. But this could be counter balanced by disabling sqlite's journal provided transaction completion can be detected by the vfs. | |||
swag: 2-3 months | |||