Session load

Job details

Name:	Session load
Platform:	Mariadb
Category:	Performance
Description:	Records the number of active and inactive sessions over time.
Long description:	Records the number of active and inactive sessions over time.
Version:	2.22
Default schedule:	0,10,20,30,40,50 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & is_mariadb_branch=‘1′]/.[hasengine=‘YES’ & global_status_performance_schema = ‘1′ ]

Name	Default value	Description
keep data for	7	The number of days to keep the data for.

Purpose: The primary purpose of this job is to record the number of active and inactive database sessions over time. This allows for monitoring the workload and usage of the database server, particularly for MariaDB instances.
Why: This job is crucial for understanding and managing database performance and server capacity. By tracking session statistics, administrators can identify trends in database usage, diagnose performance issues, and plan for scaling operations effectively. If thresholds of sessions are breached, it may indicate potential performance bottlenecks or the need for immediate intervention to prevent server overload.
Manual checking: You can check these statistics manually in the database by issuing the following SQL commands:

SELECT * FROM dbw_session_load_histr;

SELECT * FROM dbw_session_load_aggr_histr;

“dbw_session_load”: This is the main stored procedure used to collect the session data.
“dbw_session_load_histr”: A table storing historical data about total and active sessions.
“dbw_session_load_aggr_histr”: A table storing aggregated session statistics, such as averages, maximums, and minimums.

If the job fails, the cleanup process will drop these tables and the procedure:

DROP TABLE dbw_session_load_aggr_histr;

DROP TABLE dbw_session_load_histr;

DROP PROCEDURE dbw_session_load;

The implementation involves several SQL queries and operations:

Tracking and storing the current number of connections and active connections.
Aggregating previous session data daily to compute average, maximum, and minimum sessions for both total and active categories.
Cleaning up historical data based on a predefined retention period (default is 7 days).

The job includes a report template which provides detailed visualization and data on session statistics:

“Connections Total and Active Sessions Over Time”: This charts the fluctuations in total and active sessions recorded.
“Average, Maximum and Minimum Session Statistics”: This section gives insights into the daily averages, peaks, and troughs in session counts, providing a clearer picture of the database load.

Column Name	Data Type	Purpose
number	Integer	Represents the count of sessions (total or active)
Aggregation type	String	Indicates if the data is about total sessions or active sessions
History date	Date	The date on which the sessions were recorded

Changes introduced in version 2.22 include updated procedure logic and enhancements in the job’s task values update mechanism. If upgrading from version 2.21, the procedure “dbw_session_load” will be dropped and recreated to reflect the new logic.

The job is critical in a database monitoring setup, especially for performance tuning and proactive management of database environments.