Re: [sqlite] Strange eviction or bypass of Linux page cache

2013-02-07 Thread James Vanns
> Don't write your database to NFS. I'd guess that your problem is that
> NFS driver for some reason thinks that the file was changed on the
> server (could be as easy as rounding of file modification time) and
> thus re-reads it from NFS server. And it has nothing to do with
> SQLite.

Nope. Makes no difference. I can generate and write the file locally then
copy it to NFS and serve it read-only from there.

Read my next post - I've confirmed that in fact, this isn't restricted
to NFS as I first thought.

Jim

> 
> Pavel
> 
> 
> On Thu, Feb 7, 2013 at 3:27 AM, James Vanns
>  wrote:
> > (Sorry if this gets posted twice - our damn mail server rewrites
> > outgoing mails so I had to unsubscribe and re-subscribe under a
> > different Email address)
> >
> > 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
> >
> > PS. This only happens over NFS - local DB files behave as expected
> > and fill the OS page cache.
> >
> > --
> > 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 or bypass of Linux page cache

2013-02-07 Thread Pavel Ivanov
> Anyone out there know how to correct this undesirable behaviour?
>
> PS. This only happens over NFS - local DB files behave as expected and fill 
> the OS page cache.

Don't write your database to NFS. I'd guess that your problem is that
NFS driver for some reason thinks that the file was changed on the
server (could be as easy as rounding of file modification time) and
thus re-reads it from NFS server. And it has nothing to do with
SQLite.


Pavel


On Thu, Feb 7, 2013 at 3:27 AM, James Vanns  wrote:
> (Sorry if this gets posted twice - our damn mail server rewrites outgoing 
> mails so I had to unsubscribe and re-subscribe under a different Email 
> address)
>
> 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
>
> PS. This only happens over NFS - local DB files behave as expected and fill 
> the OS page cache.
>
> --
> 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


[sqlite] Strange eviction or bypass of Linux page cache

2013-02-07 Thread James Vanns
(Sorry if this gets posted twice - our damn mail server rewrites outgoing mails 
so I had to unsubscribe and re-subscribe under a different Email address)

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

PS. This only happens over NFS - local DB files behave as expected and fill the 
OS page cache.

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