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 = ‘0′ & global_status_information_schema = ‘1′ ] |
Parameters
| Name | Default value | Description |
|---|---|---|
| keep data for | 7 | The number of days to keep the data for. |
Job Summary
- Purpose: The purpose of this job is to monitor and record the number of active and inactive database sessions over time.
- Why: This job is important because it helps in identifying the session load patterns and potential performance bottlenecks in a MariaDB server environment. Continuous monitoring of session statistics can provide crucial insights that enable database administrators to make informed decisions regarding capacity planning and performance tuning.
- Manual checking: You can check this manually in the database by issuing the following SQL commands:
select number_of_processes_total, number_of_processes_active, histr_date from dbw_session_load_histr;Job Description
The monitoring job “Session Load” implemented in the dbWatch Control Center is specifically designed for MariaDB databases that support the information schema but not the performance schema. This job records the number of total and active sessions periodically and provides aggregated data over daily intervals. It is crucial for maintaining optimal database performance and helps in troubleshooting issues related to abrupt changes in session numbers.
Implementation Details
Tables created:
| Table Name | Description |
|---|---|
| dbw_session_load_histr | Stores history of total and active sessions along with timestamps. |
| dbw_session_load_aggr_histr | Stores aggregated session data including average, maximum, and minimum number of total and active sessions per day. |
Procedures implemented:
| Procedure Name | Description |
|---|---|
| dbw_session_load | Main procedure which inserts current session count into history tables and aggregates data daily. Also responsible for old data cleanup based on the retention policy. |
Dependencies
- Main procedure: dbw_session_load
- Dependency table: dbw_session_load_histr, dbw_session_load_aggr_histr
Cleanup Strategy on Failure
- drop table dbw_session_load_aggr_histr
- drop table dbw_session_load_histr
- drop procedure dbw_session_load
Reporting
The dbWatch report template that accompanies this job provides insights into session statistics with the following presentations:
- “Session Statistics” – Displays total and active sessions over time with visualization in a category chart.
- “Average Session Statistics” – Shows aggregated session data (average, maximum, and minimum) categorized by total and active sessions with date-based categorization, helping to understand the general session load behavior.
Each report section is designed to provide comprehensive insights into session activities, offering both numerical data and graphical representations for easier interpretation of the session trends.
Versioning and Upgrades
- Current version: 2.22
- Previous version: 2.21
- Upgrade difference: Enhanced procedure logic and updated task version.
In summary, the “Session Load” job is a crucial tool for database administrators managing MariaDB environments, providing vital data on session loads that are directly linked to database performance and health.