On Fri, Apr 21, 2017 at 5:18 PM, Kim Gräsman <kim.gras...@gmail.com> wrote: > Hi all, > > On Sun, Jan 15, 2017 at 5:50 PM, Richard Hipp <d...@sqlite.org> wrote: >> On 1/15/17, Kim Gräsman <kim.gras...@gmail.com> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
