No description
Find a file
Cédric Villemain 4fb696c023 Start with version 0.1
And gitignore for built the manpage.
2024-10-24 08:47:42 +02:00
expected Support multiple triggers to poll 2024-10-24 08:47:42 +02:00
sql Support multiple triggers to poll 2024-10-24 08:47:42 +02:00
.gitignore Start with version 0.1 2024-10-24 08:47:42 +02:00
Makefile Start with version 0.1 2024-10-24 08:47:42 +02:00
pg_psi--0.1.sql Start with version 0.1 2024-10-24 08:47:42 +02:00
pg_psi.c Support multiple triggers to poll 2024-10-24 08:47:42 +02:00
pg_psi.control Start with version 0.1 2024-10-24 08:47:42 +02:00
README.pg_psi.md Support multiple triggers to poll 2024-10-24 08:47:42 +02:00

title section header
pg_psi 1 PostgreSQL extension to interface with Linux Pressure Stall Information (PSI)

NAME

pg_psi — PostgreSQL extension to read and monitor Linux Pressure Stall Information (PSI) metrics for CPU, memory, and I/O.

SYNOPSIS

CREATE EXTENSION pg_psi;
SELECT * FROM pg_psi_read(text);
SELECT pg_psi_poll(resource text, scope text, threshold int, "window" int);
SELECT psi.pg_psi_poll((SELECT array_agg((resource, scope, threshold, "window")) FROM psi.psi_trigger WHERE scenario = 'light'));

DESCRIPTION

The pg_psi extension provides access to the Linux Pressure Stall Information (PSI) metrics available in the /proc/pressure/* files. PSI metrics provide valuable information about system resource contention, such as CPU, memory, and I/O stalls, which helps identify bottlenecks and system load issues.

This extension includes functions to read PSI metrics directly, set up triggers for threshold monitoring, and continuously poll these metrics to detect and report system pressure events.

FUNCTIONS

pg_psi_read

pg_psi_read(resource_type TEXT)
RETURNS TABLE (
    scope TEXT,
    avg10 REAL,
    avg60 REAL,
    avg300 REAL,
    total BIGINT
)

Reads PSI metrics for the specified resource type.

Parameters

  • resource_type: The resource type to read PSI metrics from. Valid values are 'cpu', 'memory', or 'io'.

Returns

A table with the following columns:

  • scope: The pressure scope, either 'some' for partial stalls or 'full' for complete stalls.
  • avg10: Average stall time over the last 10 seconds.
  • avg60: Average stall time over the last 60 seconds.
  • avg300: Average stall time over the last 300 seconds.
  • total: Total accumulated stall time (in microseconds) since boot.

Example

SELECT * FROM pg_psi_read('cpu');

This returns CPU pressure stall information for the system.

pg_psi_poll

pg_psi_poll(resource TEXT, scope TEXT, threshold INT, "window" INT)
RETURNS void

Continuously monitors PSI events and triggers based on specified conditions.

Parameters

  • resource: Resource type ('cpu', 'memory', or 'io').
  • scope: The stall scope, either 'some' for partial stalls or 'full' for complete stalls.
  • threshold: Stall time threshold in microseconds.
  • window: Time window in microseconds over which the stall threshold is evaluated.

Example

SELECT pg_psi_poll('cpu', 'some', 500000, 2000000);

This monitors CPU stalls where tasks are stalled for at least 500 milliseconds in any 2-second window.

TABLES

psi_trigger

The psi_trigger table defines monitoring scenarios for different resource types and stall thresholds. This table is designed to store configurations for various pressure levels (light, moderate, high), allowing the system to be polled based on predefined conditions.

Schema

CREATE TABLE psi_trigger (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    scenario TEXT NOT NULL,
    resource TEXT NOT NULL,
    scope TEXT NOT NULL,
    threshold INT NOT NULL,
    "window" INT NOT NULL,
    comment TEXT
);

Columns

  • scenario: A label to identify the scenario (e.g., 'light', 'moderate', 'high').
  • resource: The resource to monitor ('cpu', 'memory', 'io').
  • scope: The pressure scope ('some' for partial stalls, 'full' for complete stalls).
  • threshold: Stall time threshold in microseconds.
  • window: The time window in microseconds over which the threshold is evaluated.
  • comment: A description of the scenario.

Example Entries

INSERT INTO psi_trigger (scenario, resource, scope, threshold, "window", comment)
VALUES
    ('light', 'cpu', 'some', 100000, 2000000, 'Triggers if tasks are stalled for 100ms in a 2-second window.'),
    ('moderate', 'io', 'full', 200000, 2000000, 'Triggers on 200ms full I/O stall in 2 seconds.');

These entries define "light" and "moderate" scenarios for CPU and I/O monitoring, respectively.

EXAMPLES

Using psi_trigger for polling

You can use the entries in the psi_trigger table to define complex monitoring scenarios and initiate polling based on them.

SELECT psi.pg_psi_poll((SELECT array_agg((resource, scope, threshold, "window"))
                        FROM psi.psi_trigger WHERE scenario = 'light'));

This command polls PSI events for all resources that are defined under the 'light' scenario in the psi_trigger table.

AUTHOR

Cédric Villemain, Data Bene, 2024

LICENSE

This extension is licensed under the PostgreSQL License.