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

Configurable parameters for the test

TEST PERIOD – How often should the test be executed. Host – The IP address of the server. Port – The port on which the server is listening. The default port is 5432. User – In order to monitor a PostgreSQL server, you need to manually create a special database user account in every PostgreSQL database instance that requires monitoring. When doing so, ensure that this user is vested with the superuser privileges. The sample script we recommend for user creation for eG monitoring is: CREATE ROLE eguser LOGIN ENCRYPTED PASSWORD {‘eguser password’} SUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; The name of this user has to be specified in the USERNAME text box. Password- The password associated with the above user name (can be ‘NULL’). Here, ‘NULL’ means that the user does not have any password. Confirm password – Confirm the password (if any) by retyping it here. dbname - The name of the database to connect to. The default is “postgres”. include db - Specify a comma-separated list of databases that you wish to monitor. exclude db - Specify a comma-separated list of databases that need to be excluded from monitoring. By default, this is set to rdsadmin.   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.   ssl - The name of this user has to be specified in the USERNAME text box.

Measurements made by the test

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.