Pavel Ivanov <paivanof-re5jqeeqqe8avxtiumw...@public.gmane.org> writes: >> 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.
Yeah, that makes sense, thanks (but I still don't understand the time difference). Another question though: what is the recommended way to insert lots of data with WAL enabled? Without a transaction, the WAL file stays small but the insertions take very long time. With a transaction, things are faster but the WAL file grows extremely big. The only idea I can come up with is to either arbitrarily split the inserts into small transactions, or to switch to a different journaling mode. Is there any better solution? Best, -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
