On Tue, Aug 10, 2010 at 2:24 AM, Yoni Londner <[email protected]> wrote:
> Hello, > I have a questions about the correct use of transactions and WAL. > > I am writing an application that: > 1. should very fast > 2. should be very responsive > 3. don't care if the last N minutes of data will be lost (but DB should > never be corrupted) > > What I tried to do: > 1. open a transactions and close it every 3 minutes (So most of the tune > all work is on memory, and thus is very fast) > 2. disable wal auto checkpoint (So I wont have very slow queries due to a > checkpoint) > 3. run wal checkpoint in another thread (with another connection) - so it > wont affect the responsiveness of the main thread. > > Results: > 1. process memory is increasing with no upper limit > 2. eventually I get an I/O error > I'm guessing your write transactions are preventing the checkpoint from running to completion. Hence, the WAL grows without bound and the wal-index (an in-memory structure proportional in size to the WAL file) eventually uses up all memory. Set PRAGMA synchronous=NORMAL. This prevents all fsync() calls on the writer thread at the cost of durability, which you say you don't care about. Omit the 3-minute transactions, allowing each write to be its own transaction. Writes then will still be in-memory (if you count the operating system filesystem cache as "in-memory"). But then the checkpoints will be able to run and keep the size of the WAL file under control. > > Questions: > 1. what am I doing wrong. > 2. what is the correct way to achieve the goals I mentioned before. > > Thanks, > Jon. > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
