system_stats
A Postgres extension for exposing system metrics such as CPU, memory and disk information.
It is a fork of EnterpriseDB/system_stats, streamlining support for Windows OS
Building and Installing
git clone https://github.com/sak1/system_stats.git
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
CREATE EXTENSION
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.
Use Case
pg_sys_os_info
("""CentOS Linux 7 (Core)"" +
","Linux xxxxxxxxxxx",bingo,"(none)",1024,107,123,x86\_64,,445)
(1 row)
- Name
- Version
- Host name
- Domain name
- Handle count
- Process count
- Thread count
- Architecture
- Last bootup time
- Uptime in seconds
pg_sys_cpu_info
postgres=# select pg\_sys\_cpu\_info();
(GenuineIntel,"GenuineIntel model 94 family x","xxxxxx HQ CPU @ xxx GHz",,0,2,2,x86\_64,xxxxxxxxxx,,,,32,
32,256,8192)
(1 row)
- 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()
postgres=# select pg\_sys\_cpu\_usage\_info();
(0,0,0,100,0,0,0,,,,)
(1 row)
- 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
postgres=# select pg\_sys\_memory\_info();
(3973169152,353587200,3619581952,1073737728,0,1073737728,174931968,,,,,)
(1 row)
- 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
postgres=# select pg\_sys\_io\_analysis\_info();
(sda,5420,463,176528896,10638336,9934,2397)
(sda1,2036,4,27233792,2097152,6758,7)
(sda2,3354,419,147714048,8541184,3146,2297)
(sr0,0,0,0,0,0,0)
(dm-0,3198,522,143286272,8855040,3026,3040)
(dm-1,88,0,2256896,0,71,0)
(6 rows)
- 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
postgres=# select pg\_sys\_disk\_info();
(/,/dev/mapper/centos-root,,,xfs,xxxxxxxxxx,3472044032,5103210496,4192256,88970,4103286)
(/boot,/dev/sda1,,,xfs,1063256064,203292672,859963392,524288,334,523954)
(2 rows)
- 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
postgres=# select pg\_sys\_load\_avg\_info();
(0,0.06,0.05,)
(1 row)
- 1 minute load average
- 5 minute load average
- 10 minute load average
- 15 minute load averag
pg_sys_process_info
postgres=# select pg\_sys\_process\_info();
(107,1,106,0,0)
(1 row)
- Number of total processes
- Number of running processes
- Number of sleeping processes
- Number of stopped processes
- Number of zombie processes
pg_sys_network_info
postgres=# select pg\_sys\_network\_info();
(lo,127.0.0.1,0,0,0,0,0,0,0,0,0)
(enp0s3,192.168.0.172,94815,558,0,0,277595,888,0,0,1000)
(2 rows)
- 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
postgres=# select pg\_sys\_cpu\_memory\_by\_process();
(1,"(systemd)",445,0,0.17,6770688)
(2,"(kthreadd)",445,0,0,0)
(4,"(kworker/0:0H)",445,0,0,0)
(5,"(kworker/u4:0)",445,0,0,0)
(6,"(ksoftirqd/0)",445,0,0,0)
(7,"(migration/0)",445,0,0,0)
(8,"(rcu\_bh)",445,0,0,0)
(9,"(rcu\_sched)",445,0,0,0)
(10,"(lru-add-drain)",445,0,0,0)
(11,"(watchdog/0)",445,0,0,0)
(12,"(watchdog/1)",445,0,0,0)
(13,"(migration/1)",445,0,0,0)
(14,"(ksoftirqd/1)",445,0,0,0)
(15,"(kworker/1:0)",445,0,0,0)
(16,"(kworker/1:0H)",445,0,0,0)
(18,"(kdevtmpfs)",445,0,0,0)
......
(1395,"(bash)",381,0,0.05,2154496)
(1415,"(kworker/1:3)",290,0,0,0)
(1507,"(su)",231,0,0.06,2510848)
(1508,"(bash)",231,0,0.05,2142208)
(1526,"(postgres)",189,0,0.35,14098432)
(1527,"(postgres)",189,0,0.03,1167360)
......
(1533,"(psql)",184,0,0.05,2134016)
(1534,"(postgres)",184,9.52,0.26,10158080)
(107 rows)
- PID of the process
- Process name
- CPU usage in bytes
- Memory usage in bytes
- Total memory used in bytes
