On Tue, Nov 3, 2009 at 12:30 PM, Brian Karlak <zenkat@metaweb.com> wrote:
> Hello All --
>
> I have a simple queuing application written on top of postgres which I'm
> trying to squeeze some more performance out of.
>
> The setup is relatively simple: there is a central queue table in postgres.
> Worker daemons do a bounded, ordered, limited SELECT to grab a row, which
> they lock by setting a value in the queue.status column.
So you do a select, and then an update?
> When the task is
> complete, results are written back to the row. The system is designed to
> allow multiple concurrent daemons to access a queue. At any one time, we
> expect 1-5M active items on the queue.
>
> Now this design is never going to win any performance awards against a true
> queuing system like Active/Rabbit/Zero MQ, but it's tolerably fast for our
> applications. Fetch/mark times are about 1ms, independent of the number of
> items on the queue. This is acceptable considering that our tasks take
> ~50ms to run.
>
> However, the writing of results back to the row takes ~5ms, which is slower
> than I'd like.
It seems you have an select, and update, and another update. Where in
this process do you commit? Are you using fsync=off or
synchronous_commit=off?
> It seems that this is because I need to to do an index scan
> on the queue table to find the row I just fetched.
Why would the index scan take 1 ms two of the times it is done but 5ms
the third time? Isn't it the same index scan each time? Or does the
change in queue.status change the plan?
Cheers,
Jeff