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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





pá 28. 5. 2021 v 21:39 odesílatel Ryan Bair <ryandbair@xxxxxxxxx> 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 that out as a potential culprit. 

yes

the very strange is pretty high planning time

 Planning Time: 173.753 ms

This is unusually high number - maybe the server has bad CPU or maybe some indexes bloating

Regards

Pavel

On Fri, May 28, 2021 at 3:19 PM Campbell, Lance <lance@xxxxxxxxxxxx> 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

 

From: Andrew Dunstan <andrew@xxxxxxxxxxxx>
Date: Friday, May 28, 2021 at 2:08 PM
To: Dean Gibson (DB Administrator) <postgresql@xxxxxxxxxxx>, pgsql-performance@xxxxxxxxxxxxxxxxxxxx <pgsql-performance@xxxxxxxxxxxxxxxxxxxx>
Subject: 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 to v9.6,
> & was entirely satisfied with the result.
>
> In March of this year, AWS announced that v9.6 was nearing end of
> support, & AWS would forcibly upgrade everyone to v12 on January 22,
> 2022, if users did not perform the upgrade earlier.  My first attempt
> was successful as far as the upgrade itself, but complex queries that
> normally ran in a couple of seconds on v9.x, were taking minutes in v12.
>
> I didn't have the time in March to diagnose the problem, other than
> some futile adjustments to server parameters, so I reverted back to a
> saved copy of my v9.6 data.
>
> On Sunday, being retired, I decided to attempt to solve the issue in
> earnest.  I have now spent five days (about 14 hours a day), trying
> various things, including adding additional indexes.  Keeping the v9.6
> data online for web users, I've "forked" the data into new copies, &
> updated them in turn to PostgreSQL v10, v11, v12, & v13.  All exhibit
> the same problem:  As you will see below, it appears that versions 10
> & above are doing a sequential scan of some of the "large" (200K rows)
> tables.  Note that the expected & actual run times both differ for
> v9.6 & v13.2, by more than *two orders of magnitude*. Rather than post
> a huge eMail (ha ha), I'll start with this one, that shows an "EXPLAIN
> ANALYZE" from both v9.6 & v13.2, followed by the related table & view
> definitions.  With one exception, table definitions are from the FCC
> (Federal Communications Commission);  the view definitions are my own.
>
>
>

Have you tried reproducing these results outside RDS, say on an EC2
instance running vanilla PostgreSQL?


cheers


andrew



--
Andrew Dunstan
EDB: https://urldefense.com/v3/__https://www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux