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
- Purpose: The purpose of this job is to collect and monitor lock statistics, specifically targeting instances that are part of the MariaDB branch. This job focuses on the performance category, making it crucial for database health and optimization.
- Why: It is important because monitoring lock statistics helps in identifying slowdowns and potential deadlocks in the database, which can cause significant performance degradation. By efficiently tracking and analyzing lock waits and immediate table lock acquisitions, proactive measures can be implemented to solve potential issues.
- Manual checking: You can check lock statistics manually in the database by issuing the following SQL commands to the database:
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:
- “drop table dbw_lock_statistics_table_histr”
- “drop table dbw_lock_statistics_row_histr”
- “drop table dbw_lock_statistics_last”
- “drop procedure dbw_lock_statistics”
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:
- “Table lock statistics” – Illustrating the number of table locks that were acquired immediately vs. those that had to be waited for.
- “Row lock waits”- Showcasing the number of times the server had to wait for a row lock.
- “Row lock wait time” – Detailing the amount of time spent waiting for row locks.
This structured capture and presentation of data allow for easy monitoring and quick reaction to potential issues in database lock management.