A cyclical WAL file has this problem: You can never be sure when is the right time to wrap around. If you wrap around too early, you run the risk of hitting the wall put up by the earliest reader, who is still using the end of the WAL file. In which case the writer would have to block on the reader, which is something that the current system doesn't suffer from. My guess is that a very high proportion of use cases would never suffer this issue, provided they set an appropriate wrap around size but this does place that burden on the implementer - of picking the right heuristics.
It is a pathological case, but imagine an implementer never tests for this wraparound issue, and builds a deadlock into his app that arises when a writer blocks on a reader. This is the kind of unexpected behaviour that could really bite you. The only solution around this would be to have more than 1 WAL file. To avoid hitting this same problem with multiple WAL files, you'd need to support an arbitrary N number of WAL files. And that, undoubtedly, is a more complex implementation than what currently exists. I imagine it's quite a task for the SQLite developers to get 100% branch test coverage. On one system of mine, where blocking is an issue, I buffer up the write messages, and flush them on a background thread. Of course this may not be practical for you... Ben -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Yoni Sent: 24 November 2010 04:46 PM To: sqlite-users@sqlite.org Subject: [sqlite] WAL file size Hi, I am currently developing a system, which have the following requirements: 1. should be very fast and responsive 2. run forever (or at least a very long time) without restart. 3. have steady stream of writes to the DB. Currently I am using sqlite with one sql connection, and I run sql checkpoint every once in a while (e.g. after 5 minutes, or after 10000 inserts to the DB) - I don't use auto checkpoint. Since I run the checkpoint in the main thread, the system is unresponsive for a long time (can take from 400ms to 20000ms!) What I would like to do, is to run the checkpoint in another thread, so the main thread can keep working (and using sql), and do the long checkpoint work in the background. The problem is that when I try to do it, the WAL file grows without limit. I wrote a little program that do exactly this (insert rows as fast as C allows, and in the background run checkpoints). The result (after 30 secs and 400K records) was: DB size 19MB and WAL size 451MB! (tried on linux Debian with sqlite 3.7.2, and on winXP with same sqlite version). I think that the problem relies on the fact the during the checkpoint, I keep writing to the WAL file, thus the checkpoint can never get to the end of the file. And since WAL file is not cyclic, it will grow forever, leaving the most of the file unused (the beginning), and using only the end of the file for real data. I found this thread http://www.mail-archive.com/sqlite-users@sqlite.org/msg56074.html, discussing a similar problem, and also saw dan's commit (http://www.sqlite.org/src/info/72787c010c) approaching the same problem, but not solving it, since it requires to block all DB activity. I think this can be fixed, if sqlite use a cyclic WAL file. I found that PostgreSQL also bumped into this, and used multiple WAL files, in order to avoid this kind of problem (http://www.sql.org/sql-database/postgresql/manual/wal-implementation.html). Now, we do not need multiple files to do it, we just need to write to the beginning of the WAL file, when we have enough space (e.g. at least 16MB of unused space - configurable). So the WAL file might look like this: logical end logical start | | V V ------------------------------------------------------------------ header | committed data | unused space | committed data |==> ------------------------------------------------------------------ | ^ | ============================================================| when the data at the head of the file is logically at the end of it. When the writing is too fast, eventually they will get to logical start of the file. in this point we can force a blocking checkpoint, since this situation is very rare. This will allow limiting WAL file size, while avoiding blocking checkpoint, and will increase responsiveness significantly! Yoni. _______________________________________________ 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
