Date: Wed, 29 Jun 2022 14:52:33 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Mikkel Lauritsen <renard@tala.dk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Fluctuating performance of updates on small table with trigger
Message-ID: <20220629195233.GR28130@telsasoft.com>
References: <4a925ad4ebb17849054ef947475e618b@tala.dk>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4a925ad4ebb17849054ef947475e618b@tala.dk>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20220629195233.GR28130%40telsasoft.com>
Precedence: bulk

On Wed, Jun 29, 2022 at 09:31:58PM +0200, Mikkel Lauritsen wrote:
> In short I'm running PostgreSQL 14.1 on Linux on a small test machine with

should try to upgrade to 14.4, for $reasons

> Is there any feasible way to find out what it is that causes Postgres to
> start doing slow updates? My guess would be a buffer filling up or something
> similar, but the regularity between runs paired with the irregular lengths
> of the fast and slow phases in each run doesn't really seem to fit with
> this.

Set log_checkpoints=on, log_autovacuum_min_duration=0, log_lock_waits=on, and
enable autoexplain with auto_explain.log_nested_statements=on.

Then see what's in the logs when that happens.

@hackers: the first two of those are enabled by default in 15dev, and this
inquiry seems to support that change.

-- 
Justin