Lock statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to collect and analyze lock statistics on a MySQL database system.
• Why: This job is crucial as it tracks and monitors both table and row level locks in the database system. Such monitoring is significant since it allows for early detection of any bottlenecks or potential issues which could impact the database performance. Understanding how the database is handling locks can provide insights into potential optimizations and improvements.
• Manual Checking: You can manually check the statistics by running these SQL commands on the MySQL platform:

SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS 
    WHERE VARIABLE_NAME IN ('TABLE_LOCKS_IMMEDIATE', 'TABLE_LOCKS_WAITED', 
                            'INNODB_ROW_LOCK_TIME', 'INNODB_ROW_LOCK_TIME_AVG',  
                            'INNODB_ROW_LOCK_WAITS'); 

Detailed Description

• This job works by periodically collecting statistics from the database related to lock operations. This includes the number of times the server was able to immediately acquire a table lock, the instances it had to wait for a lock, and pertinent row lock information.

• The ‘dbw_lock_statistics’ procedure is performed, which collects and compares the current and previous lock data stats. It then updates the ‘dbw_lock_statistics_last’ table with the recent data, and inserts any differences into the ‘dbw_lock_statistics_table_histr’ and ‘dbw_lock_statistics_row_histr’ tables, which store the historical lock statistics.

• The information gathered by this job is assembled into a report, featuring two graphs showing historical data about table and row lock statistics over time.

Cleanup and Dependencies

• This job relies on the presence of a table and procedure namely ‘dbw_lock_statistics’, ‘dbw_lock_statistics_table_histr’, ‘dbw_lock_statistics_row_histr’, and ‘dbw_lock_statistics_last’ within the database.

• In case of failure, the task is designed to clean up after itself and mitigate any potential errors, by deleting the tables and dropping the procedure used in the task.

Report

• Title: Lock Statistics
• Description: Graphical representation of lock statistics both at the table and row levels. The left y-axis represents the instances where locks were acquired immediately; the right y-axis indicates instances where the system had to wait to achieve a lock.