Confirmed users, Bureaucrats and Sysops emeriti
2,088
edits
Places:FsyncApproach: Difference between revisions
no edit summary
(New page: =Problem= {{bug|421482}} =Partitioning Approach= Summary from Dr. Hipp: <blockquote>Presumably you have some large tables in your main database file that you consult frequently and upda...) |
No edit summary |
||
| Line 2: | Line 2: | ||
{{bug|421482}} | {{bug|421482}} | ||
Below are potential solutions that were discussed on an email thread between Mozilla, Linux and SQLite developers. | |||
=Partitioning Approach= | =Partitioning Approach= | ||
| Line 7: | Line 9: | ||
Summary from Dr. Hipp: | Summary from Dr. Hipp: | ||
<blockquote>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.</blockquote> | <blockquote>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.</blockquote> | ||
Pros: | |||
Cons: | |||
=Long Open Transaction Approach= | |||
Summary from Shaver: | |||
<blockquote>the long-open-transaction with forced commit for a bookmark addition or other thing needing durability</blockquote> | |||
From Shaver's friend: | |||
<blockquote>... 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).</blockquote> | |||
Pros: | |||
Cons: | |||
=Mitigation= | =Mitigation= | ||