System_Stats - a great new extension from EDB

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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