So you are caching data at the application level that is cached at the database 
page cache level which is cached in the Operating System file cache that lives 
in a file residing on disk -- effectively storing three copies of the data in 
memory.

What advantage does your third-level (application) cache provide that is not 
provided by the two lower level caches?  In other words if the data is already 
stored in the page cache and (that which isn't) is already in the OS file cache 
(which it must be since you read the data once already), what benefit does the 
third-level cache provide other than add the overhead and complication of its 
management?

Likely the most "efficient" way to ensure you do not serve stale data is to get 
rid of the application level caching and simply re-retrieve the data when you 
need it.  Unless of course you are seriously memory constrained such at running 
this on a model 5150.  Or your query's are very long and complicated.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Wout Mertens
>Sent: Tuesday, 7 November, 2017 11:08
>To: SQLite mailing list
>Subject: [sqlite] Most efficient way to detect on-disk change
>
>I'm working with a db that's only written to in transations, and each
>transaction increases a db-global version counter.
>
>This means that I can cache all reads, unless the version changed.
>
>What would be the most efficient way to make sure I *never* serve
>stale
>data?
>
>Right now everything's a single process, so it's really easy, just
>clear
>the cache on every write. However, I want to be prepared for the near
>future where I will have multiple processes using this db file.
>
>I'm thinking that to detect writes, this might be a safe approach:
>
>Before serving any cached read, check the timestamp on the wal file.
>If it
>changed, read the global version. If it changed, clear the cache.
>Otherwise, serve the cached read.
>
>Is it safe to assume that all writes would mean change of the wal
>file
>timestamp?
>More importantly, is it faster to check the timestamp or would a
>prepared
>query for the version actually be faster (and safer)?
>
>Also, I'm using WAL right now, but I wonder if that's really useful
>given
>the single-writer-at-a-time?
>
>Thank you for your insights,
>
>Wout.
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to