Re: [sqlite] Strange eviction from Linux page cache

[James Vanns]

> If you do "sqlite3 $file -vfs unix-none $stmt" I think all the
> fcntl() calls to lock and unlock the db file are omitted. Does
> that stop the pages from being evicted when sqlite opens the
> db file?

Bingo! I upgraded and now, running this over and over again all
pages in the kernel page cache are retained! Whoop! Now, somehow
I need to pass these options to the Python API :\

Thanks for your help - now see my other question ;)

Jim

> ___
> 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

Re: [sqlite] Strange eviction from Linux page cache

[Richard Hipp]

On Thu, Feb 7, 2013 at 12:34 PM, James Vanns wrote:

> > If you do "sqlite3 $file -vfs unix-none $stmt" I think all the
> > fcntl() calls to lock and unlock the db file are omitted. Does
> > that stop the pages from being evicted when sqlite opens the
> > db file?
>
> I'm not sure I can :( My version doesn't appear to offer that option;
>
> sqlite3: Error: unknown option: -vfs
> Use -help for a list of options.
>
> sqlite3 -version
> 3.6.20
>

Precompiled binaries of 3.7.15 for Linux are at
http://www.sqlite.org/download.html


>
> How would I go about doing that via the Python DBI interface?
>
> Jim
>
> > ___
> > 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
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange eviction from Linux page cache

[James Vanns]

> If you do "sqlite3 $file -vfs unix-none $stmt" I think all the
> fcntl() calls to lock and unlock the db file are omitted. Does
> that stop the pages from being evicted when sqlite opens the
> db file?

I'm not sure I can :( My version doesn't appear to offer that option;

sqlite3: Error: unknown option: -vfs
Use -help for a list of options.

sqlite3 -version
3.6.20

How would I go about doing that via the Python DBI interface?

Jim

> ___
> 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

Re: [sqlite] Strange eviction from Linux page cache

[Dan Kennedy]

On 02/08/2013 12:21 AM, James Vanns wrote:

If you don't even have to close the SQLite shell for that to
happen, I'm guessing it's an interaction with POSIX/fcntl file
locking, which theoretically works over NFS but as I recall has
some oddities.  What happens if you do this?

pragma locking_mode = exclusive; select * from ...; select * from
...;


Hah! Funny you should say that as it is precisely what I'm doing
now...

I straced sqlite3 both in 'nfs mode' and 'local mode' and observed no
great difference but I did notice the initial fcntl() F_RDLCK which
led me to read this;

http://sqlite.1065341.n5.nabble.com/SQLite-on-NFS-cache-coherency-td33697.html

 and then this;

http://www.sqlite.org/pragma.html#pragma_locking_mode

Indeed there is a difference between these;

sqlite3 /nfs/file.db 'SELECT * from big_table;SELECT * from
big_table;SELECT * from big_table;' 1> /dev/null sqlite3 /nfs/file.db
'PRAGMA locking_mode = EXCLUSIVE;SELECT * from big_table;SELECT *
from big_table;SELECT * from big_table;' 1> /dev/null

In the first instance every single SELECT results in page cache
eviction and re-reads. The second only the initial read. Much better.
However, if you execute that same 2nd instance again straight away
(so a new process) the whole lot gets evicted and re-read. So not
quite there!


If you do "sqlite3 $file -vfs unix-none $stmt" I think all the
fcntl() calls to lock and unlock the db file are omitted. Does
that stop the pages from being evicted when sqlite opens the
db file?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange eviction from Linux page cache

[James Vanns]

> If you don't even have to close the SQLite shell for that to happen,
> I'm guessing it's an interaction with POSIX/fcntl file locking, which
> theoretically works over NFS but as I recall has some oddities.  What
> happens if you do this?
> 
>   pragma locking_mode = exclusive;
>   select * from ...;
>   select * from ...;

Hah! Funny you should say that as it is precisely what I'm doing now...

I straced sqlite3 both in 'nfs mode' and 'local mode' and observed no great
difference but I did notice the initial fcntl() F_RDLCK which led me to read
this;

http://sqlite.1065341.n5.nabble.com/SQLite-on-NFS-cache-coherency-td33697.html

and then this;

http://www.sqlite.org/pragma.html#pragma_locking_mode

Indeed there is a difference between these;

sqlite3 /nfs/file.db 'SELECT * from big_table;SELECT * from big_table;SELECT * 
from big_table;' 1> /dev/null
sqlite3 /nfs/file.db 'PRAGMA locking_mode = EXCLUSIVE;SELECT * from 
big_table;SELECT * from big_table;SELECT * from big_table;' 1> /dev/null

In the first instance every single SELECT results in page cache eviction and 
re-reads. The second only the initial read. Much better. However, if you 
execute that same 2nd instance again straight away (so a new process) the whole 
lot gets evicted and re-read. So not quite there!

> If the database is write-once and read-only, then exclusive locking
> mode should not actually block any readers (since no write locks are
> ever taken).  If that helps in the two-selects case, it still might
> not help if unlock at process termination triggers your performance
> issue as well.  In that case, try specifying the "unix-none" VFS to
> sqlite3_open_v2 for those readers only, which should turn all locking
> off for them.

Interesting.

> My other guess would be an interaction with the early open calls;
> AFAIK, unless SQLite is explicitly told to open the database
> read-only,
> it will try an O_RDWR open first, which will fail on a 0444 file but
> might plausibly trigger unfortunate codepaths somewhere in the kernel
> in the process of failing.

Yes, this is what it does. The strace output confirms it;
open("/nfs/file.db", O_RDWR|O_CREAT, 0644) = -1 EACCES (Permission denied)
open("/nfs/file.db", O_RDONLY) = 3
...
lseek(3, 0, SEEK_SET)  = 0
read(3, "SQLite format 3") = 100
...
fcntl(3, F_SETLK, {type=F_RDLCK, whence=SEEK_SET, start=1073741824, len=1}) = 0
fcntl(3, F_SETLK, {type=F_RDLCK, whence=SEEK_SET, start=1073741826, len=510}) = 0
fcntl(3, F_SETLK, {type=F_UNLCK, whence=SEEK_SET, start=1073741824, len=1}) = 0
...
fcntl(3, F_SETLK, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0}) = 0
fcntl(3, F_SETLK, {type=F_RDLCK, whence=SEEK_SET, start=1073741824, len=1}) = 0
fcntl(3, F_SETLK, {type=F_RDLCK, whence=SEEK_SET, start=1073741826, len=510}) = 0
fcntl(3, F_SETLK, {type=F_UNLCK, whence=SEEK_SET, start=1073741824, len=1}) = 0
...
read()
read()
read()
etc.

> I'm interested to see whether any of the above does any good, to
> improve my own knowledge of NFS.  :-)
> 
>---> Drake Wilson
> ___
> 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

Re: [sqlite] Strange eviction from Linux page cache

[Drake Wilson]

Quoth James Vanns , on 2013-02-07 16:32:49 +:
> 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)

If you don't even have to close the SQLite shell for that to happen,
I'm guessing it's an interaction with POSIX/fcntl file locking, which
theoretically works over NFS but as I recall has some oddities.  What
happens if you do this?

  pragma locking_mode = exclusive;
  select * from ...;
  select * from ...;

If the database is write-once and read-only, then exclusive locking
mode should not actually block any readers (since no write locks are
ever taken).  If that helps in the two-selects case, it still might
not help if unlock at process termination triggers your performance
issue as well.  In that case, try specifying the "unix-none" VFS to
sqlite3_open_v2 for those readers only, which should turn all locking
off for them.

My other guess would be an interaction with the early open calls;
AFAIK, unless SQLite is explicitly told to open the database read-only,
it will try an O_RDWR open first, which will fail on a 0444 file but
might plausibly trigger unfortunate codepaths somewhere in the kernel
in the process of failing.

I'm interested to see whether any of the above does any good, to
improve my own knowledge of NFS.  :-)

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange eviction from Linux page cache

[James Vanns]

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=' over NFS then the pages are
> cached as
> expected.
> It is o

Re: [sqlite] Strange eviction from Linux page cache

[Michael Black]

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=' 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

Re: [sqlite] Strange eviction from Linux page cache

[Dan Kennedy]

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


real0m9.450s
user0m0.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


real0m9.476s
user0m0.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


real0m0.783s
user0m0.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=' 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

Re: [sqlite] Strange eviction from Linux page cache

[Jay A. Kreibich]

On Thu, Feb 07, 2013 at 04:11:18PM +0100, Eduardo Morras scratched on the wall:

> If you need cache being persistent between process on the same server,
> you can build a ram disk, write the db there and use it from any
> process. This way you read the db only once from nfs. Even better, you
> can shutdown nfs because a simple ftp/http server and wget/fetch can
> do what you want, serve/receive read only files.

  It would be more straight forward to just have SQLite create an
  in-memory database, and then use the backup APIs to copy the central
  database to the in-memory database.  Once that was done, all requests
  could be serviced out of the in-memory database.
  
  In this way, the database would always be in process memory, with no
  dependencies on either the NFS link (after load) or the file-system cache.
  It would also require a very minimal number of changes to the process
  code.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange eviction from Linux page cache

[Michael Black]

Would it be any use to you to have a separate process which mmaps the file?
Seems to me that would probably keep all the pages in cache constantly.


I just did a local test on my NFS setup and the file appears to cache just
fine.
Does yours behave differently?

#include 
#include 

int main(int argc, char *argv[])
{
  sqlite3 *db;
  int rc;
  if (argc != 2) {
printf("Usage: %s dbfile\n",argv[0]);
return 1;
  }
  rc = sqlite3_open_v2(argv[1],,SQLITE_OPEN_READONLY,NULL);
  if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
return 1;
  }
  rc=sqlite3_exec(db,"select * from t",NULL,NULL,NULL);
  if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
return 1;
  }
  sleep(60);
  return 0;
}
I had an 8.7MB database I used this on

vmtouch insert.db
   Files: 1
 Directories: 0
  Resident Pages: 0/2128  0/8M  0%
 Elapsed: 0.000394 seconds
./testro insert.db&
[1] 26598
sqlite]$ vmtouch insert.db
   Files: 1
 Directories: 0
  Resident Pages: 2128/2128  8M/8M  100%
 Elapsed: 0.000556 seconds


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange eviction from Linux page cache

[Richard Hipp]

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=' 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

Re: [sqlite] Strange eviction from Linux page cache

[James Vanns]

> You should use these pragmas too :
> 
> PRAGMA temp_store = MEMORY;
> PRAGMA read_uncommited = TRUE;

I'll look in to those too. Thanks.

> If not, a big select with a big sort could try to use temporal files
> on your nfs server. As you aren't doing any write, no need to wait
> for write locking.
> 
> If you need cache being persistent between process on the same
> server, you can build a ram disk, write the db there and use it from
> any process. This way you read the db only once from nfs. Even
> better, you can shutdown nfs because a simple ftp/http server and
> wget/fetch can do what you want, serve/receive read only files.

I *could*, but I shouldn't have to ;) It would make the whole
execution chain/data flow more complicated than it needs to be too!

Jim

> > Regards,
> > 
> > Jim Vanns
> > 
> > --
> > Jim Vanns
> > Senior Software Developer
> > Framestore
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> ---   ---
> Eduardo Morras 
> ___
> 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

Re: [sqlite] Strange eviction from Linux page cache

[James Vanns]

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=' 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

Re: [sqlite] Strange eviction from Linux page cache

[Eduardo Morras]

On Thu, 7 Feb 2013 09:56:27 + (GMT)
James Vanns  wrote:

> Hello list. I'd like to ask someone with more SQLite experience than me a 
> simple question. First, some background;
> 
> Distribution: Scientific Linux 6.3
> Kernel: 2.6.32-279.9.1.el6.x86_64
> SQLite version: 3.6.20
> 
> We have a single process that, given some data, does some processing and 
> writes it all to a single SQLite DB file. This is a write-once process. When 
> this task is finished, the file itself is marked as read only (0444).
> 
> This file exists on an NFS share for multiple users to read - nothing further 
> is ever written to it. The problem we're seeing is that when this DB file is 
> read from (over NFS) none of the pages are cached (despite ~12GB free for 
> page cache use) or at least immediately evicted. This is quite detrimental to 
> performance because our resulting data files (SQLite DB files) are between 
> 100 to 400 MB in size. We *want* it to be cached - the whole thing. The page 
> cache would do this nicely for us and allow multiple processes on the same 
> machine to share that data without any complication.
> 
> I understand that SQLite implements it's own internal page cache but why, on 
> a standard desktop machine, will it not use the page cache. Is there anyway 
> of forcing it or bypassing the internal page cache in favour of the job that 
> Linux already does? I cannot find any reference to O_DIRECT or madvise() or 
> favdise() etc. in the code. The following PRAGMAs don't help either;
> 
> PRAGMA writable_schema = OFF
> PRAGMA journal_mode = OFF
> PRAGMA synchronous = OFF
> 
> PRAGMA cache_size = -
> 
> Obviously that last one works - but only for a single process and for the 
> lifetime of that process. We want the pages to reside in RAM afterwards.
> 
> Anyone out there know how to correct this undesirable behaviour?

You should use these pragmas too :

PRAGMA temp_store = MEMORY;
PRAGMA read_uncommited = TRUE;

If not, a big select with a big sort could try to use temporal files on your 
nfs server. As you aren't doing any write, no need to wait for write locking.

If you need cache being persistent between process on the same server, you can 
build a ram disk, write the db there and use it from any process. This way you 
read the db only once from nfs. Even better, you can shutdown nfs because a 
simple ftp/http server and wget/fetch can do what you want, serve/receive read 
only files.

> Regards,
> 
> Jim Vanns
> 
> -- 
> Jim Vanns
> Senior Software Developer
> Framestore
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange eviction from Linux page cache

[James Vanns]

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

Re: [sqlite] Strange eviction from Linux page cache

[Simon Slavin]

On 7 Feb 2013, at 2:27pm, James Vanns  wrote:

> Hi Simon. Yes, the connection is closed - the process that writes the DB file 
> isn't memory resident (meaning, it isn't a daemon). The connection is 
> implicitly closed (and transaction committed?) by the process terminating.

Okay.  I've previously seen a problem where file protections were made before 
the file was closed, and the closing process reset them.

> The problem only arises when the file is hosted by an NFS server - locally, 
> we do not see the problem.

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.

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

Re: [sqlite] Strange eviction from Linux page cache

[James Vanns]

Hi Simon. Yes, the connection is closed - the process that writes the DB file 
isn't memory resident (meaning, it isn't a daemon). The connection is 
implicitly closed (and transaction committed?) by the process terminating. The 
problem only arises when the file is hosted by an NFS server - locally, we do 
not see the problem.

Hours can go by before the first read.

Thanks,

Jim

PS. Using the vmtouch tool we can see that the page cache never really reads-in 
and retains any of the file data.

- Original Message -
From: "Simon Slavin" <slav...@bigfraud.org>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Thursday, 7 February, 2013 1:47:31 PM
Subject: Re: [sqlite] Strange eviction from Linux page cache


On 7 Feb 2013, at 9:56am, James Vanns <james.va...@framestore.com> wrote:

> We have a single process that, given some data, does some processing and 
> writes it all to a single SQLite DB file. This is a write-once process. When 
> this task is finished, the file itself is marked as read only (0444).

Can you verify that your connection to the file is closed, and any journal 
files relating to the database are already deleted before the protection on the 
file is changed ?

> This file exists on an NFS share for multiple users to read - nothing further 
> is ever written to it. The problem we're seeing is that when this DB file is 
> read from (over NFS) none of the pages are cached (despite ~12GB free for 
> page cache use) or at least immediately evicted.

SQLite uses very simple file calls and doesn't rely on a deep understanding of 
file systems.  It may be that whatever NFS driver you're using never uses the 
cache even if the file is market read-only.  In other words, this is done at 
the driver level rather than inside SQLite.  Can you test this by, for example, 
accessing a long read-only text file over the same NFS connection and seeing if 
reads of different parts of this text file suggest that it is or isn't being 
cached ?

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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange eviction from Linux page cache

[James Vanns]

I'm afraid I couldn't disagree more. Everything else works fine and uses the 
*page cache*. We run 1000s of machines that do. You are getting confused 
between fs-cache and the kernels page cache.

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 1:47:03 PM
Subject: RE: [sqlite] Strange eviction from Linux page cache

Nothing to do with SQLite.  NFS won't use cache by default.  You have to
mount it with the "fsc" option.
https://access.redhat.com/knowledge/docs/en-US/Red_Hat_Enterprise_Linux/6/ht
ml/Storage_Administration_Guide/fscachenfs.html



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James Vanns
Sent: Thursday, February 07, 2013 3:56 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Strange eviction from Linux page cache

Hello list. I'd like to ask someone with more SQLite experience than me a
simple question. First, some background;

Distribution: Scientific Linux 6.3
Kernel: 2.6.32-279.9.1.el6.x86_64
SQLite version: 3.6.20

We have a single process that, given some data, does some processing and
writes it all to a single SQLite DB file. This is a write-once process. When
this task is finished, the file itself is marked as read only (0444).

This file exists on an NFS share for multiple users to read - nothing
further is ever written to it. The problem we're seeing is that when this DB
file is read from (over NFS) none of the pages are cached (despite ~12GB
free for page cache use) or at least immediately evicted. This is quite
detrimental to performance because our resulting data files (SQLite DB
files) are between 100 to 400 MB in size. We *want* it to be cached - the
whole thing. The page cache would do this nicely for us and allow multiple
processes on the same machine to share that data without any complication.

I understand that SQLite implements it's own internal page cache but why, on
a standard desktop machine, will it not use the page cache. Is there anyway
of forcing it or bypassing the internal page cache in favour of the job that
Linux already does? I cannot find any reference to O_DIRECT or madvise() or
favdise() etc. in the code. The following PRAGMAs don't help either;

PRAGMA writable_schema = OFF
PRAGMA journal_mode = OFF
PRAGMA synchronous = OFF

PRAGMA cache_size = -

Obviously that last one works - but only for a single process and for the
lifetime of that process. We want the pages to reside in RAM afterwards.

Anyone out there know how to correct this undesirable behaviour?

Regards,

Jim Vanns

-- 
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

Re: [sqlite] Strange eviction from Linux page cache

[Simon Slavin]

On 7 Feb 2013, at 9:56am, James Vanns  wrote:

> We have a single process that, given some data, does some processing and 
> writes it all to a single SQLite DB file. This is a write-once process. When 
> this task is finished, the file itself is marked as read only (0444).

Can you verify that your connection to the file is closed, and any journal 
files relating to the database are already deleted before the protection on the 
file is changed ?

> This file exists on an NFS share for multiple users to read - nothing further 
> is ever written to it. The problem we're seeing is that when this DB file is 
> read from (over NFS) none of the pages are cached (despite ~12GB free for 
> page cache use) or at least immediately evicted.

SQLite uses very simple file calls and doesn't rely on a deep understanding of 
file systems.  It may be that whatever NFS driver you're using never uses the 
cache even if the file is market read-only.  In other words, this is done at 
the driver level rather than inside SQLite.  Can you test this by, for example, 
accessing a long read-only text file over the same NFS connection and seeing if 
reads of different parts of this text file suggest that it is or isn't being 
cached ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange eviction from Linux page cache

[Michael Black]

Nothing to do with SQLite.  NFS won't use cache by default.  You have to
mount it with the "fsc" option.
https://access.redhat.com/knowledge/docs/en-US/Red_Hat_Enterprise_Linux/6/ht
ml/Storage_Administration_Guide/fscachenfs.html



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James Vanns
Sent: Thursday, February 07, 2013 3:56 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Strange eviction from Linux page cache

Hello list. I'd like to ask someone with more SQLite experience than me a
simple question. First, some background;

Distribution: Scientific Linux 6.3
Kernel: 2.6.32-279.9.1.el6.x86_64
SQLite version: 3.6.20

We have a single process that, given some data, does some processing and
writes it all to a single SQLite DB file. This is a write-once process. When
this task is finished, the file itself is marked as read only (0444).

This file exists on an NFS share for multiple users to read - nothing
further is ever written to it. The problem we're seeing is that when this DB
file is read from (over NFS) none of the pages are cached (despite ~12GB
free for page cache use) or at least immediately evicted. This is quite
detrimental to performance because our resulting data files (SQLite DB
files) are between 100 to 400 MB in size. We *want* it to be cached - the
whole thing. The page cache would do this nicely for us and allow multiple
processes on the same machine to share that data without any complication.

I understand that SQLite implements it's own internal page cache but why, on
a standard desktop machine, will it not use the page cache. Is there anyway
of forcing it or bypassing the internal page cache in favour of the job that
Linux already does? I cannot find any reference to O_DIRECT or madvise() or
favdise() etc. in the code. The following PRAGMAs don't help either;

PRAGMA writable_schema = OFF
PRAGMA journal_mode = OFF
PRAGMA synchronous = OFF

PRAGMA cache_size = -

Obviously that last one works - but only for a single process and for the
lifetime of that process. We want the pages to reside in RAM afterwards.

Anyone out there know how to correct this undesirable behaviour?

Regards,

Jim Vanns

-- 
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