Statement load
Job details
| Name: | Statement load |
| Platform: | Mysql |
| Category: | Performance |
| Description: | This job collects the number of statements executed by the server. Including statements executed within stored programs and statements sent to the server by clients. Values from status variables Queries and Questions (from global_status table) are used. |
| Long description: | This job collects the number of statements executed by the server. Including statements executed within stored programs and statements sent to the server by clients. Values from status variables Queries and Questions (from global_status table) are used. |
| Version: | 1.0 |
| Default schedule: | 7,17,27,37,47,57 * * * |
| 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 core purpose of this dbWatch Control Center job is to gather information regarding the total number of statements executed by the server. This includes statements performed within stored programs and those sent to the server from its clients. To accomplish this, it utilizes values from status variables named ‘Queries’ and ‘Questions’, which are derived from the ‘global_status’ table in the database.
- Why: This job plays a crucial role in monitoring and analyzing the workload of the database server. By keeping track of the number of statements executed on the server over time, it aids in assessing the server’s load and performance. Any significant fluctuations in these numbers could indicate potential problems or changes in usage patterns, allowing administrators to react proactively and ensure the robust and efficient operation of the server.
- Manual checking:
To manually check this in the database, pertinent SQL commands are:
INSERT INTO dbw_statement_load_histr_last SELECT t1.variable_value, t2.variable_value, NOW() FROM information_schema.global_status t1, information_schema.global_status t2 WHERE t1.variable_name = 'Questions' AND t2.variable_name = 'Queries'Task Details
This job, titled as ‘Statement load’, is part of the group ‘com.dbwatch.job’. It operates by creating and manipulating several tables in the database, such as ‘dbw_statement_load_histr’ and ‘dbw_statement_load_histr_last’. It uses procedures to insert and update data according to the current server load.
To draw a clear picture of server load, it collects data at regular intervals defined by the ‘default-schedule’. The collected data includes the ‘Queries’ and ‘Questions’ status variables from the ‘global_status’ table in the information schema database. It also compares the current values with the previous ones to calculate the load per second and the client load per second, which is then inserted into the ‘dbw_statement_load_histr’ table for historical tracking.
Dependencies
The task is dependent on the following internal entities:
- dbw_statement_load: Dependence on itself
- dbw_statement_load_histr: Dependent table
- dbw_statement_load_histr_last: Dependent table
If any error occurs during the execution of the task, all the created and manipulated tables are dropped for a clean job re-run next time.
Report
The job also includes a report template titled ‘Statement Load’, which presents the analyzed server load data in a chart format, making the results easier to comprehend. The chart displays the number of statements executed within stored programs and those sent by the server’s clients per second.