Quantcast

SQLite memory calculator (was Multiple sessions, page sizes and cache)

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

SQLite memory calculator (was Multiple sessions, page sizes and cache)

Kim Gräsman
On Fri, Apr 21, 2017 at 5:18 PM, Kim Gräsman <[hidden email]> wrote:

> Hi all,
>
> 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.
>
> 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=<our default> 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.

I've used a spreadsheet internally to forecast how much memory SQLite
will need in a given configuration.

I've cleaned/generalised it a little and posted here:
https://docs.google.com/spreadsheets/d/1cCawZE9AdF4jlmgAMjbz_pGZI0o9WkWddPyV2YglFCY/edit#gid=0

Since we now get out-of-memory with a fixed heap, I'm guessing there's
some kind of think-o in the calculations above.

Specifically, I wonder if 4MiB for the general-purpose heap is maybe
entirely unreasonable? Is there a way to forecast how much memory will
be necessary for transactions and query processing, or does that
depend entirely on the workload?

The calculator seems generally useful, so if we can iron out good
guidelines it may come in handy for someone else.

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: SQLite memory calculator (was Multiple sessions, page sizes and cache)

Simon Slavin-3

On 26 Apr 2017, at 10:00pm, Kim Gräsman <[hidden email]> wrote:

> Specifically, I wonder if 4MiB for the general-purpose heap is maybe
> entirely unreasonable? Is there a way to forecast how much memory will
> be necessary for transactions and query processing, or does that
> depend entirely on the workload?

Depends on a number of factors, including the data present in the database.  In other words, you can forecast memory-usage based on the data in your test database, but that does not allow you to predict memory-use for your customer’s databases unless you know how many rows they have in their tables, whether the chunkiness of their data has changed since the last ANALYZE and such things.  This isn’t the way you’re meant to use SQL.  You’re meant to issue your commands and have the SQL engine worry about executing them.

So do you have good indexes ?  With them, SQLite can just iterate through the entries in the table, as indicated by index order.  This dramatically reduces usage of the cache.  Without them, SQLite has to process and temporarily store the rows in the tables to figure out what order to process them in.  This means it has to access many more rows of the table.

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: SQLite memory calculator (was Multiple sessions, page sizes and cache)

Kim Gräsman
Hi Simon,

On Wed, Apr 26, 2017 at 11:46 PM, Simon Slavin <[hidden email]> wrote:

>
> On 26 Apr 2017, at 10:00pm, Kim Gräsman <[hidden email]> wrote:
>
>> Specifically, I wonder if 4MiB for the general-purpose heap is maybe
>> entirely unreasonable? Is there a way to forecast how much memory will
>> be necessary for transactions and query processing, or does that
>> depend entirely on the workload?
>
> Depends on a number of factors, including the data present in the
> database.  In other words, you can forecast memory-usage based on the
> data in your test database, but that does not allow you to predict
> memory-use for your customer’s databases unless you know how many rows
> they have in their tables, whether the chunkiness of their data has
> changed since the last ANALYZE and such things.  This isn’t the way
> you’re meant to use SQL.  You’re meant to issue your commands and have
> the SQL engine worry about executing them.

https://www.sqlite.org/malloc.html leads me to believe otherwise.
Especially the zero-malloc feature-set seems exactly aimed at
controlling this.

> So do you have good indexes ?

Yes. And we force index use explicitly to make sure we get consistent
results, instead of relying on the query planner.

> With them, SQLite can just iterate through the entries in the table, as
> indicated by index order.  This dramatically reduces usage of the cache.

Cache usage should not be a problem -- we can control that with the
soft heap limit to have it auto-adjust.

Compared to page cache, lookaside and scratch, general-purpose memory
is not as well-described. I think that makes sense -- it's essentially
"everything else" -- but I was expecting and hoping SQLite wouldn't
indiscriminately use general-purpose memory for transaction/query
processing. That said, it's hard to see through all the layers here.
Some of this is described in:
https://www.sqlite.org/malloc.html#_computing_and_controlling_parameters_m_and_n

I don't see anything there that applies directly to our situation.

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: SQLite memory calculator (was Multiple sessions, page sizes and cache)

Kim Gräsman
In reply to this post by Kim Gräsman
On Wed, Apr 26, 2017 at 11:00 PM, Kim Gräsman <[hidden email]> wrote:
>
> I've used a spreadsheet internally to forecast how much memory SQLite
> will need in a given configuration.
>
> I've cleaned/generalised it a little and posted here:
> https://docs.google.com/spreadsheets/d/1cCawZE9AdF4jlmgAMjbz_pGZI0o9WkWddPyV2YglFCY/edit#gid=0
>
> Since we now get out-of-memory with a fixed heap, I'm guessing there's
> some kind of think-o in the calculations above.

Let me confirm that.

I'd completely neglected to perform the actual Robson calculation...
So the totals I'd calculated were just a detailed forecast for the M
part of Robson's proof.

I still need to scale it by n to get a proper heap size, N:
https://www.sqlite.org/malloc.html#_mathematical_guarantees_against_memory_allocation_failures

Sorry about the noise.

This is actually simple to add to the spreadsheet, so I'll extend it
to cover Robson end-to-end.

- Kim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...