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.

[OPNenthu]

The issue is that when using WAL, writes to the DB stall until they basically timeout, which brings the app using it down.

Ah, thanks.  There had been a lot of posts recently about high number of writes related to hostwatch (uses sqlite) so I thought you were responding to that. Appreciate the PM.

[franco]

If anyone wants to use a write-reducing hostwatch 1.0.12 it's available from snapshot now:

# opnsense-revert -z hostwatch

(restart service from the GUI)

[OPNenthu]

That errors out but I got it working with '$ opnsense-revert -z hostwatch':

Code Select Expand

Fetching hostwatch.pkg: .... done
Verifying signature with trusted certificate pkg.opnsense.org.20260120... done
hostwatch-1.0.11: already unlocked
Installing hostwatch-1.0.12...
package hostwatch is already installed, forced install
===> Creating groups
Using existing group 'hostd'
===> Creating users
Using existing user 'hostd'
Extracting hostwatch-1.0.12: 100%

I'm not sure how to measure the I/O difference but I didn't notice much of a problem with the previous 1.0.11 version in any case.  FWIW, I do still see the hostwatch process popping in and out of the top spot in '$ top -S -m io -o total' every few seconds:

Code Select Expand

last pid: 92973;  load averages:  0.06,  0.08,  0.08                                                      up 1+23:29:30  12:52:12
104 processes: 2 running, 100 sleeping, 2 waiting
CPU:  0.8% user,  0.0% nice,  0.0% system,  0.0% interrupt, 99.2% idle
Mem: 300M Active, 1683M Inact, 2286M Wired, 3467M Free
ARC: 1419M Total, 993M MFU, 232M MRU, 12M Anon, 23M Header, 155M Other
     1092M Compressed, 2899M Uncompressed, 2.66:1 Ratio
Swap: 8192M Total, 8192M Free

  PID USERNAME     VCSW  IVCSW   READ  WRITE  FAULT  TOTAL PERCENT COMMAND
71347 hostd         16      0      0     16      0     16 100.00% hostwatch
    1 root           0      0      0      0      0      0   0.00% init
   33 root           0      0      0      0      0      0   0.00% aiod1
80673 root           0      0      0      0      0      0   0.00% php-cgi
27553 root           0      0      0      0      0      0   0.00% dpinger
    2 root          12      0      0      0      0      0   0.00% clock
   34 root           0      0      0      0      0      0   0.00% aiod2
25122 root           0      0      0      0      0      0   0.00% php-cgi
43874 root           0      0      0      0      0      0   0.00% php-cgi
85890 root           0      0      0      0      0      0   0.00% php-cgi
    3 root           0      0      0      0      0      0   0.00% crypto
   35 root           0      0      0      0      0      0   0.00% aiod3
19235 nobody         2      3      0      0      0      0   0.00% dnsmasq
89379 root           0      0      0      0      0      0   0.00% php-cgi
50851 root           0      0      0      0      0      0   0.00% php-cgi
    4 root           0      0      0      0      0      0   0.00% cam
   36 root           0      0      0      0      0      0   0.00% aiod4
29156 root           4      1      0      0      0      0   0.00% dpinger
76612 root           0      0      0      0      0      0   0.00% sshd-session
 3812 root           0      0      0      0      0      0   0.00% php-cgi
    5 root           0      0      0      0      0      0   0.00% busdma
53349 root           0      0      0      0      0      0   0.00% rtsold

I think there's still an issue with the vacuuming process in 1.0.11 but I'll add my datapoint to an appropriate thread for that.

[franco]

Yes, it's opnsense-revert, not opnsense-update. Thanks!


Cheers,
Franco