Lock statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to collect lock data statistics in MySQL databases. It collects data on both table lock and row lock statistics.
• Why: This job is important to monitor and keep track of lock activities within the database. If a large number of locks are being held for longer periods of time, it could suggest a potential performance issue, such as inefficient queries or a highly concurrent system. The statistics include details like the number of locks that were acquired immediately, the number of locks that the server had to wait for, and the time spent waiting for locks. Monitoring these metrics helps in diagnosing and resolving database performance issues.
• Manual checking: The results of this job can be manually checked in the database by running the following SQL commands:

SELECT * FROM dbw_lock_statistics_table_histr ORDER BY histr_date DESC

SELECT * FROM dbw_lock_statistics_row_histr ORDER BY histr_date DESC

SELECT * FROM dbw_lock_statistics_last

Job Details

The job is part of the dbWatch Control Center toolkit, specifically in the ‘mysql_lock_statistics’ group. Once executed, the job collects the statistics and stores them into three different tables:

• dbw_lock_statistics_table_histr: This table stores the history of table lock statistics.
• dbw_lock_statistics_row_histr: This table stores the history of row lock statistics.
• dbw_lock_statistics_last: This table stores the last collected lock statistics.

Should execution fail, the job is configured to perform a cleanup – it will drop all three tables, along with the procedure dbw_lock_statistics.

Report

The job also generates a report titled ‘Lock statistics’, which includes individual presentations for table lock statistics and row lock statistics. These presentations include dual-axis charts to represent the history of locks immediately acquired and locks waited for, as well as the number of times the server had to wait for row locks and the time it spent waiting.

The report can be manually generated by querying the historical tables (dbw_lock_statistics_table_histr and dbw_lock_statistics_row_histr), but these queries are also executed as part of the job and results are displayed in the report.