On Thu, Feb 7, 2013 at 10:30 AM, James Vanns <james.va...@framestore.com>wrote:
> 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. SQLite makes no effort to game the OS page cache. SQLite just calls read() and write() as necessary to read and write those parts of the file it needs to access or change. There are no magic ioctl()s or secret options to open() that try to do anything unusual with the page cache on Linux systems. Remember: SQLite is cross-platform, so it tries to keep its I/O as generic as possible. > 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. > Maybe SQLite only needed to look at 16MB of the file in order to satisfy your query. SQLite works hard to avoid reading parts of the file that it does not need, in order to make things go faster. > > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
