The team at Enterprise DB is pleased to announce the release of its new system_stats extension for Postgres and EDB Postgres Advanced Server.
What is system_stats extension?
The system_stats extension is a library of stored procedures that allow users to access system-level statistics for monitoring Postgres activity. These procedures reveal a variety of system metrics to the database server.
By using system stats, which allows you to review information about system performance as part of your database monitoring without the need for adding a separate third-party application. EDB has made the extension open source and released it under the PostgreSQL License.
Why should you monitor PostgreSQL database activity?
There are many reasons why it is advisable to actively monitor database activity, particularly if there are limitations to the availability of resources or if the risk of failure is not an option.
Monitoring is critical for optimizing your database’s performance, as well as guaranteeing availability and security. A sudden spike in disk usage, for example, could indicate an abnormal increase in errors logged in the log file or an issue with cache configuration. Similarly, an increase in memory usage may indicate the need to adjust configuration parameters like shared_buffers or work_mem, which allocate available memory, to continue operating smoothly. Efficient monitoring allows you to catch these problems before they grow out of control or the database fails.
Postgres offers a selection of standard native monitoring tools, such as its Statistics Collector subsystem.
What is the Statistics Collector in the PostgreSQL database?
The Statistics Collector is a tool that offers very helpful insights into database activity such as tracking accesses to tables, the number of rows in each table, time spent executing user-defined functions, information about vacuum and analyzes actions, and so on.
These statistics pertain mostly to monitoring the size of data and the processing of that data. The system_stats extension unlocks valuable additional system metrics—specifically, information about memory, network, CPU usage, disk space, and much more.
What are the system_stats Functions?
The sytem_stats extension includes a number of helpful functions that let you see system information and how resources are being utilized. Each function returns parameters detailing aspects of the particular component of the function monitors.
These parameters are listed in the examples given for each function below.
- #1 pg_sys_os_info()
This procedure provides the user with statistics about the operating system.
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
- #2 pg_sys_cpu_info()
This procedure provides the user with information about the CPU.
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
- #3 pg_sys_cpu_usage_info()
This procedure provides the user with information about CPU usage. Values are given as 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 |
- #4 pg_sys_memory_info()
This procedure provides the user with information about memory usage. All the values are given 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 |
- #5 pg_sys_io_analysis_info()
This procedure provides the user with 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
- #6 pg_sys_disk_info()
This procedure provides the user with disk information, such as total, used, and free space.
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
- #7 pg_sys_load_avg_info()
This procedure allows the user to see 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
- #8 pg_sys_process_info()
This procedure provides the user with information on processes.
postgres=# SELECT * FROM pg_sys_process_info();
-[ RECORD 1 ]------+----
total_processes | 531
running_processes | 528
sleeping_processes | 0
stopped_processes | 0
zombie_processes | 2
- #9 pg_sys_network_info()
This procedure provides the user with 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
- #10 pg_sys_cpu_memory_by_process()
This interface provides the user with information about CPU and memory for each process ID. (Note: macOS does not allow access to process information for other users. Only information for processes owned by the user will be fetched. Other processes will be included but only name and process ID will be shown.)
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
...
...
The Security of system_stats Extension
Because of the potentially sensitive nature of the information, these functions make available, their use is restricted to superusers and members of the monitor_system_stats role, which is created when the extension is installed. To allow users other than superusers access to the extension, simply grant them the monitor_system_stats role. For example:
GRANT monitor_system_stats TO user;
The Installation of system_stats Extension
The extension is currently supported on Linux, macOS, and Windows. Please note that not all statistics are available or relevant on all platforms; if this is the case you may see some NULL values being returned.
The code and a source release can be found on Github; please see the README for more information and detailed instructions. RPMs are also available at yum.postgresql.org.
How to Install system_stats Extension in Linux and macOS
The module can be built using the PGXS framework, PostgreSQL’s build infrastructure for extensions:
- Download and 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
How to Install system_stats Extension in Windows
The module can be built using the Visual Studio project file:
Unpack the extensions files in $PGSRC/contrib/system_stats.
- Set the PG_INCLUDE_DIR and PG_LIB_DIR environment variables to ensure 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.
Once you have built and installed the code, simply run the SQL command:
CREATE EXTENSION system_stats;
The extension should now be installed in your database.
Feedback
You are welcome to submit pull requests for additional platform support, additional functions, or bug fixes. Bug reports are welcome too. We hope you will find this extension to be a valuable addition to your monitoring toolkit.