Re: BUG #16334: We recently upgraded PG version from 9.5 to 10.10 and system performance is not so good

Hello Julien,

The procedure for doing the upgrade is different for AWS.

After the PG upgrade we can see many locks in our system we believe this is
the main reason for the issue we are facing,
I can see these locks when using pg_stat_activity.

Along with that few queries which were executing within millisecond are
taking more than few hours.

Kindly check and let us know how to fix this.

Appreciate the support.

*Regards*
*Tejaswini G C*
*IT Retail Team*

On Thu, Apr 2, 2020 at 4:39 PM Tejaswini GC <tejaswini(dot)gc(at)decathlon(dot)com>
wrote:

> Hello Julien,
>
> Thanks for your inputs,
> I shall get back to you If some information is needed.
>
>
> *Regards*
> *Tejaswini G C*
> *IT Retail Team*
>
>
> On Thu, Apr 2, 2020 at 4:35 PM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>
>> Please don't top-post, it makes it hard to follow the discussion.
>>
>> On Thu, Apr 02, 2020 at 03:52:56PM +0530, Tejaswini GC wrote:
>> >
>> > I'm in touch with our hosting team to get more information for your
>> queries.
>> > As of now I can share these details.
>> > We did analyze on the DB, but not the vacuum,
>> > We are using AWS RDS.
>> >
>> > Please help me with these queries as well.
>> >
>> > 1) Will the process change if we use AWS RDS.
>>
>>
>> No idea, that's a question you should ask them.
>>
>>
>> > 2) What kind of vacuum should be done on the DB, as there are many
>> types of
>> > vacuum.
>>
>>
>> A regular vacuum, as in:
>>
>> VACUUM ANALYZE
>>
>> in all your databases.
>>
>>
>> > On Thu, Apr 2, 2020 at 2:17 PM Julien Rouhaud <rjuju123(at)gmail(dot)com>
>> wrote:
>> >
>> > > Hi,
>> > >
>> > > On Thu, Apr 02, 2020 at 03:52:25AM +0000, PG Bug reporting form wrote:
>> > > > The following bug has been logged on the website:
>> > > >
>> > > > Bug reference: 16334
>> > > > Logged by: Tejaswini GC
>> > > > Email address: tejaswini(dot)gc(at)decathlon(dot)com
>> > > > PostgreSQL version: 10.10
>> > > > Operating system: Centos 7
>> > > > Description:
>> > >
>> > >
>> > > First of all, this is not a bug. You should have instead started a
>> > > discussion
>> > > on pgsql-general or pgsql-performance. I'm redirecting the
>> discussion on
>> > > -performance.
>> > >
>> > >
>> > > > We have upgraded our database into version 10.10.
>> > >
>> > >
>> > > How did you upgrade?
>> > >
>> > >
>> > > > After upgrading we could see that the system performance is bad ,
>> and
>> > > one of
>> > > > the applications linked to it via web service is not working.
>> > >
>> > >
>> > > Do you have any errors in the postgres logs?
>> > >
>> > >
>> > > > During this upgrade we have not done any code changes either on the
>> > > > application side or on our ERP side.
>> > > >
>> > > > We are trying to debug everything from application perse, but till
>> now
>> > > we do
>> > > > not have any lead.
>> > > >
>> > > > Can you tell us are there any measures that we need to take after
>> > > upgrade.
>> > >
>> > >
>> > > It depends on how you did the upgrade. If you used pg_upgrade, did
>> you
>> > > run the
>> > > generated script as documented in step 13 at
>> > > https://www.postgresql.org/docs/current/pgupgrade.html?
>> > >
>> > > Otherwise, at least a database-wide VACUUM ANALYZE on every database
>> is the
>> > > bare minimum to run after an upgrade.
>> > >
>>
>