From: Justin Pryzby <pryzby@telsasoft.com>
To: Robert Creager <robertc@spectralogic.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Thomas Munro <thomas.munro@gmail.com>
Cc: pgsql-performance@lists.postgresql.org, Barry Litt <barryl@spectralogic.com>
Subject: Re: Need help identifying a periodic performance issue.
Date: Wed, 24 Nov 2021 17:15:42 -0600
Message-ID: <20211124231542.GB17618@telsasoft.com> (raw)
In-Reply-To: <BA7F0900-1AD5-4680-A11D-1D47429FCF2F@spectralogic.com>
References: <20211117231815.GF17618@telsasoft.com>
<1ADFE43B-5A43-40DA-B039-2D7E5FD82B6C@spectralogic.com>
<CA+hUKGJ4597Hz1jydC5mQL29uDxbBG2CXruxx+S=qSSQmqiL3w@mail.gmail.com>
<20211118054221.GJ17618@telsasoft.com>
<8048B2EE-E627-4818-9FB4-00E78D245CC8@spectralogic.com>
<CA+hUKGJOdENwAwSDAdQbjJyvFfviXT_0Sa=oRRry3BoRgZYtnQ@mail.gmail.com>
<2977426.1637271745@sss.pgh.pa.us>
<40167D9A-8FDE-4F47-9AD0-897F7242A6B7@spectralogic.com>
<77BB05DD-9543-4AD2-A58C-533D71864637@spectralogic.com>
<BA7F0900-1AD5-4680-A11D-1D47429FCF2F@spectralogic.com>
On Wed, Nov 24, 2021 at 10:44:12PM +0000, Robert Creager wrote:
> I forgot, I had reloaded postgres, but had not re-started our app, so the connections wouldn’t have that plan setting on them. Re-doing now.
Are you sure? GUC changes should be applied for existing sessions, right ?
Would you send the logs surrounding the slow COPY ?
Specifically including the autovacuum logs.
> We are at it again. I have a DELETE operation that’s taking 48 minutes so far.
Before, you had slow COPY due to FKs. Now you have a slow DELETE, which you
only alluded to before.
> So how do we avoid this query plan? Do we need to start doing explicit analyzes after every delete?
If your DELETE is deleting the entire table, then I think you should VACUUM
anyway (or else the next inserts will bloat the table).
Or (preferably) use TRUNCATE instead, which will set relpages=0 and (one
supposes) avoid the bad plans. But read the NOTE about non-mvcc behavior of
TRUNCATE, in case that matters to you.
But first, I believe Thomas was suggesting to put plan_cache_mode back to its
default, and (for testing purposes) try using issue DISCARD PLANS.
On Fri, Nov 19, 2021 at 10:08:02AM +1300, Thomas Munro wrote:
> Just to understand what's going on, it'd be interesting to know if the
> problem goes away if you *just* inject the DISCARD PLANS statement
> before running your COPYs, but if that doesn't help it'd also be
> interesting to know what happens if you ANALYZE each table after each
> COPY. Are you running any explicit ANALYZE commands? How long do
> your sessions/connections live for?
--
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, robertc@spectralogic.com, tgl@sss.pgh.pa.us, thomas.munro@gmail.com, barryl@spectralogic.com
Subject: Re: Need help identifying a periodic performance issue.
In-Reply-To: <20211124231542.GB17618@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox