public inbox for [email protected]help / color / mirror / Atom feed
Performance degradation after upgrading from 9.5 to 14 9+ messages / 8 participants [nested] [flat]
* Performance degradation after upgrading from 9.5 to 14 @ 2024-04-17 17:13 Johnathan Tiamoh <[email protected]> 0 siblings, 3 replies; 9+ messages in thread From: Johnathan Tiamoh @ 2024-04-17 17:13 UTC (permalink / raw) To: pgsql-generallists.postgresql.org <[email protected]> Hello, I performed an upgrade from postgresql-9.5 to postgresql-14 and the performance has degraded drastically. Please, is they any advice on getting performance back ? King Regards Johnathan T. ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance degradation after upgrading from 9.5 to 14 @ 2024-04-17 17:25 Tomas Vondra <[email protected]> parent: Johnathan Tiamoh <[email protected]> 2 siblings, 1 reply; 9+ messages in thread From: Tomas Vondra @ 2024-04-17 17:25 UTC (permalink / raw) To: Johnathan Tiamoh <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On 4/17/24 19:13, Johnathan Tiamoh wrote: > Hello, > > > I performed an upgrade from postgresql-9.5 to postgresql-14 and the > performance has degraded drastically. > > Please, is they any advice on getting performance back ? > There's very little practical advice we can provide based on this report, because it's missing any useful details. There's a number of things that might have caused this, but we'd have to speculate. For example: 1) How did you upgrade? pg_dump or pg_upgrade? 2) Did you run ANALYZE to collect statistics after the upgrade? 3) Did you transfer the configuration, or did you just create a new cluster with the default values? 4) What exactly is slower? Queries? Inserts? 5) Can you quantify the impact? Is it 2x slower? 100x slower? regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance degradation after upgrading from 9.5 to 14 @ 2024-04-17 17:28 Christophe Pettus <[email protected]> parent: Johnathan Tiamoh <[email protected]> 2 siblings, 0 replies; 9+ messages in thread From: Christophe Pettus @ 2024-04-17 17:28 UTC (permalink / raw) To: Johnathan Tiamoh <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]> > On Apr 17, 2024, at 10:13, Johnathan Tiamoh <[email protected]> wrote: > I performed an upgrade from postgresql-9.5 to postgresql-14 and the performance has degraded drastically. > > Please, is they any advice on getting performance back ? Run: VACUUM (ANALYZE, VERBOSE); More seriously (although make sure you did do that), "performance" is made up of a lot of components. There's no "go faster" switch in postgresql.conf you may have neglected. You'll need to do a bit of investigation first to find out what is running slow, where it should be fast: I/O performance? Query times? Once you have that information, the community can provide much more assistance. ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance degradation after upgrading from 9.5 to 14 @ 2024-04-17 17:32 Johnathan Tiamoh <[email protected]> parent: Tomas Vondra <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Johnathan Tiamoh @ 2024-04-17 17:32 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]> 1) How did you upgrade? pg_dump or pg_upgrade? I use pg_ugrade with kink option. 2) Did you run ANALYZE to collect statistics after the upgrade? Yes. I ran vacuumdb-analyze in stages after the upgrade 3) Did you transfer the configuration, or did you just create a new cluster with the default values? I transfer the configuration 4) What exactly is slower? Queries? Inserts? queries 5) Can you quantify the impact? Is it 2x slower? 100x slower? it's more than 5 times slower than before. Very high load averages On Wed, Apr 17, 2024 at 1:25 PM Tomas Vondra <[email protected]> wrote: > On 4/17/24 19:13, Johnathan Tiamoh wrote: > > Hello, > > > > > > I performed an upgrade from postgresql-9.5 to postgresql-14 and the > > performance has degraded drastically. > > > > Please, is they any advice on getting performance back ? > > > > There's very little practical advice we can provide based on this > report, because it's missing any useful details. There's a number of > things that might have caused this, but we'd have to speculate. > > For example: > > 1) How did you upgrade? pg_dump or pg_upgrade? > > 2) Did you run ANALYZE to collect statistics after the upgrade? > > 3) Did you transfer the configuration, or did you just create a new > cluster with the default values? > > 4) What exactly is slower? Queries? Inserts? > > 5) Can you quantify the impact? Is it 2x slower? 100x slower? > > > regards > > > -- > Tomas Vondra > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance degradation after upgrading from 9.5 to 14 @ 2024-04-17 17:38 Marcin Giedz <[email protected]> parent: Johnathan Tiamoh <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Marcin Giedz @ 2024-04-17 17:38 UTC (permalink / raw) To: Johnathan Tiamoh <[email protected]>; +Cc: Tomas Vondra <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> how about this: jit = off ? Marcin On Wed, 17 Apr 2024 at 19:33, Johnathan Tiamoh <[email protected]> wrote: > 1) How did you upgrade? pg_dump or pg_upgrade? > > I use pg_ugrade with kink option. > > 2) Did you run ANALYZE to collect statistics after the upgrade? > > > Yes. I ran vacuumdb-analyze in stages after the upgrade > > 3) Did you transfer the configuration, or did you just create a new > cluster with the default values? > > I transfer the configuration > > 4) What exactly is slower? Queries? Inserts? > > queries > > 5) Can you quantify the impact? Is it 2x slower? 100x slower? > > it's more than 5 times slower than before. Very high load averages > > On Wed, Apr 17, 2024 at 1:25 PM Tomas Vondra < > [email protected]> wrote: > >> On 4/17/24 19:13, Johnathan Tiamoh wrote: >> > Hello, >> > >> > >> > I performed an upgrade from postgresql-9.5 to postgresql-14 and the >> > performance has degraded drastically. >> > >> > Please, is they any advice on getting performance back ? >> > >> >> There's very little practical advice we can provide based on this >> report, because it's missing any useful details. There's a number of >> things that might have caused this, but we'd have to speculate. >> >> For example: >> >> 1) How did you upgrade? pg_dump or pg_upgrade? >> >> 2) Did you run ANALYZE to collect statistics after the upgrade? >> >> 3) Did you transfer the configuration, or did you just create a new >> cluster with the default values? >> >> 4) What exactly is slower? Queries? Inserts? >> >> 5) Can you quantify the impact? Is it 2x slower? 100x slower? >> >> >> regards >> >> >> -- >> Tomas Vondra >> EnterpriseDB: http://www.enterprisedb.com >> The Enterprise PostgreSQL Company >> > ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance degradation after upgrading from 9.5 to 14 @ 2024-04-20 07:59 kaido vaikla <[email protected]> parent: Marcin Giedz <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: kaido vaikla @ 2024-04-20 07:59 UTC (permalink / raw) To: Marcin Giedz <[email protected]>; +Cc: Johnathan Tiamoh <[email protected]>; Tomas Vondra <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> I'm not sure, does it helps you but read this: https://www.cybertec-postgresql.com/en/b-tree-index-improvements-in-postgresql-v12/ "Since upgrading with pg_upgrade does not change the data files, indexes will still be in version 3 after an upgrade" I reindexed all my database, when did upgrade pg<12 -> pg>=12 if pg_upgrade was a tool. exp-imp for upgrade does not need reindex. br Kaido On Wed, 17 Apr 2024 at 20:39, Marcin Giedz <[email protected]> wrote: > how about this: > > jit = off ? > > Marcin > > > On Wed, 17 Apr 2024 at 19:33, Johnathan Tiamoh <[email protected]> > wrote: > >> 1) How did you upgrade? pg_dump or pg_upgrade? >> >> I use pg_ugrade with kink option. >> >> 2) Did you run ANALYZE to collect statistics after the upgrade? >> >> >> Yes. I ran vacuumdb-analyze in stages after the upgrade >> >> 3) Did you transfer the configuration, or did you just create a new >> cluster with the default values? >> >> I transfer the configuration >> >> 4) What exactly is slower? Queries? Inserts? >> >> queries >> >> 5) Can you quantify the impact? Is it 2x slower? 100x slower? >> >> it's more than 5 times slower than before. Very high load averages >> >> On Wed, Apr 17, 2024 at 1:25 PM Tomas Vondra < >> [email protected]> wrote: >> >>> On 4/17/24 19:13, Johnathan Tiamoh wrote: >>> > Hello, >>> > >>> > >>> > I performed an upgrade from postgresql-9.5 to postgresql-14 and the >>> > performance has degraded drastically. >>> > >>> > Please, is they any advice on getting performance back ? >>> > >>> >>> There's very little practical advice we can provide based on this >>> report, because it's missing any useful details. There's a number of >>> things that might have caused this, but we'd have to speculate. >>> >>> For example: >>> >>> 1) How did you upgrade? pg_dump or pg_upgrade? >>> >>> 2) Did you run ANALYZE to collect statistics after the upgrade? >>> >>> 3) Did you transfer the configuration, or did you just create a new >>> cluster with the default values? >>> >>> 4) What exactly is slower? Queries? Inserts? >>> >>> 5) Can you quantify the impact? Is it 2x slower? 100x slower? >>> >>> >>> regards >>> >>> >>> -- >>> Tomas Vondra >>> EnterpriseDB: http://www.enterprisedb.com >>> The Enterprise PostgreSQL Company >>> >> ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Performance degradation after upgrading from 9.5 to 14 @ 2024-04-22 19:01 Олександр Янін <[email protected]> parent: Johnathan Tiamoh <[email protected]> 2 siblings, 1 reply; 9+ messages in thread From: Олександр Янін @ 2024-04-22 19:01 UTC (permalink / raw) To: Johnathan Tiamoh <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]> Try setting enable_memoize to off. Our practice has shown that enabling this parameter by default often resulted in less than optimal query plans in the cache. ср, 17 апр. 2024 г. в 20:13, Johnathan Tiamoh <[email protected]>: > Hello, > > > I performed an upgrade from postgresql-9.5 to postgresql-14 and the > performance has degraded drastically. > > Please, is they any advice on getting performance back ? > > > King Regards > Johnathan T. > > > > -- Цей електронний лист та будь-які передані разом із ним файли є кoнфіденцiйною iнформацiєю, що належить ПриватБанку й призначена тільки для використання фізичною або юридичною особою, якій їх адресовано. Якщо ви не зазначений адресат, то не маєте права зберігати, поширювати або копіювати цей лист. У разі помилкового отримання просимо видалити його та повідомити автору. This email and any files transmitted with it are confidential information belonging to PrivatBank and intended solely for the use of the individual or entity to whom they are addressed. If you are not the named addressее, you are not authorised to further store, disseminate or copy it. In case of receiving this email by mistake we kindly ask to delete it and inform the author. ^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Performance degradation after upgrading from 9.5 to 14 @ 2024-04-27 09:46 Zahir Lalani <[email protected]> parent: Олександр Янін <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Zahir Lalani @ 2024-04-27 09:46 UTC (permalink / raw) To: Олександр Янін <[email protected]>; Johnathan Tiamoh <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]> Same issue and took us ages to work out that is was JIT! The default is on, and setting off solves the problem. I have seen several blogs reporting the same and so wonder why this default is on? Z From: Олександр Янін <[email protected]> Sent: Monday, April 22, 2024 8:01 PM To: Johnathan Tiamoh <[email protected]> Cc: pgsql-generallists.postgresql.org <[email protected]> Subject: Re: Performance degradation after upgrading from 9.5 to 14 You don't often get email from [email protected]<mailto:[email protected]>. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification; CAUTION: This email originated from outside of the organisation. Do not click links or open attachments unless you recognise the sender and know the content is safe. Visit the information security portal (MetaCompliance - MyCompliance Cloud)<https://launcher.myapps.microsoft.com/api/signin/c773a37f-a0e3-4e64-98a8-3bd3dc6c6392?tenantId=e519c...; to do your training. Try setting enable_memoize to off. Our practice has shown that enabling this parameter by default often resulted in less than optimal query plans in the cache. ср, 17 апр. 2024 г. в 20:13, Johnathan Tiamoh <[email protected]<mailto:[email protected]>>: Hello, I performed an upgrade from postgresql-9.5 to postgresql-14 and the performance has degraded drastically. Please, is they any advice on getting performance back ? ^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Performance degradation after upgrading from 9.5 to 14 @ 2024-04-27 11:25 Andreas Joseph Krogh <[email protected]> parent: Zahir Lalani <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Andreas Joseph Krogh @ 2024-04-27 11:25 UTC (permalink / raw) To: Zahir Lalani <[email protected]>; +Cc: Олександр Янін <[email protected]>; Johnathan Tiamoh <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> På lørdag 27. april 2024 kl. 11:46:26, skrev Zahir Lalani < [email protected] <mailto:[email protected]>>: Same issue and took us ages to work out that is was JIT! The default is on, and setting off solves the problem. I have seen several blogs reporting the same and so wonder why this default is on? I can confirm this, even in v16 we've turned JIT off. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 [email protected] <mailto:[email protected]> www.visena.com <https://www.visena.com; <https://www.visena.com; ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-04-27 11:25 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-04-17 17:13 Performance degradation after upgrading from 9.5 to 14 Johnathan Tiamoh <[email protected]> 2024-04-17 17:25 ` Tomas Vondra <[email protected]> 2024-04-17 17:32 ` Johnathan Tiamoh <[email protected]> 2024-04-17 17:38 ` Marcin Giedz <[email protected]> 2024-04-20 07:59 ` kaido vaikla <[email protected]> 2024-04-17 17:28 ` Christophe Pettus <[email protected]> 2024-04-22 19:01 ` Олександр Янін <[email protected]> 2024-04-27 09:46 ` Zahir Lalani <[email protected]> 2024-04-27 11:25 ` Andreas Joseph Krogh <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox