Now you have made me worry. If cache can only be shared by connections
created in one thread then there is no shared cache. I must investigate
this more closely. Perhaps my reading of the documentation included a
dose of wishful thinking and a belief that "shared" meant shared!
Looking through the code shows shared cache mode introducing table
locking and gives the impression that "shared cache mode" is actually an
implementation of finer granularity locking, to table level, and the
multiple connections just store the cursor context state.
I shall need to write some test programs to be certain, but at this
stage it does look like there is no shared cache mode as such and that a
server needs to single stream Sqlite access in one thread to avoid
having large amounts of data replicated in memory, but at the cost of
restricting concurrent read access.
Shared cache mode would be better named "persistent cache mode" because
its main effect is to permit one thread to not flush the cache after
each transaction. The people at Mozilla report that they use it and get
better throughput on small transactions.
Thankyou to those people who contributed to this discussion.
Ken wrote:
John,
According to the Sqlite documentation on sqlite3_enable_shared_cache:
"There is no mechanism for sharing cache between database connections running in
different threads."
This means exactly what I said in the first place: You cannot have a "shared cache" access across threads. I really wish that you could have multiple threads each with a database connection using shared cache running concurrently.
Can you provide sample code showing the concept you are describing?
I totally understand what you are getting at with the locking. Indeed handling locking internally in memory will always be faster (assuming speed of ram access is faster than disk I/O).
John Stanton <[EMAIL PROTECTED]> wrote: I think that you misunderstood the shared cache description. Cache is
shared by many connections but connections may not be passed between
threads. Each thread must maintain and use its its own connection. In
our case a thread has an associated control block and the connection
handle resides there.
As long as you only access the Sqlite connection from the thread which
created it you share the cache and it works fine.
The locking is a seperate issue and is aimed at avoiding the dreaded
"busy wait". We use a leisurely busy wait to handle mutli-process
Sqlite using file locks. The technique is to not delay after a busy is
intercepted but to force a process time slice yield but in a server our
intention is to avoid these inefficiencies by using the more efficient
synchronization features. As you would appreciate a few percent better
efficiency on your server means a corresponding increase in the number
of possible users.
Ken wrote:
John,
The server can maintaine a "shared cache" but if a thread also opens the DB then that execution line will not have a "shared cache" but rather a cache per thread. Only the server thread may open and act upon the connection utilizing a shared cache on behalf of the client. The client may not do things unto the connection handle such as open, prepare, step, reset, close, finalize.
At least thats my understanding of the "shared_cache" mode.
Using a locking primitive internally does simplify the code. But I'll contend
that if you are using multiple threads and each having a connection to a DB
with a locking structure for internal synchronization. Then you are not using
the sqlite shared cache. And you will not benefit from sqlites locking
internals (read/writer starvation ). And if it is write intensive and
concurrent you might as well have a single connections that is shared across
all threads.
I guess my point was that inside the server thread, once a transaction is
entered upon behalf of a client then only that activity may continue and no
others. So in my design i only had two choices, re-enqueu the message inside
the server until the transactional thread completed or return an error to the
client. I preferred keeping the message on the queue waiting to be serviced.
This is also programatically a pain in the arse since you must guarantee the
client doesn't abandon its responsiblities and exit without sending a close
command into the server thread, resulting in a permanently blocked server queue.
You can test this behavouir using the src/test_server.c code and some client
connections into the test_server thread.
Or I may just be totally off my rocker.. and thats ok too.
Ken
John Stanton wrote: That is why the Sqlite locking is not a good fit for a threaded server.
Why not use thread locks instead and achieve the synchronization with
minimum overhead and latency? You do miss out on a couple of Sqlite
features doing that (the pending and reserved locks which help with
concurrency and write starvation) so you need to balance the benefits of
them against the detrimental effects of polling.
In our older embedded Sqlite threaded applications we just serialized
Sqlite access using a mutex because concurrency was not a prime issue,
but use read/write locks in a higher traffic Sqlite based multi-threaded
application server.
After experimentation, which included some erroneous attempts at cache
sharing we have a strategy in place which uses Sqlite shared cache and
assigns a rwlock to each open database. Each thread has its own DB
connection with a pointer to the locking structure for the open
database. That gives good throughput since it holds each database open
while the server runs and maintains one cache per database, accelerating
reads. The downside is that we have to figure out a replacement for the
FTS2 accesses used for text searching.
Since we no longer user POSIX file locking we compile Sqlite without it
to trim some redundant overhead.
It looks like we can replace FTS by user functions using a text indexing
method recycled from another product.
The server in question services AJAX style WWW pages where there are
large numbers of short read transactions and minimum latency is required
to achieve a snappy response. It manages to achieve sub-millisecond
responses to database RPC's from the WWW browser.
BTW, with help from this forum we realized that our attempts to achieve
shared cache and FTS were doomed to fail for fundamental architecture
reasons and abandoned the effort. In retrospect we were trying to
implement PostgreSQL with Sqlite and that was not a rational project.
The Sqlite based application server allows a central site to support
many databases, each one specific to sets of users located globally.
Sqlite's single file databases make this very simple to administer.
Each database does not have a large number of users, relieving the
concurrency load.
For further background on using Sqlite this way look at the way Mozilla
implements it using shared cache.
Finally, it is important to recognize that Sqlite id not Oracle, it is a
well conceived kit of tools to permit a developer to embed SQL database
capability into an application and to make it fit transparently. The
developer has the source and nothing is chiselled in stone.
Ken wrote:
John,
The sqlite api won't block, it will return a sqlite_busy type error to any
other transactions that are attempted? Correct, so there is no sqlite blocking
which is a good thing when writing a server. The clients will always block
waiting upon a response from the server. The server simply keeps the client
requests enqueued until it can service them some time later.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
John Stanton <[EMAIL PROTECTED]> wrote: I think that you misunderstood the shared cache description. Cache is
shared by many connections but connections may not be passed between
threads. Each thread must maintain and use its its own connection. In
our case a thread has an associated control block and the connection
handle resides there.
As long as you only access the Sqlite connection from the thread which
created it you share the cache and it works fine.
The locking is a seperate issue and is aimed at avoiding the dreaded
"busy wait". We use a leisurely busy wait to handle mutli-process
Sqlite using file locks. The technique is to not delay after a busy is
intercepted but to force a process time slice yield but in a server our
intention is to avoid these inefficiencies by using the more efficient
synchronization features. As you would appreciate a few percent better
efficiency on your server means a corresponding increase in the number
of possible users.
Ken wrote:
John,
The server can maintaine a "shared cache" but if a thread also opens the DB then that execution line will not have a "shared cache" but rather a cache per thread. Only the server thread may open and act upon the connection utilizing a shared cache on behalf of the client. The client may not do things unto the connection handle such as open, prepare, step, reset, close, finalize.
At least thats my understanding of the "shared_cache" mode.
Using a locking primitive internally does simplify the code. But I'll contend
that if you are using multiple threads and each having a connection to a DB
with a locking structure for internal synchronization. Then you are not using
the sqlite shared cache. And you will not benefit from sqlites locking
internals (read/writer starvation ). And if it is write intensive and
concurrent you might as well have a single connections that is shared across
all threads.
I guess my point was that inside the server thread, once a transaction is
entered upon behalf of a client then only that activity may continue and no
others. So in my design i only had two choices, re-enqueu the message inside
the server until the transactional thread completed or return an error to the
client. I preferred keeping the message on the queue waiting to be serviced.
This is also programatically a pain in the arse since you must guarantee the
client doesn't abandon its responsiblities and exit without sending a close
command into the server thread, resulting in a permanently blocked server queue.
You can test this behavouir using the src/test_server.c code and some client
connections into the test_server thread.
Or I may just be totally off my rocker.. and thats ok too.
Ken
John Stanton wrote: That is why the Sqlite locking is not a good fit for a threaded server.
Why not use thread locks instead and achieve the synchronization with
minimum overhead and latency? You do miss out on a couple of Sqlite
features doing that (the pending and reserved locks which help with
concurrency and write starvation) so you need to balance the benefits of
them against the detrimental effects of polling.
In our older embedded Sqlite threaded applications we just serialized
Sqlite access using a mutex because concurrency was not a prime issue,
but use read/write locks in a higher traffic Sqlite based multi-threaded
application server.
After experimentation, which included some erroneous attempts at cache
sharing we have a strategy in place which uses Sqlite shared cache and
assigns a rwlock to each open database. Each thread has its own DB
connection with a pointer to the locking structure for the open
database. That gives good throughput since it holds each database open
while the server runs and maintains one cache per database, accelerating
reads. The downside is that we have to figure out a replacement for the
FTS2 accesses used for text searching.
Since we no longer user POSIX file locking we compile Sqlite without it
to trim some redundant overhead.
It looks like we can replace FTS by user functions using a text indexing
method recycled from another product.
The server in question services AJAX style WWW pages where there are
large numbers of short read transactions and minimum latency is required
to achieve a snappy response. It manages to achieve sub-millisecond
responses to database RPC's from the WWW browser.
BTW, with help from this forum we realized that our attempts to achieve
shared cache and FTS were doomed to fail for fundamental architecture
reasons and abandoned the effort. In retrospect we were trying to
implement PostgreSQL with Sqlite and that was not a rational project.
The Sqlite based application server allows a central site to support
many databases, each one specific to sets of users located globally.
Sqlite's single file databases make this very simple to administer.
Each database does not have a large number of users, relieving the
concurrency load.
For further background on using Sqlite this way look at the way Mozilla
implements it using shared cache.
Finally, it is important to recognize that Sqlite id not Oracle, it is a
well conceived kit of tools to permit a developer to embed SQL database
capability into an application and to make it fit transparently. The
developer has the source and nothing is chiselled in stone.
Ken wrote:
John,
The sqlite api won't block, it will return a sqlite_busy type error to any
other transactions that are attempted? Correct, so there is no sqlite blocking
which is a good thing when writing a server. The clients will always block
waiting upon a response from the server. The server simply keeps the client
requests enqueued until it can service them some time later.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
