Index block hitrate
Job details
| Name: | Index block hitrate |
| Platform: | Postgres |
| Category: | Performance |
| Description: | Checks the hitrate for index disk block requests. |
| Long description: | Task checks the hitrate for index disk block requests. |
| Version: | 1.5 |
| Default schedule: | 7,37 * * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘postgres’]/.[hasengine=‘YES’] |
Parameters
| Name | Default value | Description |
|---|---|---|
| warning threshold | 50 | Will give a warning if the hit ratio falls below this value (in %). |
| alarm threshold | 25 | Will give an alarm if the hit ratio falls below this value (in %). |
| keep data for | 7 | The number of days to keep the data for. |
Job Summary
- Purpose: The purpose of this job is to monitor and analyze the hit rate for index disk block requests in PostgreSQL databases, ensuring that the indices are efficiently using the cache rather than accessing the disk frequently.
- Why: This job is important because a low index block hit rate can lead to increased disk I/O, which may degrade database performance and response times. Monitoring this metric helps in maintaining optimal performance and quickly identifying potential issues for further investigation or tuning.
- Manual checking: You can check this manually in the database by issuing the following SQL commands:
select sum(idx_blks_read), sum(idx_blks_hit) from pg_statio_user_indexes;Implementation Details
The monitoring job is implemented through several SQL statements and a PL/pgSQL function:
- Three tables are created to store:
- Historical data about requests and hits for blocks.
- The last retrived data about requests and hits. This helps in calculating the difference between current and last data points.
- A PL/pgSQL function (`dbw_index_block_hitrate`) calculates the hit rate, logs the details, and determines if a notification needs to be sent based on predefined thresholds for warnings and alarms.
- Data is cleaned up based on a retention period defined by parameters to keep the database size in check.
Dependency Management
The job relies on the following objects:
- dbw_index_block_hitrate(integer): A function used to calculate and log the hit rate.
- dbw_index_block_hitrate_histr: A table used to store the historical data of hit rates.
- dbw_index_block_hitrate_last: A table that keeps track of the most recent data, used for calculating the difference with the current data to get accurate hit rate.
Should there be a failure, cleanup processes are triggered to ensure the integrity and cleanup of dependent objects.
Reporting and Visualization
The job includes detailed report templates that provide visual insights into the hit rates:
- “Index block hitrate” – a chart displaying the history of hit rates over time, aiming to maintain a high ratio indicating good performance.
- “Index block reads” – a chart showing the number of disk reads versus cache hits to visually indicate the efficiency of index usage in the database.
| Report Section | Description |
|---|---|
| Index block hitrate | Displays the hit rate over time with a line chart. |
| Index block reads | Compares the actual counts of disk reads and cache hits over time with a color-coded bar chart. |
This structured reporting helps in quick assessment and decision-making based on the visual trends and historical performance data.
Scheduling and Execution
- The default schedule for the job execution is set to run every hour, which ensures timely updates and monitoring.
- The configuration is designed for automatic execution, storing results, and generating visual reports that simplify monitoring and analysis for database administrators.