PostgreSQL active session history - new pgsentinel extension

Company pgsentinel released pgsentinel extension of the same name (github repository), which adds the pg_active_session_history view to PostgreSQL - the history of active sessions (similar to Oracle's v$active_session_history).

In fact, these are just every second snapshots from pg_stat_activity, but there are important points:

1. All accumulated information is stored only in RAM, and the amount of memory consumed is regulated by the number of the last stored records.
2. The queryid field is added - the same queryid from the pg_stat_statements extension (pre-installation required).
3. The top_level_query field is added - the text of the query from which the current query was called (in the case of using pl/pgsql)

Full list of pg_active_session_history fields:

      column | Type ------------------+-------------------------- ash_time | timestamp with time zone dated | oid dataname | text pid | integer usesysid | oid usename | text application_name | text client_addr | text client_hostname | text client_port | integer backend_start | timestamp with time zone xact_start | timestamp with time zone query_start | timestamp with time zone state_change | timestamp with time zone wait_event_type | text wait_event | text state | text backend_xid | xid backend_xmin | xid top_level_query | text query | text queryid | bigint backend_type | text                     

There is no ready package for installation yet. It is proposed to download the source code and build the library yourself. First you need to install the “devel” package for your server and set the path to pg_config in the PATH variable. We collect:

cd pgsentinel/src
make
make install

Add parameters to postgres.conf:

shared_preload_libraries = 'pg_stat_statements,pgsentinel'
track_activity_query_size = 2048
pg_stat_statements.track = all

# number of recent records held in memory
pgsentinel_ash.max_entries = 10000

Reload PostgreSQL and create an extension:

create extension pgsentinel;

The accumulated information allows us to answer questions such as:

• What expectations did the sessions spend the most time on?
• Which sessions were the most active?
• What requests were the most active?

You can, of course, get answers to these questions using SQL queries, but it is more convenient to see this clearly on the chart, highlighting the time intervals of interest with the mouse. You can do it with a free program PASH Viewer (you can download the collected binaries in the section Releases).

At startup, PASH-Viewer (starting from version 0.4.0) checks for the presence of the pg_active_session_history view, and if it exists, it loads the entire accumulated history from it and continues reading new incoming data, updating the graph every 15 seconds.

Source: habr.com