From: Jim Jarvie <jim@talentstack.to>
To: Michael Lewis <mlewis@entrata.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED
Date: Thu, 20 Aug 2020 18:39:59 -0400
Message-ID: <0e537a50-a43f-3404-27ed-3de9c3f7c281@talentstack.to> (raw)
In-Reply-To: <CAHOFxGp_r5=TLNJX7insLP=L=o24tojKnopRo9fvzKPFxJi8Ww@mail.gmail.com>
References: <c192f8bf-a747-6ad9-c54d-1bd6febafc4f@talentstack.to>
<CAHOFxGodYysPhKRwybWksDdxQu0EGVMtgL1dGpLrdXxj382ttg@mail.gmail.com>
<5e5ba420-9365-265d-c62a-4586cfb7c418@talentstack.to>
<CAHOFxGq6qCkyDV3DYynxp8BKg4Q5uPQcjUSzXH=OFBnm70S6Og@mail.gmail.com>
<ccaa5c05-196a-50c4-9d9d-705e3a6bccac@talentstack.to>
<CAHOFxGo1Aq1+c++1-p1oy2J13D2+oQJ2r=_oXH2F3y-wJ3Uc9Q@mail.gmail.com>
<e97e1113-afb2-54dc-93f8-75cf8f9559c3@talentstack.to>
<CAHOFxGp_r5=TLNJX7insLP=L=o24tojKnopRo9fvzKPFxJi8Ww@mail.gmail.com>
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 already moved error showed up: Note,
the actual process partitions are regular table partitions, these are
not hashed. Only the incoming and completed are hashed due to row
counts at either end of the processing; in flight (where the issue shows
up) is quite small:
[queuedb] # explain analyze select queueid,txobject,objectid,state from
mq.queue where (state = 'tx_active' or state='tx_fail_retryable') and
txobject = 'ticket' limit 250 for update skip locked;
ERROR: 40001: tuple to be locked was already moved to another partition
due to concurrent update
LOCATION: heapam_tuple_lock, heapam_handler.c:405
Time: 579.131 ms
[queuedb] # explain analyze select queueid,txobject,objectid,state from
mq.queue where (state = 'tx_active' or state='tx_fail_retryable') and
txobject = 'ticket' limit 250 for update skip locked;
ERROR: 40001: tuple to be locked was already moved to another partition
due to concurrent update
LOCATION: heapam_tuple_lock, heapam_handler.c:405
Time: 568.008 ms
[queuedb] # explain analyze select queueid,txobject,objectid,state from
mq.queue where (state = 'tx_active' or state='tx_fail_retryable') and
txobject = 'ticket' limit 250 for update skip locked;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..25.71 rows=250 width=34) (actual
time=1306.041..1306.338 rows=250 loops=1)
-> LockRows (cost=0.00..7934.38 rows=77150 width=34) (actual
time=1306.040..1306.315 rows=250 loops=1)
-> Append (cost=0.00..7162.88 rows=77150 width=34) (actual
time=520.685..1148.347 rows=31500 loops=1)
-> Seq Scan on queue_tx_active (cost=0.00..6764.50
rows=77000 width=34) (actual time=520.683..1145.258 rows=31500 loops=1)
Filter: ((txobject = 'ticket'::mq.queue_object)
AND ((state = 'tx_active'::mq.tx_state) OR (state =
'tx_fail_retryable'::mq.tx_state)))
-> Seq Scan on queue_tx_fail_retryable
(cost=0.00..12.62 rows=150 width=34) (never executed)
Filter: ((txobject = 'ticket'::mq.queue_object)
AND ((state = 'tx_active'::mq.tx_state) OR (state =
'tx_fail_retryable'::mq.tx_state)))
Planning Time: 0.274 ms
Execution Time: 1306.380 ms
(9 rows)
Time: 1317.150 ms (00:01.317)
[queuedb] #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jim@talentstack.to, mlewis@entrata.com, pgsql-performance@lists.postgresql.org
Subject: Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED
In-Reply-To: <0e537a50-a43f-3404-27ed-3de9c3f7c281@talentstack.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox