SQLite Forum

Is data in the OS buffers faster to read?
Login

Is data in the OS buffers faster to read?

(1) By example-user on 2020-09-13 00:12:14 [source]

E.g. If there are two processes, A, the writer, and B the reader.

Both are in wal mode, sync=normal.

If process A writes a large amount of data, and the OS has not fsync'd that to disk yet, are those writes now in the RAM of the OS buffers?

Will process B's reads now be faster as some of the data is still in the RAM backed OS buffers which should be faster to read than the disk based data?

How much faster would it be? Would it be Redis-like speeds for simple lookups?

(2) By Stephan Beal (stephan) on 2020-09-13 00:19:55 in reply to 1 [link] [source]

If process A writes a large amount of data, and the OS has not fsync'd that to disk yet, are those writes now in the RAM of the OS buffers? ... How much faster would it be? Would it be Redis-like speeds for simple lookups?

It's ostensibly always faster to read data from the OS's filesystem cache, but...

  1. Applications have neither accurate insight nor control over what is actually in those buffers, nor for how long it's in there.

  2. Apps can neither generically quantify that difference nor rely on it. Such details are opaque to the OS and can/do change from system to system depending on oodles and oodles of factors far beyond any application's control.

(It feels good to say oodles again. We don't get to say that often here in Germany.)

(3) By example-user on 2020-09-13 00:28:13 in reply to 2 [link] [source]

This is true that the application process will not have visibility or can rely on the behaviour of the OS.

But, SQLite does give you the ability to hint that writes can be stored in the buffer (with pragma sync=x), and you might be able to measure it with ebpf/dtrace probes for kernel functions.

Im just wondering if I had two processes that exhibit that pattern (new data written by A, immediately read by B) if they would see oodles of perf increases.

(4) By Keith Medcalf (kmedcalf) on 2020-09-13 01:34:54 in reply to 3 [link] [source]

Im just wondering if I had two processes that exhibit that pattern (new data written by A, immediately read by B) if they would see oodles of perf increases.

Assuming that all the following are true

  • the OS maintained the data in some sort of filesystem or block cache when process A did the write operation -- that is, that the filesystem or OS actually cached the data. Note that it is possible to open a file in "talk to the device and not the cache" mode in which case device writes will not be stored in a filesystem/OS cache -- this is dependent on the application, OS, and filesystem -- SQLite3 does not in the normal course explicitly disable the OS/filesystem cache but rather permits the OS/filesystem to do whatever it happens to think/been configured as the "jolly good thing to do".

  • that the data written by process A being in the cache is still in the cache at the time process B tries to read it (that is, assuming that it got there that it has not been evicted by the OS/filesystem for some reason under the exclusive purview of the OS/filesystem and how it has been configured)

  • that process B has been set to read from the cache and not from the underlying device. Note that it is possible to open a file in "talk to the device and not the cache" mode in which case device reads will not be serviced from a filesystem/OS cache -- this is dependent on the application, OS, and filesystem -- SQLite3 does not in the normal course explicitly disable the OS/filesystem cache but rather permits the OS/filesystem to do whatever it happens to think/been configured as the "jolly good thing to do".

  • that the time to read data from the underlying device takes an oodle of time longer than the transition to and from supervisor mode since that transition is required whether the read request is serviced from the filesystem/OS cache or the underlying device

then you would indeed see oodles of performance increase (via a reduction in supervisor time). However, you do not, for the most part, have any control whatsoever over this.

In other words, assuming that all the conditions are true the size of the oodle depends on the relative difference between the process of "transition to supervisor, retrieve data from cache, return to usermode" and "transition to supervisor, retrieve data from the device, return to usermode".

If the "cache" is in RAM and the "device" is a "ramdisk", then the size of the oodle will be close to zero. If the "cache" is RAM and the device is a book in a library on the other side of the planet, the size of the "oodle" could be measured in days (or in these current days, weeks or months).

(5) By Scott Robison (casaderobison) on 2020-09-13 18:15:34 in reply to 2 [link] [source]

Imagine "oodles and oodles" in Carl Sagan's voice...