[sqlite] sqlite3 (or sqlite4) performance on NFS
Stephen Chrzanowski wrote: >>From what I understand; > - Read-Only data > - Data doesn't change frequently > - Central repository for data > - Network latency causing issues > > My two cents on this is to keep a database revision ID kicking around and > do a SQLite backup of the remote data to a local storage medium. At > application launch, check the local version of the database, then check the > NFS version, and if there is a mismatch or a local copy doesn't exist, have > the application ask (Or force if no local copy exists) to copy the data > from remote to local, then read data from the local source. This will be a > bit of a PITA if you're talking gigabytes of storage on a saturated 100mbit > network or if drive space is limited. (I love my quiet GigE network) Congratulations, you've just reinvented AFS, but without any of its convenience. Seriously - there are many other distributed filesystems out there, all designed because users keep running into the same deficiencies of NFS, over and over again. Please, can we stop reinventing this wheel now? -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/
[sqlite] sqlite3 (or sqlite4) performance on NFS
>From what I understand; - Read-Only data - Data doesn't change frequently - Central repository for data - Network latency causing issues My two cents on this is to keep a database revision ID kicking around and do a SQLite backup of the remote data to a local storage medium. At application launch, check the local version of the database, then check the NFS version, and if there is a mismatch or a local copy doesn't exist, have the application ask (Or force if no local copy exists) to copy the data from remote to local, then read data from the local source. This will be a bit of a PITA if you're talking gigabytes of storage on a saturated 100mbit network or if drive space is limited. (I love my quiet GigE network) If you ever make a change to the database of relevance, update the database ID on the NFS version and any new client connections will do the version validation and (ask to...) pull the required changes when required. I may love my GigE network, but just two days ago I turned off a network switch accidentally and lost all communication to my server unintentionally. Stupid things like that could put a halt on your production environment. If you keep things local, the network isn't going to matter (Unless your app also talks to other network appliances/devices/etc via the affected network) and the gain is that you're also going to increase the speed of your app. Depending on database size and memory amount, you could also increase the speed of database transactions by not only copying the data from the remote storage to a local drive, but go a step further and do another backup from local drive to RAM by doing a backup to [ :memory: ] and then access what is in memory. The downside of that, again depending on size of the database, is not only memory constraint but initial load time of the application considering NAS->Local->Memory. On Sat, Apr 11, 2015 at 9:30 AM, 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. > > -- > Regards, > Peng > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] sqlite3 (or sqlite4) performance on NFS
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 = 1; 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
[sqlite] sqlite3 (or sqlite4) performance on NFS
On 11 Apr 2015, at 2:30pm, Peng Yu wrote: > 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). No. Data will flow to a local disk much faster than it can flow across your network. But you can make network access faster using the following: Open the database READ_ONLY or use "PRAGMA query_only = YES". This may increase speed a little. Use the following: PRAGMA journal_mode = OFF; PRAGMA locking_mode = EXCLUSIVE; Experiment with values for the following command: PRAGMA cache_size = 1; The best values for this depend on complicated aspects of your hardware: the size of blocks on your disk, the size of packets on your network, the speed of the data bus in your computer, and other things. The default is 2000. You may find that bigger or smaller numbers make a difference. Or you may find they make no difference. Simon.
[sqlite] sqlite3 (or sqlite4) performance on NFS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/11/2015 06:30 AM, Peng Yu wrote: > 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). The latency is what is getting you. SQLite uses synchronous/blocking I/O. What version of NFS are you using? NFSv4 has features that significantly reduce the effects of latency. If you want to maximize performance then I recommend writing your own VFS, which is very easy for read only. That will let you do read ahead and caching. Heck you could just copy the database locally and use that. One technique I used in the past (for WAN optimisers) is to record the access patterns for a newly opened file, and then on subsequent opens pre-read that same data. This was very effective across many file formats, but SQLite was not tested/relevant. It also meant not having to understand the actual file formats. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlUpwCQACgkQmOOfHg372QSt1gCgzORbl2o18gKfkzMx8qph557L AQUAn0Rcog3QZbwWeAck8tFlira84xZU =dgFW -END PGP SIGNATURE-
[sqlite] sqlite3 (or sqlite4) performance on NFS
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. -- Regards, Peng
