Independent in-memory database per thread?
(1) By mlawsonca on 2021-05-23 23:47:19 [link] [source]
Is it possible to achieve concurrency in a many-core machine by having each thread open a separate (independent) in-memory database (as opposed to opening a separate connection to a shared-cache database)? I do not need each thread to have access to all data in the database (my problem allows sharding across the threads). I have it working correctly, but the performance is the same as if the access were serialized (either with multiple threads and a single shared-cache database or just a single thread). When opening the databases, I have tried using a named in-memory database or the classic :memory:, not setting any flags, setting flags for SQLITE_OPEN_NOMUTEX, SQLITE_OPEN_PRIVATECACHE, SQLITE_OPEN_EXCLUSIVE and I even tried compiling SQLite to be single threaded (and without linking pthread). In all cases the performance comes out the same as if there is serialized access. Is there something within SQLite's library that prevents it being used in this way?
(2) By Keith Medcalf (kmedcalf) on 2021-05-24 00:30:30 in reply to 1 [link] [source]
Make sure you compile with SQLITE_DEFAULT_MEMSTATUS set to 0 in order to prevent serialization around the memory allocation routines.
(3) By mlawsonca on 2021-05-24 01:43:37 in reply to 2 [source]
Thank you! That fixed it. I had read about that setting in the documentation, but didn't realize it would result in serialization for memory allocation. Thanks again!