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

2017-04-24 Thread 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

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

2017-04-21 Thread 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 > >

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

2017-04-21 Thread 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

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

2017-04-21 Thread 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

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

2017-04-21 Thread 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

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

2017-04-21 Thread 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

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

2017-04-21 Thread 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?

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

2017-02-04 Thread 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

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

2017-01-15 Thread 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

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

2017-01-15 Thread 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

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

2017-01-15 Thread 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 >>

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

2017-01-14 Thread 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

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

2017-01-14 Thread 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

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

2017-01-14 Thread 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

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

2017-01-14 Thread 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