On 1/14/15, Jan Slodicka <j...@resco.net> wrote: > I understand that the WAL log uses less efficient storage rules than the > real > database, but this case was a real surprise for me. Here is the brief > description. > > We start from an empty database, create a few tables (each having a few > indexes), then begin a transaction, do a couple of inserts into the first > table, commit the transaction and repeat the same with the remaining > tables. > > No other active readers or writers.
Are you sure? Because the WAL file should reset automatically after a commit when the size goes above a threshold (1000 pages, by default). This can fail if you (1) do something to turn off the automatic checkpoint mechanism or (2) you are holding a read transaction open that prevents the WAL file from resetting. Usually the cause of your problem is (2). So, please double-check to make sure you are not accidently starting and leaving a transaction open. note that a prepared statement (an sqlite3_stmt object) that has been stepped a few times but never reset (sqlite3_reset()) or finalized (sqlite3_finalize()) will hold a read transaction open, because it has no way of knowing whether or not you might call sqlite3_step() again. Are you sure you don't have a stray unfinished prepared statement in your program? > > Summary of this process: > - DB size 1.022 GB > - WAL log size 7.490 GB > - SHM file size 57 MB (About 7mil pages, which - page size is 1024 - > corresponds to the WAL size.) > - Total no. of records inserted is 5.207 mil. > - Among the tables there is one that dominates - it takes about 80% of all > records. The commit of this table took over 30 min. > - The test was done on a rather old W7 notebook. Memory consumption > approached 1 GB (as opposed to the normal state ~100 MB). > > I understand that we have to modify above algorithm by cutting the > transaction into smaller pieces. The official SQLite documentation only > says > "WAL does not work well for very large transactions. For transactions > larger > than about 100 megabytes...". > > Could somebody quantify this statement? While the official documentation > mentions the size (megabytes), my gut feeling is that it has more to do > with > the record count. (In our case large records are rather exceptional.) > > Do table indexes play an important role? Wouldn't be better to create them > after all inserts are done? > > > > > > > > > > > -- > View this message in context: > http://sqlite.1065341.n5.nabble.com/Huge-WAL-log-tp79991.html > Sent from the SQLite mailing list archive at Nabble.com. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
