6 ms·
I have also found the lack of transactional guarantees in typical job queues to be very problematic. One problem with using PostgreSQL in this way (using eithe
by Diggsey 7y ago
I have also found the lack of transactional guarantees in typical job queues to be very problematic.
One problem with using PostgreSQL in this way (using either advisory locks or LOCK FOR UPDATE) is that it requires you to keep an open connection to the database whilst the job is being worked on.
For a MySQL database, this would be just fine, but PostgreSQL uses a process-per-connection model which caps the number of active connections to the database to a relatively low number (on the order of 1000x fewer connections than a similarly sized MySQL instance) and tools like PgBouncer do nothing to help with this.
As a result, if your jobs take more than a few milliseconds to execute (let's say you make external HTTP requests as part of your job) this is not a good approach to take.
I use a similar approach which avoids this problem, but it only works because I have relatively low throughput requirements. I essentially implement in-database advisory locks using a separate table - before taking a job, workers create a row in the table, and the primary key of this table is used as a worker ID. Jobs are "taken" by assigning them a worker ID. Each row in the worker table has an expiry date, so if workers die, the corresponding row will be deleted and any linked jobs released back into the queue.
As well as transactional guarantees, using a database as a job queue gives you a lot of power over how jobs are executed: for example, our service for delivering webhooks has a separate queue per customer, and we can ensure that within a single queue jobs are processed strictly in order. Meanwhile, our service for search indexing supports different priority levels, so that newly created records are indexed with a higher priority.
- z92 7y ago// One problem with using PostgreSQL in this way (using either advisory locks or LOCK FOR UPDATE) is that it requires you to keep an open connection to the database whilst the job is being worked on. // Not necessarily. You lock the row for an instant update to a field, for example called "status" into "running" and then disconnect from the database within milliseconds. Finish your job taking as much time as you want. And then connect again to change the row's status to "finished". This is how it's always designed, as I have seen. The locking problem is for querying rows where status="waiting" and then instantly change it. It's not to "keep the record locked, and DB connection up, until I finish my batch job". That would be a bad design.
- jimktrains2 7y agoWhat happens when the worker processing the job dies and never updates the status?
- drabinowitz 7y agoYou can include a locked_at field and have your update query be for not_started rows and started rows where locked_at is older than the job timeout
- shrimpx 7y agoA global job timeout might be unreasonable with high variance in workload. Eg some jobs taking 0.5 seconds and others 30 seconds. You might set a global timeout of say 60s but it sucks to wait 59.5s to reap that short job whose worker crashed. A better system is to make workers update a timestamp on an interval and you reap any jobs that haven't been updated in N seconds.
- pas 7y agoIt's a trade off between updates per sec and latency. Maybe simply using a timeout per job type is a better way. (That of course trades off simplicity.)
- shrimpx 7y agoI agree. Frequency of updates also becomes more of an issue as you add workers. Say you have 1000 workers each updating every 2 seconds. That's ~500 timestamp update statements per second which is not trivial in terms of added load on the DB.
- ukd1 7y agoSome of these systems also check if the worker running the job is still alive, then if it's not kill the transaction. The job then restarts. Also, you can do this by time - when did it lock it, how long does it have to complete before you nuke. Of course there are edge cases, but you have to make a choice of how to deal with this; just leaving it means doing it manually.
- bgentry 7y agoI elaborated a bit on this elsewhere (https://news.ycombinator.com/item?id=21537414 https://news.ycombinator.com/item?id=21537414) but Que’s design has changed significantly and no longer holds open a connection or transaction for the duration of working a job. It holds one database connection per worker process. Each worker process handles all job locking and assignment to the individual worker threads in that process, each of which only use connections that they themselves decide to open. Otherwise, I completely agree on the benefits of transactional job enqueueing. This lets you push off so much complexity until you actually need it (when you’ve scaled such that a single database doesn’t handle your needs well). At that point, you get to solve the same challenges you will have been solving all along to deal with jobs that might run which depend on transactions that may not have committed (yet or ever). I believe this model is almost always the right starting point for a web application, barring some unusual job requirements or massive initial scale.
- ianai 7y agoIs this what you’re talking about? https://github.com/que-rb/que https://github.com/que-rb/que
- s0l1dsnak3123 7y agoYep!
- deleted 7y ago[deleted]
- zahreeley 7y agoGood job
- Diggsey 7y agoThat is a very neat solution. The one downside I see is that you need to be much more careful about gracefully handling errors within workers. For example: if a single worker thread crashes but the process doesn't realise it, it may be possible for jobs to become stuck, because other workers rely on the process releasing the job back to the queue. It's definitely solvable but something to be aware of.
- ukd1 7y agoI think the biggest bonus for me of transactional queues like this is knowing that either everything worked - the inserted/updated/deleted things, and enqueuing of any jobs - or nothing did. Whilst using resque, or anything else outside of postgres, occasionally things would rollback but have sent jobs. This sucks; as you either get errors or unexpected things, or have to code around it to fix that. FYI, we've been using QueueClassic at Rainforest for a while. Not sure how many actual jobs we do, as we've reset at least when moving hosts, but we're currently at 849202793 jobs (~850m) though QC. [edit; looking at the last failed job from ~24h ago, we're doing 2.5-3m jobs per day]
- faizshah 7y agoDid you guys ever evaluate Que? What made you choose QueueClassic?
- ukd1 7y agoWe did; but I think it came around after we started. It was faster at that time, we looked at also it's well written - we'd probably have used it if it was out, but we didn't really mind about the speed - we're not latency sensitive for what we're doing. Fast forward a little, and we now maintain QueueClassic, and it's now comparable / slightly faster.
- ukd1 7y agoMaybe chanks recalls differently; but I think the backstory was he saw / was interested in this - but wanted to use newer features that came out in Postgres. QueueClassic's first release was early 2011; it wasn't fast support newer Postgres stuff, mostly as it was being used a lot (inside Heroku) and wanted to maintain support for users. I think (might recall wrong) that Chris ended up writing Que ~2013 because of QC was too slow to update / didn't want to do these features!