In the last post we discussed about checkpoint process, in this tutorial we will discuss about PostgreSQL: stats collector
PostgreSQL: stats collector
The stats collector process collects statistics about the database. It’s an optional process with the default value as on.
The process keeps track of access to tables and indexes in both disk-block and individual row-terms.
It also keeps track of record counts for tables, and tracks the vacuum and analyze actions.
The data collected is stored in a set of tables and we can access this via a number of views provided.
The views start with pg_stat.
\d pg_stat
Hitting the Tab key twice will list all the views, as shown in the following command:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
postgres=# \d pg_stat pg_stat_activity pg_statio_sys_sequences pg_stat_sys_indexes pg_stat_all_indexes pg_statio_sys_tables pg_stat_sys_tables pg_stat_all_tables pg_statio_user_indexes pg_stat_user_functions pg_stat_archiver pg_statio_user_sequences pg_stat_user_indexes pg_stat_bgwriter pg_statio_user_tables pg_stat_user_tables pg_stat_database pg_statistic pg_stat_wal_receiver pg_stat_database_conflicts pg_statistic_relid_att_inh_index pg_stat_xact_all_tables pg_statio_all_indexes pg_stat_progress_vacuum pg_stat_xact_sys_tables pg_statio_all_sequences pg_stat_replication pg_stat_xact_user_functions pg_statio_all_tables pg_stats pg_stat_xact_user_tables pg_statio_sys_indexes pg_stat_ssl |
The stats collector collects the information based on the below parameters set in postgresql.conf file
track_activities: This parameter enables monitoring of the current command being executed by any server process.
track_counts: This parameter controls whether statistics are collected about table and index accesses.
track_functions: This parameter enables tracking of usage of user-defined functions.
track_io_timing: This parameter enables monitoring of block read and write times.
The statistics collector transmits the collected information to other PostgreSQL processes through temporary files. These files are stored in the directory named by the stats_temp_directory parameter, pg_stat_tmp by default.
For better performance, stats_temp_directory can be pointed at a RAM-based file system, decreasing physical I/O requirements.
When the server shuts down cleanly, a permanent copy of the statistics data is stored in the pg_stat sub directory, so that statistics can be retained across server restarts. When recovery is performed at server start (e.g. after immediate shutdown, server crash, and point-in-time recovery), all statistics counters are reset.
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.
