Re: AWS forcing PG upgrade from v9.6 a disaster

On 05/29/21 07:08, Dean Gibson (DB Administrator) wrote: On 2021-05-28 19:43, Christophe Pettus wrote: ... The most common reason for this kind of inexplicable stuff after an RDS upgrade is, as others have said, parameter changes, since you get a new default parameter group after the upgrade.

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-05-28 19:43, Christophe Pettus wrote: ... The most common reason for this kind of inexplicable stuff after an RDS upgrade is, as others have said, parameter changes, since you get a new default parameter group after the upgrade. That being said, this does look like something happened t

Re: AWS forcing PG upgrade from v9.6 a disaster

> On May 28, 2021, at 14:30, Bruce Momjian wrote: > I think it uses pg_upgrade. It does. It does not, however, do the vacuum analyze step afterwards. A VACUUM (FULL, ANALYZE) should take care of that, and I believe the OP said he had done that after the pg_upgrade. The most common reason

Re: AWS forcing PG upgrade from v9.6 a disaster

On 5/28/21 10:27 PM, Jan Wieck wrote: > > > On Fri, May 28, 2021, 17:15 Andrew Dunstan > wrote: > > > > > AIUI he did an RDS upgrade. Surely that's not doing a dump/restore? I > assume you would know better than him or me what it actually does > do :-) > >

Re: AWS forcing PG upgrade from v9.6 a disaster

On Fri, May 28, 2021, 17:15 Andrew Dunstan wrote: > > On 5/28/21 4:23 PM, Jan Wieck wrote: > > On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote: > > > > What sticks out for me are these two scans, which balloon from 50-60 > > heap fetches to 1.5M each. > > > >> -> N

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-05-28 13:23, Jan Wieck wrote: On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote: What sticks out for me are these two scans, which balloon from 50-60 heap fetches to 1.5M each.   ->  Nested Loop (cost=0.29..0.68 rows=1 width=7) (actual time=0.003..0.004 row

Re: AWS forcing PG upgrade from v9.6 a disaster

I recently did 20 upgrades from 9.6 to 12.4 and 12.5. No issues and the upgrade process uses pg_upgrade. I don’t know if AWS modified it though. Bob Sent from my PDP11 > On May 28, 2021, at 5:15 PM, Andrew Dunstan wrote: > >  >> On 5/28/21 4:23 PM, Jan Wieck wrote: >> On 5/28/21 2:48 PM,

Re: AWS forcing PG upgrade from v9.6 a disaster

On Fri, May 28, 2021 at 05:15:33PM -0400, Andrew Dunstan wrote: > > How did you load the database? pg_dump -> psql/pg_restore? > > > > If so, did you perform a VACUUM FREEZE after the load? > > Jan > > > AIUI he did an RDS upgrade. Surely that's not doing a dump/restore? I > assume you would kno

Re: AWS forcing PG upgrade from v9.6 a disaster

On 5/28/21 4:23 PM, Jan Wieck wrote: > On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote: > > What sticks out for me are these two scans, which balloon from 50-60 > heap fetches to 1.5M each. > >>   ->  Nested Loop  (cost=0.29..0.68 rows=1 >> width=7) (actual time=0.003

Re: AWS forcing PG upgrade from v9.6 a disaster

pá 28. 5. 2021 v 21:39 odesílatel Ryan Bair napsal: > The problem is the plan. The planner massively underestimated the number > of rows arising from the _EN/_AM join. > > Usually postgres is pretty good about running ANALYZE as needed, but it > might be a good idea to run it manually to rule tha

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-05-28 12:18, Campbell, Lance wrote: Also, did you check your RDS setting in AWS after upgrading?  I run four databases in AWS.  I found that the work_mem was set way low after an upgrade.  I had to tweak many of my settings. Lance I've wondered a lot about work_mem.  The default

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-05-28 12:08, Andrew Dunstan wrote: On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote: [Reposted to the proper list] ... Have you tried reproducing these results outside RDS, say on an EC2 instance running vanilla PostgreSQL? cheers, andrew -- Andrew Dunstan EDB: https://www.

Re: AWS forcing PG upgrade from v9.6 a disaster

On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote: What sticks out for me are these two scans, which balloon from 50-60 heap fetches to 1.5M each. ->  Nested Loop  (cost=0.29..0.68 rows=1 width=7) (actual time=0.003..0.004 rows=1 loops=1487153)   

Re: AWS forcing PG upgrade from v9.6 a disaster

The plan is also influenced by cost related and memory related config settings such as random_page_cost and work_mem, right? Hence the questions if configs are matching or newer versions are using very conservative (default) settings.

Re: AWS forcing PG upgrade from v9.6 a disaster

The problem is the plan. The planner massively underestimated the number of rows arising from the _EN/_AM join. Usually postgres is pretty good about running ANALYZE as needed, but it might be a good idea to run it manually to rule that out as a potential culprit. On Fri, May 28, 2021 at 3:19 PM

Re: AWS forcing PG upgrade from v9.6 a disaster

Hi Lance, Did you customize the PG 12 DB Parameter group to be in sync as much as possible with the 9.6 RDS version?  Or are you using PG12 default DB Parameter group? Are you using the same AWS Instance Class? Did you vacuum analyze all your tables after the upgrade to 12? Regards, Michael

Re: AWS forcing PG upgrade from v9.6 a disaster

Also, did you check your RDS setting in AWS after upgrading? I run four databases in AWS. I found that the work_mem was set way low after an upgrade. I had to tweak many of my settings. Lance From: Andrew Dunstan Date: Friday, May 28, 2021 at 2:08 PM To: Dean Gibson (DB Administrator) , pg

Re: AWS forcing PG upgrade from v9.6 a disaster

On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote: > [Reposted to the proper list] > > I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4 > at one point), gradually moving to v9.0 w/ replication in 2010.  In > 2017 I moved my 20GB database to AWS/RDS, gradually upgrading