Am 21.09.2012 19:28, schrieb Keith Medcalf:
So one needs to take care that the engine operates in the properly
matching modes for the two approaches when used in threads:
- shared Cache (single dbHdl over all threads) => serialized
(SQLITE_THREADSAFE=1)
- separate Caches (a dedicated dbHdl per thread) => multi-thread
(SQLITE_THREADSAFE=2)
You descriptions are incorrect. No matter how you set
SQLITE_THREADSAFE,you have one cache per connection --
unless you open the connections in"shared cache" mode,
in which case the connections so opened share a single cache.
Not really "incorrect"... maybe "incomplete" matches better.
By default SQLite compiles (IMO) with disabled shared cache.
And that means, that a single DBHandle (shared among threads)
works against a single Cache of course - and multiple DBHandles
(one per Thread) work (by default) against separated Caches.
And for the latter case (one Connection-Hdl per Thread) the
enabling of the shared cache can make a difference of course.
SQLITE_THREADSAFE=2 means that the SQLite library DOES NOT
enforce single entrance per connection with a mutex for you.
It is how you tell SQLite that the application will enforce
those serialization requirements, and that the SQLite library
need not bother itself with enforcement, and should instead
just explode/dump core/fault/corrupt whatever it wants if you
are careless or fail to adhere to the serialization requirements.
My understanding so far was, that SQLITE_THREADSAFE=2 is only
"unsafe", when a single DB-Connection is shared among threads.
When each thread gets its own connection-handle, then this
mode should work a bit faster than with SQLITE_THREADSAFE=1
(and my tests, I've done just now, prove that).
For the multiple-connection-approach (each Cnn having its own cache)
you will probably see better results, when you work against an
sqlite-binary, which was compiled (or set to) multi-thread
(-DSQLITE_THREADSAFE=2).
If and only if you adhere to the serialization requirements.
If you fail to do so, then hell on earth is likely to ensue.
Don't know what you mean by that, because I rely on what's
stated in the SQLite-Docs - so, implementing my own serializing
seems not to be required, as long as SQLite is working with either
SQLITE_THREADSAFE=2 (and my app-code does not share Connection-
Handles among threads) or SQLITE_THREADSAFE=1 (sharing of Cnn-
Handles among threads is allowed).
The only thing I've implemented myself (with regards to potential
"locking issues") is my own SQLite_Busy-Retry-handling (I don't
use the built-in BusyHandler).
My DB-Sizes are not that huge, so I can effort the independent
DB-Connection-caches on each thread in my server-threadpool.
And that is probably why connection-per-thread works for you.
Sure - I've compiled my wrapper-lib with SQLITE_THREADSAFE=2 -
because a single Connection (shared among threads) can never
happen in my threaded scenarios (my COM-Wrapper is bound to be
used only in COM-STAs, which have isolated memory per "thread-
class-instance").
You have not yet hit the point where the supervisors'effort
of memory management for duplicated cache data exceeds
benefit from multiprogramming,...
In case the concurrently accessed DB-Files get that large, that
the benefit of "more or less redundant caches per thread" will
become questionable (degrades the over-all-performance), then
there's always the option to enable SQLites shared-cache-mode
on the Server-Instance (or the multi-threaded Process).
Further below comes a performance-comparison for you... just
wrote a test, to make sure my results are "still as before"
(against newest SQLite-version 3.7.14, compiled with MSVC-
2008 - my last tests in this regard were done already some
years ago, but the latest Dll-version still delivers the
same results which led to my decision in the past, to compile
my "wrapped SQLite-binary" with SQLITE_THREADSAFE=2).
Ok, this was done on Win7 (no VM) on a "real QuadCore"
(an Intel-i5, 2.8 GHz) having no "virtual cores".
ThreadPool-Count was set to 4, matching the CPU-cores -
and the "Count of total Selects to perform" was set to
8000 - so in multi-thread-mode, each of the 4 threads
was performing 2000 Selects in a loop (each Select in
addition also copying the incoming data over into dedicated
recordset-structures within the SQLite-step-loop).
All the Selects were working against a smaller DB, fitting
entirely into each threads page-cache - and the table
addressed by the Selects contains hierarchical Tree-Data
with about 12000 table-records...
1. A pure throughput-oriented Select (RecordCount as per Limit-Clause)
SQL = "Select * From Tree Limit 3000"
Single-threaded performance (8000 Rs-Selects in a Loop): 19.5s
And using 4 threads (2000 Selects on each thread)
SQLITE_THREADSAFE=2: 5.4s vs 34.2s <-with enabled shared cache
SQLITE_THREADSAFE=1: 6.1s vs 38.6s <-with enabled shared cache
The above scenario (more throughput-related) is the one, where
larger differences between SQLITE_THREADSAFE=2 and SQLITE_THREADSAFE=1
can be seen (matching my experiences from the past).
The following, more VDBE related task does not show a significant
difference between the two Threadsafe-Settings anymore (but as
above, significant differences between shared-cache-mode and
disabled shared cache)...
2. A VDBE-related Select (no index in use, returning 10 records)
SQL = "Select * From Tree Where (ID % 1677721) = 0"
Single-threaded performance (8000 Rs-Selects in a Loop): 11.5s
And using 4 threads (2000 Selects on each thread)
SQLITE_THREADSAFE=2: 3.2s vs 11.6s <-with enabled shared cache
SQLITE_THREADSAFE=1: 3.2s vs 11.7s <-with enabled shared cache
Hmm, the results are a bit different from what the OP (Sebastian)
has posted - don't know, if this is related to my more
"real-world"-based scenario (performing query- and serialization-
efforts, which when combined usually do not perform in the
"low sub-millisecond-range").
So for me SQLite behaves exactly "as advertised" and according
to the documentation...
In "Reads-Only-Direction" it scales nearly linearly with the
CPU-Cores (as long as we have 100% cache-hits - and each thread
has its own dedicated page-cache) ... here the comparison again:
- "Select * From Tree Where (ID % 1677721) = 0"
11.5s vs 3.2s (single-threaded vs 4Cores/Threads)
- "Select * From Tree Limit 3000"
19.5s vs 5.4s (single-threaded vs 4Cores/Threads)
The slight overhead of roughly 11% is normal, due
to thread-context-switching (maybe also Intels CPU-
Overboost-function was at play there too, which is
deactivated, when *all* the Cores have heavy workload).
Olaf
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
