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

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.gc@decathlon.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.

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@gmail.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.gc@decathlon.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.
> >

Once again, please don't top post.

On Sat, Apr 04, 2020 at 11:57:02AM +0530, Tejaswini GC wrote:
> Hello Julien,
> 
> The procedure for doing the upgrade is different for AWS.
> 


And is it possible to know what the procedure was?


> 
> 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.
> 


Any more details?  Are you talking of heavyweight locks or lightweight locks?
Since version 9.6 both are visible in pg_stat_activity.


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


Please follow https://wiki.postgresql.org/wiki/Slow_Query_Questions to provide
more details.