postgresql 扩展工具 操作系统统计信息扩展 system_stats

操作系统统计信息扩展 system_stats

16.1 介绍

system_stats 是一个 Postgres 扩展，提供了访问可用于监控的系统级统计信息的功能。它支持 Linux、macOS 和 Windows。

请注意，并非所有值都与所有操作系统相关。在这种情况下，未受影响的值返回 NULL。

版权所有 © 2019 - 2020，EnterpriseDB Corporation。版权所有。

16.2 编译与安装

Linux and macOS

该模块可以使用 PGXS 框架构建：

• 将文件存档解压缩到合适的目录中。
• 确保 PATH 环境变量包含包含您希望构建的 PostgreSQL 安装的 pg_config 二进制文件的目录。
• 编译并安装代码。

例如：

cd system_stats
PATH="/opt/pgsql/bin:$PATH" make USE_PGXS=1 
PATH="/opt/pgsql/bin:$PATH" make install USE_PGXS=1
复制

Windows

使用 Visual Studio 项目文件构建的模块：

• 解压 $PGSRC/contrib/system_stats 中的扩展文件
• 设置 PG_INCLUDE_DIR 和 PG_LIB_DIR 环境变量以确保可以找到 PostgreSQL 的 include 和 lib 目录进行编译。例如：

  PG_INCLUDE_DIR=C:\Program Files\PostgreSQL\12\include
  PG_LIB_DIR=C:\Program Files\PostgreSQL\12\lib
复制

• 打开 Visual Studio 项目文件“system_stats.vcxproj”并构建项目。

安装扩展

构建并安装代码后，您可以使用以下 SQL 命令在数据库中安装扩展：

CREATE EXTENSION system_stats;

postgres=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description                               
--------------------+---------+------------+----------------------------------------------------------------
 amcheck            | 1.3     | public     | functions for verifying relation integrity
 pg_remote_exec     | 1.0     | pg_catalog | remote shell execution for non-superusers
 pg_stat_kcache     | 2.2.0   | public     | Kernel statistics gathering
 pg_stat_statements | 1.9     | public     | track planning and execution statistics of all SQL statements executed
 pgaudit            | 1.6.2   | public     | provides auditing functionality
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 ptrack             | 2.3     | public     | block-level incremental backup engine
 system_stats       | 1.0     | public     | EnterpriseDB system statistics for PostgreSQL
(8 rows)
复制

16.3 安全

由于这些函数返回的信息的性质，访问权限仅限于超级用户和 monitor_system_stats 角色的成员，该角色将在安装扩展程序时创建。在删除扩展期间不会删除 monitor_system_stats 角色。要允许用户在不授予超级用户访问权限的情况下访问这些功能，请将它们添加到 monitor_system_stats 角色。例如：

GRANT monitor_system_stats to nagios;
复制

16.4 函数

提供以下函数来获取所有平台的系统级统计信息。

pg_sys_os_info

该接口允许用户获取操作系统统计信息。

pg_sys_cpu_info

该接口允许用户获取 CPU 信息。

pg_sys_cpu_usage_info

该接口允许用户获取 CPU 使用信息。值是 CPU 用于所有操作的时间百分比。

pg_sys_memory_info

该接口允许用户获取内存使用信息。所有值都以字节为单位。

pg_sys_io_analysis_info

该接口允许用户对块设备进行 I/O 分析。

pg_sys_disk_info

该接口允许用户获取磁盘信息。

pg_sys_load_avg_info

此接口允许用户获取系统在 1、5、10 和 15 分钟间隔内的平均负载。

pg_sys_process_info

该接口允许用户获取进程信息。

pg_sys_network_info

该接口允许用户获取网络接口信息。

pg_sys_cpu_memory_by_process

该接口允许用户获取每个进程 ID 的 CPU 和内存信息。

注意：macOS 不允许其他用户访问进程信息。例如如果数据库服务器以 postgres 用户身份运行，则此函数将仅获取 postgres 用户拥有的进程的信息。其他进程将被列出，并且仅包括进程 ID 和名称；其他列将为 NULL。

16.5 每个函数的详细输出

pg_sys_os_info

• Name
• Version
• Host name
• Domain name
• Handle count
• Process count
• Thread count
• Architecture
• Last bootup time
• Uptime in seconds

pg_sys_cpu_info

• Vendor
• Description
• Model name
• Processor type
• Logical processor
• Physical processor
• Number of cores
• Architecture
• Clock speed in hz
• CPU type
• CPU family
• Byte order
• L1d cache size
• L1i cache size
• L2 cache size
• L3 cache size

pg_sys_cpu_usage_info

• Percent time spent in processing usermode normal process
• Percent time spent in processing usermode niced process
• Percent time spent in kernel mode process
• Percent time spent in idle mode
• Percent time spent in io completion
• Percent time spent in servicing interrupt
• Percent time spent in servicing software interrupt
• Percent user time spent
• Percent processor time spent
• Percent privileged time spent
• Percent interrupt time spent

pg_sys_memory_info

• Total memory
• Used memory
• Free memory
• Total swap memory
• Used swap memory
• Free swap memory
• Total cache memory
• Total kernel memory
• Kernel paged memory
• Kernel non paged memory
• Total page file
• Available page file

pg_sys_io_analysis_info

• Block device name
• Total number of reads
• Total number of writes
• Read bytes
• Written bytes
• Time spent in milliseconds for reading
• Time spent in milliseconds for writing

pg_sys_disk_info

• File system of the disk
• File system type
• Mount point for the file system
• Drive letter
• Drive type
• Total space in bytes
• Used space in bytes
• Available space in bytes
• Number of total inodes
• Number of used inodes
• Number of free inodes

pg_sys_load_avg_info

• 1 minute load average
• 5 minute load average
• 10 minute load average
• 15 minute load average

pg_sys_process_info

• Number of total processes
• Number of running processes
• Number of sleeping processes
• Number of stopped processes
• Number of zombie processes

pg_sys_network_info

• Name of the interface_name
• ipv4 address of the interface
• Number of total bytes transmitted
• Number of total packets transmitted
• Number of transmit errors by this network device
• Number of packets dropped during transmission
• Number of total bytes received
• Number of total packets received
• Number of receive errors by this network device
• Number of packets dropped by this network device
• Interface speed in mbps

pg_sys_cpu_memory_by_process

• PID of the process
• Process name
• CPU usage in bytes
• Memory usage in bytes
• Total memory used in bytes