I have ported the changes of the fix http://www.sqlite.org/src/ci/f213e133f6 to my code (with some difficult because I have ported directly in my sqlite3.c source). Anyway I think that the problem still exist, after few test I'll see with vmmap mapped file growing up like before the fix. I think I ported the fix in the right way, and the problem still exist. I'll do some other tests. After this fix did you never see the problem?
Il 09/09/2010 9.46, Michele Pradella ha scritto: > 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
