I've now built an inventory of all our connections and their
lifetimes. They come in three primary classes:
1) Perpetual -- opened on app startup, closed on shutdown
2) Periodical, transient, serial -- only one at a time, happens quite
rarely (online backup)
3) Stochastic, transient, concurrent -- can happen whenever, and
potentially in parallel
Since 1 & 2 are unavoidable, it makes sense to reserve cache memory
for them up-front with SQLITE_CONFIG_PAGECACHE.
But the class-3 connections for the most part never happen. When they
do happen, they will definitely overlap with (1) and might overlap
So I was thinking it would be useful for us to have these connections
by-pass the global preallocated page cache buffer and just go straight
to malloc(). That way we don't risk that they steal pages from the
perpetual connections, and force *them* to go to malloc() for memory,
increasing overall memory usage.
Would something like "PRAGMA cache_allocator=malloc" be a crazy idea?
> Hi Richard,
> I got side-tracked with other work, but am coming back to this now.
> On Sun, Jan 15, 2017 at 5:50 PM, Richard Hipp <[hidden email]> wrote:
>> On 1/15/17, Kim Gräsman <[hidden email]> wrote:
>>> 1) If I configure a global SQLite heap with SQLITE_CONFIG_HEAP, won't
>>> I just trade malloc heap fragmentation for SQLite private heap
>>> fragmentation? Or does SQLite's fragmentation-prevention strategy work
>>> well even in the face of multiple page sizes and connections?
>> As described in the document that you read
>> (https://www.sqlite.org/malloc.html), if you keep your memory usage
>> below the Robson limit, then fragmentation will never be a problem.
>> If you exceed the Robson limit, it is theoretically possible that
>> fragmentation could cause problems. But in practice, we don't
>> normally have issues.
>>> 2) What happens if the configured heap is depleted?
>> You get SQLITE_NOMEM errors. Use sqlite3_memory_used() to monitor
>> your memory utilization and raise alarms if it gets too high. And/or
>> call sqlite3_db_release_memory() to try to free up memory from caches
>> if you start to run low.
> Thanks, makes sense!
> We are having a hard time estimating needed memory up-front, and we
> can't afford SQLITE_NOMEM (we'd rather take slower
> allocation/fragmentation). Especially since our app has a mix of
> long-running and transient connections, it's hard for us to tell
> exactly how many, with what cache needs, will be running concurrently
> at any one time.
> One thing that we've been discussing internally, and can't find a
> definitive answer to:
> Why are these arenas/buffers global, and not associated with each
> connection? It seems to me making a big chunk malloc() to match cache
> size when opening a connection would make it easier for an application
> such as ours, where some connections come and go. I guess it could be
> more fragmentation-/OOM-prone but with a reasonable malloc
> implementation it feels like it would be more flexible without
> sacrificing much of the benefits.
> Thank you,
> - Kim