Dan, many thanks for the quick response and detailed answers. However, a question or two still puzzle me.
>> * 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? I'd be curious if you know an answer to this, too? >> * OPTION 2: sqlite3_soft_heap_limit(max_mem_bytes); >> >> 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? I set up a high cache_size and a lower sqlite3_soft_heap_limit() and started inserting blobs. >What was the memory limit? Any chance the machine started using swap space? I will test again and let you know. >> * 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. Good to know. So I would reduce the cache_size and then use sqlite3_release_memory() to free memory, right? Maybe this is worth documenting? >Does SQLite really run faster with 1GB available than it would with 100MB? Yes. If there are indexes (regular or FTS) on the table, SQLite needs quick access to lots of pages for searching and rearranging b-tree entries. My timings show that 100MB or 500MB can sometimes make a difference of more than 100%. Richard recently talked about upcoming indexing performance improvements. I wonder if they are part of the performance refactoring due with the next release? :-) Ralf _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
