Disk block reads per database

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and collect statistics on the number of disk block fetch requests per database, focusing on both tables and indexes.
• Why: This job is important because it provides insights into disk access patterns and helps identify potential bottlenecks or excessive disk read operations which could affect database performance. Monitoring these metrics allows for proactive database management and optimization.
• Manual checking: You can check this manually in the database by issuing these SQL commands:

SELECT db_name, blocks_fetched, blocks_hit FROM dbw_disk_block_reads_last;

Job Description

This dbWatch Control Center job specifically targets PostgreSQL databases that are not hosted on AWS RDS and are newer than version 9.4. The job utilizes various PostgreSQL functions and custom tables to track and log the number of disk blocks fetched versus hit (from cache), allowing it to calculate the net disk blocks read per database.

Job Components and Implementation

Database Functions and Tables

The job setup includes the creation of several database entities:

• Tables:
  • “dbw_disk_block_reads_last” – keeps track of the latest total fetched and hit blocks per database.
  • “dbw_disk_block_reads_histr_tab” – logs historical data about fetched and hit blocks over time for each database.
• Views:
  • “dbw_disk_block_reads_rank_1h_view” and “dbw_disk_block_reads_rank_24h_view” – provide a ranking of databases based on the number of net blocks fetched over the last 1 hour and 24 hours, respectively.
• Function:
  • “dbw_disk_block_reads_fn(integer)” – the core function called by the monitoring job, which calculates and logs the fetched and hit disk blocks.

Monitoring Implementation

The monitoring process involves:

• Periodically executing the “dbw_disk_block_reads_fn” function according to a default schedule to fetch and calculate disk block read statistics.
• Using cursors to iterate over and fetch stats for all non-template databases.
• Inserting fresh stats into “dbw_disk_block_reads_last” and “dbw_disk_block_reads_histr_tab”, and updating existing records with new data.
• Cleaning up old data based on a configurable retention period.

Dependency Management

Dependencies are set to ensure that essential tables and functions are present and correctly set up before the job runs. Failure to meet these dependencies triggers cleanup actions.

Reporting

The job also includes a reporting template titled “Disk block hitrate” that provides a graphical representation and detailed statistics of disk reads for the most active databases over select periods. This report includes:

• Graphs and tables displaying top databases by disk reads per second.
• Timely comparisons using historical and recent data.

This dbWatch Control Center job, therefore, plays a crucial role in maintaining the performance and efficiency of PostgreSQL databases by monitoring important metrics related to disk access patterns.