Session load

Job details

Name:	Session load
Platform:	Postgres
Category:	Performance Performance
Description:	Gathers session load statistics.
Long description:	Task gathers session load statistics.
Version:	1.8
Default schedule:	0,10,20,30,40,50 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘postgres’]/.[hasengine=‘YES’ & is_awsrds = ‘0′ & newer_than_ninefour = 1]

Parameters

Name	Default value	Description
keep data for	7	The number of days to keep the data for.
warning threshold	80	Warning threshold percentage of maximum number of concurrent connections to the database server (parameter: “max_connections”).
alarm threshold	95	Alarm threshold percentage of maximum number of concurrent connections to the database server (parameter: “max_connections”).

Job Summary

Purpose: The job “Session load” is designed to collect and manage session load statistics on a PostgreSQL server. This includes tracking the total sessions, active and idle sessions, and comparing these figures against the maximum allowed connections to identify potential load issues or overloads.
Why: Monitoring session load is crucial for maintaining database performance and stability. By understanding how close the server is to reaching its connection limit, administrators can take preemptive actions to mitigate risks such as system slow-downs or crashes due to excessive session loads. This job uses thresholds to provide alerts and ensure performance issues can be addressed proactively.
Manual checking: You can check session statistics manually in the database by issuing the following SQL commands:

select count(*), state from pg_stat_activity group by state;

Implementation Overview

The job includes SQL functions and tables creation for storing historical session data and aggregated statistics. These allow for the monitoring and analyzing trends over time, and facilitating detailed reports on session metrics.
Alerting Logic: Based on predefined thresholds (warning and alarm), the system categorizes the current session usage to generate corresponding alerts. This involves calculating the percentage usage of sessions compared to the maximum configured connections.

Key Components

Tables

Table Name	Description
dbw_session_load_histr	Stores individual session count data including timestamps.
dbw_session_load_aggr_histr	Contains aggregated historical data such as average, max, and min session counts per time period.

Key Functions

Function Name	Description
dbw_session_load	Main procedure to execute the session monitoring logic, includes data aggregation and cleanup tasks.

Reports and Presentations

“Session Statistics”: Visual and data report depicting the total, active sessions and maximum allowed connections over time.
“Current Session Information”: Displays the most recent session details, showing users, IP addresses, and connection times.
“Parameter and Max Values”: This report shows the current setting for maximum connections and the highest session count historically observed.
“Aggregated Session History”: Provides a day-by-day historical view of session averages and peaks, useful for identifying trends or specific days with unusual activity.

Dependencies and Cleanup

The job ensures that historical data does not consume unnecessary space by periodically cleaning up older records based on the ‘keep data for’ parameter which indicates the number of days to retain the historical session data.
Dependencies include various procedural calls and checks which ensure data consistency and integrity during the monitoring process.

System and Version Compatibility

This job is specifically designed for PostgreSQL databases and is engineered to run with instances that are not hosted on AWS RDS and are version 9.4 or newer, ensuring compatibility with current database features and requirements.

Conclusion

By continuously monitoring the session load, the dbWatch job “Session load” aids in maintaining optimal database performance and availability, thereby helping avoid potential overloads and facilitating better resource planning and management.