Lock statistics


Job details

Name: Lock statistics
Platform: Mariadb
Category: Performance
Description: Collects lock statistics
Long description: This task collects lock data statistics
Version: 2.11
Default schedule: 55,25 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & is_mariadb_branch=‘1′]/.[hasengine=‘YES’ & global_status_performance_schema = ‘0′ & global_status_information_schema = ‘1′ ]

Parameters

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

Job Summary

SELECT table_locks_immediate, table_locks_waited FROM information_schema.GLOBAL_STATUS WHERE variable_name IN ('TABLE_LOCKS_IMMEDIATE', 'TABLE_LOCKS_WAITED');
SELECT row_lock_waits, row_lock_time FROM information_schema.GLOBAL_STATUS WHERE variable_name IN ('INNODB_ROW_LOCK_WAITS', 'INNODB_ROW_LOCK_TIME');

Tables and Dependencies

Object Description
dbw_lock_statistics This is a stored procedure that is invoked as part of this job’s execution.
dbw_lock_statistics_table_histr This table keeps historical data about table locks immediately acquired and those waited upon.
dbw_lock_statistics_row_histr This table stores historical data on row lock waits and the time spent waiting.
dbw_lock_statistics_last A table to store the last recorded statistics for immediate comparisons and updates.

Clean-up Actions

In case of job failure, the following clean-up actions are performed:

Implementation and Reporting

Implementation: This job involves creating necessary tables and the stored procedure for collecting the lock statistics data. It captures the changes and updates from the GLOBAL_STATUS in the information schema and records them into historical tables at regular intervals.

Reporting: Dedicated reports generated by dbWatch Control Center include:

This structured capture and presentation of data allow for easy monitoring and quick reaction to potential issues in database lock management.