Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED
От | Jim Jarvie |
---|---|
Тема | Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED |
Дата | |
Msg-id | 0e537a50-a43f-3404-27ed-3de9c3f7c281@talentstack.to обсуждение исходный текст |
Ответ на | Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED (Michael Lewis <mlewis@entrata.com>) |
Ответы |
Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED
(Michael Lewis <mlewis@entrata.com>)
Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED (Raj <raj@gusw.net>) |
Список | pgsql-performance |
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] #
В списке pgsql-performance по дате отправления:
Предыдущее
От: Michael LewisДата:
Сообщение: Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED
Следующее
От: Michael LewisДата:
Сообщение: Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED