> 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
