And I can confirm here that, over NFS, using just the sqlite3 CLI the Linux page cache is cleared every time - all the damn pages that were read in from disk are read in all over again;
sqlite3 /nfs/file.db $ select * from big_table # (vmtouch reports 163MB read and resident) (don't even have to close sqlite3) $ select * from big_table # (all pages evicted, SQLite begins to read 163MB over NFS again) Richard, I think this answers your question too. I have now run this same test using a copy of the file on a local /tmp and they (the pages) do remain in the page cache. Including when the process terminates, runs again from the shell and the same query issued. This is of course what I'd expect :) So we're back to NFS oddness - we can reduce it down to that, no Python/DBI layer to deal with now. At least for the moment. Jim ----- Original Message ----- > From: "Michael Black" <mdblac...@yahoo.com> > To: "james vanns" <james.va...@framestore.com>, "General Discussion of SQLite > Database" <sqlite-users@sqlite.org> > Sent: Thursday, 7 February, 2013 4:02:04 PM > Subject: RE: [sqlite] Strange eviction from Linux page cache > > I re-ran my test with a 33MB database. Using the shell to .dump the > file > doesn't fill the cache. > But my testro program does. If you open the database with the shell > it > clears the cache again (it's opening it read/write). > > ls -l insert.db > -rw-r--r-- 1 mblack users 35016704 Feb 7 10:54 insert.db > vmtouch insert.db > Files: 1 > Directories: 0 > Resident Pages: 0/8549 0/33M 0% > Elapsed: 0.000372 seconds > sqlite3 insert.db .dump >/dev/null > vmtouch insert.db > Files: 1 > Directories: 0 > Resident Pages: 0/8549 0/33M 0% > Elapsed: 0.002608 seconds > ./testro insert.db > vmtouch insert.db > Files: 1 > Directories: 0 > Resident Pages: 8549/8549 33M/33M 100% > Elapsed: 0.001311 seconds > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James Vanns > Sent: Thursday, February 07, 2013 9:31 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Strange eviction from Linux page cache > > 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 > > -- Jim Vanns Senior Software Developer Framestore _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
