"Idle cost is that one lightweight SELECT per millisecond per database — no page-cache pressure, no writer-lock contention, no kernel file watcher in the mix."
I think (respectfully) the LLM that probably wrote this overshot the mark here because busy-polling a select does not actually sound better to me than a "kernel file watcher".
Yeah, I had the same instinct - this feels very much like a "nice idea" but the execution falls short. I mean - busily banging on sqlite like this? Shit at that point just use Redis.
I had a manual fs polling thing a while back. It was ugly (low time budget, didn't wanna mess with the native watchers), just scanned the whole thing once per second. It averaged out to like 0.3% CPU.
Not elegant, but acceptable for my purposes! (Small-ish directory, and "ping me within a second or two" was realtime enough for this use case.)
i mean, technically this is once per millisecond, so this would happen 1000x more. In your case due to the kernel overhead you would likely not even be able to do it (300% CPU?).
Either way this does seem like a very large overhead due to the fact that there's just no other way to do it without a deeper kernel integration which might be outside the scope of what sqlite is trying to do.
Really might be in sqlite. I've learned to never trust my intuition about performance with that thing. So many times I've gone to "optimize" something and discovered that the naive hack way I had been doing it was faster anyway. It's built for this sort of bullshit.
For what it's worth, Kine (software that k3s uses to replace etcd with SQL databases) implements etcd watches on SQLite through polling[1]. The reason being that SQLite does not offer NOTIFY/LISTEN like MySQL and Postgres do. Ironically, Honkey attempts implementing NOTIFY/LISTEN through polling.
k3s has been running on my home server for about three years now (using the default SQLite backend), and there doesn't seem to be excessive CPU usage despite dozens of watches existing in the simulated etcd. Of course, this doesn't say much about Honker, but it's nonetheless worth pointing out that sometimes the choice of database forces one towards a certain design.
With SQLite, you're basically funneled towards a single-writer / single-process design anyway ... in which case why not use a more traditional condvar + mutex rather than polling?
to me it sounds like they asked it to not make a kernel file watcher, and now it writes that into every comment everywhere, despite not even being in the implementation
It’s an interesting approach and can be quite fun to use for new projects.
> How it works: honker polls SQLite’s PRAGMA data_version every millisecond. That’s a monotonic counter SQLite increments on every commit from any connection, journal mode, or process — a ~3 µs read for a precise wake signal.
I've implemented something similar in the past, but using inotify. You need to watch the -wal file for IN_MODIFY. To make it work reliably I found I had to run:
BEGIN IMMEDIATE TRANSACTION; ROLLBACK;
Otherwise the new changes weren't guaranteed to be visible to the process. I'm sure there's a more targetted approach that would work instead - maybe flock on a particular byte in the `-shm` file.
"Idle cost is that one lightweight SELECT per millisecond per database — no page-cache pressure, no writer-lock contention, no kernel file watcher in the mix."
I think (respectfully) the LLM that probably wrote this overshot the mark here because busy-polling a select does not actually sound better to me than a "kernel file watcher".
"one lightweight SELECT per millisecond"
This reminds me of the teenager who told her dad that she was just a tiny little bit pregnant.
Yeah, I had the same instinct - this feels very much like a "nice idea" but the execution falls short. I mean - busily banging on sqlite like this? Shit at that point just use Redis.
What's the CPU usage? Like 2%?
I had a manual fs polling thing a while back. It was ugly (low time budget, didn't wanna mess with the native watchers), just scanned the whole thing once per second. It averaged out to like 0.3% CPU.
Not elegant, but acceptable for my purposes! (Small-ish directory, and "ping me within a second or two" was realtime enough for this use case.)
i mean, technically this is once per millisecond, so this would happen 1000x more. In your case due to the kernel overhead you would likely not even be able to do it (300% CPU?).
Either way this does seem like a very large overhead due to the fact that there's just no other way to do it without a deeper kernel integration which might be outside the scope of what sqlite is trying to do.
I'm not even saying it's unworkable, just, my intuition is not that the "lightweight per-millisecond select" is an optimal design.
Really might be in sqlite. I've learned to never trust my intuition about performance with that thing. So many times I've gone to "optimize" something and discovered that the naive hack way I had been doing it was faster anyway. It's built for this sort of bullshit.
Maybe, I'm really writing about the language on this page, not about the design (I responded about this upthread).
Oh, yes, I see what you mean now.
For what it's worth, Kine (software that k3s uses to replace etcd with SQL databases) implements etcd watches on SQLite through polling[1]. The reason being that SQLite does not offer NOTIFY/LISTEN like MySQL and Postgres do. Ironically, Honkey attempts implementing NOTIFY/LISTEN through polling.
k3s has been running on my home server for about three years now (using the default SQLite backend), and there doesn't seem to be excessive CPU usage despite dozens of watches existing in the simulated etcd. Of course, this doesn't say much about Honker, but it's nonetheless worth pointing out that sometimes the choice of database forces one towards a certain design.
[1] https://github.com/k3s-io/kine/blob/648a2daa/pkg/logstructur...
With SQLite, you're basically funneled towards a single-writer / single-process design anyway ... in which case why not use a more traditional condvar + mutex rather than polling?
If you're not making any changes to the database, does the SELECT "kill" you?
And if you are making changes, don't you have to poll regardless after the file watcher wakes you?
For WAL mode, SQLite can probably satisfy this query just by inspecting some shared memory. But it is busy waiting, sure.
SQLite has a wal hook which calls you back every time a transaction is committed to the WAL. https://www.sqlite.org/c3ref/wal_hook.html
to me it sounds like they asked it to not make a kernel file watcher, and now it writes that into every comment everywhere, despite not even being in the implementation
It’s an interesting approach and can be quite fun to use for new projects.
> How it works: honker polls SQLite’s PRAGMA data_version every millisecond. That’s a monotonic counter SQLite increments on every commit from any connection, journal mode, or process — a ~3 µs read for a precise wake signal.
I've implemented something similar in the past, but using inotify. You need to watch the -wal file for IN_MODIFY. To make it work reliably I found I had to run:
Otherwise the new changes weren't guaranteed to be visible to the process. I'm sure there's a more targetted approach that would work instead - maybe flock on a particular byte in the `-shm` file.
Prior discussion a few days ago: https://news.ycombinator.com/item?id=47874647
Reminds me of Litestack for Rails. Eventually, it was abandoned because Rails itself started going all out on SQLite.
https://github.com/oldmoe/litestack
All in*
At the end it says: "pg-boss and Oban are the Postgres-side gold standards" -- but Oban supports SQLite now too https://github.com/oban-bg/oban
There's also Graphile Worker. https://github.com/graphile/worker
Almost feels like someone is trying to joke about similar postgres application .
To make it look even more absurd . SQLite is not concurrent and you’ll have tons of problems using it practically .
This seems especially appealing in the awkward middle: too serious for in-memory queues, not big enough to justify Kafka-shaped machinery.
Suggestion for the author wind back the polling to once a second when nothing is happening.
I can’t see any benchmarks or performance stats.
I’d like to see messages per second.
Could this work with Turso, the SQLite rust rewrite?