PostgreSQL Table I/O Test
In PostgreSQL, data is stored in tables, and tables are grouped into databases. Each table is stored in its own disk file. The contents of a table are stored in pages. A table can span many pages, depending upon the length of the row data in the table. A page that contains row data is called a heap block. As indexes are also stored in page files, a page that contains index data is called an index block.
Typically, in PostgreSQL, most disk I/O is performed on a page-by-page basis. To minimize disk I/O, PostgreSQL creates an in-memory data structure known as the buffer cache to which the frequently accessed data is stored. The buffer cache is organized as a collection of 8K pages—each page in the buffer cache corresponds to a page in some page file. The buffer cache is shared between all processes servicing a given database.
When you select a row from a table, PostgreSQL will read the heap block that contains the row into the buffer cache. If there is not enough free space in the cache, PostgreSQL will move some other block out of the cache. If a block being removed from the cache has been modified, it will be written back out to disk; otherwise, it will simply be discarded. Index blocks are also buffered in a similar manner.
If the buffer cache is not sized right, it may not be able to hold enough heap or index blocks to serve subsequent requests. If queries do not find the heap/index blocks they need in the buffer cache, they will be forced to access the disk directly to retrieve data. As direct disk accesses are I/O-intensive operations, they may cause serious performance degradations if not nipped in the bud!
Using the PostgreSQL Table I/O test, you can continuously monitor the heap blocks read from the tables in configured databases and index blocks read from the indexes that correspond to those tables. In the process, you can understand how the buffer cache serviced these read requests and learn of ineffective cache usage early, 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 table (and corresponding index) in every database that is configured for monitoring in the target PostgreSQL server
Note: If you are monitoring a PostgreSQL server on the AWS EC2 cloud, then make sure that you do not remove 'rdsadmin' from the EXCLUDE DB list.
|
Measurement | Description | Measurement Unit | Interpretation |
---|---|---|---|
Heap blocks read: |
Indicates the rate at which the heap blocks are read from this table. |
Reads/Sec |
|
Heap blocks hit: |
Indicates the number of heap block requests to this table that were serviced by the buffer cache during the last measurement period. |
Number |
Ideally, the value of this measure should be high. |
Heap hit ratio: |
Indicates the ratio of the heap block read requests to this table to the heap block requests found in 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. |
Index block reads: |
Indicates the rate at which the index blocks were read from the indexes of this table. |
Reads/Sec |
|
Blocks hit: |
Indicates the number of read requests to the indexes of this table that were found in the buffer cache during the last measurement period.
|
Number |
Ideally, the value of this measure should be high. |
Block hit ratio: |
Indicates the percentage of index block requests to the indexes of this table that were served by the buffer cache. |
Percent |
Ideally, the value of this measure should be high. A low value is indicative of 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. |