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 (similar to the v $ active_session_history).
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 query from which the current query 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
# number of recent records held in memory
pgsentinel_ash.max_entries = 10,000
Restart PostgreSQL and create an extension:
create extension pgsentinel;
Accumulated information allows you to answer questions such as:
- What are the expectations for the session?
- 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 this 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.
