On Sep 9, 2010, at 1:12 PM, Michele Pradella wrote: > Hi, do you have some news about the wasted memory? have you found the > reason for the windows backend?
Fixed here: http://www.sqlite.org/src/ci/f213e133f6 Does the problem still show up for you using fossil tip? > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
