Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ry5d4-00EV7f-Qn for pgsql-general@arkaria.postgresql.org; Sat, 20 Apr 2024 07:59:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ry5d0-008l4U-NN for pgsql-general@arkaria.postgresql.org; Sat, 20 Apr 2024 07:59:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ry5d0-008l1V-4y for pgsql-general@lists.postgresql.org; Sat, 20 Apr 2024 07:59:18 +0000 Received: from mail-qv1-xf33.google.com ([2607:f8b0:4864:20::f33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1ry5cw-003jrY-V0 for pgsql-general@lists.postgresql.org; Sat, 20 Apr 2024 07:59:16 +0000 Received: by mail-qv1-xf33.google.com with SMTP id 6a1803df08f44-69b40061bbeso16624546d6.1 for ; Sat, 20 Apr 2024 00:59:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1713599954; x=1714204754; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=1e3Oan8/0xSvtDbQEtrVyiOtmGbHrtfFWX5XQtVTiWY=; b=mueQPk07HorLmPhTE/2/mmlQJjuVciqgqqabFBkiuc3ou9xJRaRD8NQNWG/hIktqgn ltVcbzKuQxflliEbeU5OcZ6ywCh0qd7gZTa5pTNThart05cVOTI6QqxfE7nmM6rCFOvX VUQAry0Df5rILyp+N4h5kw/WpSaJpYm36hH4U/60WN0RERc5HEPv1PNltZ+ZPtNBJx6j sgC5zJw5Tw1/IiGYVSVvvWEVH9xE/uqzu3bi+W0Z7Gvtezc4pk0znDskAbXgdxKcvDTV c6/LBSi7EbjnOtEMYM7XDmNqS98ZZdWg5IKqakZ0zXA16iJEIdj4le0pNRtwvRI1nW5A F6iQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713599954; x=1714204754; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=1e3Oan8/0xSvtDbQEtrVyiOtmGbHrtfFWX5XQtVTiWY=; b=RxJTBxd1OitEwnrF09z8kQ8NNeLqTVEmnnyi44SDtW9NRRKdW6bmZdTPRAfHudH5IJ f0wEb76d/3ji2smdzOSffntS07UMeP2QiRRrShd2bylAJyRqd6v2heoyqY/5E96+a9w7 nF+EProrHKe4D3cvhj7SgPrE4hib5Dd8U9SZqX0HkZws3tiCe6TrP52DHaHhfS6XCgQc v47bhgGm5sPYEqOosrehAzecvYLMICNW49264eqXL3cPHA1FbTsET1m6VCdLa+RD6CVq bCtxm0r0ogYjxDel5Tqlg3jpI2wmGEe7D1NoubErsuYZclLiJyemk4ZsgWrFdldOnpUc dn7A== X-Forwarded-Encrypted: i=1; AJvYcCXXEPpKZ2dKGR2ZjUhW0vXVNuT4V5TmlhyyZkJgxrzw2Mey0DC0V4H4P55EHqt8fE0J0IM009RXtLZybZhpp9Set0fkzbrkpaT61s/B3oooVo+d X-Gm-Message-State: AOJu0YwzNO6CYt2K4irhWv+auSlKbE1AzHvWg9P2iYafc7apXwpomLvO oaUe/bMiuQkAAFl+WpQKK6ezl2n1fhk5OAkKINnlSE4Lyx1V6YT+c8ZKqkkfoMofMDIOaSjk7uV v/+VMlUwexXUTXHjB36ZQ89+WPks= X-Google-Smtp-Source: AGHT+IFFTtcBl2WGafk5GcH/tKJahHtQtE3FWwclBFdT5l3wxeEbpOMI4Fo2VKisiaA5ukht1zLDfPvYbqY478fQyAE= X-Received: by 2002:a0c:e351:0:b0:6a0:4134:cefa with SMTP id a17-20020a0ce351000000b006a04134cefamr4477242qvm.14.1713599953726; Sat, 20 Apr 2024 00:59:13 -0700 (PDT) MIME-Version: 1.0 References: <72c40850-1dc6-41a9-ba90-e19328999ea8@enterprisedb.com> In-Reply-To: From: kaido vaikla Date: Sat, 20 Apr 2024 10:59:02 +0300 Message-ID: Subject: Re: Performance degradation after upgrading from 9.5 to 14 To: Marcin Giedz Cc: Johnathan Tiamoh , Tomas Vondra , "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000877390061682947d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000877390061682947d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I'm not sure, does it helps you but read this: https://www.cybertec-postgresql.com/en/b-tree-index-improvements-in-postgre= sql-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>=3D12 if pg_upgra= de 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 =3D 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=E2=80=AFPM 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 >>> >> --000000000000877390061682947d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I'm not sure, does it helps you but read this:
https://www.cybertec-postgresql.com/en/b-tree-index-improveme= nts-in-postgresql-v12/
"Since upgr= ading with pg_upgrade does not change the data files, indexes will still be= in version 3 after an upgrade"

I reindexed all my database, wh= en did upgrade pg<12 -> pg>=3D12 if pg_upgrade was a tool. exp-imp= for upgrade does not need reindex.
br
Kaido


On We= d, 17 Apr 2024 at 20:39, Marcin Giedz <marcin.giedz@arise.pl> wrote:
how about this:

jit =3D off ?

Marcin


On Wed, 17 Apr 2024 at 19:33, Johnathan Tiamoh <johnathantiamoh@gmail.com> w= rote:
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 transf= er the configuration, or did you just create a new
cluster with the defa= ult values?

I transfer=C2=A0the configuration
<= br>4) What exactly is slower? Queries? Inserts?

qu= eries

5) Can you quantify the impact? Is it 2x slower? 100x s= lower?

it's more than 5 times slower than = before. Very high load averages=C2=A0

On Wed, Apr 17, 2024 at 1:25=E2= =80=AFPM Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:
On 4/17/24 19:13, Johnathan = Tiamoh wrote:
> Hello,
>
>
> I performed an=C2=A0 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 o= f
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
--000000000000877390061682947d--