Lock statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to collect and manage database lock statistics for MariaDB. It helps in monitoring different locking mechanisms such as table locks and row locks, which are crucial for understanding database performance, especially in high concurrency environments.
• Why: This job is important to observe how often locks are immediately granted versus how often the database has to wait. Understanding these metrics helps in diagnosing performance issues related to locking in MariaDB databases. It can facilitate proactive tuning and can provide insights to avoid potential deadlocks or long wait times that can affect application performance.
• Manual checking: You can check these statistics manually in the database by issuing the following SQL commands:

SELECT table_locks_immediate, table_locks_waited FROM dbw_lock_statistics_last;

SELECT row_lock_waits, row_lock_time FROM dbw_lock_statistics_last;

Tables and Stored Procedures Created

Cleanup and Maintenance Instructions

• The job includes cleanup logic to ensure all historical data older than a specified number of days (default is 7 days) is deleted from the historical tables.
• If a task fails, the following cleanup operations 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 Details

• The implementation primarily consists of SQL statements to create necessary tables and a stored procedure to collect, update, and maintain the lock statistics.
• It inserts or updates lock statistics into three main tables as seen in the scheduled execution of the stored procedure.
• It also maintains an initial cleanup activity to clear out initial dummy data, ensuring only relevant statistics are kept.

Dependency Checks and Install Conditions

• This job checks for MariaDB instances with the performance schema enabled – an essential requirement for gathering lock statistics.
• The job is set for automatic express installation if the conditions are met, ensuring no manual handling during deployment on suitable MariaDB instances.

Reporting

• The job setup includes report templates for visual representation of both table and row lock statistics.
• Reports present historical data for immediate and waited table locks, and the number of waits and time waited for row locks, offering insights over time to observe trends or spikes that may require attention.
• Visual graphs are generated for quick assessment, providing an easy understanding of the lock behavior across different periods.

This comprehensive setup of the monitoring job in dbWatch Control Center ensures efficient tracking and management of lock statistics relevant to performance optimization and troubleshooting within MariaDB environments.