PostgreSQL Active Session History - New pgsentinel extension
The pgsentinel company has released the pgsentinel extension of the same name ( github repository ), which adds the pg_active_session_history view to PostgreSQL - the history of active sessions (by analogy with v $ active_session_history).
In fact, these are simply every second snapshots of pg_stat_activity, but there are important points:
There is no ready package for installation yet. It is proposed to download the source code and build the library yourself. You first need to install a “devel” package for your server and set the path to pg_config in the PATH variable. We collect:
Add parameters to postgres.conf:
Restart PostgreSQL and create an extension:
Accumulated information allows you to answer questions such as:
You can get answers to these questions, of course, with SQL queries, but it is more convenient to see it clearly on the graph, highlighting the time intervals with the mouse. You can do this using the free program PASH-Viewer (you can download the collected binaries in the Releases section ).
When PASH-Viewer is started (starting with version 0.4.0) it checks the presence of the pg_active_session_history view and, if it exists, loads the entire accumulated history from it and continues to read new incoming data, updating the chart every 15 seconds.
In fact, these are simply every second snapshots of pg_stat_activity, but there are important points:
- All accumulated information is stored only in RAM, and the amount of memory consumed is governed by the number of last stored records.
- The queryid field is added - the same queryid from the pg_stat_statements extension (pre-installation is required).
- The top_level_query field is added - the text of the request from which the current request was called (if pl / pgsql is used)
Full list of pg_active_session_history fields:
Column | Type ------------------ + -------------------------- ash_time | timestamp with time zone datid | oid datname | 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. You first need to install a “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
# the number of recent records held in memory
pgsentinel_ash.max_entries = 10000
Restart PostgreSQL and create an extension:
create extension pgsentinel;
Accumulated information allows you to answer questions such as:
- On what expectations did the session spend the most time?
- What sessions were most active?
- What requests were most active?
You can get answers to these questions, of course, with SQL queries, but it is more convenient to see it clearly on the graph, highlighting the time intervals with the mouse. You can do this using the free program PASH-Viewer (you can download the collected binaries in the Releases section ).
When PASH-Viewer is started (starting with version 0.4.0) it checks the presence of the pg_active_session_history view and, if it exists, loads the entire accumulated history from it and continues to read new incoming data, updating the chart every 15 seconds.