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. 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
