Re: [sqlite] rollback/replay journals and durability of the mostrecent transaction
On Jul 2, 2008, at 11:07 PM, Ken wrote: > > So if I insert large amounts of data, this data would be written to > disk > twice: once into a replay journal, and then again into the database > file > on commit. With rollback journal, it is written once into the database > (with rollback journal merely noting which new pages have been > created), > and commit is nearly instantaneous. I'd rather optimize for the common > case (transactions successfully committed) than the exceptional one. > Upon first reading, I thought you were saying that the use of a rollback journal always writes the data only once, which is (of course) not true. In the common case where you are making a change to the database without extending it, the data is still written twice - once to the journal and once to the database. You are correct, however, that when extending the size of a database file with a rollback journal, the data is only written once. The rollback journal records the original size of the database file and truncates the database upon rollback. So pages beyond the end of the original database file size are never journalled. Hence when extending the size of a database file, a rollback journal does less I/ O than a replay journal. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rollback/replay journals and durability of the mostrecent transaction
Igor Tandetnik <[EMAIL PROTECTED]> wrote:Karl Tomlinson wrote: > Thank you for your comments. > >>> Karl Tomlinson wrote: >>> One thing I'm trying to understand is whether there was a reason for SQLite choosing to use a rollback journal (of the steps to undo a transaction) rather than a replay journal (of the steps to perform a transaction). > > I didn't make this quite clear. I really meant, as a first > possibility, that writing the pages to the database itself would > be performed during the commit process after syncing the replay > journal (but the database need not be synced until the journal is > about to be removed). So if I insert large amounts of data, this data would be written to disk twice: once into a replay journal, and then again into the database file on commit. With rollback journal, it is written once into the database (with rollback journal merely noting which new pages have been created), and commit is nearly instantaneous. I'd rather optimize for the common case (transactions successfully committed) than the exceptional one. Ok, I'll argue why write the entire page, why not just write what changed on the page? Allowing more information to be written to a redo journal (ie more than one modification) per redo page How often does a write actually modify the entire page? Definately agree about optimizing for the common case of successfully commited transactions. >> SQLite would have to keep track of where in the replay journal each >> new page was written, so that if it needed to reload that page for >> any reason, it could. As long as the transaction (and thus the >> replay journal) is not too big, this is not a problem. A hash table >> in memory would suffice. But sometimes transactions do get very >> large. For example, with the VACUUM command. And in those cases, >> the amount of memory needed to record the mapping from pages to >> offsets into the replay journal could become too large to fit into >> memory, especially in memory-constrained devices such as cellphones. > > The size of the transaction would be something to consider in the > decision on when to sync the journal and write its pages to the > database. So presumably, as the transaction grows, at some point you may decide to dump the replay journal into the database file even while the transaction is still open. What if the user decides to roll it back soon afterwards? Wouldn't you need both a replay and a rollback journals then? Yes you would, but then again you'd also want to put the changes for the undo journal into the redo journal so that if the "DB" crashed it would be able to recover not just the data files but the undo as well. Which if doing this will naturaly lead you to multiple transactions, concurrency and multiple redo journals and a WHOLE lot of other features. Which seems to me to be a pretty large scope creep. The ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rollback/replay journals and durability of the mostrecent transaction
Karl Tomlinson <[EMAIL PROTECTED]> wrote: > Thank you for your comments. > >>> Karl Tomlinson wrote: >>> One thing I'm trying to understand is whether there was a reason for SQLite choosing to use a rollback journal (of the steps to undo a transaction) rather than a replay journal (of the steps to perform a transaction). > > I didn't make this quite clear. I really meant, as a first > possibility, that writing the pages to the database itself would > be performed during the commit process after syncing the replay > journal (but the database need not be synced until the journal is > about to be removed). So if I insert large amounts of data, this data would be written to disk twice: once into a replay journal, and then again into the database file on commit. With rollback journal, it is written once into the database (with rollback journal merely noting which new pages have been created), and commit is nearly instantaneous. I'd rather optimize for the common case (transactions successfully committed) than the exceptional one. >> SQLite would have to keep track of where in the replay journal each >> new page was written, so that if it needed to reload that page for >> any reason, it could. As long as the transaction (and thus the >> replay journal) is not too big, this is not a problem. A hash table >> in memory would suffice. But sometimes transactions do get very >> large. For example, with the VACUUM command. And in those cases, >> the amount of memory needed to record the mapping from pages to >> offsets into the replay journal could become too large to fit into >> memory, especially in memory-constrained devices such as cellphones. > > The size of the transaction would be something to consider in the > decision on when to sync the journal and write its pages to the > database. So presumably, as the transaction grows, at some point you may decide to dump the replay journal into the database file even while the transaction is still open. What if the user decides to roll it back soon afterwards? Wouldn't you need both a replay and a rollback journals then? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
