Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED

From: Michael Lewis <mlewis(at)entrata(dot)com>
To: Jim Jarvie <jim(at)talentstack(dot)to>
Cc: Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED
Date: 2020-08-19 00:39:45
Message-ID: CAHOFxGq6qCkyDV3DYynxp8BKg4Q5uPQcjUSzXH=OFBnm70S6Og@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Tue, Aug 18, 2020 at 6:22 PM Jim Jarvie <jim(at)talentstack(dot)to> 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 sequential order.
>
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?

Table has around 192K rows, as a row is processed it is deleted as part of
> the transaction with a commit at the end after all 250 are processed
> [partitioned table, state changes and it migrates to a different partition]
> and as the queue drops to 64K it is added to with 128K rows at a time.
>
Can you expound on the partitioning? Are all consumers of the queue always
hitting one active partition and anytime a row is processed, it always
moves to one of many? archived type partitions?

Less processes does not give the throughput required because the queue
> sends data elsewhere which has a long round trip time
>

Is that done via FDW or otherwise within the same database transaction? Are
you connecting some queue consumer application code to Postgres, select for
update, doing work on some remote system that is slow, and then coming back
and committing the DB work?

By the way, top-posting is discouraged here and partial quotes with
interspersed comments are common practice.

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Michael Lewis 2020-08-19 00:45:26 Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED
Previous Message Henrique Montenegro 2020-08-19 00:38:56 Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED