Session load
Job details
| Name: | Session load |
| Platform: | Mysql |
| 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_mysql_branch=‘1′]/.[hasengine=‘YES’ & use_global_variables_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 monitoring job, named as “Session load”, is to track and record the number of active and inactive MySQL database sessions over time. It belongs to the “Performance” category and is especially crucial for MySQL databases, as identified by the specified query logic.
- How: The job achieves this by triggering the “dbw_session_load” SQL procedure which aggregates data related to the total number of processes (both active and inactive) per day and conserves the data to the “dbw_session_load_histr” and “dbw_session_load_aggr_histr” tables. It also cleans up old data based on the set parameter “keep data for”.
- Why: This job is essential as it helps in assessing the load on the database, which plays a significant role in performance optimization. It provides insights about total and active session load which can help in preventive measures if thresholds are reached.
- Manual checking: By issuing these SQL commands, you can manually check the session load in the database:
SELECT number_of_processes_total, number_of_processes_active, histr_date FROM dbw_session_load_histr ORDER BY histr_date Metadata
This job falls under the category “Performance”.
Dependencies
The job has three dependencies:
- Main procedure “dbw_session_load”
- Dependency table “dbw_session_load_histr”
- Dependency table “dbw_session_load_aggr_histr”
These are necessary for the successful execution of the job.
Job Implementation
The job is implemented by creating two tables (“dbw_session_load_histr” and “dbw_session_load_aggr_histr”) and one procedure (“dbw_session_load”). “dbw_session_load” procedure collects details of total and active sessions and stores that data in the mentioned tables.
Cleaning Up On Failure
In case the job fails, the system is set to follow a cleanup process that includes dropping the two data tables and the main procedure used in the job.
Report Template
The job also generates a report template named “Session load”, under which it presents a series of tables, graphs, and details for total and active sessions over time. These visual aids assist in tracking the boosted session load and making analytics-driven database decisions.
Job Upgrade
At the end of the file, there’s an upgrade section that contains necessary SQL commands for changing the version of the job from 2.21 to 2.22.