7 ms·
> Even the most unoptimized database should be able to handle this. Anybody had any success running a queue on top of... sqlite? With the way the sqlite file
by MuffinFlavored 4y ago
> Even the most unoptimized database should be able to handle this.
Anybody had any success running a queue on top of... sqlite?
With the way the sqlite file locking mechanisms work, are you basically guaranteed really low concurrency? You can have lots of readers but not really a lot of writers, and in order to pop a job off of the queue you need to have a process spinning waiting for work, move its status from "to do" to "in progress" and then "done" or "error", which is sort of "write" heavy?
> An EXCLUSIVE lock is needed in order to write to the database file. Only one EXCLUSIVE lock is allowed on the file and no other locks of any kind are allowed to coexist with an EXCLUSIVE lock. In order to maximize concurrency, SQLite works to minimize the amount of time that EXCLUSIVE locks are held.
> You can avoid locks when reading, if you set database journal mode to Write-Ahead Logging (see: http://www.sqlite.org/wal.html http://www.sqlite.org/wal.html).
- zamalek 4y agoThe problem with a queue on SQLite is that every successful read implies a write. SQLite is fast enough that it may not matter, though.
- jacobr1 4y agoWe used sqlite for a queue at a prior startup - enabling the WAL was critical to maintain disk throughput.
- mst 4y agoI once saw WAL turned off to make a sqlite queue perform better. But that was ... gremlins. More than one person tried to figure out wtf was going one and eventually it was better business wise to declare 'gremlins' and everybody involved in the incident has been annoyed about not figuring it out since.
- simplotek 4y ago> The problem with a queue on SQLite is that every successful read implies a write. SQLite also supports in-memory databases with shared cache. https://www.sqlite.org/inmemorydb.html https://www.sqlite.org/inmemorydb.html If a message queue does not require any form of persistence, writes don't sound like an issue.
- avinassh 4y agoBut this is purely in-memory with no disk persistence. That’s quite risky
- michaelcampbell 4y agoNot to be coy, but it only is if it is. For this application I'd agree, but there are plenty of apps that want queues for in-memory use, and if the thing that holds the memory for the queue dies, chances are the whole ecosystem has. Sometimes this is fine.
- nerpderp82 4y agoI just did a quick test, I am seeing over 1k commits per second when being driven from Python 3.11 on an old macbook pro.
- vidarh 4y agoI processed ~3m messages a day on Sqlite using a pair of ca 2006 era Xeon on spinning rust (for redundancy; each could easily handle the load by itself). The queue processor was written in Ruby and ran on the (very slow) 1.8.x series (even then it used about 10% of a single core. On modern hardware you should be able to trivially handle more than that.
- randito 4y agoDoes this mean you are processing messages on only one machine, since it's Sqlite? Depending on what you are processing, that could take longer than the queue/dequeue.
- vidarh 4y agoThe queue was managed by a server written in Ruby that spoke Stomp [1], so while for the biggest queue most of the processing did in fact happen on the same machine, that was just because it happened to fit. [1] https://stomp.github.io/ https://stomp.github.io/
- foldr 4y agoA simple implementation of a queue in SQL will need to acquire an exclusive lock on the table anyway. Although it's not necessary to use locking at the level of SQL itself: https://news.ycombinator.com/item?id=27482402 https://news.ycombinator.com/item?id=27482402
- TedDoesntTalk 4y agoThe article mentions row-level locking, not full table locking. Big difference in write performance.
- yawaramin 4y agoFor a client-server RDBMS with writes over the network, sure. For an SQLite database with writes to the local filesystem and an order of magnitude better write performance, not so much.
- alberth 4y agoExpensify does. https://bedrockdb.com/jobs.html https://bedrockdb.com/jobs.html https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
- polyrand 4y agoSQLite is missing some features like `SELECT FOR UPDATE`, but you can work around some issues with a few extra queries. I wrote litequeue[0] with this specific purpose. I haven't been able to use it a lot, so I don't have real-world numbers of how it scales, but the scaling limits depend on how fast you can insert into the database. [0]: https://github.com/litements/litequeue https://github.com/litements/litequeue
- st3fan 4y agoI wrote https://github.com/TinyWebServices/tqs https://github.com/TinyWebServices/tqs a couple of years ago. It is modelled after SQS and runs in a single threaded Tornado server. I don’t know how many messages per second it does but for a podcast crawling side project I have processed hundreds of millions of messages through this little Python wrapper around SQLite. Zero problems. It just keeps running happily.
- deleted 4y ago[deleted]
- matzf 4y agoThe huey project supports sqlite as one of its backend options (https://huey.readthedocs.io/en/latest/api.html#SqliteHuey https://huey.readthedocs.io/en/latest/api.html#SqliteHuey). It works, is all I can say.
- 0xbadcafebee 4y agoWhy use something as complicated as SQLite? You can use a plain old set of directories and files as a queue, with sane, portable, exclusive, atomic locks, on any filesystem, with concurrent readers/writers. That's how we ran mail servers that handled millions (now billions) of messages a day, 20+ years ago.
- nomel 4y agoYeah, I'm super confused with this. Getting a few thousand per second seems relatively trivial, on an Arduino. Maybe there's something I'm missing here, or is this the abstractions that software lives at these days?
- TedDoesntTalk 4y agoThis limits simultaneous writes to the maximum number of open file handles supported by the OS. I don’t know what that is, but I don’t see how it can compare to a multiple multiplexed TCP/IP sockets. When you’re writing billions of messages per day, I don’t see how a file system scales.
- MobiusHorizons 4y agoClose the file handle if you aren’t actively processing the file.
- TedDoesntTalk 4y agoThat still allows for perhaps 3000-4000 simultaneous writes, depending on how many file handles are in use by other processes.
- vidarh 4y agoOn Linux, /proc/sys/fs/file-max has the maximum number of simultaneous open filehandles supported by the kernel. On my laptop this is about 1.6 million
- sodapopcan 4y agoOban [0] is a job queuing system in the Elixir world that supports both postgres and sqlite. [0] https://getoban.pro/ https://getoban.pro/
- mst 4y agoThe p3rl.org/Minion job queue has a sqlite backend and I'm aware of it handling what seemed to me a fairly acceptable amount of throughput and the source to https://metacpan.org/pod/Minion::Backend::SQLite https://metacpan.org/pod/Minion::Backend::SQLite looks pretty comfortable to me. I've not run it myself in production but I would definitely have heard if it went wrong, I think OpenSuSe's OpenQA thing does build worker boxes that way (though of course they'll have fairly slow jobs so it may just be the write throughput doesn't matter). This being HN, I'd like to point out you can steal the SQL from the source for your own uses if you want to, this just happens to be the example full implementation I'm familiar with.