Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3818.100.11.1.3\))
Subject: Re: Suddenly all queries moved to seq scan
From: Sreejith P <sreejith@lifetrenz.com>
In-Reply-To: <B63DBDEA-950A-4CE3-85C4-7FBF6385FB6A@yesql.se>
Date: Wed, 20 Nov 2024 19:38:49 +0530
Cc: pgsql-general@lists.postgresql.org
Message-Id: <C1F257F9-4CFE-4CC8-A6BB-E66F150CEF8D@lifetrenz.com>
References: <CAOW_W8PZ7Op_vOT9szMnpZE4jOmP09NWfc3M1ORTFMzjebpK5w@mail.gmail.com>
 <B63DBDEA-950A-4CE3-85C4-7FBF6385FB6A@yesql.se>
To: Daniel Gustafsson <daniel@yesql.se>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/C1F257F9-4CFE-4CC8-A6BB-E66F150CEF8D%40lifetrenz.com>
Precedence: bulk


> On 20 Nov 2024, at 6:32=E2=80=AFPM, Daniel Gustafsson <daniel@yesql.se> w=
rote:
>=20
>> On 20 Nov 2024, at 11:50, Sreejith P <sreejith@lifetrenz.com> wrote:
>=20
>> We are using PostgresQL 10 in our production database.  We have around 8=
90 req /s request on peak time.
>=20
> PostgreSQL 10 is well out of support and does not receive bugfixes or sec=
urity
> fixes, you should plan a migration to a supported version sooner rather t=
han
> later.
>=20
>> 2 days back we applied some patches in the primary server and restarted.=
 We didn't do anything on the secondary server.
>=20
> Patches to the operating system, postgres, another application?=20
PostgreSQL Common 10.23-6=20
>=20
>> Next day, After 18 hours all our queries from secondary servers started =
taking too much time.  queries were working in 2 sec started taking 80 seco=
nds. Almost all queries behaved the same way.
>>=20
>> After half an hour of outage we restarted all db servers and system back=
 to normal.
>>=20
>> Still we are not able to understand the root case. We couldn't find any =
error log or fatal errors.  During the incident, in  one of the read server=
 disks was full. We couldn't see any replication lag or query cancellation =
due to replication.
>=20
> You say that all queries started doing sequential scans, is that an assum=
ption
> from queries being slow or did you capture plans for the queries which be
> compared against "normal" production plans?.

Queries were taking 20 ms started taking 60 seconds. So have done SQL analy=
se to understand about query plan. There we found that query planner taking=
 seq scan instead in index scan.

I would like to add one ore point.  A delete query were running in DB from =
2 days for deleting around 80 million records.=20
>=20
> --
> Daniel Gustafsson
>=20


--=20


=C2=A0

*Solutions for Care Anywhere*
*dWise HealthCare IT Solutions Pvt.=20
Ltd.* | www.lifetrenz.com <http://www.lifetrenz.com>
*Disclaimer*:
 The=20
information and attachments contained in this email are intended=20
for=20
exclusive use of the addressee(s) and may contain confidential or=20
privileged information. If you are not the intended recipient, please=20
notify the sender immediately and destroy all copies of this message and
=20
any attachments. The views expressed in this email are, unless=20
otherwise=20
stated, those of the author and not those of dWise HealthCare IT Solutions=
=20
or its management.