5 ms·
SQLite on ZFS needs the Fsync behaviour to be off, otherwise SQLite will randomly hang the application as the fsync will wait for the txg to commit. This can ta
by zaarn 1y ago
SQLite on ZFS needs the Fsync behaviour to be off, otherwise SQLite will randomly hang the application as the fsync will wait for the txg to commit. This can take a minute or two, in my experience.
Btrfs is a better choice for SQLite.
- throw0101b 1y ago> SQLite on ZFS needs the Fsync behaviour to be off […] zfs set sync=disabled mydata/mydb001 * https://openzfs.github.io/openzfs-docs/man/master/7/zfsprops.7.html#sync https://openzfs.github.io/openzfs-docs/man/master/7/zfsprops...
- zaarn 1y agoAs noted in a sibling comment, this causes corruption on power failure.
- fread2281 1y agoThis is a bug in zfs or in sqlite, sync=disabled should never cause actual corruption (it should at worst make existing corruption bugs in sqlite more likely & cause loss of committed sqlite transactions)
- fread2281 1y ago(see https://utcc.utoronto.ca/~cks/space/blog/solaris/ZFSTXGsAndZILs https://utcc.utoronto.ca/~cks/space/blog/solaris/ZFSTXGsAndZ... for an explanation of sync=disabled)
- zaarn 1y agoI highly doubt it's an SQLite bug, considering how thoroughly they test their code to behave correctly as long as their assumptions are filled. And those assumptions are clearly violated when SQLite runs on ZFS with sync=disabled (since writes may not be written to disk despite fsync).
- deleted 1y ago[deleted]
- supriyo-biswas 1y agoBtw this concern also applies to other databases, although probably it manifests in the worst way in SQLite. Essentially, you’re doing a WAL over the file systems’ own WAL-like recovery mechanism.
- zaarn 1y agoI've not observed other databases locking up on ZFS, Postgres and MySQL both function just fine, without needing to modify any settings.