Here is my final report. The problem was definitely in indexes. Simply the larger is the index table as compared to the page cache size, the faster growths the WAL log.
My solution (all these measures were important): - Drop the index before the bulk insert, create it at the end. - Intermediate commit after each 20,000 inserts. - Page cache was increased 10x. After applying above changes the WAL size decreased to less than 1/2 of the DB size. Secondary improvements: - Speed increase by some 25% - Better responsiveness: Max. time a single SQLite operation takes is 80 secs now. (Was 30+ minutes.) My suggestion to SQLite developers: Please update the WAL documentation (namely the place where you warn against large transactions) by explaining potential risks. These things are not obvious, at least for me. To illustrate the last point here is a single SQL commands that caused WAL log to grow 5x larger than the DB size: DELETE FROM discount WHERE discounttypeid NOT IN (SELECT discounttypeid FROM discounttype) -- View this message in context: http://sqlite.1065341.n5.nabble.com/Huge-WAL-log-tp79991p80185.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
