Locks held and statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and record statistics of locks held in a PostgreSQL database, manage historical lock data, and ignore specific users in lock reporting.
• Why: This job is important to ensure database performance is not degraded by excessive locking and to help identify patterns or anomalies in locking mechanisms which could impact database operations. Effective monitoring of locking statistics helps in proactive database management and troubleshooting performance issues.
• Manual checking: You can check this manually in the database by issuing these SQL commands:

SELECT * FROM dbw_locks_held_num_histr;

SELECT * FROM dbw_locks_held_histr;

SELECT * FROM dbw_users_online_histr;

Job Description and Functionality

The monitoring job titled “Locks held and statistics” focuses on capturing and analyzing lock information within a PostgreSQL database environment. Its capabilities include:

• Tracking and storing the types of locks held, and associated statistics such as lock count and lock types.
• Cleaning up old data based on a configurable data retention policy.
• Ignoring specified users to prevent their lock activity from being reported, which is useful for filtering out maintenance or system operations that should not be monitored.
• Gathering and recording user activity related to locks for further analysis.

Job Configuration Details

Tables and functions created and used by this job include:

• Tables:
  • dbw_locks_held_num_histr: Stores numeric historical data about locks.
  • dbw_locks_ignore_user: Used to store usernames to be ignored in lock monitoring.
  • dbw_users_online_histr: Keeps history of online users and their activities.
  • dbw_locks_held_histr: Records detailed historical data about each lock held.

• Functions:
  • dbw_locks_held: Main function that performs data gathering about locks, manages historical data, and records execution details.
  • dbw_gather_locks: Gathers detailed data about each lock being held.
  • dbw_gather_users_for_locks: Collects data about users involved in locks.

Key operations within these functions include aggregating lock data, filtering based on user, and maintaining a rolling window of historical data based on the “keep data for” parameter.

Dependencies and Cleanup

Dependencies include several self-referencing data tables and functions to ensure that all necessary components are available for the job’s execution. Cleanup on failure is enabled, ensuring that any partially completed operations do not leave the system in an inconsistent state.

Implementation

The implementation involves both DDL for creating necessary tables and procedural code for the PL/pgSQL functions handling the logic of data gathering, history management, and cleanup operations. These components are vital for the dynamic and automated handling of lock statistics within the database.

Reporting and Output

The job provides a detailed report that includes:

• Current locks information with user and lock type exclusions applied.
• Historical data aggregation showing locks by type and count over time.
• Usage statistics broken down by user to analyze individual contributions to locking.

Tables and charts are dynamically generated based on current and historical data, offering a clear visual and tabular representation of lock statistics to database administrators.