RE: Performance degradation after upgrading from 9.5 to 14

[Andreas Joseph Krogh]

På lørdag 27. april 2024 kl. 11:46:26, skrev Zahir Lalani <
ZahirLalani@oliver.agency >:

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
andr...@visena.com 
www.visena.com 
 

RE: Performance degradation after upgrading from 9.5 to 14

[Zahir Lalani]

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: Олександр Янін 
Sent: Monday, April 22, 2024 8:01 PM
To: Johnathan Tiamoh 
Cc: pgsql-generallists.postgresql.org 
Subject: Re: Performance degradation after upgrading from 9.5 to 14

You don't often get email from 
aleksandr.ja...@privatbank.ua<mailto:aleksandr.ja...@privatbank.ua>. 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=e519c2e6-bc6d-4fdf-8d9c-923c2f002385>
 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 
mailto:johnathantia...@gmail.com>>:
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 ?

Re: Performance degradation after upgrading from 9.5 to 14

[David Rowley]

On Tue, 23 Apr 2024 at 07:01, Олександр Янін
 wrote:
> 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.

It would be good to see a thread opened with details on this.  I
understand incorrect statistics can cause this but if there are other
reasons, it would be good to know what they are.

David

Re: Performance degradation after upgrading from 9.5 to 14

[Олександр Янін]

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 :

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

Re: Performance degradation after upgrading from 9.5 to 14

[kaido vaikla]

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  wrote:

> how about this:
>
> jit = off ?
>
> Marcin
>
>
> On Wed, 17 Apr 2024 at 19:33, Johnathan Tiamoh 
> 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 <
>> tomas.von...@enterprisedb.com> 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
>>>
>>

Re: Performance degradation after upgrading from 9.5 to 14

[Marcin Giedz]

how about this:

jit = off ?

Marcin


On Wed, 17 Apr 2024 at 19:33, Johnathan Tiamoh 
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 <
> tomas.von...@enterprisedb.com> 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
>>
>

Re: Performance degradation after upgrading from 9.5 to 14

[Johnathan Tiamoh]

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

Re: Performance degradation after upgrading from 9.5 to 14

[Christophe Pettus]

> On Apr 17, 2024, at 10:13, Johnathan Tiamoh  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.

Re: Performance degradation after upgrading from 9.5 to 14

[Tomas Vondra]

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

Performance degradation after upgrading from 9.5 to 14

[Johnathan Tiamoh]

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.