Hello! If I have a database that is larger than the system's physical RAM, am I correct in thinking I should actually set a very *small* page cache so as to avoid "double caching" the same pages in both sqlite and the file cache?
To clarify, if the database is *smaller* than system RAM, I imagine the best case scenario (for read performance at least) would be to set a page cache size equal to or larger than the database size. This would ensure there is always enough RAM set aside to store up to the entire database in the page cache. (Some subset of the database would also be stored in the file cache, but once the page cache is warmed up, you would never touch the file cache.) However, if the database is *larger* than system RAM, even assigning all of it to the page cache wouldn't enable it to store the whole database in RAM, no matter how "warmed up" it gets. Furthermore, every time you "miss" the page cache, you would always *also* miss the file system cache (because the only way a page makes it into the page cache is by first reading it from the file system). So every page read into the page cache requires not just that memory, but the same amount of memory in the file system cache. So the larger the ratio between database and system size, the more likely you spend 2x the RAM for each page in the page cache. Accordingly, it would seem that the best configuration for a database significantly larger than the system RAM (assuming a perfectly uniform access pattern where every page is accessed with equal probability) would be to disable the page cache entirely -- and thereby preserve as much RAM as possible for the file cache. Yes, it would be better to have it in the page cache than the file cache, but I imagine it's vastly better to read from the file cache than to go to the disk. Is this right? Sorry for the long explanation; I'm not 100% sure how to describe it and want to make sure I'm asking the question right. Thanks! -david
