just a point: In your pseudocode you precreate statements for the begin and commit and trying to reuse them later on. If your real code is like this I would suggest to take a look at this. I think it is not possible to "re-step" a begin or commit, you may try to recreate these statements completely. Maybe that is a reason you are seeing a lot of file-syncs because the begin/commit mechanism doesn't actually work as intended by you ?
Marcus Zachary Turner wrote: > On Tue, Jul 21, 2009 at 6:45 PM, Pavel Ivanov<paiva...@gmail.com> wrote: >>> If I remove or comment out the >>> three lines with //* in the code above, I get no memory leaks. >> So basically you're saying that if you don't insert any data into your >> database and thus effectively don't do with your database anything and >> thus SQLite don't have to cache anything from database then you >> observe no memory leaks, right? Is it by any chance a growing database >> cache is what you see? What if you execute at the very beginning of >> your application this: >> pragma cache_size = 1; >> >> Pavel > > Thanks for your suggestion. I didn't have too much luck with it > unfortunately, but I did learn a few more things about the problem. > Just to make sure I understood you though, after creating the database > with sqlite3_open_v2 I ran the command > > sqlite3_exec(db_, "pragma cache_size=1;"); > > The return value was successful so I assume it worked. > > This doesn't appear to change the allocation behavior. I feel like it > might be related to me issuing INSERT queries too fast for sqlite to > deal with. This is asynchronous (not using the experimental Sqlite > asynchronous vfs but a completely different async model I wrote > myself) and the thread that issues inserts to sqlite is different than > the thread that reads data from the file. > > I changed the code a little so that it permanently stops reading more > data from the file after some fixed amount (I chose about 500MB) but > continues writing until all data has been committed to the database. > when I do this, I notice that after I stop my reads, it takes a *long > time* before all the writes are finished in sqlite. 99% of this time > is spent in the winSync() function in the sqlite source code > constantly calling FlushFileBuffers(). > > Upon reflection it makes sense that this would happen in an > asynchronous environment, but I thought that after a certain threshold > running an insert statement on a non-in-memory table would generate a > sync and simply block my sqlite background thread until there was no > more buffered data. > > In my async model I post messages between threads, so for example the > main thread posts a message to the sqlite thread saying "write this > data", which runs the pseudocode I posted in the OP, and after that's > done it posts a message to the main thread saying "I'm done writing > that data". the main thread only issues new read requests as a result > of getting that message, so if sqlite were syncing every so often then > I could guarantee that I wouldn't be in this situation because the > main thread wouldn't be able to issue any more reads until the sync > was done. > > > Am I misunderstanding sqlite's filesystem sync'ing algorithm? > _______________________________________________ > 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
