zfs and sqlite

[tessus]

After reading a few topics here, I have noticed that hostwatch uses sqlite. ZFS has been known for having issues with sqlite.

I have done some digging and there was a PR for FreeBSD in ZFS, which was closed (unmerged) by reverting a previous attempt to fix it.

So I don't really know what is going on. The fix for ZFS on Linux (which was based on the FreeBSD fix) was included in 2.4.0, yet the fix for FreeBSD was reverted.
Maybe the OPNsense devs have an idea what the status is, since they have deep knowledge of FreeBSD and ZFS.

[OPNenthu]

I can't tell from skimming the comments- what's the connection between sqlite and this PR?

--

UPDATE: I ran this through an AI for summary and what I got is that it addresses an issue with synchronous writes taking a long time (slows down sqlite) but it would not affect the number of writes done by sqlite or have any bearing on write amplification in ZFS.  Not sure how accurate...

[tessus]

I didn't want to distract from my original question, which is why I didn't add too much info that is not BSD related, especially since this forum doesn't allow sections or to hide text. As mentioned in my original post, the sqlite/zfs issue in Linux was based on the PR I referenced. I really don't want this in this topic. I'll send a PM.

FreeBSD ZFS experts should know what I was asking. I was not talking about write amplification. The issue is that when using WAL, writes to the DB stall until they basically timeout, which brings the app using it down. The ones that were supposed to fix it, were reverted and the fix for the fix was closed w/o merging. But hey, maybe ZFS for BSD got a fix that solved it and I missed it. This is why I asked. I don't know the status. I only know that sqlite and zfs is usually a no-no.

[Patrick M. Hausen]

I run all my applications on ZFS and more than one use SQLite. Never had a single problem. But after looking closely none of them seems to use WAL.