ok thank you, today I'm going to port the difference to my source code and I'm going to try if the memory it's ok
Il 09/09/2010 9.37, Dan Kennedy ha scritto: > 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 > > -- 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
