Quantcast

Multiple sessions, page sizes and cache

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Multiple sessions, page sizes and cache

Kim Gräsman
Hi all,

We have an application with multiple databases of different page
sizes. We keep a few long-running sessions, but also open transient
sessions occasionally.

In an effort to reduce memory usage/fragmentation, we're trying to
configure SQLite to allocate as much memory as necessary up-front
(based on the excellent information in
https://www.sqlite.org/malloc.html).

Naively, I would expect SQLite to allocate a page cache per session.
But reading the documentation, it sounds like there's only one page
cache buffer, shared between all sessions.

So if we want to use SQLITE_CONFIG_PAGECACHE to provide a
preallocated buffer for the page cache, it looks like we have to
provide it for the worst case, i.e. max(page size) * sum(cache
requirements for all sessions). Is that the case?

Moreover, if SQLITE_CONFIG_PAGECACHE is under-allocated, the docs say
SQLite goes to malloc (or some variety thereof):

> If SQLite needs a page-cache entry that is larger than "sz" bytes or if it needs more than N entries,
> it falls back to using the general-purpose memory allocator.
https://www.sqlite.org/malloc.html#_page_cache_memory

and:

> If additional page cache memory is needed beyond what is provided by this option, then
> SQLite goes to sqlite3_malloc() for the additional storage space.
https://www.sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfigpagecache

Does it do this for every page that doesn't fit, or just up-front to
allocate a page cache block? That is, is SQLITE_CONFIG_PAGECACHE used
to specify an arena for page caches, or a buffer for pages between all
sessions?

Since we have different page sizes (512 bytes and 4K respectively) I'm
worried we'll have lots of dead space if SQLITE_CONFIG_PAGECACHE is
set up for 4K pages and requests for 512-byte pages are served from
there directly.

Many thanks,
- Kim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multiple sessions, page sizes and cache

Clemens Ladisch
Kim Gräsman wrote:
> I would expect SQLite to allocate a page cache per session.

There is typically one page cache instance per connection.
(A connection is what you get from sqlite3_open*(); a session would be
what you get from sqlite3session_create().)

> So if we want to use SQLITE_CONFIG_PAGECACHE to provide a
> preallocated buffer for the page cache, it looks like we have to
> provide it for the worst case, i.e. max(page size) * sum(cache
> requirements for all sessions). Is that the case?

Yes; the SQLITE_CONFIG_PAGECACHE memory is used by all cache instances.

> is SQLITE_CONFIG_PAGECACHE used to specify an arena for page caches,
> or a buffer for pages between all sessions?

A page cache instance is a list of pages; memory for each page is
allocated individually.

> Since we have different page sizes (512 bytes and 4K respectively) I'm
> worried we'll have lots of dead space if SQLITE_CONFIG_PAGECACHE is
> set up for 4K pages and requests for 512-byte pages are served from
> there directly.

If your use case is more complex than the simple mechanism offered by
SQLITE_CONFIG_PAGECACHE, consider using SQLITE_CONFIG_PCACHE2 (or
SQLITE_CONFIG_MALLOC).


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multiple sessions, page sizes and cache

Kim Gräsman
Hi Clemens,

Thanks for your help!

On Sat, Jan 14, 2017 at 9:54 AM, Clemens Ladisch <[hidden email]> wrote:
> Kim Gräsman wrote:
>> I would expect SQLite to allocate a page cache per session.
>
> There is typically one page cache instance per connection.
> (A connection is what you get from sqlite3_open*(); a session would be
> what you get from sqlite3session_create().)

Oh, I thought I'd seen 'session' somewhere, so went with that. Sorry
about the confusion.

>> So if we want to use SQLITE_CONFIG_PAGECACHE to provide a
>> preallocated buffer for the page cache, it looks like we have to
>> provide it for the worst case, i.e. max(page size) * sum(cache
>> requirements for all sessions). Is that the case?
>
> Yes; the SQLITE_CONFIG_PAGECACHE memory is used by all cache instances.
>
>> is SQLITE_CONFIG_PAGECACHE used to specify an arena for page caches,
>> or a buffer for pages between all sessions?
>
> A page cache instance is a list of pages; memory for each page is
> allocated individually.

... from the buffer provided to SQLITE_CONFIG_PAGECACHE, right?

Great, that confirms our suspicion.

>> Since we have different page sizes (512 bytes and 4K respectively) I'm
>> worried we'll have lots of dead space if SQLITE_CONFIG_PAGECACHE is
>> set up for 4K pages and requests for 512-byte pages are served from
>> there directly.
>
> If your use case is more complex than the simple mechanism offered by
> SQLITE_CONFIG_PAGECACHE, consider using SQLITE_CONFIG_PCACHE2 (or
> SQLITE_CONFIG_MALLOC).

As for SQLITE_CONFIG_MALLOC, we were considering using that to
configure a fixed-size arena for everything not covered by scratch +
page cache. Not sure if it's even necessary.

SQLITE_CONFIG_PCACHE2 looks comparatively difficult to get right, but
I guess it would allow us to keep two arenas, one for 512-byte pages
and one for 4K pages.

Are these reasonable approaches?

Also, having thought some more about this... The places where the docs
warn that a page cache instance will fall back on sqlite3_malloc -- if
the size of SQLITE_CONFIG_PAGECACHE is configured smaller than the
cache sizes actually requested by connections, will every excess page
allocation hit sqlite3_malloc?

Many thanks,
- Kim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multiple sessions, page sizes and cache

Simon Slavin-3
In reply to this post by Clemens Ladisch

On 14 Jan 2017, at 8:54am, Clemens Ladisch <[hidden email]> wrote:

> If your use case is more complex than the simple mechanism offered by
> SQLITE_CONFIG_PAGECACHE, consider using SQLITE_CONFIG_PCACHE2 (or
> SQLITE_CONFIG_MALLOC).

However, the improvement in time provided by this may not be as great as you think.  Before investing lots of time in building special page-size-handling into your program, do some tests in a demo application running on your hardware.  You can spend a forty hours learning all about the internals of SQLite just to end up with a speed increase of 1%.  And then you have a ton of complicated low-level code to maintain.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multiple sessions, page sizes and cache

Richard Hipp-3
In reply to this post by Kim Gräsman
On 1/13/17, Kim Gräsman <[hidden email]> wrote:
>
> In an effort to reduce memory usage/fragmentation, we're trying to
> configure SQLite to allocate as much memory as necessary up-front
> (based on the excellent information in
> https://www.sqlite.org/malloc.html).
>

The easiest way to do this is to compile with SQLITE_ENABLE_MEMSYS5
then give SQLite 10 or 20MB of memory to use at start-time by invoking

   sqlite3_config(SQLITE_CONFIG_HEAP, malloc(10000000), 10000000, 64);

or similar.  The page-cache and scratch memory will both be
automatically served from this pool if you take no further actions,
and this usually works just fine.  Use sqlite3_memory_highwater(0) to
see how close you are getting to memory exhaustion so that you can
judge if 10MB is too little or too much and make appropriate changes.

Note that you cannot increase or decrease the amount of memory
available to memsys5 at runtime, except by completely closing all
SQLite database connections and interfaces, running sqlite3_shutdown()
and then redoing the sqlite3_config() call above with the new memory
size.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multiple sessions, page sizes and cache

Kim Gräsman
In reply to this post by Simon Slavin-3
On Sat, Jan 14, 2017 at 1:09 PM, Simon Slavin <[hidden email]> wrote:

>
> On 14 Jan 2017, at 8:54am, Clemens Ladisch <[hidden email]> wrote:
>
>> If your use case is more complex than the simple mechanism offered by
>> SQLITE_CONFIG_PAGECACHE, consider using SQLITE_CONFIG_PCACHE2 (or
>> SQLITE_CONFIG_MALLOC).
>
> However, the improvement in time provided by this may not be as great as you think.
> Before investing lots of time in building special page-size-handling into your program,
> do some tests in a demo application running on your hardware.  You can spend a forty
> hours learning all about the internals of SQLite just to end up with a speed increase of 1%.
> And then you have a ton of complicated low-level code to maintain.

Yes, we're not looking for speed-ups, primarily, but rather reduced
heap fragmentation and control over memory usage.

SQLITE_CONFIG_PCACHE2 is definitely furthest down on my list, for
precisely the reasons you quote.

- Kim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multiple sessions, page sizes and cache

Kim Gräsman
In reply to this post by Richard Hipp-3
Hi Richard,

On Sat, Jan 14, 2017 at 2:17 PM, Richard Hipp <[hidden email]> wrote:

> On 1/13/17, Kim Gräsman <[hidden email]> wrote:
>>
>> In an effort to reduce memory usage/fragmentation, we're trying to
>> configure SQLite to allocate as much memory as necessary up-front
>> (based on the excellent information in
>> https://www.sqlite.org/malloc.html).
>>
>
> The easiest way to do this is to compile with SQLITE_ENABLE_MEMSYS5
> then give SQLite 10 or 20MB of memory to use at start-time by invoking
>
>    sqlite3_config(SQLITE_CONFIG_HEAP, malloc(10000000), 10000000, 64);
>
> or similar.  The page-cache and scratch memory will both be
> automatically served from this pool if you take no further actions,
> and this usually works just fine.  Use sqlite3_memory_highwater(0) to
> see how close you are getting to memory exhaustion so that you can
> judge if 10MB is too little or too much and make appropriate changes.
>
> Note that you cannot increase or decrease the amount of memory
> available to memsys5 at runtime, except by completely closing all
> SQLite database connections and interfaces, running sqlite3_shutdown()
> and then redoing the sqlite3_config() call above with the new memory
> size.

I like the sound of 'easiest' :-)

Two concerns;

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?

2) What happens if the configured heap is depleted?

Thanks,
- Kim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multiple sessions, page sizes and cache

Richard Hipp-3
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.
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.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multiple sessions, page sizes and cache

Kim Gräsman
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.
> 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]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...