Subject: Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED
To: Thomas Munro <thomas.munro@gmail.com>, David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
References: <c192f8bf-a747-6ad9-c54d-1bd6febafc4f@talentstack.to>
 <CA+hUKGKri4MSsOa+5FxduxO4Cd2kt9gwYn+9tCYkLpwyxngcsw@mail.gmail.com>
From: Jim Jarvie <jim@talentstack.to>
Message-Id: <0385e562-19a9-dffe-79e0-64b89142f6a2@talentstack.to>
Date: Fri, 21 Aug 2020 11:24:20 -0400
User-Agent: Mozilla/5.0 (Windows NT 6.1; rv:68.0) Gecko/20100101
 Thunderbird/68.11.0
Mime-Version: 1.0
In-Reply-To: 
 <CA+hUKGKri4MSsOa+5FxduxO4Cd2kt9gwYn+9tCYkLpwyxngcsw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Language: en-CA
Precedence: bulk

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's a SELECT FOR UPDATE. However, with
> partitioned table, a concurrent UPDATE may have caused the row to have
> been moved off to another partition, in which case the tuple's item
> pointer cannot point to it since we don't have enough address space,
> we only have 6 bytes for a TID. To get around the fact that we can't
> follow these update chains, we just throw the serialization error,
> which is what you're getting. Ideally, we'd figure out where the live
> version of the tuple is and check if it matches the WHERE clause and
> lock it if it does, but we've no means to do that with the current
> design.
This is the absolute best description of what causes the "tuple to be=20
locked was already moved to another partition due to concurrent update"=20
message I have ever seen.=C2=A0 It totally makes sense why this happens =
given=20
your explanation.=C2=A0 Thank you for giving the detail.

I am backing off the query with a time delay and then retrying and that=20
seems to be the correct approach as well, but I only stumbled upon that=20
by accident.=C2=A0 Hopefully when the google gnomes index the mailing =
list=20
this message will come up to save others time and worry about the =
message.

On 21-Aug.-2020 00:34, Thomas Munro wrote:
>
> Maybe it's just getting algorithmically ugly.  To claim some job rows,
> you have to skip all dead/non-matching tuples left behind so far at
> the start of the table by all the other sessions, and then also all
> currently locked tuples, and you have to do update-chain walks on some
> of them too.  It all gets a bit explosive once you have such high
> numbers of workers.
Yes, fundamentally it seems to come down to traffic volume.=C2=A0 When I =
get=20
over 128 connections all selecting and locking that one table, applying=20
the locks seems to struggle and the problem grows exponentially.=C2=A0 =
I'm an=20
extreme edge case, so not really a scenario that locking could ever have=20
been expected to handle but it's pretty good that it gets up to 128 at =
all.
> I think I'd experiment with splitting the job table up into N tables
> and feed jobs into all of them about evenly (by hashing, at random,
> whatever), and then I'd assign each consumer a "preferred" table where
> it looks for jobs first (perhaps my_worker_id % ntables), before
> trying the others in round robin order.  Then they won't trample on
> each other's toes so much.
I think this is a good idea, but (in my case) I think this is where it=20
will need v13 which is going to give that via "Allow ROW values to be=20
used as partitioning expressions" ? (e.g. will v13 permit queueid mod=20
200 as the partition expression to make 200 partitions to allow 200=20
[contention free] consumers?).
> In the past I've wondered about a hypothetical circular_seqscan
> option, which would cause table scans to start where they left off
> last time in each backend, so SELECT * FROM t LIMIT 1 repeated would
> show you a different row each time until you get all the way around to
> the start again (as we're entirely within our rights to do for a query
> with no ORDER BY).  That'd give the system a chance to vacuum and
> start refilling the start of the table before you get around to it
> again, instead of repeatedly having to step over the same useless
> pages every time you need a new job.
I like the sound of this; if I understand correctly, it would=20
essentially walk in insertion(-ish) order which would be OK for me. As=20
long as it was documented clearly; perhaps I should put a page online=20
about high traffic message queues with PostgreSQL for people to find=20
when they try the same thing.
>
> Hmm.  I guess another way to avoid colliding with others' work would
> be to try to use SELECT * FROM t TABLESAMPLE SYSTEM (10) WHERE ... FOR
> UPDATE SKIP LOCKED LIMIT ....  It's less cache-friendly, and less
> order-preserving, but way more contention-friendly.  That has another
> complication though; how do you pick 10?  And if it doesn't return any
> or enough rows, it doesn't mean there isn't enough, so you may need to
> be ready to fall back to the plain approach if having 250 rows is
> really important to you and TABLESAMPLE doesn't give you enough.  Or
> something.

For me, this is an acceptable compromise.=C2=A0 I just need to consume=20
something on each pass and up to 250 items but getting something less=20
than 250 would still allow progress and if it removed wait time, could=20
even be an overall greater throughput.=C2=A0 I may try this just to see =
how=20
it performs.

If someone has a strictly ordered queue, they will never really have=20
this issue as they must start at the beginning and go sequentially using=20
only 1 consumer.=C2=A0 Because I only need loose/approximate ordering =
and=20
throughput is the objective, all the locking and contention comes into =
play.

> By the way, when working with around 64 consumer processes I was also
> annoyed by the thundering herd problem when using NOTIFY.  I found
> various workaround solutions to that, but ultimately I think we need
> more precise wakeups for that sort of thing, which I hope to revisit
> one day.

I'm making a note of this because I also happen to have a different=20
scenario which has NOTIFY with well over 100 LISTEN consumers...=C2=A0 =
That's=20
not given me problems - yet - but I'm now aware of this should problems=20
arise.