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_performance_schema = ‘1′ ]

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

Purpose: The purpose of this job is to collect and record the number of statements executed by the MySQL server. Statements considered include those executed within stored programs and statements sent via clients.

Why: This job is essential as it provides an evaluative measurement of the server’s performance and workload. Understanding the frequency and volume of executed statements can help indicate the efficiency, concurrently identifying any potential problems in real-time.

Manual Checking: The frequency and number of executed statements can be manually checked in the MySQL database by issuing SQL commands. Here are some of the relevant commands:

"Q1: SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Queries';"

"Q2: SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Questions';"

"Q3: SELECT queries, questions, histr_date FROM dbw_statement_load_histr_last;"

Statement Load: This job uses the ‘mysql_statement_load’ artifact to collect the number of executed statements from the global_status table in the MySQL database. These statements include those executed within stored programs and those sent to it by clients.

Dependencies: This job has dependencies on the task ‘dbw_statement_load’ and the tables ‘dbw_statement_load_histr’ and ‘dbw_statement_load_histr_last’. If these tasks or tables fail, there are cleanup codes to remove these tables and the ‘dbw_statement_load’ procedure.

Procedures: In addition to data collection, this job contains a procedure ‘dbw_statement_load’. This procedure calculates the execution speed of statements and their frequency on the server. Additionally, it maintains the hygiene of the ‘dbw_statement_load_histr’ table by deleting entries older than a configurable number of days (default is 7 days).

Report: The job also generates a report titled ‘Statement load’, graphically presenting the statements per second sent to the server by clients and executed within stored programs in a chart format.

Implementation: The job creates two tables – ‘dbw_statement_load_histr’ and ‘dbw_statement_load_histr_last’ – to store statement load data. Additionally, it includes SQL commands to update and extract useful metrics from these tables.