MIME-Version: 1.0
References: <CAB+=1TWE48wbtY8Sa1T2vpWHkaQ7wcodBnx8WNyTRB5D=7j1Mw@mail.gmail.com>
 <c54c42b5-c612-41f3-a436-d8944a692357@aklaver.com>
In-Reply-To: <c54c42b5-c612-41f3-a436-d8944a692357@aklaver.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Mon, 7 Oct 2024 09:23:08 -0400
Message-ID: <CAKAnmmJ=fAU3PgrOAg20a5nH+8g=BjB8NLTEX+Mpp8kUiiyhHg@mail.gmail.com>
Subject: Re: Question on pg_stat* views
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: veem v <veema0000@gmail.com>, pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000113c050623e2ee8c"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmJ%3DfAU3PgrOAg20a5nH%2B8g%3DBjB8NLTEX%2BMpp8kUiiyhHg%40mail.gmail.com>
Precedence: bulk

--000000000000113c050623e2ee8c
Content-Type: text/plain; charset="UTF-8"

Adrian and Veem were saying:

>  > so does it mean that we should increase the pg_stat_statement.max to
> further higher value?
>

Yes, you should raise this setting if you find your queries are getting
pushed out. Moving to version 17 will also help, as myself and others have
been working on normalizing more queries, which means less overall entries
in pg_stat_statements, and more room before hitting pg_stat_statements.max.
But bump it to 10000 for now.

Now what I don't know is if pg_stat_statement exhibits the same behavior
> as the core statistics and resets on an unclean shutdown.
>

It does - pg_stat_statements will be emptied out if Postgres restarts after
a crash.

> 3)As pg_stat_statements holds the aggregated stats of all the execution
> > for a particular sql query ,so it's not easy to identify if in the past
> > at some point in time the same query suffered and thus went for high
> > response time. So to debug such performance  issues scenarios , is it
> > advisable to insert the records from this pg_stat* views to another
> > table manually periodically through a cron job?
>

Typically, you use pg_stat_statements in conjunction with a good
log_min_duration_statements setting to allow you to see specific slow
queries as well as the aggregated info. It's also a good idea to rotate
things via cron, as you mention.

Cheers,
Greg

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

<div dir=3D"ltr"><div>Adrian and Veem=C2=A0were saying:</div><div class=3D"=
gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px =
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=C2=A0&gt; s=
o does it mean that we should increase the pg_stat_statement.max to further=
 higher value?<br></blockquote><div><br></div><div>Yes, you should raise th=
is setting if you find your queries are getting pushed out. Moving to versi=
on 17 will also help, as myself and others have been working on normalizing=
 more queries, which means less overall entries in pg_stat_statements, and =
more room before hitting pg_stat_statements.max. But bump it to 10000 for n=
ow.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"margin:0=
px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
Now what I don&#39;t know is if pg_stat_statement exhibits the same behavio=
r <br>
as the core statistics and resets on an unclean shutdown.<br></blockquote><=
div><br></div><div>It does - pg_stat_statements will be emptied out if Post=
gres restarts after a crash.</div><div><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">&gt; 3)As pg_stat_statements holds the aggregated s=
tats of all the execution <br>
&gt; for a particular sql query ,so it&#39;s not easy to identify if in the=
 past <br>
&gt; at some point in time the same query suffered and thus went for high <=
br>
&gt; response time. So to=C2=A0debug such performance=C2=A0 issues scenario=
s , is it <br>
&gt; advisable to insert the records from this pg_stat* views to another <b=
r>
&gt; table manually periodically through a cron job?<br></blockquote><div><=
br></div><div>Typically, you use pg_stat_statements in conjunction with a g=
ood log_min_duration_statements setting to allow you to see specific slow q=
ueries as well as the aggregated info. It&#39;s also a good idea to rotate =
things via cron, as you mention.</div><div><br></div><div>Cheers,</div><div=
>Greg</div><div><br></div></div></div>

--000000000000113c050623e2ee8c--