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. 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. 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
