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

> On 20 Nov 2024, at 11:50, Sreejith P <sreejith@lifetrenz.com> wrote:

> We are using PostgresQL 10 in our production database.  We have around =
890 req /s request on peak time.

PostgreSQL 10 is well out of support and does not receive bugfixes or =
security
fixes, you should plan a migration to a supported version sooner rather =
than
later.

> 2 days back we applied some patches in the primary server and =
restarted. We didn't do anything on the secondary server.

Patches to the operating system, postgres, another application?

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

You say that all queries started doing sequential scans, is that an =
assumption
from queries being slow or did you capture plans for the queries which =
be
compared against "normal" production plans?

--
Daniel Gustafsson