On 10/6/24 12:11, veem v wrote:
Hi,
As per my understanding , the pg_stat_statements stores the aggregated
statistics of the query execution history(with almost near real time
executions) and the max number of statements or queries it can store
depends on the pg_stat_statement.max value(which I am seeing as ~5000 in
pg_settings). I have below questions,
Information below comes from:
https://www.postgresql.org/docs/current/pgstatstatements.html#PGSTATSTATEMENTS-CONFIG-PARAMS
1)Doing a count(*) on pg_stat_statements giving ~4818. But still
pg_stat_statements_info showing ~1990 as "dealloc" which means there are
more sql queries coming up and they are getting flushed out of the
pg_stat_statements, so does it mean that we should increase the
pg_stat_statement.max to further higher value?
"dealloc bigint
Total number of times pg_stat_statements entries about the
least-executed statements were deallocated because more distinct
statements than pg_stat_statements.max were observed
"
So the number reflects past ejection of queries from the view to make
room for newer more distinct queries in order to keep the query count
<= pg_stat_statements.max.
2)The stats_reset column in pg_stat_statements_info view is showing as
16th august , so does it mean that, whatever query stats are getting
logged in the pg_stat_statements, those are the aggregated stats for all
the executions that have happened since the day 16th august till today?
Also as we have not done any "stats reset" manually , so does it happen
automatically when the DB instance restarts or with any other database
events?
"
pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint,
minmax_only boolean) returns timestamp with time zone
pg_stat_statements_reset discards statistics gathered so far by
pg_stat_statements corresponding to the specified userid, dbid and
queryid. If any of the parameters are not specified, the default value
0(invalid) is used for each of them and the statistics that match with
other parameters will be reset. If no parameter is specified or all the
specified parameters are 0(invalid), it will discard all statistics. If
all statistics in the pg_stat_statements view are discarded, it will
also reset the statistics in the pg_stat_statements_info view. When
minmax_only is true only the values of minimum and maximum planning and
execution time will be reset (i.e. min_plan_time, max_plan_time,
min_exec_time and max_exec_time fields). The default value for
minmax_only parameter is false. Time of last min/max reset performed is
shown in minmax_stats_since field of the pg_stat_statements view. This
function returns the time of a reset. This time is saved to stats_reset
field of pg_stat_statements_info view or to minmax_stats_since field of
the pg_stat_statements view if the corresponding reset was actually
performed. By default, this function can only be executed by superusers.
Access may be granted to others using GRANT.
"
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.
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?
Would that not be covered by?:
"
max_exec_time double precision
Maximum time spent executing the statement, in milliseconds, this field
will be zero until this statement is executed first time after reset
performed by the pg_stat_statements_reset function with the minmax_only
parameter set to true
"
Regards
Veem
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
