MIME-Version: 1.0
References: 
 <CAJk5AtZvkEdQsGLOEBAXuFv085End7H2T7SGkaEfTO1+QEARPw@mail.gmail.com>
 <2fd0442020d5b23609873f999b7d374875875689.camel@cybertec.at>
 <CAJk5AtZ_7nKTLpVvduJCeVD=qMD=A9nVia=UmCWGo6JkbRMoRQ@mail.gmail.com>
 <efb70f15e61eaa95bcfa6039f6dd086481fc4bdb.camel@cybertec.at>
 <CAJk5AtZ1-N4hs=6D1NtnNNt116e9kjEhpFdakrkJUJtgaL_xKQ@mail.gmail.com>
In-Reply-To: 
 <CAJk5AtZ1-N4hs=6D1NtnNNt116e9kjEhpFdakrkJUJtgaL_xKQ@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Mon, 13 Apr 2026 15:04:54 -0400
Message-ID: 
 <CANzqJaA8GpURz3fAKhnpXLQvOguhwBAyacvCK1-wdkFGP0Nh0w@mail.gmail.com>
Subject: Re: Slowness
To: Raj <rajeshkumar.dba09@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>,
	Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000025d1e4064f5c296a"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaA8GpURz3fAKhnpXLQvOguhwBAyacvCK1-wdkFGP0Nh0w%40mail.gmail.com>
Precedence: bulk

--00000000000025d1e4064f5c296a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

I'd:
1. enable log_min_duration_statement,
2. capture pg_stat_user_indexes,
3. tune autovacuum vacuum and analyze threshold values (the defaults are in
my experience way too high),
4. query pg_stat_user_tables joined to pg_class to see which tables need
more manual vacuuming and/or analyzing, and
5. check effective_cache_size, shared_buffers, work_mem and
maintenance_work_mem to see if they're set to Best Practice values.

On Mon, Apr 13, 2026 at 1:44=E2=80=AFPM Raj <rajeshkumar.dba09@gmail.com> w=
rote:

> Ok great. If I start with normal health check top, free -h, patronictl
> list, and all status of components such as etcd, haproxy etcd pgnouncer ,
> then stat user tables, pgstatactivity, pgstatstatements and error log.
> Apart from this, what dba should do?
>
> On Mon, 13 Apr 2026, 22:45 Laurenz Albe, <laurenz.albe@cybertec.at> wrote=
:
>
>> On Mon, 2026-04-13 at 22:05 +0530, Raj wrote:
>> > On Mon, 13 Apr 2026, 18:32 Laurenz Albe, <laurenz.albe@cybertec.at>
>> wrote:
>> > > On Mon, 2026-04-13 at 18:25 +0530, Raj wrote:
>> > > > When customer says they are facing slowness, what all wee need to
>> check in
>> > > > postgres db with 3 node patroni set up (sync between 1 and 2) -
>> async with dr.
>> > > >
>> > > > We recently migrated from oracle to postgres..vacuum analyze is
>> done.
>> > > >
>> > > > How to check this during the time slowness faced and also after
>> couple of hrs of issue window.
>> > > >
>> > > > Should we start with pgstatstatements and logs or how is it.
>> Help.me hight level what all I need to check
>> > >
>> > > You have to figure out *what exactly* is slow.  The customer has to
>> tell you which
>> > > statements are slow. The parameter "log_min_duratoin_statement" migh=
t
>> help.
>> > >
>> > > Then you have to tune those statements.
>> >
>> > How long min duration statement is decided. Is it dba who decide how
>> much needs to be set?
>>
>> Sorry, I made a typo.  It is a database parameter and called
>> "log_min_duration_statement".
>>
>> Your questions seem to indicate that you have almost no knowledge about
>> PostgreSQL.
>> Without database knowledge, it is impossible to find slow statements, le=
t
>> alone tune
>> them.  Perhaps you should spend some time with the PostgreSQL
>> documentation or hire
>> a consultant.
>>
>> Yours,
>> Laurenz Albe
>>
>

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--00000000000025d1e4064f5c296a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">I&#39;d:</div><div dir=3D"ltr">1. enable =
log_min_duration_statement,=C2=A0</div><div dir=3D"ltr">2. capture=C2=A0pg_=
stat_user_indexes,=C2=A0</div><div>3. tune autovacuum vacuum and analyze th=
reshold values (the defaults=C2=A0are in my experience way too high),=C2=A0=
</div><div>4. query=C2=A0pg_stat_user_tables joined to pg_class to see whic=
h tables need more manual vacuuming and/or analyzing, and</div><div>5. chec=
k effective_cache_size, shared_buffers, work_mem and maintenance_work_mem t=
o see if they&#39;re set to Best Practice values.</div><br><div class=3D"gm=
ail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On M=
on, Apr 13, 2026 at 1:44=E2=80=AFPM Raj &lt;<a href=3D"mailto:rajeshkumar.d=
ba09@gmail.com">rajeshkumar.dba09@gmail.com</a>&gt; wrote:<br></div><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex"><div dir=3D"auto"><div>Ok great.=
 If I start with normal health check top, free -h, patronictl list, and all=
 status of components such as etcd, haproxy etcd pgnouncer , then stat user=
 tables, pgstatactivity, pgstatstatements and error log. Apart from this, w=
hat dba should do?<br><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=
=3D"gmail_attr">On Mon, 13 Apr 2026, 22:45 Laurenz Albe, &lt;<a href=3D"mai=
lto:laurenz.albe@cybertec.at" target=3D"_blank">laurenz.albe@cybertec.at</a=
>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px=
 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On =
Mon, 2026-04-13 at 22:05 +0530, Raj wrote:<br>
&gt; On Mon, 13 Apr 2026, 18:32 Laurenz Albe, &lt;<a href=3D"mailto:laurenz=
.albe@cybertec.at" rel=3D"noreferrer" target=3D"_blank">laurenz.albe@cybert=
ec.at</a>&gt; wrote:<br>
&gt; &gt; On Mon, 2026-04-13 at 18:25 +0530, Raj wrote:<br>
&gt; &gt; &gt; When customer says they are facing slowness, what all wee ne=
ed to check in<br>
&gt; &gt; &gt; postgres db with 3 node patroni set up (sync between 1 and 2=
) - async with dr.<br>
&gt; &gt; &gt; <br>
&gt; &gt; &gt; We recently migrated from oracle to postgres..vacuum analyze=
 is done.<br>
&gt; &gt; &gt; <br>
&gt; &gt; &gt; How to check this during the time slowness faced and also af=
ter couple of hrs of issue window.<br>
&gt; &gt; &gt; <br>
&gt; &gt; &gt; Should we start with pgstatstatements and logs or how is it.=
 Help.me hight level what all I need to check<br>
&gt; &gt; <br>
&gt; &gt; You have to figure out *what exactly* is slow.=C2=A0 The customer=
 has to tell you which<br>
&gt; &gt; statements are slow. The parameter &quot;log_min_duratoin_stateme=
nt&quot; might help.<br>
&gt; &gt; <br>
&gt; &gt; Then you have to tune those statements.<br>
&gt;<br>
&gt; How long min duration statement is decided. Is it dba who decide how m=
uch needs to be set?<br>
<br>
Sorry, I made a typo.=C2=A0 It is a database parameter and called &quot;log=
_min_duration_statement&quot;.<br>
<br>
Your questions seem to indicate that you have almost no knowledge about Pos=
tgreSQL.<br>
Without database knowledge, it is impossible to find slow statements, let a=
lone tune<br>
them.=C2=A0 Perhaps you should spend some time with the PostgreSQL document=
ation or hire<br>
a consultant.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div></div></div>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div=
>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobs=
ter!</div></div></div></div></div></div>

--00000000000025d1e4064f5c296a--