sqlite backend extremely slow on zfs

[yorickvP]

I was getting extremely slow speeds with the default sqlite backend on zfs, I switched to postgres and got a 24x upload rate speedup. I was also hitting #24 occationally.

Could there be an option to turn off fsync on the sqlite backend? That should speed it up massively.

[nazarewk]

this is the same as:

• Using ZFS on desktop with stock kernel is bad experience NixOS/nixpkgs#169457 (comment)
• reported directly in ZFS Sometimes a delay of up to zfs_txg_timeout seconds when doing ftruncate openzfs/zfs#14290

[Mic92]

This is how I work around in atuin: atuinsh/atuin#952 (comment)

[srd424]

I'm seeing pathological slow speeds and timeouts even on ext4 - my underlying storage stack is a bit of a muddle, but shouldn't be anywhere as bad as I'm seeing. I switched to hosting the database on a small ext2 LV and things are a bit better, though not a lot.

I'm beginning to suspect that there's something about attic's access patterns that just interacts really badly with sqlite. I tried switching the database to WAL journalling but that produced a negligible improvement if any. Using eatmydata to neuter fsyncs is the only thing that really helps - that or sticking the database on a tmpfs :)