Re: [sqlite] Strange eviction from Linux page cache

> 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

Re: [sqlite] Strange eviction from Linux page cache

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

Re: [sqlite] Strange eviction from Linux page cache

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

Re: [sqlite] Strange eviction from Linux page cache

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

Re: [sqlite] Strange eviction from Linux page cache

> 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

Re: [sqlite] Strange eviction from Linux page cache

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

Re: [sqlite] Strange eviction from Linux page cache

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

Re: [sqlite] Strange eviction from Linux page cache

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

Re: [sqlite] Strange eviction from Linux page cache

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

Re: [sqlite] Strange eviction from Linux page cache

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 >

Re: [sqlite] Strange eviction from Linux page cache

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

Re: [sqlite] Strange eviction from Linux page cache

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

Re: [sqlite] Strange eviction from Linux page cache

> 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

Re: [sqlite] Strange eviction from Linux page cache

t: 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 fo

Re: [sqlite] Strange eviction from Linux page cache

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

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

Re: [sqlite] Strange eviction from Linux page cache

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

Re: [sqlite] Strange eviction from Linux page cache

ot;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 th

Re: [sqlite] Strange eviction from Linux page cache

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

Re: [sqlite] Strange eviction from Linux page cache

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

Re: [sqlite] Strange eviction from Linux page cache

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