Re: Performance degradation after upgrading from 9.5 to 14 - Mailing list pgsql-general

From kaido vaikla
Subject Re: Performance degradation after upgrading from 9.5 to 14
Date
Msg-id CA+427g8YgWMYOO-Cow7pbDYc3=d5e-MqJSMnqub+v+AXTRtscA@mail.gmail.com
Whole thread Raw
In response to Re: Performance degradation after upgrading from 9.5 to 14  (Marcin Giedz <marcin.giedz@arise.pl>)
List pgsql-general
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 <marcin.giedz@arise.pl> wrote:
how about this:

jit = off ?

Marcin


On Wed, 17 Apr 2024 at 19:33, Johnathan Tiamoh <johnathantiamoh@gmail.com> 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.vondra@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

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: [help] Error in database import
Next
From: Lok P
Date:
Subject: Logging statement having any threat?