artemp/trac2github — Issue #798: Enable sqlite multithreaded and shared cache mode
Author: artemp
Created: 2011-10-09 20:51:31+00:00
State: open
Labels: SQLite_Input_Driver

Recent versions of sqlite3 (from approx 3.5.0 [1]) robustly support lock free operation and shared caches. This patch enables both by calling sqlite3_enable_shared_cache(1) and passing SQLITE_OPEN_NOMUTEX|SQLITE_OPEN_SHAREDCACHE to sqlite3_open_v2.

In this mode, sqlite calls are not guarded by a mutex and can be reliably shared between threads so long as the same connection is not accessed concurrently. In addition, the shared cache can dramatically reduce memory consumption and increase cache hits for database access done in the way that mapnik typically does it.

I have found the shared cache setting to be necessary to use sqlite in a concurrent environment where there are many layers referring to the same physical sqlite database files. In my application, I have a minimum 4 sets of map definitions. Each map definition has 46 layers that reference a sqlite database for the overall map definition. Each map definition is loaded up to 4 times for concurrent multi-threaded rendering. Using sqlite defaults, this results in a steady state of 736 sqlite connections active. The default page cache size is 2000 1KB pages per connection, resulting in a maximum memory usage of about 1.4GB in caches. I originally thought this was a memory leak but then identified it as normal (but undesirable) behavior for this use case.

In this case, enabling the shared cache will result in 4 shared caches being allocated (one per physical database file) with a maximum size of around 8MB. Further, since the caches are shared, they should theoretically be more effective.

I recommend that this patch be applied as soon as possible as non trivial sqlite maps in a multi-threaded environment are basically unusable without it.

[1] http://www.sqlite.org/c3ref/enable_shared_cache.html