system_stats 是由 EnterpriseDB 开源的一个 Postgresql 扩展插件，提供了数据库服务器 cpu、内存、磁盘和网络相关的统计指标，可以用于替代操作系统级别的监控。
1、安装
下载地址：https://github.com/EnterpriseDB/system_stats
? ? ? ? ? ? ? ? ?Postgresql数据库system_stats监控插件-Java文档类资源-CSDN下载
Linux解压安装：
tar -zxvf system_stats-1.0.tar.gz
cd system_stats-1.0
PATH="/usr/local/pgsql/bin:$PATH" make USE_PGXS=1
sudo PATH="/usr/local/pgsql/bin:$PATH" make install USE_PGXS=1windows安装：
- ?解压到：$PGSRC/contrib/system_stats
- 设置环境变量
-
PG_INCLUDE_DIR=C:\Program Files\PostgreSQL\12\include PG_LIB_DIR=C:\Program Files\PostgreSQL\12\lib - 使用Visual Studio编译
2、使用
添加扩展：
bill=# create extension system_stats ;
CREATE EXTENSION
bill=# \dx+ system_stats
Objects in extension "system_stats"
Object description
-----------------------------------------
function pg_sys_cpu_info()
function pg_sys_cpu_memory_by_process()
function pg_sys_cpu_usage_info()
function pg_sys_disk_info()
function pg_sys_io_analysis_info()
function pg_sys_load_avg_info()
function pg_sys_memory_info()
function pg_sys_network_info()
function pg_sys_os_info()
function pg_sys_process_info()
(10 rows)
pg_sys_os_info() 函数
pg_sys_os_info() 函数可以用于获取操作系统相关的信息。例如：
hrdb=> \x
Expanded display is on.
hrdb=> select * from pg_sys_os_info();
-[ RECORD 1 ]-------+---------------------------------
name | "CentOS Linux 7 (Core)" +
|
version | Linux 3.10.0-957.27.2.el7.x86_64
host_name | sqlhost
domain_name | (none)
handle_count | 5632
process_count | 199
thread_count | 525
architecture | x86_64
last_bootup_time |
os_up_since_seconds | 10309
pg_sys_cpu_info() 函数
pg_sys_cpu_info() 函数用于获取 CPU 相关的信息。例如：
hrdb=> select * from pg_sys_cpu_info();
-[ RECORD 1 ]------+------------------------------------------
vendor | GenuineIntel
description | GenuineIntel model 63 family 6
model_name | Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz
processor_type |
logical_processor | 0
physical_processor | 4
no_of_cores | 4
architecture | x86_64
clock_speed_hz | 2394456064
cpu_type |
cpu_family |
byte_order |
l1dcache_size | 32
l1icache_size | 32
l2cache_size | 256
l3cache_size | 15360
pg_sys_cpu_usage_info() 函数
pg_sys_cpu_usage_info() 函数用于获取 CPU 使用信息，按照 CPU 占用时间百分比显示。例如：
hrdb=> select * from pg_sys_cpu_usage_info();
-[ RECORD 1 ]-------------------+----
usermode_normal_process_percent | 0
usermode_niced_process_percent | 0
kernelmode_process_percent | 0
idle_mode_percent | 100
io_completion_percent | 0
servicing_irq_percent | 0
servicing_softirq_percent | 0
user_time_percent |
processor_time_percent |
privileged_time_percent |
interrupt_time_percent |
pg_sys_memory_info() 函数
pg_sys_memory_info() 函数用于获取内存使用信息，单位为字节。例如：
hrdb=> select * from pg_sys_memory_info();
-[ RECORD 1 ]----+-----------
total_memory | 4142108672
used_memory | 3824459776
free_memory | 317648896
swap_total | 2147479552
swap_used | 0
swap_free | 2147479552
cache_total | 2108534784
kernel_total |
kernel_paged |
kernel_non_paged |
total_page_file |
avail_page_file |
pg_sys_io_analysis_info() 函数
pg_sys_io_analysis_info() 函数用于获取块设备的 I/O 性能分析。例如：
hrdb=> select * from pg_sys_io_analysis_info();
-[ RECORD 1 ]-+-----------
device_name | sdb
total_reads | 4673
total_writes | 11215
read_bytes | 593527808
write_bytes | 258909184
read_time_ms | 110966
write_time_ms | 368935
-[ RECORD 2 ]-+-----------
device_name | sdb1
total_reads | 4642
total_writes | 5556
read_bytes | 591815680
write_bytes | 258909184
read_time_ms | 110804
write_time_ms | 314021
-[ RECORD 3 ]-+-----------
device_name | sda
total_reads | 18147
total_writes | 21426
read_bytes | 1220719104
write_bytes | 712343040
read_time_ms | 378269
write_time_ms | 1152711
...
pg_sys_disk_info() 函数
pg_sys_disk_info() 函数用于获取磁盘信息。例如：
hrdb=> select * from pg_sys_disk_info();
-[ RECORD 1 ]----+------------------------
mount_point | /
file_system | rootfs
drive_letter |
drive_type |
file_system_type | rootfs
total_space | 39709573120
used_space | 30819479552
free_space | 8890093568
total_inodes | 17583816
used_inodes | 219948
free_inodes | 17363868
-[ RECORD 2 ]----+------------------------
mount_point | /
file_system | /dev/mapper/centos-root
drive_letter |
drive_type |
file_system_type | xfs
total_space | 39709573120
used_space | 30819479552
free_space | 8890093568
total_inodes | 17583816
used_inodes | 219948
free_inodes | 17363868
-[ RECORD 3 ]----+------------------------
mount_point | /boot
file_system | /dev/sda1
drive_letter |
drive_type |
file_system_type | xfs
total_space | 1063256064
used_space | 258994176
free_space | 804261888
total_inodes | 524288
used_inodes | 354
free_inodes | 523934
...
pg_sys_load_avg_info() 函数
pg_sys_load_avg_info() 函数用于获取系统在 1、5、10 和 15 分钟内的平均负载。例如：
hrdb=> select * from pg_sys_process_info();
-[ RECORD 1 ]------+----
total_processes | 199
running_processes | 1
sleeping_processes | 198
stopped_processes | 0
zombie_processes | 0
pg_sys_process_info() 函数
pg_sys_process_info() 函数用于获取进程数量信息。例如：
hrdb=> select * from pg_sys_process_info();
-[ RECORD 1 ]------+----
total_processes | 199
running_processes | 1
sleeping_processes | 198
stopped_processes | 0
zombie_processes | 0
pg_sys_network_info() 函数
pg_sys_network_info() 函数用于获取网络接口信息。例如：
hrdb=> select * from pg_sys_network_info();
-[ RECORD 1 ]---+---------------
interface_name | lo
ip_address | 127.0.0.1
tx_bytes | 2529261
tx_packets | 5136
tx_errors | 0
tx_dropped | 0
rx_bytes | 2529261
rx_packets | 5136
rx_errors | 0
rx_dropped | 0
link_speed_mbps | 0
-[ RECORD 2 ]---+---------------
interface_name | enp0s3
ip_address | 10.0.2.15
tx_bytes | 2827967
tx_packets | 41026
tx_errors | 0
tx_dropped | 0
rx_bytes | 131550730
rx_packets | 123962
rx_errors | 0
rx_dropped | 0
link_speed_mbps | 1000
-[ RECORD 3 ]---+---------------
interface_name | enp0s8
ip_address | 192.168.56.104
tx_bytes | 2947505
tx_packets | 4640
tx_errors | 0
tx_dropped | 0
rx_bytes | 1019052
rx_packets | 9951
rx_errors | 0
rx_dropped | 0
link_speed_mbps | 1000
pg_sys_cpu_memory_by_process() 函数
pg_sys_cpu_memory_by_process() 函数用于获取每个进程的 CPU 和内存使用信息。例如：
hrdb=> select * from pg_sys_cpu_memory_by_process();
-[ RECORD 1 ]---------+--------------
pid | 1
name | (systemd)
running_since_seconds | 12630
cpu_usage | 0
memory_usage | 0.1
memory_bytes | 4272128
-[ RECORD 2 ]---------+--------------
pid | 2
name | (kthreadd)
running_since_seconds | 12630
cpu_usage | 0
memory_usage | 0
memory_bytes | 0
-[ RECORD 3 ]---------+--------------
pid | 3
name | (ksoftirqd/0)
running_since_seconds | 12630
cpu_usage | 0
memory_usage | 0
memory_bytes | 0
...
