I have a highly threaded application that writes to a number of database files. Each thread opens a database file, uses its own handle, and then closes that handle when finished. As the threads come and go, some will likely overlap with others using the same database, so it seems like the process-wide shared cache available in 3.5 would be a good optimization. I'm wondering if I should adjust the PRAGMA cache_size setting.
Imagine 5 threads each hitting a single database, and 5 other threads each hitting individual databases. That would be 10 x 2000 (default cache size) = 20,000 pages of cache by default in the process. If I call sqlite3_enable_shared_cache for the process, I assume that there is now only one cache that is still the default size of 2000 pages. It seems like there would be some cache thrashing since the same amount of work is being done, but squeezed into a smaller cache. I'm assuming it would make sense to at least set the cache size to 6 x 2000 (2000 for the 5 independent threads plus 2000 for the 5 threads sharing a database) and probably higher. Are there any good rules of thumb here? Are my assumptions faulty? Thanks for any input.
