5 ms·

There is no point to use sqlite3 in default journaling mode. I bet results may be even better than dbm if you use PRAGMA journal_mode=WAL;

nonethewiser 4y ago
more info https://sqlite.org/wal.html https://sqlite.org/wal.html
kuschku 4y ago
That only works if all processes accessing it share a memory namespace (aka, are not in containers/jails/VMs and on the same physical machine).
- joppy 4y ago
  WAL works for different processes accessing the same SQLite database. It's meant to improve concurrency, in fact.
  - kuschku 4y ago
    Yes, but the processes still need to share a memory namespace. In WAL mode, SQLite creates a shared memory file which gets mapped into any process trying to access it (that’s why WAL mode creates additional .shm files). But that only works if all given processes run on the same physical machine, and not in containers/jails/VM.
    - formerly_proven 4y ago
      I dunno what SQLite is doing specifically but you can certainly mmap a file across containers and it behaves like you’d think. SELinux and other hardening options might interfere though, because shared mmaps should imho be viewed as a very likely vector for cross-process exploitation.
- fuckstick 4y ago
  There isn’t really a thing called a memory namespace, in Linux or the BSDs at least. In Linux there are cgroups that deal with accounting and you can share VM space among cloned processes (which we then typically refer to as threads). Neither of these affect the ability to share memory among processes. The only namespace that matters here is the filesystem/mount namespace. There is no reason you can’t access the same shared SQLite database on a common volume between containers on Linux for instance.
  - kuschku 4y ago
    If you're using a system with SELinux, your containers won't be able to use the same shared memory file. Which is relatively common, as CoreOS nowadays has SELinux by default.
    - fuckstick 4y ago
      > If you're using a system with SELinux, your containers won't be able to use the same shared memory file. That is false. Whether you mmap the same file is dependent on the specific SELinux policy - which is by design, highly configurable. I am also surprised/skeptical that the default configuration for those OSes for a shared volume/mount point would disallow mmaping by default (which is all that is required) - since what is the attack vector they’re preventing? If you can exfiltrate via memory you could just do so via read/write.
- singron 4y ago
  You are probably thinking of issues with networked filesystems like nfs, 9p, or vboxsf where you can't mmap a file and actually share memory. Basically any other real filesystem that 2 processes actually open the same file on will allow shared memory.
  - kuschku 4y ago
    Which is relatively common if you're running a service on your k8s cluster with networked storage and SELinux enabled for your containers (CoreOS now has SELinux per default). You'll either have to reduce safety and usability, or drop SQLite.
bob1029 4y ago
> There is no point to use sqlite3 in default journaling mode. There are situations where you wouldn't want to, but they are probably very uncommon outside of the embedded computing world. Copying 3 files vs 1 is not a gigantic deal. Most of the time you aren't even moving a SQLite database around.
- WorldMaker 4y ago
  You can still copy just the primary file and not the WAL or any other files if you want to live dangerously and possibly lose transactions if there are write operations in parallel. Of course, you'd be better off with WAL-based replication tech like litestream instead of plain file copies if you are truly worried about parallel operations during your file copies.
  - epilys 4y ago
    You can even distribute individual WAL frames and arrange distributed writes with raft consensus and a time lease. I never formally modelled this but it seemed to work perfectly: the approach only lacked checkpointing the Wal file and synchronising the database file across raft nodes.
- quietbritishjim 4y ago
  There's never a need to copy 3 files anyway. If the database is closed cleanly then the WAL file and lock file are deleted. If not (either it's still open or not closed cleanly) then I think any half-finished transactions will be discarded if you copy all files to a new location. Certainly safest not to copy the 2 extra files in any case.

nonethewiser 4y ago

more info https://sqlite.org/wal.html https://sqlite.org/wal.html

kuschku 4y ago

That only works if all processes accessing it share a memory namespace (aka, are not in containers/jails/VMs and on the same physical machine).

joppy 4y ago
WAL works for different processes accessing the same SQLite database. It's meant to improve concurrency, in fact.
- kuschku 4y ago
  Yes, but the processes still need to share a memory namespace. In WAL mode, SQLite creates a shared memory file which gets mapped into any process trying to access it (that’s why WAL mode creates additional .shm files). But that only works if all given processes run on the same physical machine, and not in containers/jails/VM.
  - formerly_proven 4y ago
    I dunno what SQLite is doing specifically but you can certainly mmap a file across containers and it behaves like you’d think. SELinux and other hardening options might interfere though, because shared mmaps should imho be viewed as a very likely vector for cross-process exploitation.
fuckstick 4y ago
There isn’t really a thing called a memory namespace, in Linux or the BSDs at least. In Linux there are cgroups that deal with accounting and you can share VM space among cloned processes (which we then typically refer to as threads). Neither of these affect the ability to share memory among processes. The only namespace that matters here is the filesystem/mount namespace. There is no reason you can’t access the same shared SQLite database on a common volume between containers on Linux for instance.
- kuschku 4y ago
  If you're using a system with SELinux, your containers won't be able to use the same shared memory file. Which is relatively common, as CoreOS nowadays has SELinux by default.
  - fuckstick 4y ago
    > If you're using a system with SELinux, your containers won't be able to use the same shared memory file. That is false. Whether you mmap the same file is dependent on the specific SELinux policy - which is by design, highly configurable. I am also surprised/skeptical that the default configuration for those OSes for a shared volume/mount point would disallow mmaping by default (which is all that is required) - since what is the attack vector they’re preventing? If you can exfiltrate via memory you could just do so via read/write.
singron 4y ago
You are probably thinking of issues with networked filesystems like nfs, 9p, or vboxsf where you can't mmap a file and actually share memory. Basically any other real filesystem that 2 processes actually open the same file on will allow shared memory.
- kuschku 4y ago
  Which is relatively common if you're running a service on your k8s cluster with networked storage and SELinux enabled for your containers (CoreOS now has SELinux per default). You'll either have to reduce safety and usability, or drop SQLite.

bob1029 4y ago

> There is no point to use sqlite3 in default journaling mode. There are situations where you wouldn't want to, but they are probably very uncommon outside of the embedded computing world. Copying 3 files vs 1 is not a gigantic deal. Most of the time you aren't even moving a SQLite database around.

WorldMaker 4y ago
You can still copy just the primary file and not the WAL or any other files if you want to live dangerously and possibly lose transactions if there are write operations in parallel. Of course, you'd be better off with WAL-based replication tech like litestream instead of plain file copies if you are truly worried about parallel operations during your file copies.
- epilys 4y ago
  You can even distribute individual WAL frames and arrange distributed writes with raft consensus and a time lease. I never formally modelled this but it seemed to work perfectly: the approach only lacked checkpointing the Wal file and synchronising the database file across raft nodes.
quietbritishjim 4y ago
There's never a need to copy 3 files anyway. If the database is closed cleanly then the WAL file and lock file are deleted. If not (either it's still open or not closed cleanly) then I think any half-finished transactions will be discarded if you copy all files to a new location. Certainly safest not to copy the 2 extra files in any case.