A quick post on an excellent new extension from EDB - system_stats, PostgreSQL views into system processes, memory, cpu, disk - highly recommended. this test was run against an PostgreSQL 11.4 instance
You can find the installation instructions on GitHub here
You can now find out anything you need to know about your systems right within PostgreSQL
- pg_sys_os_info() - top level o/s information:
# select * from pg_sys_os_info(); -[ RECORD 1 ]-------+---------------------------------- name | "Red Hat Enterprise Linux" + | version | Linux 3.10.0-1127.18.2.el7.x86_64 host_name | linux2 domain_name | (none) handle_count | 9280 process_count | 336 thread_count | 951 architecture | x86_64 last_bootup_time | os_up_since_seconds | 536186
- pg_sys_cpu_info() - cpu information:
# select * from pg_sys_cpu_info(); -[ RECORD 1 ]------+------------------------------------------------ vendor | GenuineIntel description | GenuineIntel model 44 family 6 model_name | Intel(R) Xeon(R) CPU X5690 @ 3.47GHz processor_type | logical_processor | 0 physical_processor | 4 no_of_cores | 4 architecture | x86_64 clock_speed_hz | 3457989120 cpu_type | cpu_family | byte_order | l1dcache_size | 32 l1icache_size | 32 l2cache_size | 256 l3cache_size | 12288
- pg_sys_cpu_usage_info() - current cpu usage as % time:
# select * from pg_sys_cpu_usage_info(); -[ RECORD 1 ]-------------------+------ usermode_normal_process_percent | 0 usermode_niced_process_percent | 0 kernelmode_process_percent | 25.42 idle_mode_percent | 74.58 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() - memory usage:
# select * from pg_sys_memory_info(); -[ RECORD 1 ]----+------------ total_memory | 33557921792 used_memory | 33100804096 free_memory | 457117696 swap_total | 22544379904 swap_used | 7929856 swap_free | 22536450048 cache_total | 15520219136 kernel_total | kernel_paged | kernel_non_paged | total_page_file | avail_page_file |
- pg_sys_io_analysis_info() - io stats by block device:
# select * from pg_sys_io_analysis_info(); -[ RECORD 2 ]-+------------- device_name | sda total_reads | 201794 total_writes | 7619123 read_bytes | 7313778688 write_bytes | 112596156928 read_time_ms | 1012402 write_time_ms | 9058630 -[ RECORD 3 ]-+------------- device_name | sda1 total_reads | 164 total_writes | 47 read_bytes | 14549504 write_bytes | 15634432 read_time_ms | 1090 write_time_ms | 1038 -[ RECORD 4 ]-+------------- device_name | sda2 total_reads | 201599 total_writes | 6057521 read_bytes | 7297517056 write_bytes | 112580522496 read_time_ms | 1011278 write_time_ms | 8267410
- pg_sys_disk_info() - disk space/inode info:
# select * from pg_sys_disk_info(); -[ RECORD 3 ]----+----------------------------- mount_point | /boot file_system | /dev/sda1 drive_letter | drive_type | file_system_type | xfs total_space | 1063256064 used_space | 295636992 free_space | 767619072 total_inodes | 524288 used_inodes | 352 free_inodes | 523936 -[ RECORD 4 ]----+----------------------------- mount_point | /home file_system | /dev/mapper/rhel_linux2-home drive_letter | drive_type | file_system_type | xfs total_space | 34342961152 used_space | 3981733888 free_space | 30361227264 total_inodes | 16777216 used_inodes | 61435 free_inodes | 16715781
- pg_sys_load_avg_info() - load average:
# select * from pg_sys_load_avg_info(); -[ RECORD 1 ]------------+----- load_avg_one_minute | 2.31 load_avg_five_minutes | 2.72 load_avg_ten_minutes | 2.88 load_avg_fifteen_minutes |
- pg_sys_process_info() - current process allocation:
# select * from pg_sys_process_info(); -[ RECORD 1 ]------+---- total_processes | 340 running_processes | 3 sleeping_processes | 337 stopped_processes | 0 zombie_processes | 0
- pg_sys_network_info() - network interface information:
# select * from pg_sys_network_info(); -[ RECORD 1 ]---+-------------- interface_name | lo ip_address | 127.0.0.1 tx_bytes | 2550855698 tx_packets | 29927366 tx_errors | 0 tx_dropped | 0 rx_bytes | 2550855698 rx_packets | 29927366 rx_errors | 0 rx_dropped | 0 link_speed_mbps | 0 -[ RECORD 2 ]---+-------------- interface_name | eth0 ip_address | 10.0.0.1 tx_bytes | 716279960 tx_packets | 9927697 tx_errors | 0 tx_dropped | 0 rx_bytes | 1033216455 rx_packets | 11163185 rx_errors | 0 rx_dropped | 432 link_speed_mbps | 10000
- pg_sys_cpu_memory_by_process() - an excellent function - cpu/memory usage by process:
# select * from pg_sys_cpu_memory_by_process() where name like '%post%' limit 5; -[ RECORD 1 ]---------+----------- pid | 45396 name | (postgres) running_since_seconds | 527510 cpu_usage | 7.27 memory_usage | 0.15 memory_bytes | 49901568 -[ RECORD 2 ]---------+----------- pid | 45413 name | (postgres) running_since_seconds | 527510 cpu_usage | 0 memory_usage | 0.02 memory_bytes | 7618560 -[ RECORD 3 ]---------+----------- pid | 45427 name | (postgres) running_since_seconds | 527510 cpu_usage | 0 memory_usage | 0.13 memory_bytes | 42172416
