Buffer cache reads per database

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and collect statistics on the number of block fetch requests and hits for each table or index across all databases in a PostgreSQL instance. It helps in assessing the performance related to buffer cache usage.
• Why: This job is important to ensure efficient database operations by analyzing buffer cache read patterns, identifying potential caching issues, and providing insights for performance tuning.
• Manual checking: You can check this manually in the database by issuing these SQL commands:

select db_id, db_name, blocks_fetched_total, blocks_hit_total, histr_date from dbw_buffer_cache_reads_last;

Job Components

• Task Name: Buffer cache reads per database
• Object: dbw_buffer_cache_reads_fn
• Description: Collects the number of block fetch requests for table or index per database.
• Long Description: Collects the number of block fetch requests and hits for table or index using specific PostgreSQL statistics functions. Detailed information logged includes total and individual statistics archived per database.
• Version: 1.3
• Company: dbwatch.no
• Category: Performance

Job Dependencies

Implementation

-- SQL Statements for initializing schema objects --
create table dbw_buffer_cache_reads_last (
  db_id bigint, 
  db_name varchar(256), 
  blocks_fetched_total bigint, 
  blocks_hit_total bigint, 
  histr_date timestamp
);

create table dbw_buffer_cache_reads_histr_tab (
  db_id bigint, 
  db_name varchar(256), 
  blocks_fetched_total bigint, 
  blocks_fetched bigint, 
  blocks_hit_total bigint, 
  blocks_hit bigint, 
  period bigint, 
  histr_date timestamp
);

create view dbw_buffer_cache_reads_rank_1h_view as
  select db_name, sum(blocks_fetched) as blocks_fetched
  from dbw_buffer_cache_reads_histr_tab
  where histr_date > (now() - cast('1 hour' as interval))
  group by db_name
  order by blocks_fetched desc;

create view dbw_buffer_cache_reads_rank_24h_view as
  select db_name, sum(blocks_fetched) as blocks_fetched
  from dbw_buffer_cache_reads_histr_tab
  where histr_date > (now() - cast('24 hours' as interval))
  group by db_name
  order by blocks_fetched desc;

-- PostgreSQL Function for collecting and processing buffer cache reads --
CREATE OR REPLACE FUNCTION dbw_buffer_cache_reads_fn(taskId int) ...

Reporting

• Report Title: Disk block hitrate
• Description: This report assesses the efficiency of disk block requests, focusing on the hit rate, which is crucial for database performance.

Each visualization and dataset derived are aimed at providing clear, actionable insights into the performance of the buffer cache system within a PostgreSQL environment.