Enable Shared Pager Cache in sqlite using PHP PDO

Question

I'm studying sqlite features and I've discovered the SQLite Shared-Cache Mode which is disabled by default.

Shared-Cache is:

intended for use in embedded servers

because it shares a single data and schema cache between threads or processes. I'm interested in using sqlite (with shared-cache) in PHP (and Python) so my questions are:

1) is every PDO connection in a PHP script to an sqlite DB considered a single isolated connection?

2) if yes, using Shared-Cache Mode could improve performance in an high concurrency scenario; to activate Shared-Cache Mode one have to call this C function:

int sqlite3_enable_shared_cache(int);

how to call that function through PDO?

It seems almost impossible but maybe there is a solution.

Best Regards, Fabio Buda Web Developer/Designer @ netdesign

Answer 1

You can do the following trick to enable SQLite shared cache feature in PHP code:

define( 'SQLITE3_OPEN_SHAREDCACHE' , 0x00020000 );
$sqlite = new SQLite3( 'sqlite.db3' , SQLITE3_OPEN_READWRITE  | SQLITE3_OPEN_CREATE |     SQLITE3_OPEN_SHAREDCACHE );

And it works, though somewhat ugly - performance tests on 10k users database shows a little (~3%) performance degradation. Looks like this is not query (results) cache, but a memory cache of raw table data. That's why it should benefit only on really huge databases with high concurrency level.