BUG #16334: We recently upgraded PG version from 9.5 to 10.10 and system performance is not so good
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:
Hello Team,
We have upgraded our database into version 10.10.
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.
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.
What is the ideal setup or paramters that we should have when we upgrade to
v 10.10
Thanks
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.
Hello Julien,
Thanks for your response.
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.
2) What kind of vacuum should be done on the DB, as there are many types of
vacuum.
Awaiting your reply!
Thanks!
*Regards*
*Tejaswini G C*
*IT Retail Team*
On Thu, Apr 2, 2020 at 2:17 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
Show quoted text
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.
Show quoted text
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.
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@gmail.com> wrote:
Show quoted text
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 discussionon
-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.
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.gc@decathlon.com>
wrote:
Show quoted text
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@gmail.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@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 thediscussion 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.