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

Configurable parameters for the test
  1. TEST PERIOD – How often should the test be executed
  2. Host – The IP address of the server
  3. Port – The port on which the server is listening. The default port is 5432.

  4. 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.

  5. Password- The password associated with the above user name (can be ‘NULL’). Here, ‘NULL’ means that the user does not have any password.
  6. Confirm password – Confirm the password (if any) by retyping it here.
  7. dbname - The name of the database to connect to. The default is “postgres”.
  8. include db - Specify a comma-separated list of databases that you wish to monitor.

  9. 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.

     

  10. 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

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.