PostgreSQL Index I/O Test
Indexes are buffered in the same way as tables are. Therefore, if too many index blocks are not found in the buffer cache, disk I/O increases, causing the overall performance of the PostgreSQL server to suffer. It is hence imperative to monitor how the cache services index block read requests.
The PostgreSQL Index I/O test helps monitor each index in a database for read requests. In the process, the test reveals how the buffer cache serviced these read requests and provides early pointers to ineffective cache usage, so that you can investigate the reasons for the same (whether/not it is owing to an under-sized cache) and initiate appropriate remedial action.
Target of the test : PostgreSQL server
Agent deploying the test: An internal/remote agent
Outputs of the test : One set of results for every in index in every database that is configured for monitoring in the target PostgreSQL server
|
Measurement | Description | Measurement Unit | Interpretation |
---|---|---|---|
Index block reads: |
Indicates the rate at which the index blocks were read from this index during the last measurement period. |
Reads/Sec |
|
Blocks hit: |
Indicates the number of read requests to this index that were found in the buffer cache during the last measurement period. |
Number |
Ideally, the value of this measure should be high. |
Hit ratio: |
Indicates the percentage of index block requests to this index that were served by the buffer cache. |
Percent |
Ideally, the value of this measure should be high. A low value is indicative ineffective cache usage, which in turn can increase disk I/O and degrade server performance. One of the most common reasons for a low cache hit ratio is small cache size. In such a case, you can consider increasing the cache size. There are two ways that you can adjust the size of the cache. You could edit PostgreSQL’s configuration file ($PGDATA/postgresql.conf) and change the shared_buffers variable therein. Alternatively, you can override the shared_buffers configuration variable when you start the postmaster. A sample command for implementing a shared_buffers override while starting the postmaster is given below: pg_start -o “-B 65” -l /tmp/pg.log If increasing the cache size also does not help, then, you can include a limit clause in your queries to select a sub-set of the queried tables and add them to the cache. |