On 2015-04-11 03:30 PM, Peng Yu wrote: > Hi, > > I know that sqlite3 is not recommended to be used on NFS. But I only > use sqlite3 for read only (the only time that I write is to load the > initial data to the data base). With this restriction, NFS should be > fine as the storage since no file locking is needed. > > I remember that sqlite3 on NFS is significantly slower than on a local > disk (a few times slower when I tested a few years ago). > > I am wondering for my limited usage scenario, is it possible to make > sqlite3 on NFS as fast as sqlite3 on local storage (such as disable > file locking). Thanks.
You can use SQLite for reading over a network, it's just write concurrency that cannot be guaranteed (as you seem to know already). You can switch of syncing easily with pragma synchronous = 0; but that only really helps write speeds. Not sure about disabling File locking completely but you can create your own VFS to circumvent it similar to the "unix-none" VFS, but I wouldn't advise that. The slowness is not due to SQLite doing anything different over a network, it's because the data is on the other side of a router, Nothing much can make that any faster except maybe some network tweaking. The other advice for speed on a read-only DB is the same as when the DB file is local: - Set the Page size to match your physical layer (i.e Network in your case), usually 4K (pragma page_size = 4096;). - Make sure the cache has ample space to push large query data around (pragma chache_size = 10000; perhaps). - Put the temp store to memory if you use temp tables during queries (pragma temp_store = 2;). - Use a memory-based journal mode (or at least a truncate) when doing queries in transactions (pragma journal_mode = MEMORY; ). Make sure whatever process builds the database keeps the Indices up to date and vacuums the database after update. If the network is still slow (such as an internet based VPN on a low bandwidth connection), what I find works well is periodic updates. I RAR the DB file in a script on the server, rename it to some hashed value, when all is done, publish the hash key in a file so all clients can see which is the latest complete DB, copy that to their own drives and uncompress it, This can be done by a second thread so that only when the data is completely available locally and uncompressed does it switch to that DB file as the new data source. The user on the client machine never realises any slowness. (You might have to indicate exactly how "fresh" the data is though). Links for all the above: https://www.sqlite.org/vfs.html https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_cache_size https://www.sqlite.org/pragma.html#pragma_page_size https://www.sqlite.org/pragma.html#pragma_journal_mode https://www.sqlite.org/pragma.html#pragma_temp_store https://www.sqlite.org/lang_reindex.html https://www.sqlite.org/lang_vacuum.html Good luck Ryan
