digoal
2021-01-18
PostgreSQL , 统计 , SaaS
PG 14 新增了一些计数器, 用于统计数据库维度的session创建数量, 处于active状态, idle 状态, 以及其他状态的耗时等.
为什么会增加这些计数器指标呢, 因为PG自设计之初就支持在一个cluster里面创建多个database, 在一些云厂商中, 例如heroku支持以server less的方式提供服务, 每个database提供给某一个用户使用, 这个用户用了多少资源, 除了存储空间, 还有一项比较重要的就是cpu资源的消耗, PG 14 新增的database维度资源消耗统计是一个不错的SaaS行业数据库特性.
Add pg_stat_database counters for sessions and session time
author Magnus Hagander <magnus@hagander.net>
Sun, 17 Jan 2021 12:34:09 +0000 (13:34 +0100)
committer Magnus Hagander <magnus@hagander.net>
Sun, 17 Jan 2021 12:52:31 +0000 (13:52 +0100)
commit 960869da0803427d14335bba24393f414b476e2c
tree c36a0991f757696e4d46c079055c1e926e4623f6 tree | snapshot
parent 891a1d0bca262ca78564e0fea1eaa5ae544ea5ee commit | diff
Add pg_stat_database counters for sessions and session time
This add counters for number of sessions, the different kind of session
termination types, and timers for how much time is spent in active vs
idle in a database to pg_stat_database.
Internally this also renames the parameter "force" to disconnect. This
was the only use-case for the parameter before, so repurposing it to
this mroe narrow usecase makes things cleaner than inventing something
new.
Author: Laurenz Albe
Reviewed-By: Magnus Hagander, Soumyadeep Chakraborty, Masahiro Ikeda
Discussion: https://postgr.es/m/b07e1f9953701b90c66ed368656f2aef40cac4fb.camel@cybertec.at
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>session_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Time spent by database sessions in this database, in milliseconds
+ (note that statistics are only updated when the state of a session
+ changes, so if sessions have been idle for a long time, this idle time
+ won't be included)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>active_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Time spent executing SQL statements in this database, in milliseconds
+ (this corresponds to the states <literal>active</literal> and
+ <literal>fastpath function call</literal> in
+ <link linkend="monitoring-pg-stat-activity-view">
+ <structname>pg_stat_activity</structname></link>)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>idle_in_transaction_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Time spent idling while in a transaction in this database, in milliseconds
+ (this corresponds to the states <literal>idle in transaction</literal> and
+ <literal>idle in transaction (aborted)</literal> in
+ <link linkend="monitoring-pg-stat-activity-view">
+ <structname>pg_stat_activity</structname></link>)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>sessions</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total number of sessions established to this database
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>sessions_abandoned</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of database sessions to this database that were terminated
+ because connection to the client was lost
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>sessions_fatal</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of database sessions to this database that were terminated
+ by fatal errors
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>sessions_killed</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of database sessions to this database that were terminated
+ by operator intervention
+ </para></entry>
+ </row>
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
