Re: [sqlite] Multiple sessions, page sizes and cache

[Kim Gräsman]

On Fri, Apr 21, 2017 at 5:18 PM, Kim Gräsman  wrote:
>
> I have a nagging suspicion that the tempdb is allocated from the
> private heap (we've set pragma temp_store=memory) -- what are the
> characteristics of that database? Can I forecast how large it will be?

The behavior is the same with pragma temp_store=file (turns out it was
`file` when this statement ran all along.)

I've tried dumping all available global memory statistics when I run
out of memory, but I don't see much I can use:

SQLite error 7: failed to allocate 1024 bytes
SQLite error 7: failed to allocate 1024 bytes
SQLite error 7: statement aborts at 3: [UPDATE mytable SET ... WHERE ...]
Effective soft heap limit: 22192128 bytes
Memory used 5064704 (max 22535968)
Number of outstanding allocations: 9807 (max 26872)
Number of pcache pages used: 0 (max 0)
Number of pcache overflow bytes: 3239936 (max 3239936)
Number of scratch allocations used: 0 (max 0)
Number of scratch overflow bytes: 0 (max 1024)
Largest allocation: 65540 bytes
Largest pcache allocation: 672 bytes
Largest scratch allocation: 560 bytes

It looks like the highwater for memory-used is when the heap was
depleted. But other than that, I'm stumped. What's a good way to
understand where memory is going in this statement?

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

Re: [sqlite] Multiple sessions, page sizes and cache

[Simon Slavin]

On 21 Apr 2017, at 8:57pm, Kim Gräsman  wrote:

> There is a WHERE clause, but EXPLAIN QUERY PLAN does not mention any indexes:
> 
>sele  order  from  deta
>- 
>0  00   SCAN TABLE mytable
> 
> Also, this is just one of many (hundreds) statements, and I don't have
> a good way to audit all of them. You're essentially saying that any
> statement may allocate pages corresponding to an index for filtered
> columns... I can see how that can get large, quickly.

The 'SCAN' means it’s just reading every row of the table, checking to see 
which ones fit your WHERE clause.  This doesn’t involve any indexes, so you’re 
okay here.

I don’t know enough about SQLite to answer the rest of your post.  I hope 
someone else where will.

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

Re: [sqlite] Multiple sessions, page sizes and cache

[Kim Gräsman]

On Fri, Apr 21, 2017 at 6:21 PM, Simon Slavin  wrote:
>
> On 21 Apr 2017, at 5:16pm, Kim Gräsman  wrote:
>
>> Could be. Would that show up in EXPLAIN somehow?
>
> You would find it easier to spot using EXPLAIN QUERY PLAN.  'scan' means it’s 
> just looking through the entire table, but you’ll see mentions of indexes 
> there.
>
>> The statement I've
>> had trouble with so far is an UPDATE of a 5-million-row table.
>
> Does it have a WHERE clause ?  SQLite may decide to do ordering when you use 
> WHERE or ORDER BY.  If there is no convenience index then it will create a 
> temporary one.
>
> There are ways to minimise this, using ANALYZE and creating permanent 
> indexes, but it would probably be easier to start from your WHERE clause and 
> which indexes you have already created.

Thanks.

There is a WHERE clause, but EXPLAIN QUERY PLAN does not mention any indexes:

sele  order  from  deta
- 
0  00   SCAN TABLE mytable

Also, this is just one of many (hundreds) statements, and I don't have
a good way to audit all of them. You're essentially saying that any
statement may allocate pages corresponding to an index for filtered
columns... I can see how that can get large, quickly.

Reading https://sqlite.org/tempfiles.html, it looks like these
temporary/transient indexes go into the temp store. I'm guessing the
temp store is also allocated from the private heap if
temp_store=memory?

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

Re: [sqlite] Multiple sessions, page sizes and cache

[Simon Slavin]

On 21 Apr 2017, at 5:16pm, Kim Gräsman  wrote:

> Could be. Would that show up in EXPLAIN somehow?

You would find it easier to spot using EXPLAIN QUERY PLAN.  'scan' means it’s 
just looking through the entire table, but you’ll see mentions of indexes there.

> The statement I've
> had trouble with so far is an UPDATE of a 5-million-row table.

Does it have a WHERE clause ?  SQLite may decide to do ordering when you use 
WHERE or ORDER BY.  If there is no convenience index then it will create a 
temporary one.

There are ways to minimise this, using ANALYZE and creating permanent indexes, 
but it would probably be easier to start from your WHERE clause and which 
indexes you have already created.

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

Re: [sqlite] Multiple sessions, page sizes and cache

[Kim Gräsman]

On Fri, Apr 21, 2017 at 6:05 PM, Simon Slavin  wrote:
>
> On 21 Apr 2017, at 4:18pm, Kim Gräsman  wrote:
>
>> Unfortunately, we're getting occasional out-of-memory errors on
>> statement execution.
>
> Is there a chance that your statements are making up temporary indexes ?  
> They can be arbitrarily large depending on how many rows you have in a table.

Could be. Would that show up in EXPLAIN somehow? The statement I've
had trouble with so far is an UPDATE of a 5-million-row table.

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

Re: [sqlite] Multiple sessions, page sizes and cache

[Simon Slavin]

On 21 Apr 2017, at 4:18pm, Kim Gräsman  wrote:

> Unfortunately, we're getting occasional out-of-memory errors on
> statement execution.

Is there a chance that your statements are making up temporary indexes ?  They 
can be arbitrarily large depending on how many rows you have in a table.

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

Re: [sqlite] Multiple sessions, page sizes and cache

[Kim Gräsman]

Hi all,

On Sun, Jan 15, 2017 at 5:50 PM, Richard Hipp  wrote:
> On 1/15/17, Kim Gräsman  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.

I've now worked through our application to be able to use
SQLITE_CONFIG_HEAP. I thought I'd share some observations:

- With SQLITE_CONFIG_HEAP, different page sizes between connections
are no longer a problem -- they are allocated from the same arena, but
can co-exist nicely. That said, we've gone to the same page size for
all connections/databases to keep the maths easier
- Prepared statements were an unexpected problem. We had unbounded
statement caches with a large number of statements, hogging memory
from SQLite's private heap. The total size requirement in extreme
situations came to almost 20MB. By turning the statement caches into
LRU caches and limiting their size in bytes, we could cap the maximum
number of bytes used for cached statements and use that number to
forecast heap size requirements.
- We've set the minimum allocation size to 32. This led to higher
memory usage compared to malloc, because small allocations were
rounded up, e.g. for statements.
- We recompiled SQLite with SQLITE_DEFAULT_PAGE_SIZE= and
SQLITE_DEFAULT_CACHE_SIZE=128. This means any ad-hoc connection that
doesn't explicitly specify page/cache size will play well with the
system at large. It may be slow by default, but can be trivially sped
up by issuing "PRAGMA cache_size=n" after making system-wide
adjustments to heap size, to cover for the additional memory pressure.
- Besides forecast for scratch, page cache, lookaside and cached
statements, I've added 4MB, arbitrarily, for general-purpose
allocations
- We use sqlite3_soft_heap_limit64 to get softer handling of heap
depletion. I've set the soft heap limit to (heapsize - 1MB), to give
SQLite ample room for small "soft" overruns. On the assumption that
individual allocations typically aren't larger than 1MB.

Unfortunately, we're getting occasional out-of-memory errors on
statement execution.

I've started looking into `.stats` in the sqlite3 shell, and am
thinking about the pulling out the memory statistics inside our
application to try and understand where memory is going. Any idea what
I might look out for?

I have a nagging suspicion that the tempdb is allocated from the
private heap (we've set pragma temp_store=memory) -- what are the
characteristics of that database? Can I forecast how large it will be?

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

Re: [sqlite] 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  wrote:
> On 1/15/17, Kim Gräsman  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple sessions, page sizes and cache

[Richard Hipp]

On 1/15/17, Kim Gräsman  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple sessions, page sizes and cache

[Kim Gräsman]

Hi Richard,

On Sat, Jan 14, 2017 at 2:17 PM, Richard Hipp  wrote:
> On 1/13/17, Kim Gräsman  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(1000), 1000, 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple sessions, page sizes and cache

[Kim Gräsman]

On Sat, Jan 14, 2017 at 1:09 PM, Simon Slavin  wrote:
>
> On 14 Jan 2017, at 8:54am, Clemens Ladisch  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple sessions, page sizes and cache

[Richard Hipp]

On 1/13/17, Kim Gräsman  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(1000), 1000, 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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple sessions, page sizes and cache

[Simon Slavin]

On 14 Jan 2017, at 8:54am, Clemens Ladisch  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users