Can in-memory SQLite databases scale with concurrency?

Question

In order to prevent a SQLite in-memory database from being cleaned up, one must use the same connection to access the database. However, using the same connection causes SQLite to synchronize access to the database. Thus, if I have many threads performing reads against an in-memory database, it is slower on a multi-core machine than the exact same code running against a file-backed database.

Is there any way to get the best of both worlds? That is, an in-memory database that permits multiple, concurrent calls to the database?

Bronwyn

Answer 1

The answer is no. I asked on the SQLite user group and got the following response from Pavel Ivanov:

No, SQLite doesn't support full concurrent access to any database. The only concurrency you can earn is having on-disk database without shared cache (so actually having several copies of the database in memory). Of course I don't consider option of concurrency from different processes.

Bronwyn

Answer 2

If you set the page size and cache size large enough to contain the entire database, read operations will be fulfilled from the cache and the performance will be nearly equal to an in-memory database.

Varicelloid

Answer 3

from here i understood that answer is yes http://www.sqlite.org/faq.html#q6

Erdah

That just means it's safe to use from multiple threads. I can attest to that. But it doesn't scale - it uses only a single core of my dual core. So it's safety at the expense of performance. Updating my title to clarify. – Bronwyn

"it uses only a single core" - it means that your threads were dispatched that way! This may be caused by internal locking or other synchronization in sqlite. If you really want DB+concurrency you should consider some custom code with final flushing to shared DB. i think you want from sqlite too much. – Erdah

the point is that it works with a file-based SQLite database (100% CPU consumption), but not with an in-memory database. If SQLite doesn't support this scenario then that's fine but I need a citation. – Bronwyn

well. 100% CPU consumption looks strange, because writing to HDD can be done really by only one thread at a time (there is only one head in HDD). So may be you are testing incorrectly? – Erdah