Re: [sqlite] memsys3 vs memsys5
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 = 10 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
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 = 10 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
Re: [sqlite] memsys3 vs memsys5
On Sep 8, 2008, at 8:28 PM, Dave Toll wrote: > Hello list > > > > I'm using SQLite 3.6.2 on an embedded C platform, and I recently > switched from memsys3 to memsys5. SQLite memory usage > (sqlite3_memory_used()) for a fully-cached database nearly doubled > from > 4.7MB to 8.9MB (using the same DB file in each test). Is this > normal/expected? > > > > I've also played with SQLITE_CONFIG_PAGECACHE, which seems to be more > appropriate for a limited-memory system where the amount of data will > often be close to the memory limit. I discovered that it was necessary > to allow an overhead on top of the page_size when specifying the > page-cache allocation size (sz argument), otherwise the page-cache > would > not be used as the page-cache units were too small. Have you read http://www.sqlite.org/malloc.html > 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, ...) D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] memsys3 vs memsys5
Hello list I'm using SQLite 3.6.2 on an embedded C platform, and I recently switched from memsys3 to memsys5. SQLite memory usage (sqlite3_memory_used()) for a fully-cached database nearly doubled from 4.7MB to 8.9MB (using the same DB file in each test). Is this normal/expected? I've also played with SQLITE_CONFIG_PAGECACHE, which seems to be more appropriate for a limited-memory system where the amount of data will often be close to the memory limit. I discovered that it was necessary to allow an overhead on top of the page_size when specifying the page-cache allocation size (sz argument), otherwise the page-cache would not be used as the page-cache units were too small. Is there any reliable method to determine the minimum page-cache allocation size needed for a given page_size? Cheers, Dave Toll. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
