Groups > comp.databases.postgresql > #897

Re: Postgresql database behaving unpredictably

From	George Neuner <gneuner2@comcast.net>
Newsgroups	comp.databases.postgresql
Subject	Re: Postgresql database behaving unpredictably
Date	2021-01-21 14:06 -0500
Organization	A noiseless patient Spider
Message-ID	<icij0gpg97qt2ka2lnkb13evoajnd0ofrm@4ax.com> (permalink)
References	<3d4228fc-3e2c-4dfb-b0c5-28775615ec9fn@googlegroups.com> <kl240g1u861jn9ntuotetdfac809g2nhpl@4ax.com> <ruav47$13s8$1@gioia.aioe.org>

Show all headers | View raw

On Thu, 21 Jan 2021 04:15:03 +0000 (UTC), Mladen Gogala
<mgogala@yahoo.com> wrote:

>On Fri, 15 Jan 2021 16:53:12 -0500, George Neuner wrote:
>
>> However, sudden drastic performance drop smells a lot like imminent disk
>> failure.
>
>Huh? I am an Oracle DBA with some experience in Postgres and have 
>experienced many performance drops after a version upgrade and precisely 
>0 of those performance drops were caused by "imminent disk failure".

The OP complained of major performance loss *BEFORE* upgrading, and
claimed better performance following the upgrade (which likely was due
to the new PG version having parallel query turned on by default).

It is (fairly) well known that PG neither transfers existing index
data, nor builds indexes during an upgrade.  The indexes are created
during the upgrade and marked to be rebuilt incrementally, but for
best performance immediately following the upgrade, the DBA must
rebuild the indexes manually.

>I would check whether the plans have changed, what is the difference in 
>parameters, check where the time is spent by using perf or strace and 
>check the logs. If the plans have changed, try installing pg_hint_plan 
>extension and force the desired plans. Then compare.

Assuming no query or configuration changes, "under 10 seconds" to "4
plus minutes" is a huge difference to be explained by a plan change.
Yes, it can happen ... but it would have to be the result of either
corrupted indexes or large changes to the table data.

The OP did not mention large changes to the table, and I did mention
that the indexes might have been corrupted.

George

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Find similar

Thread

Postgresql database behaving unpredictably shariqtariq <shariqatariq@gmail.com> - 2021-01-14 17:08 -0800
  Re: Postgresql database behaving unpredictably George Neuner <gneuner2@comcast.net> - 2021-01-15 16:53 -0500
    Re: Postgresql database behaving unpredictably Mladen Gogala <mgogala@yahoo.com> - 2021-01-21 04:15 +0000
      Re: Postgresql database behaving unpredictably George Neuner <gneuner2@comcast.net> - 2021-01-21 14:06 -0500

csiph-web