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. * 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? * Does "PRAGMA cache_size=some_smaller_value;" cause SQLite to free used pages and release their memory straight away? * Is there another runtime -- important! -- setting to establish a maximum memory limit, possibly undocumented? 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? I will be very grateful for any suggestion! Many thanks, Ralf _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
