Hi, do you have some news about the wasted memory? have you found the reason for the windows backend? do you think it could be due to the windows implementation of the mmap?
Il 02/09/2010 16.46, Richard Hipp ha scritto: > 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 >> > > -- Selea s.r.l. Michele Pradella R&D SELEA s.r.l. Via Aldo Moro 69 Italy - 46019 Cicognara (MN) Tel +39 0375 889091 Fax +39 0375 889080 *michele.prade...@selea.com* <mailto:michele.prade...@selea.com> *http://www.selea.com* _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
