On 02/07/2013 10:30 PM, James Vanns 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. 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
I used a smaller database, but it cached the whole thing for me
(see below). Are you using a vanilla Linux system?
dan@darkstar:~/work/sqlite/bld$ vmtouch -ve ../src.fossil
Evicting ../src.fossil
Files: 1
Directories: 0
Evicted Pages: 15989 (62M)
Elapsed: 0.007548 seconds
dan@darkstar:~/work/sqlite/bld$ time ./sqlite3 ../src.fossil .dump >
/dev/null
real 0m9.450s
user 0m0.865s
sys 0m0.275s
dan@darkstar:~/work/sqlite/bld$ vmtouch -ve ../src.fossil
Evicting ../src.fossil
Files: 1
Directories: 0
Evicted Pages: 15989 (62M)
Elapsed: 0.007399 seconds
dan@darkstar:~/work/sqlite/bld$ time ./sqlite3 ../src.fossil .dump >
/dev/null
real 0m9.476s
user 0m0.860s
sys 0m0.280s
dan@darkstar:~/work/sqlite/bld$ vmtouch ../src.fossil
Files: 1
Directories: 0
Resident Pages: 15989/15989 62M/62M 100%
Elapsed: 0.001726 seconds
dan@darkstar:~/work/sqlite/bld$ time ./sqlite3 ../src.fossil .dump >
/dev/null
real 0m0.783s
user 0m0.722s
sys 0m0.061s
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
