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

Reply via email to