暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / PostgreSQL 操作系统监控插件 - system_stats - cpu,memory,network,filesystem,block dev等监控

PostgreSQL 操作系统监控插件 - system_stats - cpu,memory,network,filesystem,block dev等监控

digoal 2020-07-09
815

作者

digoal

日期

2020-07-09

标签

PostgreSQL , system , system_stats , 系统监控 , 操作系统 , 插件

背景

https://github.com/EnterpriseDB/system_stats

https://pgsnake.blogspot.com/2020/06/systemstats-extension-for-postgresql.html

监控操作系统的插件, 对于DBA或nagios类似的监控套件来说, 比较方便. 特别是只有数据库连接, 无法登录OS的DBA来说, 使用这个插件可以了解操作系统的一些使用情况.

git clone https://github.com/EnterpriseDB/system_stats cd system_stats/ export PATH=$PGHOME/bin:$PATH USE_PGXS=1 make USE_PGXS=1 make install psql -c "create extension system_stats;"

pg_sys_os_info()
This interface allows the user to get operating system statistics.

postgres=# SELECT * FROM pg_sys_os_info(); -[ RECORD 1 ]-------+-------------------------------------------------------------------------------------------------- name | Darwin version | Darwin Kernel Version 19.4.0: Wed Mar 4 22:28:40 PST 2020; root:xnu-6153.101.6~15/RELEASE_X86_64 host_name | hal.local domain_name | handle_count | process_count | 531 thread_count | architecture | x86_64 last_bootup_time | os_up_since_seconds | 281466

pg_sys_cpu_info()
This interface allows the user to get CPU information.

postgres=# SELECT * FROM pg_sys_cpu_info(); -[ RECORD 1 ]------+--------------- vendor | description | model_name | MacBookPro15,1 processor_type | logical_processor | 12 physical_processor | 6 no_of_cores | 12 architecture | x86_64 clock_speed_hz | 2900000000 cpu_type | 7 cpu_family | 260141638 byte_order | 1234 l1dcache_size | 32 l1icache_size | 32 l2cache_size | 256 l3cache_size | 12288

pg_sys_cpu_usage_info()
This interface allows the user to get CPU usage information. Values are a percentage of time spent by CPUs for all operations.

postgres=# SELECT * FROM pg_sys_cpu_usage_info(); -[ RECORD 1 ]-------------------+---------- usermode_normal_process_percent | 1.6806724 usermode_niced_process_percent | 0 kernelmode_process_percent | 0 idle_mode_percent | 98.31933 io_completion_percent | servicing_irq_percent | servicing_softirq_percent | user_time_percent | processor_time_percent | privileged_time_percent | interrupt_time_percent |

pg_sys_memory_info()
This interface allows the user to get memory usage information. All the values are in bytes.

postgres=# SELECT * FROM pg_sys_memory_info(); -[ RECORD 1 ]----+------------ total_memory | 34359738368 used_memory | 31668727808 free_memory | 2691010560 swap_total | 3221225472 swap_used | 2672033792 swap_free | 549191680 cache_total | kernel_total | kernel_paged | kernel_non_paged | total_page_file | avail_page_file |

pg_sys_io_analysis_info()
This interface allows the user to get an I/O analysis of block devices.

postgres=# SELECT * FROM pg_sys_io_analysis_info(); -[ RECORD 1 ]-+------------- device_name | disk0 total_reads | 9044867 total_writes | 6688535 read_bytes | 127989366784 write_bytes | 127197491200 read_time_ms | 3270007 write_time_ms | 3959392 -[ RECORD 2 ]-+------------- device_name | disk2 total_reads | 2222011 total_writes | 3213728 read_bytes | 25915008512 write_bytes | 99796177408 read_time_ms | 22302918 write_time_ms | 65947889

pg_sys_disk_info()
This interface allows the user to get the disk information.

postgres=# SELECT * FROM pg_sys_disk_info(); -[ RECORD 1 ]----+--------------------- mount_point | / file_system | apfs drive_letter | drive_type | file_system_type | /dev/disk1s5 total_space | 2000796545024 used_space | 11124047872 free_space | 1328550260736 total_inodes | 19539028760 used_inodes | 487630 free_inodes | 19538541130 -[ RECORD 2 ]----+--------------------- mount_point | /System/Volumes/Data file_system | apfs drive_letter | drive_type | file_system_type | /dev/disk1s1 total_space | 2000796545024 used_space | 11124047872 free_space | 1328550260736 total_inodes | 19539028760 used_inodes | 487630 free_inodes | 19538541130 -[ RECORD 3 ]----+--------------------- mount_point | /private/var/vm file_system | apfs drive_letter | drive_type | file_system_type | /dev/disk1s4 total_space | 2000796545024 used_space | 11124047872 free_space | 1328550260736 total_inodes | 19539028760 used_inodes | 487630 free_inodes | 19538541130 -[ RECORD 4 ]----+--------------------- mount_point | /Volumes/Backup file_system | hfs drive_letter | drive_type | file_system_type | /dev/disk3 total_space | 2000796545024 used_space | 11124047872 free_space | 1328550260736 total_inodes | 19539028760 used_inodes | 487630 free_inodes | 19538541130

pg_sys_load_avg_info()
This interface allows the user to get the average load of the system over 1, 5, 10 and 15 minute intervals.

postgres=# SELECT * FROM pg_sys_load_avg_info(); -[ RECORD 1 ]------------+---------- load_avg_one_minute | 1.4116211 load_avg_five_minutes | 1.5273438 load_avg_ten_minutes | load_avg_fifteen_minutes | 1.5668945

pg_sys_process_info()
This interface allows the user to get process information.

postgres=# SELECT * FROM pg_sys_process_info(); -[ RECORD 1 ]------+---- total_processes | 531 running_processes | 528 sleeping_processes | 0 stopped_processes | 0 zombie_processes | 2

pg_sys_network_info()
This interface allows the user to get network interface information.

postgres=# SELECT * FROM pg_sys_network_info(); -[ RECORD 1 ]---+------------- interface_name | lo0 ip_address | 127.0.0.1 tx_bytes | 281804800 tx_packets | 882164 tx_errors | 0 tx_dropped | 0 rx_bytes | 281804800 rx_packets | 882164 rx_errors | 0 rx_dropped | 0 link_speed_mbps | 0 -[ RECORD 2 ]---+------------- interface_name | en0 ip_address | 192.168.0.2 tx_bytes | 3770546176 tx_packets | 13311181 tx_errors | 1164 tx_dropped | 0 rx_bytes | 7829563392 rx_packets | 9070534 rx_errors | 0 rx_dropped | 0 link_speed_mbps | 0 -[ RECORD 3 ]---+------------- interface_name | utun4 ip_address | 172.24.64.89 tx_bytes | 5830656 tx_packets | 27350 tx_errors | 0 tx_dropped | 0 rx_bytes | 75328512 rx_packets | 124340 rx_errors | 0 rx_dropped | 0 link_speed_mbps | 0

pg_sys_cpu_memory_by_process()
This interface allows the user to get the CPU and memory information for each process ID.

postgres=# SELECT * FROM pg_sys_cpu_memory_by_process(); -[ RECORD 1 ]---------+----------------- pid | 62633 name | postgres running_since_seconds | cpu_usage | 4.48 memory_usage | 0.02 memory_bytes | 6848512 -[ RECORD 2 ]---------+----------------- pid | 62632 name | psql running_since_seconds | cpu_usage | 0 memory_usage | 0.01 memory_bytes | 2453504 -[ RECORD 3 ]---------+----------------- pid | 62594 name | Google Chrome He running_since_seconds | cpu_usage | 0 memory_usage | 0.09 memory_bytes | 31694848 ... ...

System Statistics

system_stats is a Postgres extension that provides functions to access system
level statistics that can be used for monitoring. It supports Linux, macOS and
Windows.

Note that not all values are relevant on all operating systems. In such cases
NULL is returned for affected values.

Copyright (c) 2019 - 2020, EnterpriseDB Corporation. All Rights Reserved.

Building and Installing

Linux and macOS

The module can be built using the PGXS framework:

  • Unpack the file archive in a suitable directory.
  • Ensure the PATH environment variable includes the directory containing the
    pg_config binary for the PostgreSQL installation you wish to build against.
  • Compile and install the code.

For example: 

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=1

Windows

The module built using the Visual Studio project file:

  • Unpack the extensions files in $PGSRC/contrib/system_stats

  • Set PG_INCLUDE_DIR and PG_LIB_DIR environment variables to make sure the
    PostgreSQL include and lib directories can be found for compilation. For
    example: 

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

  • Open the Visual Studio project file "system_stats.vcxproj" and build the
    project.

Installing the Extension

Once the code has been built and installed, you can install the extension in
a database using the following SQL command: 

CREATE EXTENSION system_stats;

Security

Due to the nature of the information returned by these functions, access is
restricted to superusers and members of the monitor_system_stats role which
will be created when the extension is installed. monitor_system_stats role
will not be removed during drop extension. To allow users to access
the functions without granting them superuser access, add them to the
monitor_system_stats role. For example: 

GRANT monitor_system_stats to nagios;

Functions

The following functions are provided to fetch system level statistics for all
platforms.

pg_sys_os_info

This interface allows the user to get operating system statistics.

pg_sys_cpu_info

This interface allows the user to get CPU information.

pg_sys_cpu_usage_info

This interface allows the user to get CPU usage information. Values are a
percentage of time spent by CPUs for all operations.

pg_sys_memory_info

This interface allows the user to get memory usage information. All the values
are in bytes.

pg_sys_io_analysis_info

This interface allows the user to get an I/O analysis of block devices.

pg_sys_disk_info

This interface allows the user to get the disk information.

pg_sys_load_avg_info

This interface allows the user to get the average load of the system over 1, 5,
10 and 15 minute intervals.

pg_sys_process_info

This interface allows the user to get process information.

pg_sys_network_info

This interface allows the user to get network interface information.

pg_sys_cpu_memory_by_process

This interface allows the user to get the CPU and memory information for each
process ID.

NOTE: macOS does not allow access to to process information for other users.
e.g. If the database server is running as the postgres user, this function
will fetch information only for processes owned by the postgres user.
Other processes will be listed and include only the process ID and name;
other columns will be NULL.

Detailed output of each function

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

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.

digoal's wechat

postgresqldigoal
文章转载自digoal，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论