From a9c70b46dbe152e094f137f7e6ba9cd3a638ee25 Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Sat, 11 Feb 2023 09:51:58 -0800 Subject: Add pg_stat_io view, providing more detailed IO statistics Builds on 28e626bde00 and f30d62c2fc6. See the former for motivation. Rows of the view show IO operations for a particular backend type, IO target object, IO context combination (e.g. a client backend's operations on permanent relations in shared buffers) and each column in the view is the total number of IO Operations done (e.g. writes). So a cell in the view would be, for example, the number of blocks of relation data written from shared buffers by client backends since the last stats reset. In anticipation of tracking WAL IO and non-block-oriented IO (such as temporary file IO), the "op_bytes" column specifies the unit of the "reads", "writes", and "extends" columns for a given row. Rows for combinations of IO operation, backend type, target object and context that never occur, are ommitted entirely. For example, checkpointer will never operate on temporary relations. Similarly, if an IO operation never occurs for such a combination, the IO operation's cell will be null, to distinguish from 0 observed IO operations. For example, bgwriter should not perform reads. Note that some of the cells in the view are redundant with fields in pg_stat_bgwriter (e.g. buffers_backend). For now, these have been kept for backwards compatibility. Bumps catversion. Author: Melanie Plageman Author: Samay Sharma Reviewed-by: Maciek Sakrejda Reviewed-by: Lukas Fittl Reviewed-by: Andres Freund Reviewed-by: Justin Pryzby Discussion: https://postgr.es/m/20200124195226.lth52iydq2n2uilq@alap3.anarazel.de --- doc/src/sgml/monitoring.sgml | 321 +++++++++++++++++++++++++++++++++++++++++-- 1 file changed, 307 insertions(+), 14 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index b246ddc6341..dca50707ad4 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -469,6 +469,16 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser + + pg_stat_iopg_stat_io + + One row for each combination of backend type, context, and target object + containing cluster-wide I/O statistics. + See + pg_stat_io for details. + + + pg_stat_replication_slotspg_stat_replication_slots One row per replication slot, showing statistics about the @@ -665,20 +675,16 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser - The pg_statio_ views are primarily useful to - determine the effectiveness of the buffer cache. When the number - of actual disk reads is much smaller than the number of buffer - hits, then the cache is satisfying most read requests without - invoking a kernel call. However, these statistics do not give the - entire story: due to the way in which PostgreSQL - handles disk I/O, data that is not in the - PostgreSQL buffer cache might still reside in the - kernel's I/O cache, and might therefore still be fetched without - requiring a physical read. Users interested in obtaining more - detailed information on PostgreSQL I/O behavior are - advised to use the PostgreSQL statistics views - in combination with operating system utilities that allow insight - into the kernel's handling of I/O. + The pg_stat_io and + pg_statio_ set of views are useful for determining + the effectiveness of the buffer cache. They can be used to calculate a cache + hit ratio. Note that while PostgreSQL's I/O + statistics capture most instances in which the kernel was invoked in order + to perform I/O, they do not differentiate between data which had to be + fetched from disk and that which already resided in the kernel page cache. + Users are advised to use the PostgreSQL + statistics views in combination with operating system utilities for a more + complete picture of their database's I/O performance. @@ -3669,6 +3675,293 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i last_archived_wal have also been successfully archived. + + + + <structname>pg_stat_io</structname> + + + pg_stat_io + + + + The pg_stat_io view will contain one row for each + combination of backend type, target I/O object, and I/O context, showing + cluster-wide I/O statistics. Combinations which do not make sense are + omitted. + + + + Currently, I/O on relations (e.g. tables, indexes) is tracked. However, + relation I/O which bypasses shared buffers (e.g. when moving a table from one + tablespace to another) is currently not tracked. + + + + <structname>pg_stat_io</structname> View + + + + + + Column Type + + + Description + + + + + + + + + backend_type text + + + Type of backend (e.g. background worker, autovacuum worker). See + pg_stat_activity for more information + on backend_types. Some + backend_types do not accumulate I/O operation + statistics and will not be included in the view. + + + + + + + + io_object text + + + Target object of an I/O operation. Possible values are: + + + + relation: Permanent relations. + + + + + temp relation: Temporary relations. + + + + + + + + + + + io_context text + + + The context of an I/O operation. Possible values are: + + + + + normal: The default or standard + io_context for a type of I/O operation. For + example, by default, relation data is read into and written out from + shared buffers. Thus, reads and writes of relation data to and from + shared buffers are tracked in io_context + normal. + + + + + vacuum: I/O operations performed outside of shared + buffers while vacuuming and analyzing permanent relations. Temporary + table vacuums use the same local buffer pool as other temporary table + IO operations and are tracked in io_context + normal. + + + + + bulkread: Certain large read I/O operations + done outside of shared buffers, for example, a sequential scan of a + large table. + + + + + bulkwrite: Certain large write I/O operations + done outside of shared buffers, such as COPY. + + + + + + + + + + reads bigint + + + Number of read operations, each of the size specified in + op_bytes. + + + + + + + + writes bigint + + + Number of write operations, each of the size specified in + op_bytes. + + + + + + + + extends bigint + + + Number of relation extend operations, each of the size specified in + op_bytes. + + + + + + + + op_bytes bigint + + + The number of bytes per unit of I/O read, written, or extended. + + + Relation data reads, writes, and extends are done in + block_size units, derived from the build-time + parameter BLCKSZ, which is 8192 by + default. + + + + + + + + evictions bigint + + + Number of times a block has been written out from a shared or local + buffer in order to make it available for another use. + + + In io_context normal, this counts + the number of times a block was evicted from a buffer and replaced with + another block. In io_contexts + bulkwrite, bulkread, and + vacuum, this counts the number of times a block was + evicted from shared buffers in order to add the shared buffer to a + separate, size-limited ring buffer for use in a bulk I/O operation. + + + + + + + + reuses bigint + + + The number of times an existing buffer in a size-limited ring buffer + outside of shared buffers was reused as part of an I/O operation in the + bulkread, bulkwrite, or + vacuum io_contexts. + + + + + + + + fsyncs bigint + + + Number of fsync calls. These are only tracked in + io_context normal. + + + + + + + + stats_reset timestamp with time zone + + + Time at which these statistics were last reset. + + + + + +
+ + + Some backend types never perform I/O operations on some I/O objects and/or + in some I/O contexts. These rows are omitted from the view. For example, the + checkpointer does not checkpoint temporary tables, so there will be no rows + for backend_type checkpointer and + io_object temp relation. + + + + In addition, some I/O operations will never be performed either by certain + backend types or on certain I/O objects and/or in certain I/O contexts. + These cells will be NULL. For example, temporary tables are not + fsynced, so fsyncs will be NULL for + io_object temp relation. Also, the + background writer does not perform reads, so reads will + be NULL in rows for backend_type background + writer. + + + + pg_stat_io can be used to inform database tuning. + For example: + + + + A high evictions count can indicate that shared + buffers should be increased. + + + + + Client backends rely on the checkpointer to ensure data is persisted to + permanent storage. Large numbers of fsyncs by + client backends could indicate a misconfiguration of + shared buffers or of the checkpointer. More information on configuring + the checkpointer can be found in . + + + + + Normally, client backends should be able to rely on auxiliary processes + like the checkpointer and the background writer to write out dirty data + as much as possible. Large numbers of writes by client backends could + indicate a misconfiguration of shared buffers or of the checkpointer. + More information on configuring the checkpointer can be found in . + + + + + -- cgit v1.2.3