> 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) > > The last one does well for me at least on the Win-OS' > (not tested on Linux here).
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. Page operations within a cache instance are serialized (and you have no option to modify this behaviour). SQLITE_THREADSAFE=1 means that the SQLite library ENFORCES single entrance per connection by associating a mutex with a connection, acquiring the mutex when you make an sqlite_* call, and releasing it on returning to your application code. 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. > 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. Whether mulitple threads per connection, multiple connections (with or without multiple threads per connection), or even some kind of shared-cache provides the best performance is highly dependant on your application design and the workload it is performing. The only thing which can be said for certain is that if you application is very heavily I/O bound the single-process single-thread with SQLITE_THREADSAFE=0 and _CRT_DISABLE_PERFCRIT_LOCKS (for MSVC -- use the single-thread library for real compilers) will be the most efficient. Of course, the most quickest way to accomplish any operation is to not do it. This especially applies to I/O, context switching, spin/polling locks, and memory cache (L1 & L2) loads and evictions. > 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. You have not yet hit the point where the supervisors' effort of memory management for duplicated cache data exceeds benefit from multiprogramming, or the long length of the operations performed inside the library are not conducive to interleaving (serialization) on a single connection. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
