Nicolas Williams wrote: > > I wonder too, what does the page cache do when doing full table scans? > If the cache has an LRU/LFU page eviction algorithm then full table > scans should not be a big deal. Ideally it should not allow pages read > during a full table scan to push out other pages, but if the cache is > cold then a full table scan just might fill the cache. > > In this case we have full table scans in the process of copying on-disk > tables to a memory DB. And it looks like the cache is cold in this > case. > > The default cache size is 2000 pages (there's a way to persist a cache > size). The default page size is 1024 bytes, but it's hard to tell > exactly what it is in the OP's case. > > So it looks like the cache size should be ~20MB. And the DB size is > ~9MB. The cache is plenty large enough to hold a copy of the on-disk > DB. > > So we have: 9MB will be consumed in the page cache, and 9MB will be > consumed by the memory DB (I assume memory DB pages aren't cached). Add > in the overhead per-page, which seems to be .5KB, and you have > > 18MB + 9MB / 2 = 22.5MB > > That seems close to what the OP claimed. >
But my testing using the same code, but a different database file, uses only slightly more memory than required to hold the database file. This code uses the default cache size of 2000 pages. I built a copy of my test database using a 4096 byte page size and it reduced the database file size from 17.5 MB to 12.2 MB. When I repeat the tests using this database file I get the same 18102 KB of memory used, but a slightly higher highwater mark of 26418 KB used. Again this was all done with the default page cache size. Note, I also tried to vacuum the original file to see if there were a lot of unused pages in the original 17.5 MB file. After the vacuum it was reduced to only 17.4 MB, so there were very few free pages in the database. This database just fits much better on the larger 4K pages. Changing the cache size from the default 2000 to a much smaller 100 pages reduced the highwater mark to 18544 KB, which is only slightly higher than the 18102 KB of memory used after the table is built. The actual memory used is exactly the same (as expected since it is storing the same tables). Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
