Disk block hitrate
Job details
| Name: |
Disk block hitrate |
| Platform: |
Postgres |
| Category: |
Performance |
| Description: |
Checks the hitrate for disk block requests. |
| Long description: |
Task checks the hitrate for disk block requests. |
| Version: |
1.4 |
| Default schedule: |
0,30 * * * |
| 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: This job monitors and reports on the disk block request hitrate in a PostgreSQL database environment. It specifically checks how efficiently the database is utilizing its cache to hit disk blocks, which is crucial for performance optimization.
- Why: Efficient disk block usage reduces the need for disk reads, which can significantly affect database performance. Monitoring the hitrate can help in proactive management, ensuring that performance issues related to disk block requests are identified and mitigated early.
- Manual checking: You can manually verify the hitrate of disk block requests using the following SQL:
SELECT db_name, (hits / requests) * 100 AS hit_ratio FROM dbw_disk_block_hitrate_last;
Job Configuration Overview
- “Name”: Disk block hitrate
- “Object”: dbw_disk_block_hitrate
- “Version”: 1.4
- “Company”: dbwatch.no
- “Scheduled”: Every hour and half-hour
Thresholds
- “Warning Threshold”: A warning is issued if the hit rate falls below 50%.
- “Alarm Threshold”: An alarm is triggered if the hit rate drops below 25%.
Data Retention
- “Keep Data For”: Data is retained for 7 days, after which it is purged.
Dependencies and Implementation Details
- This job uses stored procedures and has dependencies on tables for tracking historic and current hitrate data.
- The principal routine involved is “CREATE OR REPLACE FUNCTION dbw_disk_block_hitrate(taskId int)” which calculate the hitrates, updates monitoring tables, and manages notifications based on set thresholds.
Tables and Functions
| Table Name |
Purpose |
| dbw_disk_block_hitrate_histr |
Stores historical data of disk block hitrate per database |
| dbw_disk_block_hitrate_last |
Stores the most recent snapshot of hitrate data for comparison and future checks |
- The function performs checks, log updates, threshold comparison, and clean-up operations as per the default and configured job schedules.
Monitoring Reports
- Reports consist of historical and real-time data visualization of the disk block hitrate, presented through charts and tables within the dbWatch Control Center or equivalent interface.
- Key information provided includes hit ratios, database names, and timestamped data logs that track performance over time.
| Report Chart Title |
Description |
Data Points |
| Disk block hitrate |
Displays the hitrate for each database over time through a graph |
Hit Ratio (%) by Date |
| Disk block reads |
Shows the number of physical disk block read operations, segmented by database |
Disk Block Reads by Date |
- These reports support database administrators in visualizing trends, identifying potential issues, and making informed decisions regarding database performance optimization.
Additional Notes
- The implementation is robust and includes protective measures to ensure data consistency and system integrity by managing potential errors and exceptions.
- Modifications to the job, such as adjusting thresholds or changing the data retention period, can be easily implemented through the dbWatch Control Center interface.