On 2020-Sep-14, David Rowley wrote:
> On Tue, 8 Sep 2020 at 06:05, Raj wrote:
> >
> > > This would not exactly look like a bug, because the message says "to
> > > be locked", so at least it's not allowing two workers to lock the same
> > > tuple. But it seems that the skip-locked mode should not
On Tue, 8 Sep 2020 at 06:05, Raj wrote:
>
> > This would not exactly look like a bug, because the message says "to
> > be locked", so at least it's not allowing two workers to lock the same
> > tuple. But it seems that the skip-locked mode should not make an error
> > out of this, but treat it as
Hi Gunther
On 07-Sep.-2020 14:04, r...@gusw.net wrote:
Hi all, especially Jim Jarvie, I saw your email to me only now on my
related issue. My issue remains this one:
---8<---
I remain with my original question from 30th of June, to me it feels
like a bug of some sort:
"tuple to be locked was
Hi all, especially Jim Jarvie, I saw your email to me only now on my
related issue. My issue remains this one:
Well this got interesting - the already moved error showed up:
and I have already gone through all those index pruning and all that
good stuff.
I remain with my original question f
On Tue, Aug 18, 2020 at 8:22 PM Jim Jarvie wrote:
> I've tuned the LIMIT value both up and down. As I move the limit up, the
> problem becomes substantially worse; 300 swamps it and the selects take > 1
> hour to complete; at 600 they just all lock everything up and it stops
> processing. I did
Merging a couple of emails:
On 20-Aug.-2020 19:37, David Rowley wrote:
When updates occur in a non-partitioned table we can follow item
pointer chains to find the live row and check if the WHERE clause
still matches to determine if the row should be updated, or in this
case just locked since it
On Fri, Aug 21, 2020 at 9:58 AM Jim Jarvie wrote:
> However, as I scale up the number of concurrent connections, I see a spike in
> CPU (to 100% across 80 cores) when the SELECT FOR UPDATE SKIP LOCKED executes
> and the select processes wait for multiple minutes (10-20 minutes) before
> complet
On Fri, 21 Aug 2020 at 11:01, Michael Lewis wrote:
>
> On Thu, Aug 20, 2020 at 4:40 PM Jim Jarvie wrote:
>>
>> On 20-Aug.-2020 17:42, Michael Lewis wrote:
>>
>> Can you share an explain analyze for the query that does the select for
>> update? I wouldn't assume that partition pruning is possible
On Thu, Aug 20, 2020 at 4:40 PM Jim Jarvie wrote:
> On 20-Aug.-2020 17:42, Michael Lewis wrote:
>
> Can you share an explain analyze for the query that does the select for
> update? I wouldn't assume that partition pruning is possible at all with
> hash, and it would be interesting to see how it
On 20-Aug.-2020 17:42, Michael Lewis wrote:
Can you share an explain analyze for the query that does the select for
update? I wouldn't assume that partition pruning is possible at all with
hash, and it would be interesting to see how it is finding those rows.
Well this got interesting - the a
Can you share an explain analyze for the query that does the select for
update? I wouldn't assume that partition pruning is possible at all with
hash, and it would be interesting to see how it is finding those rows.
>
On 20-Aug.-2020 13:30, Michael Lewis wrote:
Great to hear that some of the issues are now mitigated. Though, perhaps
you actually require that ORDER BY if items are expected to be sitting in
the queue quite some time because you have incoming queue items in a burst
pattern and have to play catch
Great to hear that some of the issues are now mitigated. Though, perhaps
you actually require that ORDER BY if items are expected to be sitting in
the queue quite some time because you have incoming queue items in a burst
pattern and have to play catch up sometimes. If so, I highly suspect the
inde
Updates added, mixture of good and bad news:
On 18-Aug.-2020 20:39, Michael Lewis wrote:
How long does each process take in total? How strict does that FIFO really
need to be when you are already doing SKIP LOCKED anyway?
The processes all bottleneck[ed] for several minutes, approximately
exp
On Tue, 2020-08-18 at 19:52 -0400, Jim Jarvie wrote:
> I have a system which implements a message queue with the basic pattern that
> a process selects a group of,
> for example 250, rows for processing via SELECT .. LIMIT 250 FOR UPDATE SKIP
> LOCKED.
>
> When there are a small number of concu
Also, have you checked how bloated your indexes are getting? Do you run
default autovacuum settings? Did you update to the new default 2ms cost
delay value? With a destructive queue, it would be very important to ensure
autovacuum is keeping up with the churn. Share your basic table structure
and i
On Tue, Aug 18, 2020 at 6:22 PM Jim Jarvie wrote:
> There is some ordering on the select [ ORDER BY q_id] so each block of 250
> is sequential-ish queue items; I just need them more or less in the order
> they were queued so as near FIFO as possible without being totally strict
> on absolute sequ
Did you try using NOWAIT instead of SKIP LOCKED to see if the behavior
still shows up?
On Tue, Aug 18, 2020, 8:22 PM Jim Jarvie wrote:
> Thank you for the quick response.
>
> No adjustments of fill factors. Hadn't though of that - I'll investigate
> and try some options to see if I can measure
Thank you for the quick response.
No adjustments of fill factors. Hadn't though of that - I'll
investigate and try some options to see if I can measure an effect.
There is some ordering on the select [ ORDER BY q_id] so each block of
250 is sequential-ish queue items; I just need them more o
Message queue...
Are rows deleted? Are they updated once or many times? Have you adjusted
fillfactor on table or indexes? How many rows in the table currently or on
average? Is there any ordering to which rows you update?
It seems likely that one of the experts/code contributors will chime in and
Using V12, Linux [Ubuntu 16.04LTS]
I have a system which implements a message queue with the basic pattern
that a process selects a group of, for example 250, rows for processing
via SELECT .. LIMIT 250 FOR UPDATE SKIP LOCKED.
When there are a small number of concurrent connections to process
21 matches
Mail list logo
