Re: [sqlite] Smallest reasonable cache size

[Kim Gräsman]

On Fri, Feb 17, 2017 at 8:37 PM, Richard Hipp  wrote:
> On 2/17/17, Kim Gräsman  wrote:
>>
>> Thanks, shrink_memory could come in handy. But this is not the problem
>> we're trying to solve -- rather we want to claim as much memory as possible
>> up-front to make mem usage deterministic and make it easier to diagnose
>> other subsystems with run-away memory usage.
>
> In that case, consider using MEMSYS5
> (https://www.sqlite.org/malloc.html#experimental_memory_allocators).
> You give SQLite a single big chunk of RAM up front, and that is all
> the memory it will ever use.  And it won't share any of it with any
> other subsystems.

As I've said earlier, we don't want to risk SQLITE_NOMEM.

But if we can combine sqlite_soft_heap_limit64 with
SQLITE_CONFIG_HEAP, that might give us a nice self-adjusting system
(where caches are flushed when heap is running low) in the rare cases
that this might happen.

Do the two play nice together? I guess the challenge is to find a good
number for the limit, but we can afford to "waste" some memory to play
it safe here.

Thank you,
- Kim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Smallest reasonable cache size

[Richard Hipp]

On 2/17/17, Kim Gräsman  wrote:
>
> Thanks, shrink_memory could come in handy. But this is not the problem
> we're trying to solve -- rather we want to claim as much memory as possible
> up-front to make mem usage deterministic and make it easier to diagnose
> other subsystems with run-away memory usage.

In that case, consider using MEMSYS5
(https://www.sqlite.org/malloc.html#experimental_memory_allocators).
You give SQLite a single big chunk of RAM up front, and that is all
the memory it will ever use.  And it won't share any of it with any
other subsystems.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Smallest reasonable cache size

[Kim Gräsman]

Den 17 feb. 2017 5:33 em skrev "Richard Hipp" :

On 2/17/17, Kim Gräsman  wrote:
>
> If we let it use malloc directly and control it
> with the soft heap limit, we'll have a steadily rising memory curve
> until all caches are fully loaded (could take weeks.)
>

Caches get flushed from time to time, for example when another process
writes to the database.

If that does not work out, you can always reclaim memory on demand
using "PRAGMA shrink_memory;"
(https://www.sqlite.org/pragma.html#pragma_shrink_memory) or
equivalently sqlite3_db_release_memory().  Both will call free() on
unused cache lines


Thanks, shrink_memory could come in handy. But this is not the problem
we're trying to solve -- rather we want to claim as much memory as possible
up-front to make mem usage deterministic and make it easier to diagnose
other subsystems with run-away memory usage.

- Kim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Smallest reasonable cache size

[Richard Hipp]

On 2/17/17, Kim Gräsman  wrote:
>
> If we let it use malloc directly and control it
> with the soft heap limit, we'll have a steadily rising memory curve
> until all caches are fully loaded (could take weeks.)
>

Caches get flushed from time to time, for example when another process
writes to the database.

If that does not work out, you can always reclaim memory on demand
using "PRAGMA shrink_memory;"
(https://www.sqlite.org/pragma.html#pragma_shrink_memory) or
equivalently sqlite3_db_release_memory().  Both will call free() on
unused cache lines.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Smallest reasonable cache size

[Kim Gräsman]

Thanks for your responses!

On Thu, Feb 16, 2017 at 8:56 PM, Dominique Pellé
 wrote:
>
> I think it's probably best to give a large cache_size to
> each connection, and limit the total amount of memory
> used by SQLite with sqlite3_soft_heap_limit64().
> This will effectively limit the global amount of cache
> used if cache pages comes from the heap.
>
> For example, if you give 20MB to each connection
> (PRAGMA cache_size=) and limit the global SQLite
> heap usage to 30 MB with sqlite3_soft_limit() then even
> if you have say 100 connections, SQLite will not use
> more than 30 MB of heap. If a connection needs to
> cache pages, unpinned cache pages from other
> connections will be discarded in LRU order. Strictly
> speaking, SQLite could use more than the 30 MB
> soft limit if it has no other choice to allocate memory
> (hence a soft limit), but in general that does not happen.

We want to limit/avoid incremental growth of caches, i.e. we prefer to
allocate X amount of memory up-front for caches, and have SQLite work
primarily with that. If we let it use malloc directly and control it
with the soft heap limit, we'll have a steadily rising memory curve
until all caches are fully loaded (could take weeks.)

Also, my mental model is that there's one memory arena, whether system
malloc or configured with SQLITE_CONFIG_PAGECACHE, but any number of
independent connection caches competing for memory from that arena. It
seems to me the behavior you're describing would require coordination
between caches, so maybe my assumptions are wrong here?

Thank you,
- Kim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Smallest reasonable cache size

[Dominique Pellé]

Kim Gräsman  wrote:

> Hi all,
>
> In my battles with lots of connections competing over precious cache
> memory, I've considered giving some non-critical connections zero
> cache using `PRAGMA cache_size=0`.
>
> Is this a reasonable thing to do? If zero is too extreme, what might a
> more moderate small cache size be? 32? 64? 100?
>
> Some of these connections are only opened to do `PRAGMA quick_check;`,
> I'm guessing that operation is not very cache-intensive, assuming it
> reads pages sequentially and checks them?
>
> Thanks for any advice on this,
> - Kim


I think it's probably best to give a large cache_size to
each connection, and limit the total amount of memory
used by SQLite with sqlite3_soft_heap_limit64().
This will effectively limit the global amount of cache
used if cache pages comes from the heap.

For example, if you give 20MB to each connection
(PRAGMA cache_size=) and limit the global SQLite
heap usage to 30 MB with sqlite3_soft_limit() then even
if you have say 100 connections, SQLite will not use
more than 30 MB of heap. If a connection needs to
cache pages, unpinned cache pages from other
connections will be discarded in LRU order. Strictly
speaking, SQLite could use more than the 30 MB
soft limit if it has no other choice to allocate memory
(hence a soft limit), but in general that does not happen.

That way, inactive connections (connections that
are opened, but no queries have been done in a
long time) do hog pages in memory. Their pages
get discarded after a while. Memory will be best
used automatically to cache pages of the most active
connections. At least that's my understanding.

Dominique
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Smallest reasonable cache size

[Richard Hipp]

On 2/16/17, Kim Gräsman  wrote:
> I've considered giving some non-critical connections zero
> cache using `PRAGMA cache_size=0`.
>
> Is this a reasonable thing to do?

Yes.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users