From: Dean Gibson (DB Administrator) <postgresql@mailpen.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
Date: Fri, 28 May 2021 17:38:43 -0700
Message-ID: <988e2438-c322-c1d5-ca88-683943a80087@mailpen.com> (raw)
In-Reply-To: <2b7b9a25-e3c7-b980-a1e3-d21d8d91d34d@gmail.com>
References: <3fa6fd38-1f92-98ab-0055-3e0eb7e4e772@ultimeth.com>
<2a1b34f4-73d4-585b-f4fc-3bd9b9ba248e@aklaver.com>
<88255b75-e7e3-b29c-0e75-f989ea320570@mailpen.com>
<70edb767-6ba0-1065-2f74-f01440e3caea@gmail.com>
<d73e2731-31ba-df14-9b9c-974342200ae8@mailpen.com>
<2b7b9a25-e3c7-b980-a1e3-d21d8d91d34d@gmail.com>
On 2021-05-28 16:51, Ron wrote:
> On 5/28/21 5:06 PM, Dean Gibson (DB Administrator) wrote:
>> On 2021-05-28 12:38, Ron wrote:
>>> On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote:
>>>> On 2021-05-28 08:12, Adrian Klaver wrote:
>>>>> On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
>>>>>> 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.
>>>>>
>>>>> Did you run a plain
>>>>> ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on
>>>>> the tables in the new install?
>>>>
>>>> After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL
>>>> ANALYZE". On 10 through 12, it took about 45 minutes & significant
>>>> CPU activity, & temporarily doubled the size of the disk space
>>>> required. As you know, that disk space is not shrinkable under
>>>> AWS's RDS. On v13, it took 10 hours with limited CPU activity, &
>>>> actually slightly less disk space required.
>>>
>>> Under normal conditions, VACUUM FULL is pointless on a
>>> freshly-loaded database; in RDS, it's *anti-useful*.
>>>
>>> That's why Adrian asked if you did a plain ANALYZE.
>>
>> Just now did. No change in EXPLAIN ANALYZE output.
>
> Did it run in less than 10 hours?
>
The original VACUUM FULL ANALYZE ran in 10 hours. The plain ANALYZE ran
in 88 seconds.
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: postgresql@mailpen.com, pgsql-general@lists.postgresql.org
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
In-Reply-To: <988e2438-c322-c1d5-ca88-683943a80087@mailpen.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