One row for each table in the current database, showing statistics about accesses to that specific table. See pg_stat_database_conflicts for details. One row per database, showing database-wide statistics about query cancels due to conflict with recovery on standby servers. One row per database, showing database-wide statistics. One row only, showing statistics about the background writer process's activity. One row only, showing statistics about the WAL archiver process's activity.
#POSTGRES LOCK QUEUE UPDATE#
These numbers do not act as stated above instead they update continuously throughout the transaction. The next use of statistical information will cause a new snapshot to be fetched.Ī transaction can also see its own statistics (as yet untransmitted to the collector) in the views pg_stat_xact_all_tables, pg_stat_xact_sys_tables, pg_stat_xact_user_tables, and pg_stat_xact_user_functions. Alternatively, you can invoke pg_stat_clear_snapshot(), which will discard the current transaction's statistics snapshot (if any). But if you want to see new results with each query, be sure to do the queries outside any transaction block. This is a feature, not a bug, because it allows you to perform several queries on the statistics and correlate the results without worrying that the numbers are changing underneath you. Similarly, information about the current queries of all sessions is collected when any such information is first requested within a transaction, and the same information will be displayed throughout the transaction. So the statistics will show static information as long as you continue the current transaction. However, current-query information collected by track_activities is always up-to-date.Īnother important point is that when a server process is asked to display any of these statistics, it first fetches the most recent report emitted by the collector process and then continues to use this snapshot for all statistical views and functions until the end of its current transaction. So the displayed information lags behind actual activity. Also, the collector itself emits a new report at most once per PGSTAT_STAT_INTERVAL milliseconds (500 ms unless altered while building the server). Each individual server process transmits new statistical counts to the collector just before going idle so a query or transaction still in progress does not affect the displayed totals. When using the statistics to monitor collected data, it is important to realize that the information does not update instantaneously. Alternatively, one can build custom views using the underlying statistics functions, as discussed in Section 28.2.3. There are also several other views, listed in Table 28.2, available to show the results of statistics collection. Several predefined views, listed in Table 28.1, are available to show the current state of the system. When recovery is performed at server start (e.g., after immediate shutdown, server crash, and point-in-time recovery), all statistics counters are reset.
When the server shuts down cleanly, a permanent copy of the statistics data is stored in the pg_stat subdirectory, so that statistics can be retained across server restarts. For better performance, stats_temp_directory can be pointed at a RAM-based file system, decreasing physical I/O requirements. These files are stored in the directory named by the stats_temp_directory parameter, pg_stat_tmp by default. The statistics collector transmits the collected information to other PostgreSQL processes through temporary files. (To prevent ordinary users from hiding their activity from the administrator, only superusers are allowed to change these parameters with SET.) Normally these parameters are set in nf so that they apply to all server processes, but it is possible to turn them on or off in individual sessions using the SET command. The parameter track_io_timing enables monitoring of block read and write times. The parameter track_functions enables tracking of usage of user-defined functions. The parameter track_counts controls whether statistics are collected about table and index accesses. The parameter track_activities enables monitoring of the current command being executed by any server process.
(See Chapter 19 for details about setting configuration parameters.) This is controlled by configuration parameters that are normally set in nf. Since collection of statistics adds some overhead to query execution, the system can be configured to collect or not collect information. 28.2.1. Statistics Collection Configuration
0 kommentar(er)
