Date: Wed, 20 Nov 2024 13:21:34 +0000 (UTC)
From: "Efrain J. Berdecia" <ejberdecia@yahoo.com>
To: Sreejith P <sreejith@lifetrenz.com>, Daniel Gustafsson <daniel@yesql.se>
Cc: 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Message-ID: <641572272.759924.1732108894204@mail.yahoo.com>
In-Reply-To: <B63DBDEA-950A-4CE3-85C4-7FBF6385FB6A@yesql.se>
References: <CAOW_W8PZ7Op_vOT9szMnpZE4jOmP09NWfc3M1ORTFMzjebpK5w@mail.gmail.com> <B63DBDEA-950A-4CE3-85C4-7FBF6385FB6A@yesql.se>
Subject: Re: Suddenly all queries moved to seq scan
MIME-Version: 1.0
Content-Type: multipart/alternative; 
	boundary="----=_Part_759923_1608389794.1732108894203"
Content-Length: 5136
Archived-At: <https://www.postgresql.org/message-id/641572272.759924.1732108894204%40mail.yahoo.com>
Precedence: bulk

------=_Part_759923_1608389794.1732108894203
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Make sure to run analyze on the entire database, possibly using vacuumdb wo=
uld be faster.
Also, check for invalid indexes.
Efrain J. Berdecia=20

    On Wednesday, November 20, 2024 at 08:02:36 AM EST, Daniel Gustafsson <=
daniel@yesql.se> wrote: =20
=20
 > On 20 Nov 2024, at 11:50, Sreejith P <sreejith@lifetrenz.com> wrote:

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

PostgreSQL 10 is well out of support and does not receive bugfixes or secur=
ity
fixes, you should plan a migration to a supported version sooner rather tha=
n
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 t=
aking too much time.=C2=A0 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 e=
rror log or fatal errors.=C2=A0 During the incident, in=C2=A0 one of the re=
ad server disks was full. We couldn't see any replication lag or query canc=
ellation due to replication.

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

--
Daniel Gustafsson


 =20
------=_Part_759923_1608389794.1732108894203
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html><head></head><body><div class=3D"ydpd1ad547fyahoo-style-wrap" style=
=3D"font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:13px=
;"><div><div dir=3D"ltr" data-setdir=3D"false">Make sure to run analyze on =
the entire database, possibly using vacuumdb would be faster.</div><div dir=
=3D"ltr" data-setdir=3D"false"><br></div><div dir=3D"ltr" data-setdir=3D"fa=
lse">Also, check for invalid indexes.</div><div><br></div><div class=3D"ydp=
d1ad547fsignature">Efrain J. Berdecia</div></div>
        <div><br></div><div><br></div>
       =20
        <div id=3D"ydpd1ad547fyahoo_quoted_2594163218" class=3D"ydpd1ad547f=
yahoo_quoted">
            <div style=3D"font-family:'Helvetica Neue', Helvetica, Arial, s=
ans-serif;font-size:13px;">
               =20
                <div>
                    On Wednesday, November 20, 2024 at 08:02:36 AM EST, Dan=
iel Gustafsson &lt;daniel@yesql.se&gt; wrote:
                </div>
                <div><br></div>
                <div><br></div>
                <div><div dir=3D"ltr">&gt; On 20 Nov 2024, at 11:50, Sreeji=
th P &lt;<a shape=3D"rect" href=3D"mailto:sreejith@lifetrenz.com" rel=3D"no=
follow" target=3D"_blank">sreejith@lifetrenz.com</a>&gt; wrote:<br clear=3D=
"none"><br clear=3D"none">&gt; We are using PostgresQL 10 in our production=
 database.&nbsp; We have around 890 req /s request on peak time.<br clear=
=3D"none"><br clear=3D"none">PostgreSQL 10 is well out of support and does =
not receive bugfixes or security<br clear=3D"none">fixes, you should plan a=
 migration to a supported version sooner rather than<br clear=3D"none">late=
r.<br clear=3D"none"><br clear=3D"none">&gt; 2 days back we applied some pa=
tches in the primary server and restarted. We didn't do anything on the sec=
ondary server.<br clear=3D"none"><br clear=3D"none">Patches to the operatin=
g system, postgres, another application?<div class=3D"ydpd1ad547fyqt1181161=
372" id=3D"ydpd1ad547fyqtfd51004"><br clear=3D"none"><br clear=3D"none">&gt=
; Next day, After 18 hours all our queries from secondary servers started t=
aking too much time.&nbsp; queries were working in 2 sec started taking 80 =
seconds. Almost all queries behaved the same way.<br clear=3D"none">&gt; <b=
r clear=3D"none">&gt; After half an hour of outage we restarted all db serv=
ers and system back to normal.<br clear=3D"none">&gt; <br clear=3D"none">&g=
t; Still we are not able to understand the root case. We couldn't find any =
error log or fatal errors.&nbsp; During the incident, in&nbsp; one of the r=
ead server disks was full. We couldn't see any replication lag or query can=
cellation due to replication.</div><br clear=3D"none"><br clear=3D"none">Yo=
u say that all queries started doing sequential scans, is that an assumptio=
n<br clear=3D"none">from queries being slow or did you capture plans for th=
e queries which be<br clear=3D"none">compared against "normal" production p=
lans?<br clear=3D"none"><br clear=3D"none">--<br clear=3D"none">Daniel Gust=
afsson<div class=3D"ydpd1ad547fyqt1181161372" id=3D"ydpd1ad547fyqtfd52967">=
<br clear=3D"none"><br clear=3D"none"><br clear=3D"none"><br clear=3D"none"=
></div></div></div>
            </div>
        </div></div></body></html>
------=_Part_759923_1608389794.1732108894203--