public inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedSlowness
9+ messages / 4 participants
[nested] [flat]
* Slowness
@ 2026-04-13 12:55 Raj <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Raj @ 2026-04-13 12:55 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Slowness
@ 2026-04-13 13:02 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Raj <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2026-04-13 13:02 UTC (permalink / raw)
To: Raj <rajeshkumar.dba09@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Slowness
@ 2026-04-13 16:35 Raj <rajeshkumar.dba09@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 9+ messages in thread
From: Raj @ 2026-04-13 16:35 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
How long min duration statement is decided. Is it dba who decide how much
needs to be set?
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.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Slowness
@ 2026-04-13 17:11 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Raj <rajeshkumar.dba09@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Ron Johnson @ 2026-04-13 17:11 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
"All the stakeholders" decide the threshold. For example, if *they*
complain about queries taking more than 2 seconds, you obviously shouldn't
set log_min_duratoin_statement to 5 seconds.
If they don't know, are too clueless to know, or it's a mixed OLTP &
reporting database, pick a reasonable value and go from there.
You can always change it later...
On Mon, Apr 13, 2026 at 12:36 PM Raj <rajeshkumar.dba09@gmail.com> wrote:
> How long min duration statement is decided. Is it dba who decide how much
> needs to be set?
>
> 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.
>>
>> Yours,
>> Laurenz Albe
>>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Slowness
@ 2026-04-13 17:15 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Raj <rajeshkumar.dba09@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2026-04-13 17:15 UTC (permalink / raw)
To: Raj <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Slowness
@ 2026-04-13 17:43 Raj <rajeshkumar.dba09@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 9+ messages in thread
From: Raj @ 2026-04-13 17:43 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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" 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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Slowness
@ 2026-04-13 19:04 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Raj <rajeshkumar.dba09@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Ron Johnson @ 2026-04-13 19:04 UTC (permalink / raw)
To: Raj <rajeshkumar.dba09@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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 PM Raj <rajeshkumar.dba09@gmail.com> wrote:
> 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" 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
>>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Slowness
@ 2026-04-14 05:33 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Raj <rajeshkumar.dba09@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2026-04-14 05:33 UTC (permalink / raw)
To: Raj <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Slowness
@ 2026-04-17 07:19 kaido vaikla <kaido.vaikla@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 9+ messages in thread
From: kaido vaikla @ 2026-04-17 07:19 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Raj <rajeshkumar.dba09@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
If "customer says they are facing slowness" then my three first steps
usually are
1) grep "ERROR:\|FATAL:" server.log
2) top. Maybe there is some unexpected process(es). Use a f, to sort by
mem, cpu etc
3) query:
select datname, usename, client_addr, leader_pid, pid, DATE_TRUNC('second',
query_start) query_start, wait_event_type, wait_event, query_id from
pg_stat_activity where xact_start is not NULL order by 6;
and
\watch
And take a look at wait_event_type, wait_event
It gives glance, what is currently going on.
br
Kaido
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-04-17 07:19 UTC | newest]
Thread overview: 9+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-13 12:55 Slowness Raj <rajeshkumar.dba09@gmail.com>
2026-04-13 13:02 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-04-13 16:35 ` Raj <rajeshkumar.dba09@gmail.com>
2026-04-13 17:11 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-04-13 17:15 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-04-13 17:43 ` Raj <rajeshkumar.dba09@gmail.com>
2026-04-13 19:04 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-04-14 05:33 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-04-17 07:19 ` kaido vaikla <kaido.vaikla@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox