I'm proposing that we add pg_stat_io_histogram that would track/show I/O
latencies profile, so we could quickly identify I/O outliers. From time to
time users complain that 'PostgreSQL is slow or stuck' (usually COMMIT is
slow), when it is quite apparent that it is down to somewhere in the I/O
stack. It is quite easy to prove once one has proper measurement tools in
place and is able to correlate, but it takes IMHO way too much time and
energy to cross-correlate all of that information (iostat -x 1s,
wait events 1s, and so on), especially if one would like to provide rapid
response.
Right now the patch does not include per-backend/PID tracking, hopefully if
there will be interest in this, I'll add it, but I would like to first hear
if that's a good idea. The current implementation uses fast bucket calculation
to avoid overheads and tries to cover most useful range of devices via buckets
(128us..256ms, so that covers both NVMe/SSD/HDD and abnormally high latency
too as from time to time I'm try to help with I/O stuck for *seconds*,
usually a sign
of some I/O multipath issues, device resetting, or hypervisor woes).
postgres=# select
substring(backend_type,1,8) as backend,object,context,io_type,
bucket_latency_us as lat_us,
round(bucket_latency_us/1000.0, 3) as lat_ms,
bucket_count as count
from pg_stat_get_io_histogram()
where
bucket_count > 0
order by 1,2,3,4,5;
backend | object | context | io_type | lat_us | lat_ms | count
----------+----------+-----------+-----------+--------+--------+-------
autovacu | relation | normal | read | 128 | 0.128 | 54
autovacu | relation | normal | read | 256 | 0.256 | 7
autovacu | relation | normal | read | 512 | 0.512 | 1
autovacu | relation | vacuum | read | 128 | 0.128 | 8
autovacu | relation | vacuum | read | 256 | 0.256 | 5
backgrou | relation | bulkread | read | 128 | 0.128 | 658
backgrou | relation | normal | read | 128 | 0.128 | 5
checkpoi | relation | normal | fsync | 2048 | 2.048 | 37
checkpoi | relation | normal | fsync | 4096 | 4.096 | 7
checkpoi | relation | normal | fsync | 16384 | 16.384 | 4
checkpoi | relation | normal | fsync | 32768 | 32.768 | 1
checkpoi | relation | normal | fsync | 65536 | 65.536 | 1
checkpoi | relation | normal | write | 128 | 0.128 | 2059
checkpoi | relation | normal | write | 256 | 0.256 | 2
checkpoi | relation | normal | write | 512 | 0.512 | 1
checkpoi | relation | normal | writeback | 128 | 0.128 | 64
checkpoi | relation | normal | writeback | 256 | 0.256 | 1
client b | relation | bulkread | read | 128 | 0.128 | 675
client b | relation | bulkread | read | 256 | 0.256 | 1
client b | relation | bulkwrite | extend | 128 | 0.128 | 260
client b | relation | bulkwrite | extend | 512 | 0.512 | 1
client b | relation | bulkwrite | write | 128 | 0.128 | 14404
client b | relation | normal | extend | 128 | 0.128 | 6
client b | relation | normal | read | 128 | 0.128 | 273
client b | relation | normal | read | 256 | 0.256 | 6
client b | relation | vacuum | read | 128 | 0.128 | 907
client b | relation | vacuum | read | 256 | 0.256 | 3
client b | relation | vacuum | read | 512 | 0.512 | 2
Of course most of the I/O calls today are hitting page cache, so one would
expect they'll be < 128us most of the time, but above you can see here degraded
fsync/fdatasync as well (BTW that was achieved via device mapper
delayed device). My hope that above would help tremendously when dealing
with flaky storage, or I/O path issues, or even hypervisors being paused.
Alternative idea I was having would be simply to add logging of slow I/O
outliers, but meh.. then one would to answer all those questions:
what should be the threshold (=>guc?), risk of spamming the log and so on
(and I wouldn't be fond of proposing yet another log_* GUC ;))
Any hints, co-authors, or help are more than welcome!
-J.
From bc81b4e1fff22e598df59258bc5b4bc1f97031c4 Mon Sep 17 00:00:00 2001
From: Jakub Wartak <[email protected]>
Date: Fri, 23 Jan 2026 08:10:09 +0100
Subject: [PATCH v1] Add pg_stat_io_histogram view to provide more detailed
insight into IO profile
pg_stat_io_histogram displays a histogram of IO latencies for specific
backend_type, object, context and io_type. The histogram has buckets that allow
faster identification of I/O latency outliers due to faulty hardware and/or
misbehaving I/O stack. Such I/O outliers e.g. slow fsyncs could sometimes
cause intermittent issues e.g. for COMMIT or affect the synchronous standbys
performance.
Author: Jakub Wartak <[email protected]>
Reviewed-by:
Discussion: https://postgr.es/m/XXX
---
doc/src/sgml/config.sgml | 12 +-
doc/src/sgml/monitoring.sgml | 257 ++++++++++++++++++++++++-
doc/src/sgml/wal.sgml | 5 +-
src/backend/catalog/system_views.sql | 11 ++
src/backend/utils/activity/pgstat_io. | 0
src/backend/utils/activity/pgstat_io.c | 72 +++++++
src/backend/utils/adt/pgstatfuncs.c | 123 ++++++++++++
src/include/catalog/pg_proc.dat | 9 +
src/include/pgstat.h | 14 ++
src/include/port/pg_bitutils.h | 27 +++
src/test/regress/expected/rules.out | 8 +
src/test/regress/expected/stats.out | 23 +++
src/test/regress/sql/stats.sql | 15 ++
13 files changed, 569 insertions(+), 7 deletions(-)
create mode 100644 src/backend/utils/activity/pgstat_io.
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 5560b95ee60..614af387ee5 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8729,9 +8729,11 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
displayed in <link linkend="monitoring-pg-stat-database-view">
<structname>pg_stat_database</structname></link>,
<link linkend="monitoring-pg-stat-io-view">
- <structname>pg_stat_io</structname></link> (if <varname>object</varname>
- is not <literal>wal</literal>), in the output of the
- <link linkend="pg-stat-get-backend-io">
+ <structname>pg_stat_io</structname></link> and
+ <link linkend="monitoring-pg-stat-io-histogram-view">
+ <structname>pg_stat_io_histogram</structname></link>
+ (if <varname>object</varname> is not <literal>wal</literal>),
+ in the output of the <link linkend="pg-stat-get-backend-io">
<function>pg_stat_get_backend_io()</function></link> function (if
<varname>object</varname> is not <literal>wal</literal>), in the
output of <xref linkend="sql-explain"/> when the <literal>BUFFERS</literal>
@@ -8761,7 +8763,9 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
measure the overhead of timing on your system.
I/O timing information is displayed in
<link linkend="monitoring-pg-stat-io-view">
- <structname>pg_stat_io</structname></link> for the
+ <structname>pg_stat_io</structname></link> and
+ <link linkend="monitoring-pg-stat-io-histogram-view">
+ <structname>pg_stat_io_histogram</structname></link> for the
<varname>object</varname> <literal>wal</literal> and in the output of
the <link linkend="pg-stat-get-backend-io">
<function>pg_stat_get_backend_io()</function></link> function for the
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 88450facebd..21acedbd394 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -493,6 +493,17 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</entry>
</row>
+ <row>
+ <entry><structname>pg_stat_io_histogram</structname><indexterm><primary>pg_stat_io_histogram</primary></indexterm></entry>
+ <entry>
+ One row for each combination of backend type, context, target object,
+ IO operation type and latency bucket (in microseconds) containing
+ cluster-wide I/O statistics.
+ See <link linkend="monitoring-pg-stat-io-histogram-view">
+ <structname>pg_stat_io_histogram</structname></link> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_replication_slots</structname><indexterm><primary>pg_stat_replication_slots</primary></indexterm></entry>
<entry>One row per replication slot, showing statistics about the
@@ -690,7 +701,7 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
<para>
The <structname>pg_stat_io</structname> and
- <structname>pg_statio_</structname> set of views are useful for determining
+ <structname>pg_statio_histogram</structname> 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 <productname>PostgreSQL</productname>'s I/O
statistics capture most instances in which the kernel was invoked in order
@@ -699,6 +710,8 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
Users are advised to use the <productname>PostgreSQL</productname>
statistics views in combination with operating system utilities for a more
complete picture of their database's I/O performance.
+ Furthermore the <structname>pg_stat_io_histogram</structname> view can be helpful
+ identifing latency outliers for specific I/O operations.
</para>
</sect2>
@@ -3118,6 +3131,248 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</sect2>
+ <sect2 id="monitoring-pg-stat-io-histogram-view">
+ <title><structname>pg_stat_io</structname></title>
+
+ <indexterm>
+ <primary>pg_stat_io_histogram</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_io_histogram</structname> view will contain one row for each
+ combination of backend type, target I/O object, and I/O context, IO operation
+ type, bucket latency cluster-wide I/O statistics. Combinations which do not make sense
+ are omitted.
+ </para>
+
+ <para>
+ Currently, I/O on relations (e.g. tables, indexes) and WAL activity are
+ tracked. However, relation I/O which bypasses shared buffers
+ (e.g. when moving a table from one tablespace to another) is currently
+ not tracked.
+ </para>
+
+ <table id="pg-stat-io-histogram-view" xreflabel="pg_stat_io">
+ <title><structname>pg_stat_io_histogram</structname> View</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para>
+ </entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>backend_type</structfield> <type>text</type>
+ </para>
+ <para>
+ Type of backend (e.g. background worker, autovacuum worker). See <link
+ linkend="monitoring-pg-stat-activity-view">
+ <structname>pg_stat_activity</structname></link> for more information
+ on <varname>backend_type</varname>s. Some
+ <varname>backend_type</varname>s do not accumulate I/O operation
+ statistics and will not be included in the view.
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>object</structfield> <type>text</type>
+ </para>
+ <para>
+ Target object of an I/O operation. Possible values are:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>relation</literal>: Permanent relations.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>temp relation</literal>: Temporary relations.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>wal</literal>: Write Ahead Logs.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>context</structfield> <type>text</type>
+ </para>
+ <para>
+ The context of an I/O operation. Possible values are:
+ </para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>normal</literal>: The default or standard
+ <varname>context</varname> 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 <varname>context</varname>
+ <literal>normal</literal>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>init</literal>: I/O operations performed while creating the
+ WAL segments are tracked in <varname>context</varname>
+ <literal>init</literal>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>vacuum</literal>: 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
+ I/O operations and are tracked in <varname>context</varname>
+ <literal>normal</literal>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>bulkread</literal>: Certain large read I/O operations
+ done outside of shared buffers, for example, a sequential scan of a
+ large table.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>bulkwrite</literal>: Certain large write I/O operations
+ done outside of shared buffers, such as <command>COPY</command>.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>io_type</structfield> <type>text</type>
+ </para>
+ <para>
+ Type of IO operation. Possible values are: FIXME!
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>bucket_latency_us</structfield> <type>bigint</type>
+ </para>
+ <para>
+ The higher value of latency bucket (in microseconds).
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>bucket_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Numer of times latency of the I/O operation hit this specific bucket (with
+ up to <varname>bucket_latency_us</varname> microseconds).
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Time at which these statistics were last reset.
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ 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 <varname>backend_type</varname> <literal>checkpointer</literal> and
+ <varname>object</varname> <literal>temp relation</literal>.
+ </para>
+
+ <para>
+ 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
+ <literal>fsync</literal>ed, so <varname>fsyncs</varname> will be NULL for
+ <varname>object</varname> <literal>temp relation</literal>. Also, the
+ background writer does not perform reads, so <varname>reads</varname> will
+ be NULL in rows for <varname>backend_type</varname> <literal>background
+ writer</literal>.
+ </para>
+
+ <para>
+ For the <varname>object</varname> <literal>wal</literal>,
+ <varname>fsyncs</varname> and <varname>fsync_time</varname> track the
+ fsync activity of WAL files done in <function>issue_xlog_fsync</function>.
+ <varname>writes</varname> and <varname>write_time</varname>
+ track the write activity of WAL files done in
+ <function>XLogWrite</function>.
+ See <xref linkend="wal-configuration"/> for more information.
+ </para>
+
+ <para>
+ <structname>pg_stat_io_histogram</structname> can be used to identify
+ I/O storage issues
+ For example:
+ <itemizedlist>
+ <listitem>
+ <para>
+ Presence of abnormally high latency for <varname>fsyncs</varname> might
+ indicate I/O saturation, oversubscription or hardware connectivity issues.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Unusually high latency for <varname>fsyncs</varname> on standby's startup
+ backend type, might be responsible for high duration of commits in
+ synchronous replication setups.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <note>
+ <para>
+ Columns tracking I/O wait time will only be non-zero when
+ <xref linkend="guc-track-io-timing"/> is enabled. The user should be
+ careful when referencing these columns in combination with their
+ corresponding I/O operations in case <varname>track_io_timing</varname>
+ was not enabled for the entire time since the last stats reset.
+ </para>
+ </note>
+ </sect2>
+
<sect2 id="monitoring-pg-stat-bgwriter-view">
<title><structname>pg_stat_bgwriter</structname></title>
diff --git a/doc/src/sgml/wal.sgml b/doc/src/sgml/wal.sgml
index f3b86b26be9..8b8c407e69f 100644
--- a/doc/src/sgml/wal.sgml
+++ b/doc/src/sgml/wal.sgml
@@ -832,8 +832,9 @@
of times <function>XLogWrite</function> writes and
<function>issue_xlog_fsync</function> syncs WAL data to disk are also
counted as <varname>writes</varname> and <varname>fsyncs</varname>
- in <structname>pg_stat_io</structname> for the <varname>object</varname>
- <literal>wal</literal>, respectively.
+ in <structname>pg_stat_io</structname> and
+ <structname>pg_stat_io_histogram</structname> for the
+ <varname>object</varname> <literal>wal</literal>, respectively.
</para>
<para>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 7553f31fef0..c44c4167646 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1219,6 +1219,17 @@ SELECT
b.stats_reset
FROM pg_stat_get_io() b;
+CREATE VIEW pg_stat_io_histogram AS
+SELECT
+ b.backend_type,
+ b.object,
+ b.context,
+ b.io_type,
+ b.bucket_latency_us,
+ b.bucket_count,
+ b.stats_reset
+FROM pg_stat_get_io_histogram() b;
+
CREATE VIEW pg_stat_wal AS
SELECT
w.wal_records,
diff --git a/src/backend/utils/activity/pgstat_io. b/src/backend/utils/activity/pgstat_io.
new file mode 100644
index 00000000000..e69de29bb2d
diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/activity/pgstat_io.c
index 28de24538dc..7ce43f59e8b 100644
--- a/src/backend/utils/activity/pgstat_io.c
+++ b/src/backend/utils/activity/pgstat_io.c
@@ -107,6 +107,42 @@ pgstat_prepare_io_time(bool track_io_guc)
return io_start;
}
+
+/*
+ * Calculate bucket index based on value in microseconds. We have up to
+ * PGSTAT_IO_HIST_BUCKETS buckets.
+ * If < 128 (2^7) -> bucket 0
+ * If < 256 (2^(7+1)) -> bucket 1
+ * If < 512 (2^(7+2)) -> bucket 2
+ * ..
+ * If < 262144 (2^(7+11)=512ms!) -> bucket 11
+ */
+static inline int get_bucket_index(uint32_t val) {
+#define MIN_PG_STAT_IO_HIST_LATENCY 127
+ const uint32_t max_index = PGSTAT_IO_HIST_BUCKETS - 1;
+ /*
+ * hopefully calculated to be 25 by the compiler:
+ * clz(127) = clz(01111111b on uint32) = 25
+ */
+ const uint32_t min_latency_leading_zeros =
+ pg_leading_zero_bits32(MIN_PG_STAT_IO_HIST_LATENCY);
+
+ /*
+ * make sure the tmp value at least 127 (our minimum bucket size)
+ * as __builtin_clz might return undefined behavior when operating on 0
+ */
+ uint32_t tmp = val | MIN_PG_STAT_IO_HIST_LATENCY;
+
+ /* count leading zeros */
+ int leading_zeros = pg_leading_zero_bits32(tmp);
+
+ /* normalize the index */
+ uint32_t index = min_latency_leading_zeros - leading_zeros;
+
+ /* clamp it to the maximum */
+ return (index > max_index) ? max_index : index;
+}
+
/*
* Like pgstat_count_io_op() except it also accumulates time.
*
@@ -125,6 +161,7 @@ pgstat_count_io_op_time(IOObject io_object, IOContext io_context, IOOp io_op,
if (!INSTR_TIME_IS_ZERO(start_time))
{
instr_time io_time;
+ int bucket_index;
INSTR_TIME_SET_CURRENT(io_time);
INSTR_TIME_SUBTRACT(io_time, start_time);
@@ -152,6 +189,10 @@ pgstat_count_io_op_time(IOObject io_object, IOContext io_context, IOOp io_op,
INSTR_TIME_ADD(PendingIOStats.pending_times[io_object][io_context][io_op],
io_time);
+ /* calculate the bucket_index based on latency in us */
+ bucket_index = get_bucket_index(INSTR_TIME_GET_MICROSEC(io_time));
+ PendingIOStats.pending_hist_time_buckets[io_object][io_context][io_op][bucket_index]++;
+
/* Add the per-backend count */
pgstat_count_backend_io_op_time(io_object, io_context, io_op,
io_time);
@@ -221,6 +262,10 @@ pgstat_io_flush_cb(bool nowait)
bktype_shstats->times[io_object][io_context][io_op] +=
INSTR_TIME_GET_MICROSEC(time);
+
+ for(int b = 0; b < PGSTAT_IO_HIST_BUCKETS; b++)
+ bktype_shstats->hist_time_buckets[io_object][io_context][io_op][b] +=
+ PendingIOStats.pending_hist_time_buckets[io_object][io_context][io_op][b];
}
}
}
@@ -274,6 +319,33 @@ pgstat_get_io_object_name(IOObject io_object)
pg_unreachable();
}
+const char *
+pgstat_get_io_op_name(IOOp io_op)
+{
+ switch (io_op)
+ {
+ case IOOP_EVICT:
+ return "evict";
+ case IOOP_FSYNC:
+ return "fsync";
+ case IOOP_HIT:
+ return "hit?";
+ case IOOP_REUSE:
+ return "reuse";
+ case IOOP_WRITEBACK:
+ return "writeback";
+ case IOOP_EXTEND:
+ return "extend";
+ case IOOP_READ:
+ return "read";
+ case IOOP_WRITE:
+ return "write";
+ }
+
+ elog(ERROR, "unrecognized IOOp value: %d", io_op);
+ pg_unreachable();
+}
+
void
pgstat_io_init_shmem_cb(void *stats)
{
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 73ca0bb0b7f..a23f27b32a3 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1356,6 +1356,24 @@ typedef enum io_stat_col
IO_NUM_COLUMNS,
} io_stat_col;
+/*
+* When adding a new column to the pg_stat_io view and the
+* pg_stat_get_backend_io() function, add a new enum value here above
+* IO_NUM_COLUMNS.
+*/
+typedef enum hist_io_stat_col
+{
+ HIST_IO_COL_INVALID = -1,
+ HIST_IO_COL_BACKEND_TYPE,
+ HIST_IO_COL_OBJECT,
+ HIST_IO_COL_CONTEXT,
+ HIST_IO_COL_IOTYPE,
+ HIST_IO_COL_BUCKET_US,
+ HIST_IO_COL_COUNT,
+ HIST_IO_COL_RESET_TIME,
+ HIST_IO_NUM_COLUMNS
+} history_get_history_state;
+
/*
* When adding a new IOOp, add a new io_stat_col and add a case to this
* function returning the corresponding io_stat_col.
@@ -1627,6 +1645,111 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+/*
+ * pg_stat_io_histogram_build_tuples
+ *
+ * Helper routine for pg_stat_get_io_histogram() and pg_stat_get_backend_io()XXX
+ * filling a result tuplestore with one tuple for each object and each
+ * context supported by the caller, based on the contents of bktype_stats.
+ */
+static void
+pg_stat_io_histogram_build_tuples(ReturnSetInfo *rsinfo,
+ PgStat_BktypeIO *bktype_stats,
+ BackendType bktype,
+ TimestampTz stat_reset_timestamp)
+{
+ Datum bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype));
+
+ for (int io_obj = 0; io_obj < IOOBJECT_NUM_TYPES; io_obj++)
+ {
+ const char *obj_name = pgstat_get_io_object_name(io_obj);
+
+ for (int io_context = 0; io_context < IOCONTEXT_NUM_TYPES; io_context++)
+ {
+ const char *context_name = pgstat_get_io_context_name(io_context);
+
+ /*
+ * Some combinations of BackendType, IOObject, and IOContext are
+ * not valid for any type of IOOp. In such cases, omit the entire
+ * row from the view.
+ */
+ if (!pgstat_tracks_io_object(bktype, io_obj, io_context))
+ continue;
+
+ for (int io_op = 0; io_op < IOOP_NUM_TYPES; io_op++)
+ {
+ const char *op_name = pgstat_get_io_op_name(io_op);
+
+ for(int b = 0; b < PGSTAT_IO_HIST_BUCKETS; b++) {
+ Datum values[HIST_IO_NUM_COLUMNS] = {0};
+ bool nulls[HIST_IO_NUM_COLUMNS] = {0};
+
+ values[HIST_IO_COL_BACKEND_TYPE] = bktype_desc;
+ values[HIST_IO_COL_OBJECT] = CStringGetTextDatum(obj_name);
+ values[HIST_IO_COL_CONTEXT] = CStringGetTextDatum(context_name);
+ values[HIST_IO_COL_IOTYPE] = CStringGetTextDatum(op_name);
+
+ /*
+ * this bucket's max latency:
+ * 2^7 = MIN_PG_STAT_IO_HIST_LATENCY = 128(us)
+ */
+ values[HIST_IO_COL_BUCKET_US] = Int64GetDatum(1 << (7 + b));
+
+ /* bucket count */
+ values[HIST_IO_COL_COUNT] = Int64GetDatum(
+ bktype_stats->hist_time_buckets[io_obj][io_context][io_op][b]);
+
+ if (stat_reset_timestamp != 0)
+ values[HIST_IO_COL_RESET_TIME] = TimestampTzGetDatum(stat_reset_timestamp);
+ else
+ nulls[HIST_IO_COL_RESET_TIME] = true;
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+ values, nulls);
+ }
+ }
+ }
+ }
+}
+
+Datum
+pg_stat_get_io_histogram(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo;
+ PgStat_IO *backends_io_stats;
+
+ InitMaterializedSRF(fcinfo, 0);
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ backends_io_stats = pgstat_fetch_stat_io();
+
+ for (int bktype = 0; bktype < BACKEND_NUM_TYPES; bktype++)
+ {
+ PgStat_BktypeIO *bktype_stats = &backends_io_stats->stats[bktype];
+
+ /*
+ * In Assert builds, we can afford an extra loop through all of the
+ * counters (in pg_stat_io_build_tuples()), checking that only
+ * expected stats are non-zero, since it keeps the non-Assert code
+ * cleaner.
+ */
+ Assert(pgstat_bktype_io_stats_valid(bktype_stats, bktype));
+
+ /*
+ * For those BackendTypes without IO Operation stats, skip
+ * representing them in the view altogether.
+ */
+ if (!pgstat_tracks_io_bktype(bktype))
+ continue;
+
+ /* save tuples with data from this PgStat_BktypeIO */
+ pg_stat_io_histogram_build_tuples(rsinfo, bktype_stats, bktype,
+ backends_io_stats->stat_reset_timestamp);
+ }
+
+ return (Datum) 0;
+}
+
/*
* pg_stat_wal_build_tuple
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 894b6a1b6d6..32102549996 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6026,6 +6026,15 @@
proargnames => '{backend_type,object,context,reads,read_bytes,read_time,writes,write_bytes,write_time,writebacks,writeback_time,extends,extend_bytes,extend_time,hits,evictions,reuses,fsyncs,fsync_time,stats_reset}',
prosrc => 'pg_stat_get_io' },
+{ oid => '6149', descr => 'statistics: per backend type IO latency histogram',
+ proname => 'pg_stat_get_io_histogram', prorows => '30', proretset => 't',
+ provolatile => 'v', proparallel => 'r', prorettype => 'record',
+ proargtypes => '',
+ proallargtypes => '{text,text,text,text,int8,int8,timestamptz}',
+ proargmodes => '{o,o,o,o,o,o,o}',
+ proargnames => '{backend_type,object,context,io_type,bucket_latency_us,bucket_count,stats_reset}',
+ prosrc => 'pg_stat_get_io_histogram' },
+
{ oid => '6386', descr => 'statistics: backend IO statistics',
proname => 'pg_stat_get_backend_io', prorows => '5', proretset => 't',
provolatile => 'v', proparallel => 'r', prorettype => 'record',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index fff7ecc2533..65b7b7296ca 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -322,11 +322,23 @@ typedef enum IOOp
(((unsigned int) (io_op)) < IOOP_NUM_TYPES && \
((unsigned int) (io_op)) >= IOOP_EXTEND)
+/*
+ * This should represent balance between being fast and providing value
+ * to the users:
+ * 1. We want to cover various fast and slow device types (0.1ms - 15ms)
+ * 2. We want to also cover sporadic long tail latencies (hardware issues,
+ * delayed fsyncs, stuck I/O)
+ * 3. We want to be below cacheline size here probably:
+ * 12 * sizeof(uint64) = 48 which should be less than cacheline size (64b)
+ */
+#define PGSTAT_IO_HIST_BUCKETS 12
+
typedef struct PgStat_BktypeIO
{
uint64 bytes[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES];
PgStat_Counter counts[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES];
PgStat_Counter times[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES];
+ uint64 hist_time_buckets[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES][PGSTAT_IO_HIST_BUCKETS];
} PgStat_BktypeIO;
typedef struct PgStat_PendingIO
@@ -334,6 +346,7 @@ typedef struct PgStat_PendingIO
uint64 bytes[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES];
PgStat_Counter counts[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES];
instr_time pending_times[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES];
+ uint64 pending_hist_time_buckets[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES][PGSTAT_IO_HIST_BUCKETS];
} PgStat_PendingIO;
typedef struct PgStat_IO
@@ -606,6 +619,7 @@ extern void pgstat_count_io_op_time(IOObject io_object, IOContext io_context,
extern PgStat_IO *pgstat_fetch_stat_io(void);
extern const char *pgstat_get_io_context_name(IOContext io_context);
extern const char *pgstat_get_io_object_name(IOObject io_object);
+extern const char *pgstat_get_io_op_name(IOOp io_op);
extern bool pgstat_tracks_io_bktype(BackendType bktype);
extern bool pgstat_tracks_io_object(BackendType bktype,
diff --git a/src/include/port/pg_bitutils.h b/src/include/port/pg_bitutils.h
index 35761f509ec..f456bcef1bd 100644
--- a/src/include/port/pg_bitutils.h
+++ b/src/include/port/pg_bitutils.h
@@ -32,6 +32,33 @@ extern PGDLLIMPORT const uint8 pg_leftmost_one_pos[256];
extern PGDLLIMPORT const uint8 pg_rightmost_one_pos[256];
extern PGDLLIMPORT const uint8 pg_number_of_ones[256];
+
+/*
+ * pg_leading_zero_bits32
+ * Returns the number of leading 0-bits in x, starting at the most significant bit position.
+ * Word must not be 0 (as it is undefined behavior).
+ */
+static inline int
+pg_leading_zero_bits32(uint32 word)
+{
+#ifdef HAVE__BUILTIN_CLZ
+ Assert(word != 0);
+
+ return __builtin_clz(word);
+#else
+ int n = 32;
+ uint32 y;
+ if (word == 0)
+ return 32;
+
+ y = word >> 16; if (y != 0) { n -= 16; word = y; }
+ y = word >> 8; if (y != 0) { n -= 8; word = y; }
+ y = word >> 4; if (y != 0) { n -= 4; word = y; }
+ y = word >> 2; if (y != 0) { n -= 2; word = y; }
+ y = word >> 1; if (y != 0) { return n - 2; }
+#endif
+}
+
/*
* pg_leftmost_one_pos32
* Returns the position of the most significant set bit in "word",
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index f4ee2bd7459..88dc703bbde 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1951,6 +1951,14 @@ pg_stat_io| SELECT backend_type,
fsync_time,
stats_reset
FROM pg_stat_get_io() b(backend_type, object, context, reads, read_bytes, read_time, writes, write_bytes, write_time, writebacks, writeback_time, extends, extend_bytes, extend_time, hits, evictions, reuses, fsyncs, fsync_time, stats_reset);
+pg_stat_io_histogram| SELECT backend_type,
+ object,
+ context,
+ io_type,
+ bucket_latency_us,
+ bucket_count,
+ stats_reset
+ FROM pg_stat_get_io_histogram() b(backend_type, object, context, io_type, bucket_latency_us, bucket_count, stats_reset);
pg_stat_progress_analyze| SELECT s.pid,
s.datid,
d.datname,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index cd00f35bf7a..4c95f09d651 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1765,6 +1765,29 @@ SELECT :my_io_stats_pre_reset > :my_io_stats_post_backend_reset;
t
(1 row)
+-- Check that pg_stat_io_histograms sees some growing counts in buckets
+-- We could also try with checkpointer, but it often runs with fsync=off
+-- during test.
+SET track_io_timing TO 'on';
+SELECT sum(bucket_count) AS hist_bucket_count_sum FROM pg_stat_get_io_histogram()
+WHERE backend_type='client backend' AND object='relation' AND context='normal' \gset
+CREATE TABLE test_io_hist(id bigint);
+INSERT INTO test_io_hist SELECT generate_series(1, 100) s;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT sum(bucket_count) AS hist_bucket_count_sum2 FROM pg_stat_get_io_histogram()
+WHERE backend_type='client backend' AND object='relation' AND context='normal' \gset
+SELECT :hist_bucket_count_sum2 > :hist_bucket_count_sum;
+ ?column?
+----------
+ t
+(1 row)
+
+RESET track_io_timing;
-- Check invalid input for pg_stat_get_backend_io()
SELECT pg_stat_get_backend_io(NULL);
pg_stat_get_backend_io
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 8768e0f27fd..063b1011d7e 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -841,6 +841,21 @@ SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) +
FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
SELECT :my_io_stats_pre_reset > :my_io_stats_post_backend_reset;
+
+-- Check that pg_stat_io_histograms sees some growing counts in buckets
+-- We could also try with checkpointer, but it often runs with fsync=off
+-- during test.
+SET track_io_timing TO 'on';
+SELECT sum(bucket_count) AS hist_bucket_count_sum FROM pg_stat_get_io_histogram()
+WHERE backend_type='client backend' AND object='relation' AND context='normal' \gset
+CREATE TABLE test_io_hist(id bigint);
+INSERT INTO test_io_hist SELECT generate_series(1, 100) s;
+SELECT pg_stat_force_next_flush();
+SELECT sum(bucket_count) AS hist_bucket_count_sum2 FROM pg_stat_get_io_histogram()
+WHERE backend_type='client backend' AND object='relation' AND context='normal' \gset
+SELECT :hist_bucket_count_sum2 > :hist_bucket_count_sum;
+RESET track_io_timing;
+
-- Check invalid input for pg_stat_get_backend_io()
SELECT pg_stat_get_backend_io(NULL);
SELECT pg_stat_get_backend_io(0);
--
2.43.0
