Re: Need help identifying a periodic performance issue.

[Robert Creager <robertc@xxxxxxxxxxxxxxxx>]

On Nov 15, 2021, at 10:50 PM, Thomas Munro <thomas.munro@xxxxxxxxx> wrote:

This message originated outside your organization.

On Tue, Nov 16, 2021 at 5:43 PM Robert Creager <robertc@xxxxxxxxxxxxxxxx> wrote:

One CPU is pegged, the data has been sent over STDIN, so Postgres is not waiting for more, there are no other queries running using this select:

So PostgreSQL is eating 100% CPU, with no value shown in
wait_event_type, and small numbers of system calls are counted.  In
that case, is there an interesting user stack that jumps out with a
profiler during the slowdown (or the kernel version, stack())?

sudo dtrace -n 'profile-99 /arg0/ { @[ustack()] = count(); } tick-10s
{ exit(0); }

I setup a monitoring script to do the dtrace stack sampler you sent once a minute on the top CPU consuming Postgres process.  Now I wait until we reproduce it.

#!/usr/local/bin/bash

while [[ true ]]; do

   DATE=$(date "+%d-%H:%M:%S")

   PID=$(top -b | grep postgres | head -n 1  | awk '{print $1}')

   echo "${DATE} ${PID}"

   dtrace -n 'profile-99 /pid == '$PID'/ { @[ustack()] = count(); } tick-10s { exit(0); }' > dtrace/dtrace_${DATE}.txt

   sleep 60

done

Presuming this is the type of output you are expecting:

CPU     ID                    FUNCTION:NAME

  0  58709                        :tick-10s 

              postgres`AtEOXact_LargeObject+0x11

              postgres`CommitTransaction+0x127

              postgres`CommitTransactionCommand+0xf2

              postgres`PostgresMain+0x1fef

              postgres`process_startup_packet_die

              postgres`0x73055b

              postgres`PostmasterMain+0xf36

              postgres`0x697837

              postgres`_start+0x100

              `0x80095f008

                1

              postgres`printtup+0xf3

              postgres`standard_ExecutorRun+0x136

              postgres`PortalRunSelect+0x10f

              postgres`PortalRun+0x1c8

              postgres`PostgresMain+0x1f94

              postgres`process_startup_packet_die

              postgres`0x73055b

              postgres`PostmasterMain+0xf36

              postgres`0x697837

              postgres`_start+0x100

              `0x80095f008

                1

...