Thanks Ralf, this info confirms my observations. I believe it should be possible to write a function that initialises SQLite, calls SQLITE_STATUS_PAGECACHE_SIZE to calculate the required page-cache overhead for a given page size, and then reinitialises SQLite with the new page-cache settings.
According to the documentation, the sz argument of SQLITE_CONFIG_PAGECACHE must be a power of 2. If the page-cache has to allow even a 1-byte overhead, this implies that page-cache pages must be at least double the size of the actual pages in order to be usable. However, I have found that I can get away with specifying sz as any multiple of 4 and it seems to work ok. Is this luck, or a mistake in the documentation? Going back to memsys3 vs memsys5 - I have read the malloc page (I was directed there after an earlier post), but I still don't see why the difference in memory usage between memsys3 and memsys5 is so large - I suspect I don't fully understand the "power of 2, first fit" algorithm. My tests show that as page size increases, SQLite memory usage as a percentage of DB file size decreases with memsys3. Using memsys5 and the same data, SQLite memory used is consistently over double the DB file size for any page size value. On an embedded system with limited memory, I don't see the advantages of using memsys5 over memsys3. One last point - I wasn't able to find a link to the malloc page from anywhere in the SQLite site. Cheers, Dave Toll. -----Original Message----- From: Ralf Junker [mailto:[EMAIL PROTECTED] Sent: 09 September 2008 10:18 To: General Discussion of SQLite Database Subject: Re: [sqlite] memsys3 vs memsys5 >> Is there any >> reliable method to determine the minimum page-cache allocation size >> needed for a given page_size? >> > >sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, ...) Unfortunately, there is extra overhead involved which is not handled by the page cache but is allocated by malloc() if I understand correctly. From reading the sources (some weeks ago, the exact numbers might have changed) I determined an extra of at least 152 bytes are allocated for each page. Reference is nByteHdr in pager.c, plus an extra 4 byte (on 32 bit) for the slot the page hash (probably a little more to accommodate for empty slots). Interestingly, the page cache overhead size can differ between SQLite versions and also depends on SQLITE_OMIT_... compiler settings. 152 bytes might not seem much but this is nearly 15% for a page cache size of 1024 bytes, and nearly 30% for a 512 byte page cache. So you safe overhead by choosing a larger page size. Concluding, memory requirements are much higher than just pragma page_size * pragma cache_size This can sum up to lots of RAM when using page_size = 100000 or more to speed up indexing of very large databases. Users should be aware of this when running SQLite on extreme (low RAM or high size database) scenarios. Ralf _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
