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
