> 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 > <james.va...@framestore.com> 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 = -<size of DB file in kbytes> > > > > 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
