From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | Robert Creager <robertc(at)spectralogic(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, pgsql-performance(at)lists(dot)postgresql(dot)org, Barry Litt <barryl(at)spectralogic(dot)com> |
Subject: | Re: Need help identifying a periodic performance issue. |
Date: | 2021-11-24 23:15:42 |
Message-ID: | 20211124231542.GB17618@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Daulat | 2021-11-25 09:11:34 | pg_dump backup verification |
Previous Message | Robert Creager | 2021-11-24 22:44:12 | Re: Need help identifying a periodic performance issue. |