Hi Shane, have you look at the Virtual Bytes in the performance monitor? in my situation is that counter that grow up to 2GB and than application crash because virtual space finish. I'm using Windows XP professional SP3. I can reproduce the problem in my DB with this SQL statement:
DELETE FROM metadata INDEXED BY metadata_datetime_sourcename WHERE ((DateTime<=timestamp_value) AND (Source='source_name')) Usually timestamp_value identify 1 week records. So I Delete 1 week of records at a time. The DELETE statement is inside a BEGIN;COMMIT; statement and I can have a lot of delete, like this: BEGIN; DELETE FROM metadata INDEXED BY metadata_datetime_sourcename WHERE ((DateTime<=timestamp_value1) AND (Source='source_name1')) DELETE FROM metadata INDEXED BY metadata_datetime_sourcename WHERE ((DateTime<=timestamp_value2) AND (Source='source_name2')) DELETE FROM metadata INDEXED BY metadata_datetime_sourcename WHERE ((DateTime<=timestamp_value3) AND (Source='source_name3')) DELETE FROM metadata INDEXED BY metadata_datetime_sourcename WHERE ((DateTime<=timestamp_value4) AND (Source='source_name4')) DELETE FROM metadata INDEXED BY metadata_datetime_sourcename WHERE ((DateTime<=timestamp_value5) AND (Source='source_name5')) ...... COMMIT; could be event 50 DELETE inside BEGIN; COMMIT; My table have 3 indexes and have the size I told you yesterday. We are using the default page size, because I do not change it. I create the table like this: CREATE TABLE IF NOT EXISTS metadata (Id INTEGER PRIMARY KEY AUTOINCREMENT,DateTime INTEGER,Source TEXT,SensorName TEXT,SensorValue TEXT) Il 09/09/2010 23.25, Shane Harrelson ha scritto: > Michele- > > I've looked at trying to reproduce your issue on an 32-bit Windows XP > system using the latest code, and could not. > > Even assuming the "worst case" of a 512 byte page size, starting with > a 1.2gb DB file, deleting all the records would result in a WAL file > of roughly the same size containing 2mil+ pages. Each of the WAL > pages has an 8 byte entry in the SHM file. The SHM file uses 32k > regions which can each hold 4k entries. For 2mil+ entries, we would > need around 500 regions. The SHM file is what is memory mapped, with > each 32k region being mapped into memory. The Windows implementation > uses an average 48k for each region, so at most we would use around > 24mb of memory. > > I attempted to reproduce this by creating a 1.5gb DB, containing 17mil > records, each 65 bytes long, using a page size of 512 bytes. Starting > with this DB in WAL mode, I deleted all records. Maximum shared > memory usage did not exceed 24mb. > > If you're sure you added the fix Dan indicated correctly into your > build, then we're going to need more info on exactly what you're > doing. What Windows version are you using specifically? What page > size are you using? What SQL queries are you executing? Can you > provide a short series of statements with the CLI to reproduce this? > > HTH. > -Shane > > > > > On Thu, Sep 9, 2010 at 11:36 AM, Michele Pradella > <michele.prade...@selea.com> wrote: >> Hi Max, I got the problem in both situations: >> >> 1. I have a journal_mode=DELETE database and I convert it to WAL. >> 2. I create a new database with WAL mode. >> >> I never check the handles in the task manager, but I always see the >> mapped files in vmmap growing up. I think it's the same. >> Anyway I have the memory wasted especially when I have a quite big >> DB(about 1.2GB with about 17milions of records) and I try to Delete a >> lot of records: in this situation I see mapped files growing up and >> waste a lot of memory (I reached the maximum 32bit windows memory limit >> so my application crash). >> >> ps.With this DB the "SELECT count(ID) FROM table_name" it's very >> slow...it take minutes(with the sqlite shell)! >> >> Il 09/09/2010 17.04, Max Vlasov ha scritto: >>> On Thu, Sep 9, 2010 at 11:37 AM, Dan Kennedy<danielk1...@gmail.com> >>> wrote: >>> >>>> 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 >>>> >>>> >>> Dan, don't know whether it is related, but I detected memory leak in 3.7.2 >>> related to handle count increasing. >>> >>> Steps to reproduce >>> (Windows XP SP3, sqlite3.7.2.dll compiled with bcc) >>> >>> 1. Create or use previous db with the table >>> CREATE TABLE [TestTable] ( >>> [Id] INTEGER PRIMARY KEY AUTOINCREMENT, >>> [Text] VARCHAR(200) >>> ) >>> >>> 2. Open the db that currently in journal_mode=delete >>> >>> 3. Change journal_mode=WAL; >>> >>> 4. BEGIN TRANSACTION >>> >>> 4. Make 50,000 repeated queries >>> INSERT INTO TestTable (Text) VALUES ("12345678912345.... (in my case the >>> lengh of this string was about 1100 bytes) >>> >>> 5. See while the queries are processing how handles in Task manager >>> increasing (total about 14). The followiing commit does not help in >>> decreasing the number to the start value. >>> >>> I tried to look with Process explorer, it seems there are many handles >>> titled "section". >>> Also I could not reproduce this when the db is already in WAL mode when >>> opened. Michele, can you tell us what is the mode when you initially open >>> db? >>> >>> Max >>> _______________________________________________ >>> 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
