I fear I must correct myself. SQLite appears to 2nd guess/avoid the Linux kernel page cache both when the file is local and when it is remote. I'd wager that it's own internal cache (an LRU of somesort?) only ever ensures that there are n pages in RAM and therefore it is only these pages that Linux itself will cache. In fact, this is easy to confirm;
a) Write local DB file b) Use vmtouch to monitor the file state in the OS page cache c) Use SQLite to read local DB file d) Observe Only 16MB of the file resides in cache after the processes terminate. a) Write local DB file b) Use vmtouch to monitor the file state in the OS page cache c) cat/dd the file to /dev/null - read pages reside in RAM d) Use SQLite to read local DB file e) Observe All 200MB of the file resides in cache after processes terminate. This behaviour seems almost identical for NFS with the addition that SQLite will evict all the pages from the OS cache entirely. I shall ask on the developer list why this is and if I can just prevent SQLite trying to do the job of the page cache. I understand that it may have to do this for small, mobile devices or for a platform that doesn't have a page cache, but it shouldn't for normal Linux/UNIX/Windows workstations, servers etc. Jim ----- Original Message ----- From: "James Vanns" <jim.va...@framestore.com> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Sent: Thursday, 7 February, 2013 2:52:30 PM Subject: Re: [sqlite] Strange eviction from Linux page cache > I would be interested to know if handing a sequential file over the > same NFS connection shows the same behaviour. This would use > fread() which should trigger any caching that the operating system > and file system implement for that type of connection. You could > test this using a text editor and a very long text file. Already tested that and as expected, pages remain in the cache. I basically did cat /nfs/machine/location/file.txt (a file of around 5GB) 1> /tmp/foobar. I can see using both xosview and vmtouch that the pages aren't evicted - until a process needs RAM of course. In fact, if I 'dd if=<the DB file>' over NFS then the pages are cached as expected. It is only when SQLite itself opens the file are the pages immediately evicted. Jim > I haven't looked at the code for SQLite. As far as I know, even > though you can tell SQLite that /you/ aren't going to make changes > to the file, there's no way to tell it that nobody else is going to > make changes between your SELECT commands. Consequently there's no > way to force it to use the cache. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Jim Vanns Senior Software Developer Framestore -- Jim Vanns Senior Software Developer Framestore _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
