> Furthermore, if I turn off auto checkpointing, the WAL file grows to > more than 5 GB without transactions, but only to about 922 MB with a > transaction. Are the commit markers really taking that much space?
WAL-journal is not some kind of change log with commit markers. It's a sequence of changed pages with commit markers (which AFAIK can take as much as 1Kb in size). So let's say your database has 1Kb pages, each transaction adds a row of 50 bytes. For each transaction SQLite must write all changed pages into WAL-journal. It would be at least one page per transaction. So the size of WAL-journal would be like 20-40 times bigger than amount of data changed. If you combine for example each 40 transactions into one then you'll get size of WAL-journal probably only twice or triple the total size of changed data. With all transactions combined into single one the size of WAL-journal will be almost equal to size of all changed pages written once... I hope you understand now why you see the difference in WAL-journal size. Pavel On Tue, Apr 5, 2011 at 10:01 PM, Nikolaus Rath <nikol...@rath.org> wrote: > Hello, > > I'm experimenting with creation of a 156 MB database from scratch. I've > set synchronous = NORMAL and locking_mode = exlusive. > > With journal_mode = off and without wrapping the INSERTs into a > transaction, creating the db takes 54 seconds. > > With journal_mode = WAL (and still no transaction), it takes an > impressive 900 seconds. > > With journal_mode = WAL and the INSERTs packed into a transaction, the > total time is 60 seconds. > > I am slightly surprised by this. I was expecting that the difference > between transaction/no transaction with WAL is just the writing of an > additional few bytes of commit marker into the WAL file with every > insert. But apparently something much more time consuming is happening > as well. > > Furthermore, if I turn off auto checkpointing, the WAL file grows to > more than 5 GB without transactions, but only to about 922 MB with a > transaction. Are the commit markers really taking that much space? > > Can someone shed some light on what factor I am missing here? > > > Thanks, > > -Nikolaus > > -- > »Time flies like an arrow, fruit flies like a Banana.« > > PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
