public inbox for [email protected]help / color / mirror / Atom feed
Question on pg_stat* views 3+ messages / 3 participants [nested] [flat]
* Question on pg_stat* views @ 2024-10-06 19:11 veem v <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: veem v @ 2024-10-06 19:11 UTC (permalink / raw) To: pgsql-general <[email protected]> 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, 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? 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? 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? Regards Veem ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question on pg_stat* views @ 2024-10-06 20:27 Adrian Klaver <[email protected]> parent: veem v <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Adrian Klaver @ 2024-10-06 20:27 UTC (permalink / raw) To: veem v <[email protected]>; pgsql-general <[email protected]> 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 [email protected] ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question on pg_stat* views @ 2024-10-07 13:23 Greg Sabino Mullane <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Greg Sabino Mullane @ 2024-10-07 13:23 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: veem v <[email protected]>; pgsql-general <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-10-07 13:23 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-10-06 19:11 Question on pg_stat* views veem v <[email protected]> 2024-10-06 20:27 ` Adrian Klaver <[email protected]> 2024-10-07 13:23 ` Greg Sabino Mullane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox