Re: Multiple connections, page sizes and cache

Previous Topic Next Topic
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

Re: Multiple connections, page sizes and cache

Kim Gräsman
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
with (2).

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?

- Kim

On Sat, Feb 4, 2017 at 3:21 PM, Kim Gräsman <[hidden email]> wrote:

> 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
>> (, if you keep your memory usage
>> below the Robson limit, then fragmentation will never be a problem.
>> Guaranteed.
>> 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
sqlite-users mailing list
[hidden email]