PostgreSQL 18 preview - backend process IO 统计功能 pg_stat_get_backend_io($PID)

PostgreSQL 18 preview - backend process IO 统计功能 pg_stat_get_backend_io($PID)

作者

digoal

日期

2025-01-15

标签

PostgreSQL , PolarDB , DuckDB , backend process io stat

背景

IO统计信息从后台进程认证通过后开始收集, 退出后自动释放, 不支持持久化存储到磁盘, 可以通过pg_stat_reset_backend_stats主动置零, 通过pg_stat_get_backend_io($PID)获得某个后台进程的IO统计信息.

辅助进程例如checkpoint,bg writer, autovacuum launcher, startup process等不支持作为PID传入pg_stat_get_backend_io($PID)返回, 因为在pg_stat_io视图中已有这些进程的统计数据.

未来可能会扩展更多的进程级别统计信息.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9aea73fc61d4e77e000724ce0b2f896590a10e03

Add backend-level statistics to pgstats  
  
This adds a new variable-numbered statistics kind in pgstats, where the  
object ID key of the stats entries is based on the proc number of the  
backends.  This acts as an upper-bound for the number of stats entries  
that can exist at once.  The entries are created when a backend starts  
after authentication succeeds, and are removed when the backend exits,  
making the stats entry exist for as long as their backend is up and  
running.  These are not written to the pgstats file at shutdown (note  
that write_to_file is disabled, as a safety measure).  
  
Currently, these stats include only information about the I/O generated  
by a backend, using the same layer as pg_stat_io, except that it is now  
possible to know how much activity is happening in each backend rather  
than an overall aggregate of all the activity.  A function called  
pg_stat_get_backend_io() is added to access this data depending on the  
PID of a backend.  The existing structure could be expanded in the  
future to add more information about other statistics related to  
backends, depending on requirements or ideas.  
  
Auxiliary processes are not included in this set of statistics.  These  
are less interesting to have than normal backends as they have dedicated  
entries in pg_stat_io, and stats kinds of their own.  
  
This commit includes also pg_stat_reset_backend_stats(), function able  
to reset all the stats associated to a single backend.  
  
Bump catalog version and PGSTAT_FILE_FORMAT_ID.  
  
Author: Bertrand Drouvot  
Reviewed-by: Álvaro Herrera, Kyotaro Horiguchi, Michael Paquier, Nazir  
Bilal Yavuz  
Discussion: https://postgr.es/m/ZtXR+CtkEVVE/LHF@ip-10-97-1-34.eu-west-3.compute.internal  

+      <row>  
+       <entry id="pg-stat-get-backend-io" role="func_table_entry"><para role="func_signature">  
+        <indexterm>  
+         <primary>pg_stat_get_backend_io</primary>  
+        </indexterm>  
+        <function>pg_stat_get_backend_io</function> ( <type>integer</type> )  
+        <returnvalue>setof record</returnvalue>  
+       </para>  
+       <para>  
+        Returns I/O statistics about the backend with the specified  
+        process ID. The output fields are exactly the same as the ones in the  
+        <structname>pg_stat_io</structname> view.  
+       </para>  
+       <para>  
+        The function does not return I/O statistics for the checkpointer,  
+        the background writer, the startup process and the autovacuum launcher  
+        as they are already visible in the <structname>pg_stat_io</structname>  
+        view and there is only one of each.  
+       </para></entry>  
+      </row>  

+      <row>  
+       <entry role="func_table_entry"><para role="func_signature">  
+        <indexterm>  
+         <primary>pg_stat_reset_backend_stats</primary>  
+        </indexterm>  
+        <function>pg_stat_reset_backend_stats</function> ( <type>integer</type> )  
+        <returnvalue>void</returnvalue>  
+       </para>  
+       <para>  
+        Resets statistics for a single backend with the specified process ID  
+        to zero.  
+       </para>  
+       <para>  
+        This function is restricted to superusers by default, but other users  
+        can be granted EXECUTE to run the function.  
+       </para></entry>  
+      </row>  

期望 PostgreSQL|开源PolarDB 增加什么功能?

PolarDB 开源数据库

PolarDB 学习图谱

PostgreSQL 解决方案集合

德哥 / digoal’s Github - 公益是一辈子的事.

About 德哥