To: pgsql-performance@lists.postgresql.org
From: Jim Jarvie <jim@talentstack.to>
Subject: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED
Message-Id: <c192f8bf-a747-6ad9-c54d-1bd6febafc4f@talentstack.to>
Date: Tue, 18 Aug 2020 19:52:56 -0400
User-Agent: Mozilla/5.0 (Windows NT 6.1; rv:68.0) Gecko/20100101
 Thunderbird/68.11.0
Mime-Version: 1.0
Content-Type: multipart/alternative;
 boundary=------------4549C326236A3BB98AF7F146
Content-Language: en-CA
Precedence: bulk

--------------4549C326236A3BB98AF7F146
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable

Using V12, Linux [Ubuntu 16.04LTS]

I have a system which implements a message queue with the basic pattern=20
that a process selects a group of, for example 250, rows for processing=20
via SELECT .. LIMIT 250 FOR UPDATE SKIP LOCKED.

When there are a small number of concurrent connections to process the=20
queue, this seems to work as expected and connections quickly obtain a=20
unique block of 250 rows for processing.

However, as I scale up the number of concurrent connections, I see a=20
spike in CPU (to 100% across 80 cores) when the SELECT FOR UPDATE SKIP=20
LOCKED executes and the select processes wait for multiple minutes=20
(10-20 minutes) before completing.=C2=A0 My use case requires around 256=20
concurrent processors for the queue but I've been unable to scale beyond=20
128 without everything grinding to a halt.

The queue table itself fits in RAM (with 2M hugepages) and during the=20
wait, all the performance counters drop to almost 0 - no disk read or=20
write (semi-expected due to the table fitting in memory) with 100%=20
buffer hit rate in pg_top and row read around 100/s which is much=20
smaller than expected.

After processes complete the select and the number of waiting selects=20
starts to fall, CPU load falls and then suddenly the remaining processes=20
all complete within a few seconds and things perform normally until the=20
next time there are a group of SELECT FOR UPDATE statements which bunch=20
together and things then repeat.

I found that performing extremely frequent vacuum analyze (every 30=20
minutes) helps a small amount but this is not that helpful so problems=20
are still very apparent.

I've exhausted all the performance tuning and analysis results I can=20
find that seem even a little bit relevant but cannot get this cracked.

Is anyone on the list able to help with suggestions of what I can do to=20
track why this CPU hogging happens as this does seem to be the root of=20
the problem?

Thanks in advance,

Jim


--------------4549C326236A3BB98AF7F146
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>

    <meta http-equiv=3D"content-type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body>
    <p>Using V12, Linux [Ubuntu 16.04LTS]<br>
    </p>
    <p>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.</p>
    When there are a small number of concurrent connections to process
    the queue, this seems to work as expected and connections quickly
    obtain a unique block of 250 rows for processing.
    <p>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 completing.=C2=A0 My use =
case
      requires around 256 concurrent processors for the queue but I've
      been unable to scale beyond 128 without everything grinding to a
      halt.<br>
    </p>
    <p>The queue table itself fits in RAM (with 2M hugepages) and during
      the wait, all the performance counters drop to almost 0 - no disk
      read or write (semi-expected due to the table fitting in memory)
      with 100% buffer hit rate in pg_top and row read around 100/s
      which is much smaller than expected.</p>
    <p>After processes complete the select and the number of waiting
      selects starts to fall, CPU load falls and then suddenly the
      remaining processes all complete within a few seconds and things
      perform normally until the next time there are a group of SELECT=C2=
=A0
      FOR UPDATE statements which bunch together and things then =
repeat.<br>
    </p>
    <p>I found that performing extremely frequent vacuum analyze (every
      30 minutes) helps a small amount but this is not that helpful so
      problems are still very apparent.</p>
    <p>I've exhausted all the performance tuning and analysis results I
      can find that seem even a little bit relevant but cannot get this
      cracked.</p>
    <p>Is anyone on the list able to help with suggestions of what I can
      do to track why this CPU hogging happens as this does seem to be
      the root of the problem?</p>
    <p>Thanks in advance,</p>
    <p>Jim</p>
    <p><br>
    </p>
  </body>
</html>

--------------4549C326236A3BB98AF7F146--