On Apr 14, 2008, at 5:53 AM, Ralf Junker wrote: > I need to create a huge database (about 6 GB, more than 6 mio > records, blobs, and FTS text) in as little time as possible. Since > memory is the key to speed, I try to use as much memory as is > available. However, there is the danger of running out of memory. > This is where memory usage control comes into play. I can see there > are two options: > > > * OPTION 1: PRAGMA cache_size = 10000000; > > Advantage: SQLite will use ample memory, but no more than that. > > Disadvantage: Difficulty to establish exact memory requirements in > advance. The help states that "Each page uses about 1.5K of > memory.", but I found this to be wrong. Memory usage obviously > depends on the page size, and my measurement shows that there is an > additional small overhead of undocumented size. Is there a formula > to calculate the required memory for a cache_size of x? > > > * OPTION 2: sqlite3_soft_heap_limit(max_mem_bytes); > > Advantage: Memory limit can be set to a known value (amount of free > memory as returned from the OS). > > Disadvantage: My tests indicate that SQLite slows down drastically > when it hits the memory limit. Inserts drop from a few hundred per > second to just one or two per sec.
That is an odd result. How did you test it? What was the memory limit? Any chance the machine started using swap space? > > * OPTION 3: Catch out-of-memory errors and reduce cache_size > accordingly (untested scenario). > > Advantage: Use memory up to the least bits available. > > Disadvantage: How to avoid data loss after the out-of-memory error. > Can I just call sqlite3_release_memory(some_bytes) and sqlite3_step > again and again until it passes without the out-of-memory error? > > > This raises a few questions: > > * Do sqlite3_soft_heap_limit(), or "PRAGMA cache_size=x;", or both > establish SQLite's upper memory limit? Do they work independently > of each other, i.e. does the lower limit always kick in first? Both limits can be used simultaneously. The cache_size limit is per database cache, soft_heap_limit() sets a global parameter that governs all sqlite connections opened by the process. > > * Does "PRAGMA cache_size=some_smaller_value;" cause SQLite to free > used pages and release their memory straight away? No. If the cache_size parameter is set to a value that is less than the number of pages currently allocated for the cache, no more pages will be allocated. But no existing pages will be freed. > * Is there another runtime -- important! -- setting to establish a > maximum memory limit, possibly undocumented? There is the SQLITE_MEMORY_SIZE option. But that's not really useful for the very large memory limits you're talking about. So soft_heap_limit() and pragma cache_size are it. > In the end this boils down to a simple problem: > > * Wow to keep SQLite's memory usage as close to, but not exceeding > the memory available to applications? It's not really that simple. On a workstation, not all memory is equal. The maximum amount of memory available to an application is all of the RAM + all of the swap space. Best performance probably comes by using up all of the RAM and never using the swap. Realistically, you should probably just set a large cache_size as in option 1. Does SQLite really run faster with 1GB available than it would with 100MB? Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
