Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Tue, Aug 08, 2017 at 10:30:33AM -0700, Jens Alfke wrote: > > On Aug 4, 2017, at 11:28 AM, Nico Williams wrote: > > Imagine a mode where there is only a WAL, and to checkpoint is to write > > a new WAL with only live contents and... rename(2) into place. > > What you’re

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On 8 Aug 2017, at 6:30pm, Jens Alfke wrote: > We’ve also seen that, on low-end hardware like mobile devices, I/O bandwidth > is limited enough that a running compaction can really harm the > responsiveness of the _entire OS_, as well as cause significant battery drain.

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

> On Aug 4, 2017, at 11:28 AM, Nico Williams wrote: > > Imagine a mode where there is only a WAL, and to checkpoint is to write > a new WAL with only live contents and... rename(2) into place. What you’re describing is exactly how CouchDB’s storage engine works, as well

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 07:05:06PM +, Peter Da Silva wrote: > Step 2 seems rather expensive, even if you’re filtering out dead blocks in > the process. It's no more expensive than WAL checkpointing is today. You could always do what LMDB does to reuse free blocks in a DB and avoid having to

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 01:59:07PM -0500, Nico Williams wrote: > The checkpoint process would look like this: > > - make a new file in the same directory > - copy the DB to the new file The copy would basically be copying all the live data as a single transaction on the new DB/WAL file. At

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On 8/4/17, 1:59 PM, "sqlite-users on behalf of Nico Williams" wrote: > The checkpoint process would look like this: > - make a new file in the same directory > - copy the DB to the new file > - rename the new

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 06:49:42PM +, Peter Da Silva wrote: > On 8/4/17, 1:45 PM, "sqlite-users on behalf of Nico Williams" > n...@cryptonector.com> wrote: > > SQLite3's WAL is already log-structured. The main DB file isn't. > >

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On 8/4/17, 1:45 PM, "sqlite-users on behalf of Nico Williams" wrote: > SQLite3's WAL is already log-structured. The main DB file isn't. So SQLite3 > is a hybrid. But it doesn't have to be a hybrid. One issue I

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 06:43:08PM +, Peter Da Silva wrote: > On 8/4/17, 1:28 PM, "sqlite-users on behalf of Nico Williams" wrote: > > [...] > > A log-structured database, like a log-structured file system? Yes. Filesystems and databases are each other's dual. SQLite3's WAL is already

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On 8/4/17, 1:28 PM, "sqlite-users on behalf of Nico Williams" wrote: > Imagine a mode where there is only a WAL, and to checkpoint is to write a new > WAL with only live contents and... rename(2) into place. Such

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 11:53:05AM -0500, Nico Williams wrote: > WAL mode still ends up having no read concurrency when it's time to > checkpoint the WAL. The same would happen with this concept. I don't > think this works well. Speaking of which... and I know I've mentioned this before and I

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 08:50:05AM +0200, Luc DAVID wrote: > sqlite has WAL mode for better concurrency and this could maybe be used to > extend the number of writters: > > Do you think it would be possible to create a > MyDb.WAL001...MyDb.WAL.002...MyDb.WAL.nnn when a write operation is >

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 12:58:43PM +0100, Simon Slavin wrote: > The problem you’re trying to fix is one of the big problems with > distributed databases. Nobody has found a good solution for it yet. It's impossible to solve for the eventually-consistent types. You just have to explicitly

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 09:33:31AM +0200, Eric Grange wrote: > The main problem with multiple writers would be in conflict resolution, > locking and deadlocks. There is plenty in the literature about this. You have to code more defensively, you may need things like "FOR UPDATE", etc. > Imagine

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

> Le 4 août 2017 à 14:15, Richard Hipp a écrit : > > Another alternative is the newer server-process-edition branch > (https://sqlite.org/src/timeline?n=all=server-process-edition) which > you can read about here: > https://sqlite.org/src/artifact/0c6bc6f55191b690 Looks

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On 8/4/17, Luc DAVID wrote: > Hello, > > I was thinking about a possible solution for sqlite "only single writer > is allowed at the same time" and database lock. > > sqlite has WAL mode for better concurrency and this could maybe be used > to extend the number of writters:

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On 4 Aug 2017, at 11:43am, Luc DAVID wrote: > sqlite was not designed for this kind of access but It would be great to have > a higher level of concurrency The problem with these things is that you have SQLite trying to read the minds of he programmer and user.

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

Yes this would be the case if no one closes the write transactions before reading. Would a possible solution be to use "read uncommited" or to include a kind of timestamp or autoInc identifier used internally by sqlite engine ? even if I am not sure it would be enough to avoid conflicts...

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

The main problem with multiple writers would be in conflict resolution, locking and deadlocks. Imagine a simple accounting transactions "update accounts set value = value + 1 where ..." if run at the same time from 2 threads (or close enough), then if you do not have conflict resolution in place

[sqlite] Thinking about a way to extend the number of writers in WAL mode

Hello, I was thinking about a possible solution for sqlite "only single writer is allowed at the same time" and database lock. sqlite has WAL mode for better concurrency and this could maybe be used to extend the number of writters: Do you think it would be possible to create a