3 ms·
"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."
by tptacek 6mo ago
"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".
- felooboolooomba 6mo ago"one lightweight SELECT per millisecond" This reminds me of the teenager who told her dad that she was just a tiny little bit pregnant.
- giraffe_lady 6mo ago[flagged]
- tptacek 6mo agoYeah, again, to be clear: I get how SQLite works and I'm not dunking on the design, I'm just saying the comparison set up on this page snags. It's a classic LLM negated triptych, but "one of these things is not like the other": cache pressure: bad, writer contention: bad, kernel file watcher: ... good, actually? Intuitively seems better than this design?
- rv64imafdc 6mo agoHold on -- if it really is "one lightweight SELECT per millisecond", and you're saying a select is "a couple hundred microseconds", say generously 200us?, then you're spending 200us out of every 1000us just selecting. That's a lot of polling!
- giraffe_lady 6mo agoI mean only in the same sense that you spend 1 second per second doing something. Time is probably not the best way to evaluate the resources this consumes and I doubt it takes much of anything else either. It does seem weird though even for sqlite. I wonder how oban does it. I also wonder if OP knows oban can run on sqlite.
- sroussey 6mo agoThing of the battery! (read that in the way of "think of the children!")
- nine_k 6mo agoOne cannot be a little bit pregnant. But a DB can be only a little bit in the RAM, and specifically in the page cache. SQLite can act exactly like that, and it's damn fast as long as it does not need to durably write a transaction. Polling once a millisecond could spend a few microseconds. I wonder if using a tiny Redis instance, or even something like LevelDB would be even more efficient.
- Retr0id 6mo agoWith the file-watch APIs is that you don't need to poll at all - free is better than cheap.
- djdillon 6mo ago[flagged]
- d1l 6mo agoYeah, 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.
- andai 6mo agoWhat'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.)
- booi 6mo agoi 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.
- nine_k 6mo agoIf the fs tree scanned once per second had 1000 files, it would be once per millisecond for a file.
- kimixa 6mo agoIf this stops the core being able to drop to a lower power state it can be whole multiples of power use on some devices. Wake ups are death for mobile form factors, even if not really doing much work.
- andai 6mo agoThis is a pretty good argument against the way we do operating systems now, right?
- ncruces 6mo agoIf 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.
- redsocksfan45 6mo ago[dead]
- billywhizz 6mo agoSQLite 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 https://www.sqlite.org/c3ref/wal_hook.html
- ncruces 6mo agoThat 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.
- billywhizz 6mo agogood 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?
- billywhizz 6mo agoi did a quick benchmark on this with a single db connection updating user_version in a tight loop with the wal_hook callback enabled. on my crappy old i5 with the db file on /dev/shm it can do ~150k writes a second with the wal_hook callback called on every write. and this is using JS bindings to C++ so has some unnecessary overhead.
- 8note 6mo agoto 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
- russellthehippo 6mo agoYup
- paulddraper 6mo ago> one lightweight SELECT per millisecond For the low, low cost of $1 per minute, you can also lease a supercar.
- russellthehippo 6mo agoRespectfully (thanks haha) - yeah probably right. Original intent was to use inotify type thing but i avoided per-platform differences at the outset. this was definitely a for fun project that blew up unintentionally and am working to harden/improve. Love Fly.
- ncruces 6mo agoOne of the things people seem to forget is that SQLite itself polls every millisecond or so to grab a lock. So yes, don't use this in a mobile device, or a server if you want to let the CPU enter a low power state. Otherwise, a single thread doing this in an otherwise idle server, doesn't seem that terrible. And if it's not idle, inotify won't help you (need to query what changed afterwards).
- russellthehippo 6mo agoAppreciated your input on the original thread as well. Maybe I should note this recommendation in the docs or something.
- conradev 6mo agoA prepared `PRAGMA data_version` is likely quite cheap to run because it hits the same page every time… …but some other push-based IPC mechanism would be a lot more battery friendly