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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
