Hi, > In which case the writer would have to block on the reader, > which is something that the current system doesn't suffer from. I agree when we are talking about small systems, which does not read/write allot. But, on a larger scale system, who have a several threads reading and writing to the DB non-stop, there always will be at least one reader who block the checkpoint from being completed, thus making the WAL file grow forever (unless of course, you block during the checkpoint - but then you make your system unresponsive).
So, if you have a large scale system, you have two choices: block during the checkpoint, or suffer from huge WAL file (and a crash if the file is too big). > 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. If we choose a large enough size to be free at the beginning of the file (can be proportional to the WAL file size of just a constant), the chances that this will happen a very low. and even when this will happen, the write wont have to block the reader, it just have to force running checkpoint (while they are reading a page), and wait until they finish read the specific page (not the whole read transaction), since the next page they need to read is already in the main DB file. > 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. I think my solution will work too. on PostgreSQL they already attend the problem of how many WAL files should exist. see here: http://www.sql.org/sql-database/postgresql/manual/wal-configuration.html. > 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... I think this kind of a solution can work of me too (I dont want to block the main thread EVER, and I have a steady stream of writes to the DB). Can you please elaborate how did you implemented your solution? Yoni. On 25/11/2010 8:42 AM, Ben Harper wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users