Blocking statistics

Job details

Name:	Blocking statistics
Platform:	Postgres
Category:	Performance Performance
Description:	Checks whether there exists any blocked sessions.
Long description:	Checks whether there exists any blocked sessions. To find out what processes are holding or waiting for locks pg_stat_activity and pg_locks are cross-referenced.
Version:	1.0
Default schedule:	0,5,10,15,20,25,30,35,40,45,50,55 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘postgres’]/.[hasengine=‘YES’ & is_awsrds = ‘0′ & newer_than_ninefour = 1]

Parameters

Name	Default value	Description
keep data for	60	The number of days to keep blocking statistics for.
warning threshold	300	The maximum number of seconds a session can be blocked before a warning is triggered.
alarm threshold	1800	The maximum number of seconds a session can be blocked before an alarm is triggered.

Job Summary

Purpose: The purpose of this job is to monitor and report on blocking statistics within a PostgreSQL database environment, specifically focusing on sessions that are blocked due to waiting for locks.
Why: This job is important to ensure smooth database operations by identifying and reporting sessions that have been blocked beyond acceptable thresholds. It helps in proactive monitoring, allowing for timely intervention which can prevent potential downtime or performance degradation due to long waiting sessions.
Manual checking: You can check this manually in the database by issuing these SQL commands:

SELECT a.datname as database_name, a.query_start as query_start, bl.pid as blocked_pid, a.usename as blocked_user, kl.pid as blocking_pid, ka.usename as blocking_user, a.application_name as blocked_application, ka.application_name as blocking_application, a.query as blocked_statement, ka.query as blocking_statement, EXTRACT(EPOCH FROM (NOW() - a.query_start)) as waiting_duration FROM pg_catalog.pg_locks bl JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid WHERE NOT bl.granted ORDER BY waiting_duration DESC;

Details and Implementation

Functionality: The job involves creating and maintaining a table ‘dbw_blocking_tab’ to keep track of current blocking statistics and a historical table ‘dbw_blocking_tab_histr’ for storing data beyond the current observation period.
Execution: The primary function ‘dbw_blocking_proc’ is invoked. It first sets the status of entries in ‘dbw_blocking_tab’ to ‘old’, fetches rows from PostgreSQL system catalogs which are then inserted into or updated in ‘dbw_blocking_tab’. The function records the longest waiting session and captures relevant data. Post completion of data collection in real-time tables, older entries are moved to the respective historical tables depending on their age defined by the ‘keep data for’ parameter.
Alerts: Alerts are generated based on defined thresholds for warning and alarm. The current waiting durations are compared against these thresholds and if exceeded, respective alerts are set up.
Cleaning Up: The function concludes by cleaning up the records in both the current and historical tables, deleting entries under specified conditions, and updates check values which could include task status or execution details based on the outcomes of the function.

Reporting Structure

Report: Blocking Statistics Report – Shows real-time and historical data concerning blocking events in the PostgreSQL database.
Tables and Visual Representation:

Column	Description
Waiting duration sec.	Duration a session has been waiting
Database	Database where the lock is occurring
Query start	Start time of the query causing block
Blocked user	User who owns the blocked session
Blocking user	User who owns the blocking session
Blocked statement	SQL statement of the blocked session
Last known blocking statement	SQL statement of the blocking session

Historical Data: Display the last 10 records from the ‘dbw_blocking_tab_histr’, showing detailed information about blocked and blocking processes over time.
- The Data in the tabular format focuses on giving insight on how long sessions were blocked and in which context, helping in identifying trends or persistent issues in the database environment.

This monitoring job enhances overall database performance visibility, easing the management and troubleshooting processes associated with database blocks in a PostgreSQL environment.