caching SQLite database.
(1) By den-t8 on 2022-04-07 20:45:43 [link] [source]
I use an application built around an SQLite DB, but I find when it is updating the DB it is very slow and stalls the GUI display for 10-15 seconds during the update. This is probably because the DB is on a file server (the application isn't aware of this) so any access to the DB is at network speed, not disk speed. I have used the application where the DB is on the local disk and the access times are very much faster and impercectible on the GUI This application is the only user of the DB, so would it be possible to cache the DB in local memory somehow to speed up access. OR Have a synched copy of the DB in memory for the application to use and only update (synchronize) the file server occasionally and when the app closes. The DB size is in the order of 70 - 100 Mb. and consists of multiple tables. I haven't approached the application team to see if they would even consider this approach.
(2) By Bjoern Hoehrmann (bjoern) on 2022-04-08 18:00:23 in reply to 1 [link] [source]
If you do not need synchronisation with open write transactions, then the backup API could be used for this (restore remote database to in-memory database, periodically backup in-memory database to remote).
You might also be interested in https://litestream.io/
(3.1) By Larry Brasfield (larrybr) on 2022-04-09 15:03:50 edited from 3.0 in reply to 1 [link] [source]
You should at least understand this article: SQLite Over a Network, Caveats and Considerations. It was written because many people end up in the same unhappy place.
(4) By Simon Slavin (slavin) on 2022-04-09 14:47:36 in reply to 3.0 [link] [source]
It's great to see a new article written about that, which includes many of the things we've said before in one place.
Could the word 'locking' be added somewhere in that article ? It's search-engine bait. The current landing page is the obsolete
https://sqlite.org/lockingv3.html
and I'm hoping this new page will take its place.
(5) By Larry Brasfield (larrybr) on 2022-04-09 15:19:54 in reply to 4 [link] [source]
Thanks. The article was published almost 14 months ago, so that "new article" term threw me for a bit. Then I noticed that I inadvertently linked a draft version. (That's fixed now.)
I wanted to put "locking" into the article, and pulled it into Emacs looking for the right place. But as I sought such, and considered why there was no mention of locking there, I realized that I am unaware of locking failure being a factor in problems with using SQLite over a network filesystem. So, I hesitate.
(To be clear, that last sentence is what I call "a statement of ignorance", not a positive assertion.)
Are you aware of discussion here or anywhere else implicating file locking as being responsible for problems with using SQLite to directly modify a DB over a network? I will be happily educated on this.
(7) By Simon Slavin (slavin) on 2022-04-10 13:58:02 in reply to 5 [source]
I was mostly thinking of
https://www.sqlite.org/howtocorrupt.html#_filesystems_with_broken_or_missing_lock_implementations
and perhaps the 2.2.2 section below it.
I haven't experienced those myself, but I once clearly demonstrated to myself an installation where a second computer opening a file over an NFS had its locks ignored. In other words, the first connection to open a file could place a lock, but the second (and all subsequent ?) to open that file had all lock attempts silently dropped. This was me writing my own software, though, not something involving SQLite. The devs of that NFS acknowledged my bug report, and did nothing about it.
(8.1) By Larry Brasfield (larrybr) on 2022-04-10 15:40:22 edited from 8.0 in reply to 7 [link] [source]
I can see that the cited history of NFS deficiencies warrants brief mention of the poor lock implementation issue in the "... Caveats ..." article. Thanks.
(6.6) By cj (sqlitening) on 2022-04-09 15:52:50 edited from 6.5 in reply to 1 [link] [source]
Windows? Does delay occur when a single user? "Begin Exclusive" is required with multiple users. Are you returning megabytes of data? Hopefully not some virus checker. 10-15 seconds during an update should be milliseconds so I'd look for multiple users trying to start a transaction causing a deadlock. Maybe display a begin transaction and end transaction on the screen and figure out where it happens. Be sure there is error code after each SQLite call including "Begin Exclusive" and "End".