On Thu, Sep 2, 2010 at 9:59 AM, Marcus Grimm <mgr...@medcom-online.de>wrote:
> > Michele Pradella wrote: > > ok, I'll wait for the walk around. > > I always use a BEGIN; COMMIT; transaction but often, after a COMMIT; the > > -wal file does not change in size, it seams it's not checkponted. > > Anyway do you think that with WAL journal mode I should continue to use > > BEGIN; COMMIT; statement? or not? > > as Richard mentioned, the wal mode is not intended to work well > for bulk-insert kind of actions. You may try to split your insert > cycles into smaller pieces. > > However, that might not help if you do sql statements which involve > a huge implicit transaction, for example "CREATE INDEX .." on a huge table. > At least on windows it can fail with IO error on a GB sized db. > We are working on that problem. In the meantime, your workaround is to switch to journal_mode=DELETE before creating large indices. > > Btw, I think the wal file doesn't shrink because sqlite doesn't truncate > that file after completing the checkpoint. That's by design I guess. > Correct. The -wal file is deleted when the last connection to the database is closed. But prior to that, the WAL file is kept open and is not truncated. This is a performance optimization. Most filesystems are faster at overwriting an existing file than they are at appending to the end of a file. (Note the qualifier "Most" in the previous sentence. There are exceptions to the rule. We try to optimize for the common case.) > > Marcus > > > > > > > > > Il 02/09/2010 14.43, Richard Hipp ha scritto: > >> On Thu, Sep 2, 2010 at 8:34 AM, Michele Pradella< > michele.prade...@selea.com > >>> wrote: > >>> Hi, > >>> I found a strange behavior of the sqlite 3.7.2 with WAL journal mode. > >>> Yesterday I found my application DB with a -wal file of 1,5GB and a > -shm > >>> file of few MB (about 9MB) with a DB file of 1,2GB: in this > >>> situation I got the process memory wasted by "mapped file" of the -shm > >>> file. It seams that the file is mapped a lot of times in memory so the > >>> process memory become 2GB and it can't allocate more memory. In that > >>> situation operation made on the DB cause I/O disk errors probably due > to > >>> the wasted memory. > >>> > >> By coincidence, the SQLite developers were just discussing this problem > >> earlier this morning. There are technical issues with windows that make > a > >> solution difficult. We are trying to come up with a work-around. (The > >> problem you describe is specific to the windows backend and does not > come up > >> in unix.) > >> > >> > >>> I'm doing some other test to reproduce the problem, but I think that > >>> could be when I got a lot of operation between a BEGIN; COMMIT; > >>> So is it ok to use the BEGIN; COMMIT; with the WAL journal activated? > >>> is there some kind of limit in the number of operation between a BEGIN; > >>> COMMIT; statement? > >>> > >> SQLite will not checkpoint the journal until you commit your > transaction. > >> So if you leave the transaction open too long, the WAL file and the -shm > >> file will grow excessively large. WAL works best with many smaller > >> transactions. If you have one or two big transactions, then using a > >> traditional rollback-journal mode works better. > >> > >> > >> > >>> I try to use the PRAGMA wal_checkpoint; to try resolve this situation, > >>> but seams that command was ignored by sqlite because the -wal file does > >>> not change in size, even the DB file. > >>> _______________________________________________ > >>> 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
