Обсуждение: Slowness

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" might help.

Then you have to tune those statements.

Yours,
Laurenz Albe

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" might 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, let alone tune
them.  Perhaps you should spend some time with the PostgreSQL documentation or hire
a consultant.

Yours,
Laurenz Albe

On Mon, 2026-04-13 at 23:13 +0530, Raj wrote:
> 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?

I'm not sure what a "normal health check" is.

I thought your problem was that the some statements are too slow.
That is not directly connected to the health of the database.

To deal with slow statements, you first have to find them.
Tools are log_min_duration_statement and pg_stat_statements.

Then you have to make the statements faster.  The tool here is
EXPLAIN (ANALYZE, BUFFERS).  But you need some experience to be
able to read execution plans, figure out where the problem is
and fix that problem.

Yours,
Laurenz Albe