Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Honker – Durable queues, streams, pub/sub, and cron scheduler in a SQLite file

honker.dev

Post reply on HN
Print view View original

31–40 of 69 posts

Re: Honker – Durable queues, streams, pub/sub, and cron scheduler in a SQLite file

#31

Post by booi »

andai wrote: post #12
d1l wrote: post #10

Earlier quoted context omitted.

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.

Re: Honker – Durable queues, streams, pub/sub, and cron scheduler in a SQLite file

#32

Post by 8note »

tptacek wrote: post #5

"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".

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

Re: Honker – Durable queues, streams, pub/sub, and cron scheduler in a SQLite file

#33

Post by billywhizz »

ncruces wrote: post #11
tptacek wrote: post #5

"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".

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

Re: Honker – Durable queues, streams, pub/sub, and cron scheduler in a SQLite file

#34

Post by paulddraper »

tptacek wrote: post #5

"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

For the low, low cost of $1 per minute, you can also lease a supercar.

Re: Honker – Durable queues, streams, pub/sub, and cron scheduler in a SQLite file

#35

Post by ncruces »

billywhizz wrote: post #33
ncruces wrote: post #11

Earlier quoted context omitted.

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

That only catches changes made by the database connection being "hooked."

This has a thread running in the background trying to catch changes made by other connections, potentially (I'm not sure here, but I suspect as much) in different processes that are modifying the same database.

Re: Honker – Durable queues, streams, pub/sub, and cron scheduler in a SQLite file

#36

Post by codedokode »

> Once real work flows through a SQLite-backed app, you need a queue. The usual answer is “add Redis + Celery.”

Are they joking? SQLite is usually used for single-process (mutliple threads) applications. The proper way to communicate between threads/processes is a ring buffer, where you allocate structs (allocation typically is incrementing a pointer), and futex/eventfd for notifications (+ some spinlocking to avoid going to kernel when the tasks arrive quickly). Why do you need redis for that? If you need persistent tasks, then you can store them in the table, and still use futex for notifications. This polling is inefficient and they should not make it a library which will cause other lazy developers add it to their app.

> 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

That's 3 ms per second = 0.3% CPU time wasted for every waiting thread.

Like Electron, this feels like written by a web developer and not a real programmer.

Re: Honker – Durable queues, streams, pub/sub, and cron scheduler in a SQLite file

#37

Post by deepsun »

codedokode wrote: post #36

> Once real work flows through a SQLite-backed app, you need a queue. The usual answer is “add Redis + Celery.” Are they joking? SQLite is usually used for single-process (mutliple threads) applications. The proper way to communicate between threads/processes is a ring buffer, where you allocate structs (allocation typically is incrementing a pointer), and futex/eventfd for notifications (+ some spinlocking to avoid…

Nevertheless, expect articles like "We replaced our redis cluster with this simple extension and got it N times faster".

Re: Honker – Durable queues, streams, pub/sub, and cron scheduler in a SQLite file

#38

Post by billywhizz »

ncruces wrote: post #35
billywhizz wrote: post #33

Earlier quoted context omitted.

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

That only catches changes made by the database connection being "hooked." This has a thread running in the background trying to catch changes made by other connections, potentially (I'm not sure here, but I suspect as much) in different processes that are modifying the same database.

good point. but ime and as seems to be widely understood writing from multiple connections is a bit of a minefield in SQLite. and afaik it still would be possible to have a hook on all connections you expect to be writing?

Re: Honker – Durable queues, streams, pub/sub, and cron scheduler in a SQLite file

#39

Post by Groxx »

codedokode wrote: post #36

> Once real work flows through a SQLite-backed app, you need a queue. The usual answer is “add Redis + Celery.” Are they joking? SQLite is usually used for single-process (mutliple threads) applications. The proper way to communicate between threads/processes is a ring buffer, where you allocate structs (allocation typically is incrementing a pointer), and futex/eventfd for notifications (+ some spinlocking to avoid…

>That's 3 ms per second = 0.3% CPU time wasted for every waiting thread.

I suspect that's actually "per process, per database (usually 1)", and not based on number of threads or tables. `data_version` semantics mean there's no need for more than one connection polling it, and it's being used as a relatively lightweight "DB has changed, check queues" check (that's pretty much its whole purpose).

Also I believe this is mostly intended for multi-process use, e.g. out-of-process workers, so an in-process dirty tracker (e.g. just check after insert/update/delete) isn't sufficient.

So I do think it's somewhat crazy, but it is at least very simple. fsnotify-like monitoring seems like a fairly obvious improvement tho, not sure why that isn't part of it. Maybe it's slower? I haven't tried to do anything actually-performant-or-reliable with fs notifications, dunno what dragons lie in wait.

Re: Honker – Durable queues, streams, pub/sub, and cron scheduler in a SQLite file

#40

Post by sroussey »

felooboolooomba wrote: post #8
tptacek wrote: post #5

"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.

Thing of the battery!

(read that in the way of "think of the children!")

Post reply on HN