Re: [sqlite] server process gives better concurrency - why?

[dave lilley]

Might i humbly suggest you do this

1. user app writes data in CSV format to a file (each user program has a
defined datafile name and extension to it now if there is more than 1 file
from the same user already in the directory it's extension is made unuiqe -
eg user1.1, user1.2, user2.1 etc).

2. you write another program that polls that directory and if a file is
found the data is processed and that file then deleted BUT it also handles
the reading So if your database "controller" isn't processing a read requst
it is processing write's if there are any to be done.

this way you have a fudged way of allowing writes to be performed regardless
of the backed being tried up with a read operation at the time another user
goes to "post" a change.

all this i also presume (and it's a big one) is that the reads and writes
are not going to take very long (this time frame is the catch - 1sec or 10
milliseconds which is acceptable)?

this would give zero config setup of the DB backend but adds a little more
complexity in code for the programmer to manage.

cheers,

dave (a not so novice DB programmer but novice to SQLite).

2009/9/10 Iker Arizmendi 

> I'm hopeful that it's possible to devise a scheme that will let SQLite
> support
> multiple readers and writers while completely preserving all of its current
> benefits (eg, serverless, efficient, zero-conf, simple API, small
> footprint).
> To that end, I'm trying to understand some of the "sub problems" that get
> in
> the way of multiple writers in order to take a stab at working them out. I
> can
> see that the overall problem is nontrivial and an acceptable solution has
> not
> been found. But this only proves that the problem is hard, not that it's
> impossible (*).
>
> Iker
>
> (*) Of course, if folks have actually shown that solving this problem
> amounts to squaring the circle then that's another story.
>
> Pavel Ivanov wrote:
> >
> > I just keep wondering: do you want to write some new database engine
> > based on SQLite so that it will heed all these caveats? Otherwise this
> > discussion is useless because all these features are not implementable
> > on top of SQLite and are way nontrivial to implement inside SQLite...
> >
> > Pavel
>
> --
> Iker Arizmendi
> AT Labs - Research
> Speech and Image Processing Lab
>
> ___
> 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

Re: [sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

Simon Slavin wrote:
> On 10 Sep 2009, at 12:02am, Iker Arizmendi wrote:
> 
>> The assumption being that a lock facility that can handle these issues
>> is needed by any concurrency scheme (MVCC, shadow pages, etc) and so  
>> can
>> be thought about independently. Does the ability of a client-server  
>> DB to
>> support multiple writers follow solely from the fact that it  
>> centralizes
>> the lock bookkeeping and that it can easily detect death of its  
>> children?
> 
> 'Death of children' is an issue in some DBMS systems (like SQLite  
> currently is), but not others.  In particular, if you are using MVCC  
> correctly, death of children hardly matters at all.  All it means is  
> that the DBMS engine will be using a little extra memory (and no extra  
> processing power) until it decides that a child which hasn't done  
> anything for an hour is probably not going to come back to life.
> 
> Read up on MVCC.  Read up on how to support ACID in a multiuser  
> concurrent system.  Neither allow a client to explicitly lock  
> anything.  Locks are handled by low-level processes inside the engine  
> and last fractions of a second.
> 
> Simon.

Even with MVCC write locks need to be kept somewhere so it does not
address the problems that are resolved by a persistent server process.
In any event, (I think) SQLite uses write ahead logging and infers
the death of a writer by the presence of the rollback journal so
this is the scenario of interest.

Iker

-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab
e: i...@research.att.com
w: http://research.att.com
p: 973-360-8516

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

Cory Nelson wrote:
> 
> Right now sqlite works over a network via NFS and the like -- if you
> wanted to keep it an embedded library (with no server), you'd have to
> drop support for that.
> 
> PS. please stop top posting!
> 
> 

It looks like any move away from file based locks suffers from this
drawback. Perhaps the locking scheme could be offered as a compile
time option or left to the application as a pragma at the cost of
making things a bit less "zero-conf".

Iker

-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] server process gives better concurrency - why?

[Simon Slavin]

On 10 Sep 2009, at 12:02am, Iker Arizmendi wrote:

> The assumption being that a lock facility that can handle these issues
> is needed by any concurrency scheme (MVCC, shadow pages, etc) and so  
> can
> be thought about independently. Does the ability of a client-server  
> DB to
> support multiple writers follow solely from the fact that it  
> centralizes
> the lock bookkeeping and that it can easily detect death of its  
> children?

'Death of children' is an issue in some DBMS systems (like SQLite  
currently is), but not others.  In particular, if you are using MVCC  
correctly, death of children hardly matters at all.  All it means is  
that the DBMS engine will be using a little extra memory (and no extra  
processing power) until it decides that a child which hasn't done  
anything for an hour is probably not going to come back to life.

Read up on MVCC.  Read up on how to support ACID in a multiuser  
concurrent system.  Neither allow a client to explicitly lock  
anything.  Locks are handled by low-level processes inside the engine  
and last fractions of a second.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

I'm not planning to develop a new SQL engine, nor am I proposing any
changes to SQLite. I'm only looking for some pointers regarding the
relevant issues in order to study them more closely. If something useful
were to come as a result of doing so then I might be in a position to
actually flesh out a proposal.

Because SQLite is a complex piece of code (as others have pointed out)
I found it helpful to focus on just the following items:

   1) maintaining shared lock state
   2) efficiently detecting failed database processes

The assumption being that a lock facility that can handle these issues
is needed by any concurrency scheme (MVCC, shadow pages, etc) and so can
be thought about independently. Does the ability of a client-server DB to
support multiple writers follow solely from the fact that it centralizes
the lock bookkeeping and that it can easily detect death of its children?
If so, then I think one could get a similar result using a mmap'ed file
and futexes among unrelated processes. This is the area I thought worth
looking into provided that (1) and (2) really are at the heart of the
matter. If not, course corrections are appreciated :)

Regards,
Iker

Pavel Ivanov wrote:
>> I'm hopeful that it's possible to devise a scheme that will let SQLite 
>> support
>> multiple readers and writers while completely preserving all of its current
>> benefits
> 
> So the answer to my question is yes, you want to develop a new SQL engine...
> Then first of all you have to describe one thing: how will you
> implement ACID properties and most probably MVCC? What kind of OS and
> programming tools will you use for this? How exactly you'll make sure
> that multiple writers don't write to the same place in database and
> readers don't read inconsistent data while writers are writing?
> 
> As you explain your concepts in these topics we will be happy to
> discuss it with you and help you make a great SQL engine.
> 
> BTW, you can search list archives and find what DRH said about why
> ACID cannot be implemented along with fine-grained locks using just
> file system locking as SQLite does now.
> 
> Pavel
> 
> On Wed, Sep 9, 2009 at 4:09 PM, Iker Arizmendi  wrote:
>> I'm hopeful that it's possible to devise a scheme that will let SQLite 
>> support
>> multiple readers and writers while completely preserving all of its current
>> benefits (eg, serverless, efficient, zero-conf, simple API, small footprint).
>> To that end, I'm trying to understand some of the "sub problems" that get in
>> the way of multiple writers in order to take a stab at working them out. I 
>> can
>> see that the overall problem is nontrivial and an acceptable solution has not
>> been found. But this only proves that the problem is hard, not that it's
>> impossible (*).
>>
>> Iker
>>
>> (*) Of course, if folks have actually shown that solving this problem
>> amounts to squaring the circle then that's another story.
>>
>> Pavel Ivanov wrote:
>>> I just keep wondering: do you want to write some new database engine
>>> based on SQLite so that it will heed all these caveats? Otherwise this
>>> discussion is useless because all these features are not implementable
>>> on top of SQLite and are way nontrivial to implement inside SQLite...
>>>
>>> Pavel
>> --
>> Iker Arizmendi
>> AT Labs - Research
>> Speech and Image Processing Lab
>>
>> ___
>> 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
> 


-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab
e: i...@research.att.com
w: http://research.att.com
p: 973-360-8516

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] server process gives better concurrency - why?

[Simon Slavin]

On 9 Sep 2009, at 9:09pm, Iker Arizmendi wrote:

> I'm hopeful that it's possible to devise a scheme that will let  
> SQLite support
> multiple readers and writers while completely preserving all of its  
> current
> benefits (eg, serverless, efficient, zero-conf, simple API, small  
> footprint).

Sorry but I don't think it is.  The amount of code you'd have to add  
to SQLite to do concurrency correctly and fast is probably about as  
much code as there already is.  It's not a small addon to the existing  
system.

> To that end, I'm trying to understand some of the "sub problems"  
> that get in
> the way of multiple writers in order to take a stab at working them  
> out. I can
> see that the overall problem is nontrivial and an acceptable  
> solution has not
> been found. But this only proves that the problem is hard, not that  
> it's
> impossible (*).

I would recommend books with titles like 'DBMS Systems' or 'Principles  
of Multiuser Database Design' or 'Client/Server Database System  
Architecture'.  Or you might want to sign up for a university course  
or something.  This stuff is hard.

One reason SQLite is small, fast, portable is that it does not try to  
do these things.  And the core parts of SQLite might not be easy to  
adapt to concurrency.  It might be simpler to start from the ground up  
and not support any legacy SQLite facilities.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] server process gives better concurrency - why?

[Cory Nelson]

On Wed, Sep 9, 2009 at 1:09 PM, Iker Arizmendi  wrote:
> I'm hopeful that it's possible to devise a scheme that will let SQLite support
> multiple readers and writers while completely preserving all of its current
> benefits (eg, serverless, efficient, zero-conf, simple API, small footprint).
> To that end, I'm trying to understand some of the "sub problems" that get in
> the way of multiple writers in order to take a stab at working them out. I can
> see that the overall problem is nontrivial and an acceptable solution has not
> been found. But this only proves that the problem is hard, not that it's
> impossible (*).
>
> Iker
>
> (*) Of course, if folks have actually shown that solving this problem
>     amounts to squaring the circle then that's another story.

Right now sqlite works over a network via NFS and the like -- if you
wanted to keep it an embedded library (with no server), you'd have to
drop support for that.

PS. please stop top posting!


-- 
Cory Nelson
http://int64.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] server process gives better concurrency - why?

[Pavel Ivanov]

> I'm hopeful that it's possible to devise a scheme that will let SQLite support
> multiple readers and writers while completely preserving all of its current
> benefits

So the answer to my question is yes, you want to develop a new SQL engine...
Then first of all you have to describe one thing: how will you
implement ACID properties and most probably MVCC? What kind of OS and
programming tools will you use for this? How exactly you'll make sure
that multiple writers don't write to the same place in database and
readers don't read inconsistent data while writers are writing?

As you explain your concepts in these topics we will be happy to
discuss it with you and help you make a great SQL engine.

BTW, you can search list archives and find what DRH said about why
ACID cannot be implemented along with fine-grained locks using just
file system locking as SQLite does now.

Pavel

On Wed, Sep 9, 2009 at 4:09 PM, Iker Arizmendi  wrote:
> I'm hopeful that it's possible to devise a scheme that will let SQLite support
> multiple readers and writers while completely preserving all of its current
> benefits (eg, serverless, efficient, zero-conf, simple API, small footprint).
> To that end, I'm trying to understand some of the "sub problems" that get in
> the way of multiple writers in order to take a stab at working them out. I can
> see that the overall problem is nontrivial and an acceptable solution has not
> been found. But this only proves that the problem is hard, not that it's
> impossible (*).
>
> Iker
>
> (*) Of course, if folks have actually shown that solving this problem
>     amounts to squaring the circle then that's another story.
>
> Pavel Ivanov wrote:
>>
>> I just keep wondering: do you want to write some new database engine
>> based on SQLite so that it will heed all these caveats? Otherwise this
>> discussion is useless because all these features are not implementable
>> on top of SQLite and are way nontrivial to implement inside SQLite...
>>
>> Pavel
>
> --
> Iker Arizmendi
> AT Labs - Research
> Speech and Image Processing Lab
>
> ___
> 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

Re: [sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

I'm hopeful that it's possible to devise a scheme that will let SQLite support
multiple readers and writers while completely preserving all of its current
benefits (eg, serverless, efficient, zero-conf, simple API, small footprint).
To that end, I'm trying to understand some of the "sub problems" that get in
the way of multiple writers in order to take a stab at working them out. I can
see that the overall problem is nontrivial and an acceptable solution has not
been found. But this only proves that the problem is hard, not that it's
impossible (*).

Iker

(*) Of course, if folks have actually shown that solving this problem
 amounts to squaring the circle then that's another story.

Pavel Ivanov wrote:
> 
> I just keep wondering: do you want to write some new database engine
> based on SQLite so that it will heed all these caveats? Otherwise this
> discussion is useless because all these features are not implementable
> on top of SQLite and are way nontrivial to implement inside SQLite...
> 
> Pavel

-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] server process gives better concurrency - why?

[P Kishor]

On Wed, Sep 9, 2009 at 1:39 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
>> But I imagine
>> there are other issues as well - these are the issues I'd like to get
>> a bead on.
>
> I just keep wondering: do you want to write some new database engine
> based on SQLite so that it will heed all these caveats? Otherwise this
> discussion is useless because all these features are not implementable
> on top of SQLite and are way nontrivial to implement inside SQLite...
>

well said. This thread is like asking,

"What are the reasons SQLite is not like Oracle or PostGres?"

"Well, because... if it were then it wouldn't be zero conf, fit on a
floppy, and cost less than a popsicle"

"But, why is it not like PostGres or Oracle? Can't we make it so?"

"Given a few years of development, yes, we might, but then it would be
like Oracle and PostGres, and what would be the point of that? Just go
get Oracle or PostGres right now."

"Yes, but wouldn't it be nice to make SQLite like Oracle and PostGres?"


> Pavel
>
> On Wed, Sep 9, 2009 at 1:44 PM, Iker Arizmendi <i...@research.att.com> wrote:
>> Proposals for techniques like MVCC and shadow paging have been made
>> on this list before and they appeared feasible (IIUC) without a server
>> process. The problems with the serverless approach arise once multiple
>> writers are introduced. For example, efficiently detecting crashed
>> writers and initiating recovery. I think this problem may be soluble,
>> at least non-portably, with robust futexes or mutexes. But I imagine
>> there are other issues as well - these are the issues I'd like to get
>> a bead on.
>>
>> Iker
>>
>> Ken wrote:
>>> The key to increased concurrency is MVCC. Without MVCC concurrency is 
>>> limited to page locking, table locking etc.
>>>
>>> Google MVCC...
>>>
>>> --- On Tue, 9/8/09, Iker Arizmendi <i...@research.att.com> wrote:
>>>
>>>> From: Iker Arizmendi <i...@research.att.com>
>>>> Subject: Re: [sqlite] server process gives better concurrency - why?
>>>> To: sqlite-users@sqlite.org
>>>> Date: Tuesday, September 8, 2009, 9:34 PM
>>>> The question is whether a
>>>> client-server design is /necessary/ to
>>>> efficiently implement higher concurrency. It appears to be
>>>> easier
>>>> to do so with a client-server model, but is such a model
>>>> required?
>>>> Are there functions performed by a server process that
>>>> cannot be
>>>> carried out at all without it?
>>>>
>>>> Iker
>>>>
>>>> Simon Slavin wrote:
>>>>  > If SQLite was to be
>>>>  > designed to handle multiple processes 'properly', it
>>>> would have to be
>>>>  > rewritten as a client/server system.
>>>>  >
>>>>  > This would, of course, kill all the advantages of
>>>> SQLite: it could no
>>>>  > longer be tiny, fast, and ultra-portable.  So it
>>>> would be a bad design
>>>>  > choice for SQLite (bowing, of course, to DRH's right
>>>> to do whatever he
>>>>  > pleases with it).
>>>>  >
>>>>  > This is why I get uneasy when I see posts here that
>>>> suggest spinning
>>>>  > off threads especially to deal with locking issues,
>>>> or do other things
>>>>  > that solve concurrency or latency problems.
>>>> Often you find that
>>>>  > making such a change in your program just leads to
>>>> one of the threads
>>>>  > immediately being blocked by another, defeating the
>>>> point of threading
>>>>  > in the first place.  Software has to be designed
>>>> around what is
>>>>  > possible with the tools you're using, not around some
>>>> mythical idea of
>>>>  > the perfect generic SQL engine.
>>>>  >
>>>>  > Simon.
>>>>
>>>> --
>>>> Iker Arizmendi
>>>> AT Labs - Research
>>>> Speech and Image Processing Lab
>>>> e: i...@research.att.com
>>>> w: http://research.att.com
>>>> p: 973-360-8516
>>>>
>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users@sqlite.org
>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>> __

Re: [sqlite] server process gives better concurrency - why?

[Pavel Ivanov]

> But I imagine
> there are other issues as well - these are the issues I'd like to get
> a bead on.

I just keep wondering: do you want to write some new database engine
based on SQLite so that it will heed all these caveats? Otherwise this
discussion is useless because all these features are not implementable
on top of SQLite and are way nontrivial to implement inside SQLite...

Pavel

On Wed, Sep 9, 2009 at 1:44 PM, Iker Arizmendi <i...@research.att.com> wrote:
> Proposals for techniques like MVCC and shadow paging have been made
> on this list before and they appeared feasible (IIUC) without a server
> process. The problems with the serverless approach arise once multiple
> writers are introduced. For example, efficiently detecting crashed
> writers and initiating recovery. I think this problem may be soluble,
> at least non-portably, with robust futexes or mutexes. But I imagine
> there are other issues as well - these are the issues I'd like to get
> a bead on.
>
> Iker
>
> Ken wrote:
>> The key to increased concurrency is MVCC. Without MVCC concurrency is 
>> limited to page locking, table locking etc.
>>
>> Google MVCC...
>>
>> --- On Tue, 9/8/09, Iker Arizmendi <i...@research.att.com> wrote:
>>
>>> From: Iker Arizmendi <i...@research.att.com>
>>> Subject: Re: [sqlite] server process gives better concurrency - why?
>>> To: sqlite-users@sqlite.org
>>> Date: Tuesday, September 8, 2009, 9:34 PM
>>> The question is whether a
>>> client-server design is /necessary/ to
>>> efficiently implement higher concurrency. It appears to be
>>> easier
>>> to do so with a client-server model, but is such a model
>>> required?
>>> Are there functions performed by a server process that
>>> cannot be
>>> carried out at all without it?
>>>
>>> Iker
>>>
>>> Simon Slavin wrote:
>>>  > If SQLite was to be
>>>  > designed to handle multiple processes 'properly', it
>>> would have to be
>>>  > rewritten as a client/server system.
>>>  >
>>>  > This would, of course, kill all the advantages of
>>> SQLite: it could no
>>>  > longer be tiny, fast, and ultra-portable.  So it
>>> would be a bad design
>>>  > choice for SQLite (bowing, of course, to DRH's right
>>> to do whatever he
>>>  > pleases with it).
>>>  >
>>>  > This is why I get uneasy when I see posts here that
>>> suggest spinning
>>>  > off threads especially to deal with locking issues,
>>> or do other things
>>>  > that solve concurrency or latency problems.
>>> Often you find that
>>>  > making such a change in your program just leads to
>>> one of the threads
>>>  > immediately being blocked by another, defeating the
>>> point of threading
>>>  > in the first place.  Software has to be designed
>>> around what is
>>>  > possible with the tools you're using, not around some
>>> mythical idea of
>>>  > the perfect generic SQL engine.
>>>  >
>>>  > Simon.
>>>
>>> --
>>> Iker Arizmendi
>>> AT Labs - Research
>>> Speech and Image Processing Lab
>>> e: i...@research.att.com
>>> w: http://research.att.com
>>> p: 973-360-8516
>>>
>>> ___
>>> 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
>>
>
>
> --
> Iker Arizmendi
> AT Labs - Research
> Speech and Image Processing Lab
> e: i...@research.att.com
> w: http://research.att.com
> p: 973-360-8516
>
> ___
> 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

Re: [sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

Proposals for techniques like MVCC and shadow paging have been made
on this list before and they appeared feasible (IIUC) without a server
process. The problems with the serverless approach arise once multiple
writers are introduced. For example, efficiently detecting crashed
writers and initiating recovery. I think this problem may be soluble,
at least non-portably, with robust futexes or mutexes. But I imagine
there are other issues as well - these are the issues I'd like to get
a bead on.

Iker

Ken wrote:
> The key to increased concurrency is MVCC. Without MVCC concurrency is limited 
> to page locking, table locking etc. 
> 
> Google MVCC...
> 
> --- On Tue, 9/8/09, Iker Arizmendi <i...@research.att.com> wrote:
> 
>> From: Iker Arizmendi <i...@research.att.com>
>> Subject: Re: [sqlite] server process gives better concurrency - why?
>> To: sqlite-users@sqlite.org
>> Date: Tuesday, September 8, 2009, 9:34 PM
>> The question is whether a
>> client-server design is /necessary/ to
>> efficiently implement higher concurrency. It appears to be
>> easier
>> to do so with a client-server model, but is such a model
>> required?
>> Are there functions performed by a server process that
>> cannot be
>> carried out at all without it?
>>
>> Iker
>>
>> Simon Slavin wrote:
>>  > If SQLite was to be
>>  > designed to handle multiple processes 'properly', it
>> would have to be
>>  > rewritten as a client/server system.
>>  >
>>  > This would, of course, kill all the advantages of
>> SQLite: it could no
>>  > longer be tiny, fast, and ultra-portable.  So it
>> would be a bad design
>>  > choice for SQLite (bowing, of course, to DRH's right
>> to do whatever he
>>  > pleases with it).
>>  >
>>  > This is why I get uneasy when I see posts here that
>> suggest spinning
>>  > off threads especially to deal with locking issues,
>> or do other things
>>  > that solve concurrency or latency problems. 
>> Often you find that
>>  > making such a change in your program just leads to
>> one of the threads
>>  > immediately being blocked by another, defeating the
>> point of threading
>>  > in the first place.  Software has to be designed
>> around what is
>>  > possible with the tools you're using, not around some
>> mythical idea of
>>  > the perfect generic SQL engine.
>>  >
>>  > Simon.
>>
>> -- 
>> Iker Arizmendi
>> AT Labs - Research
>> Speech and Image Processing Lab
>> e: i...@research.att.com
>> w: http://research.att.com
>> p: 973-360-8516
>>
>> ___
>> 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
> 


-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab
e: i...@research.att.com
w: http://research.att.com
p: 973-360-8516

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] server process gives better concurrency - why?

[Ken]

The key to increased concurrency is MVCC. Without MVCC concurrency is limited 
to page locking, table locking etc. 

Google MVCC...

--- On Tue, 9/8/09, Iker Arizmendi <i...@research.att.com> wrote:

> From: Iker Arizmendi <i...@research.att.com>
> Subject: Re: [sqlite] server process gives better concurrency - why?
> To: sqlite-users@sqlite.org
> Date: Tuesday, September 8, 2009, 9:34 PM
> The question is whether a
> client-server design is /necessary/ to
> efficiently implement higher concurrency. It appears to be
> easier
> to do so with a client-server model, but is such a model
> required?
> Are there functions performed by a server process that
> cannot be
> carried out at all without it?
> 
> Iker
> 
> Simon Slavin wrote:
>  > If SQLite was to be
>  > designed to handle multiple processes 'properly', it
> would have to be
>  > rewritten as a client/server system.
>  >
>  > This would, of course, kill all the advantages of
> SQLite: it could no
>  > longer be tiny, fast, and ultra-portable.  So it
> would be a bad design
>  > choice for SQLite (bowing, of course, to DRH's right
> to do whatever he
>  > pleases with it).
>  >
>  > This is why I get uneasy when I see posts here that
> suggest spinning
>  > off threads especially to deal with locking issues,
> or do other things
>  > that solve concurrency or latency problems. 
> Often you find that
>  > making such a change in your program just leads to
> one of the threads
>  > immediately being blocked by another, defeating the
> point of threading
>  > in the first place.  Software has to be designed
> around what is
>  > possible with the tools you're using, not around some
> mythical idea of
>  > the perfect generic SQL engine.
>  >
>  > Simon.
> 
> -- 
> Iker Arizmendi
> AT Labs - Research
> Speech and Image Processing Lab
> e: i...@research.att.com
> w: http://research.att.com
> p: 973-360-8516
> 
> ___
> 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

Re: [sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

The benefits are the same as they would be for any application that
wants to use SQLite instead of a traditional database: trivial install
and configuration, simple administration, etc. Were SQLite to support
higher concurrency then these benefits could be extended to a wider
variety of applications. The point is try to identify the technical
issues that have stood in the way thus far and distilling them to
something simple that can be explored and hacked on without diving
too deeply into SQLite internals.

Iker

Igor Tandetnik wrote:
> 
> A better question may be - what's the benefit? What's the point of the 
> exercise? What's the advantage over the traditional server architecture?
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab
e: i...@research.att.com
w: http://research.att.com
p: 973-360-8516

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] server process gives better concurrency - why?

[Igor Tandetnik]

Iker Arizmendi wrote:
> A single server process can be used to track "global" information, but
> shared memory mapped by unrelated processes might do as well. For
> example, a common mmap'ed file might serve to track lock info for
> each process.
> Of course, such a scheme would have to support handling of crashed
> processes without burdening the common case. But assuming it did, is
> this the main obstacle?

A better question may be - what's the benefit? What's the point of the 
exercise? What's the advantage over the traditional server architecture?

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

A single server process can be used to track "global" information, but
shared memory mapped by unrelated processes might do as well. For example,
a common mmap'ed file might serve to track lock info for each process.
Of course, such a scheme would have to support handling of crashed
processes without burdening the common case. But assuming it did, is
this the main obstacle?

Iker

Igor Tandetnik wrote:
> Iker Arizmendi wrote:
>> The question is whether a client-server design is /necessary/ to
>> efficiently implement higher concurrency. It appears to be easier
>> to do so with a client-server model, but is such a model required?
>> Are there functions performed by a server process that cannot be
>> carried out at all without it?
> 
> On a high, theoretical level, the advantage of a single server process 
> is that it has more context. It knows intimate details about everything 
> going on in the system, and can manage concurrent tasks more efficiently 
> using this information (e.g. use fine-grained locks). On the other hand, 
> multiple cooperating processes share only a limited amount of 
> information; each process knows very little beyond what it itself is 
> doing.
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab
e: i...@research.att.com
w: http://research.att.com
p: 973-360-8516

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] server process gives better concurrency - why?

[Simon Slavin]

On 9 Sep 2009, at 4:35am, Igor Tandetnik wrote:

> Iker Arizmendi wrote:
>> The question is whether a client-server design is /necessary/ to
>> efficiently implement higher concurrency.

Until you specify how high you want to go, it's impossible to figure  
out if something is necessary.

>> It appears to be easier
>> to do so with a client-server model, but is such a model required?
>> Are there functions performed by a server process that cannot be
>> carried out at all without it?
>
> On a high, theoretical level, the advantage of a single server process
> is that it has more context. It knows intimate details about  
> everything
> going on in the system, and can manage concurrent tasks more  
> efficiently
> using this information (e.g. use fine-grained locks). On the other  
> hand,
> multiple cooperating processes share only a limited amount of
> information; each process knows very little beyond what it itself is
> doing.

Also, it requires the client programmers to understand how to exploit  
the concurrency facilities.  They have to write special bits in their  
software to call the locking routines, etc..  The advantage of not  
having fine-grained locks is that the programmers just write standard  
SQL, with BEGIN ... COMMIT, and the DBMS figures out how to implement  
it.  Makes the system usable for programmers who don't have a lot of  
time to become experts in your particular SQL engine.

There's a model for concurrency that /doesn't/ require locks.  What  
happens is that if a user does something that begins a transaction,  
they get their own private copy of the database.  If the transaction  
ends with COMMIT, the shared version is thrown away and the private  
version is the new shared version.  If the transaction ends with  
ROLLBACK (or an error) then the private version is thrown away.  Under  
this model nobody ever locks out anyone else.  No locking, no waiting,  
no chance of deadlock.  Fantastic.

Except that two users can be in the middle of transactions at the same  
time.  So you have to figure out a way to merge two private copies of  
a database into one.  And it may or may not work just to execute the  
SQL commands you journalled, depending on how you feel updates should  
work.

There's no right answer to the general problem: it's down to what  
works best for your particular requirements in writing your particular  
application that uses an SQL engine.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] server process gives better concurrency - why?

[Igor Tandetnik]

Iker Arizmendi wrote:
> The question is whether a client-server design is /necessary/ to
> efficiently implement higher concurrency. It appears to be easier
> to do so with a client-server model, but is such a model required?
> Are there functions performed by a server process that cannot be
> carried out at all without it?

On a high, theoretical level, the advantage of a single server process 
is that it has more context. It knows intimate details about everything 
going on in the system, and can manage concurrent tasks more efficiently 
using this information (e.g. use fine-grained locks). On the other hand, 
multiple cooperating processes share only a limited amount of 
information; each process knows very little beyond what it itself is 
doing.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

The question is whether a client-server design is /necessary/ to
efficiently implement higher concurrency. It appears to be easier
to do so with a client-server model, but is such a model required?
Are there functions performed by a server process that cannot be
carried out at all without it?

Iker

Simon Slavin wrote:
 > If SQLite was to be
 > designed to handle multiple processes 'properly', it would have to be
 > rewritten as a client/server system.
 >
 > This would, of course, kill all the advantages of SQLite: it could no
 > longer be tiny, fast, and ultra-portable.  So it would be a bad design
 > choice for SQLite (bowing, of course, to DRH's right to do whatever he
 > pleases with it).
 >
 > This is why I get uneasy when I see posts here that suggest spinning
 > off threads especially to deal with locking issues, or do other things
 > that solve concurrency or latency problems.  Often you find that
 > making such a change in your program just leads to one of the threads
 > immediately being blocked by another, defeating the point of threading
 > in the first place.  Software has to be designed around what is
 > possible with the tools you're using, not around some mythical idea of
 > the perfect generic SQL engine.
 >
 > Simon.

-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab
e: i...@research.att.com
w: http://research.att.com
p: 973-360-8516

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] server process gives better concurrency - why?

[Simon Slavin]

On 9 Sep 2009, at 1:36am, Pavel Ivanov wrote:

>> Multiple processes has nothing to do with it, other than the usual
>> increases in complexity that would be added to any app.
>
> Except that it's much-much easier to implement fine-grained locks
> working inside one process, not dealing with file system locks and
> being sure that nobody but yourself will write to the database.

To the point where there are SQL systems that offer locking on the  
record level, column level, and even field level.  {shudder}

This is one of those things that get more complicated the longer you  
look at it.  For instance, there's no real difference between several  
different applications (or even processes within one application)  
using a database at the same time, and several different computers  
talking to a database server at one time.  If SQLite was to be  
designed to handle multiple processes 'properly', it would have to be  
rewritten as a client/server system.

This would, of course, kill all the advantages of SQLite: it could no  
longer be tiny, fast, and ultra-portable.  So it would be a bad design  
choice for SQLite (bowing, of course, to DRH's right to do whatever he  
pleases with it).

This is why I get uneasy when I see posts here that suggest spinning  
off threads especially to deal with locking issues, or do other things  
that solve concurrency or latency problems.  Often you find that  
making such a change in your program just leads to one of the threads  
immediately being blocked by another, defeating the point of threading  
in the first place.  Software has to be designed around what is  
possible with the tools you're using, not around some mythical idea of  
the perfect generic SQL engine.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] server process gives better concurrency - why?

[Pavel Ivanov]

> Multiple processes has nothing to do with it, other than the usual
> increases in complexity that would be added to any app.

Except that it's much-much easier to implement fine-grained locks
working inside one process, not dealing with file system locks and
being sure that nobody but yourself will write to the database.

Pavel

On Tue, Sep 8, 2009 at 8:23 PM, Cory Nelson wrote:
> On Tue, Sep 8, 2009 at 4:58 PM, Iker Arizmendi  wrote:
>> Hello all,
>>
>> One oft-cited method to address SQLite's limited concurrency support is to
>> use a client-server database. Such databases enjoy better concurrency thanks
>> to their use of a master, coordinating process which is hard to obtain
>> efficiently using unrelated processes like those of SQLite. What's the key
>> to this master process's increased concurrency that is difficult to emulate
>> using a scheme among unrelated processes? Does the primary benefit of the
>> master process lie in its ability to efficiently detect abnormal termination
>> of child processes and deal with their locks?
>
> The reason for sqlite's poor concurrency is due to it using a single
> global reader/writer lock.  Other dbs usually have much finer grained
> page- or row- level locking, and MVCC.
>
> Multiple processes has nothing to do with it, other than the usual
> increases in complexity that would be added to any app.
>
> --
> Cory Nelson
> http://int64.org
> ___
> 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

Re: [sqlite] server process gives better concurrency - why?

[Cory Nelson]

On Tue, Sep 8, 2009 at 4:58 PM, Iker Arizmendi  wrote:
> Hello all,
>
> One oft-cited method to address SQLite's limited concurrency support is to
> use a client-server database. Such databases enjoy better concurrency thanks
> to their use of a master, coordinating process which is hard to obtain
> efficiently using unrelated processes like those of SQLite. What's the key
> to this master process's increased concurrency that is difficult to emulate
> using a scheme among unrelated processes? Does the primary benefit of the
> master process lie in its ability to efficiently detect abnormal termination
> of child processes and deal with their locks?

The reason for sqlite's poor concurrency is due to it using a single
global reader/writer lock.  Other dbs usually have much finer grained
page- or row- level locking, and MVCC.

Multiple processes has nothing to do with it, other than the usual
increases in complexity that would be added to any app.

-- 
Cory Nelson
http://int64.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

Hello all,

One oft-cited method to address SQLite's limited concurrency support is to
use a client-server database. Such databases enjoy better concurrency thanks
to their use of a master, coordinating process which is hard to obtain
efficiently using unrelated processes like those of SQLite. What's the key
to this master process's increased concurrency that is difficult to emulate
using a scheme among unrelated processes? Does the primary benefit of the
master process lie in its ability to efficiently detect abnormal termination
of child processes and deal with their locks?

Regards,
Iker

-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab
w: http://research.att.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users