> 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.
Granted. I glanced over the code and saw nothing funky as far as I/O calls go. > 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. I'll confirm this shortly - we only have 2 or 3 tables in the whole database. I'll drop any index and read the whole of the large table with SELECT *. This should read the whole file in. > 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 -- Jim Vanns Senior Software Developer Framestore _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
