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. To know how to create such a user based on where the target PostgreSQL server is installed (whether on-premises or hosted on Cloud), refer to How does eG Enterprise Monitor PostgreSQL Server?.

  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.
  11. Verify CA - If the eG agent is required to establish an encrypted connection with the target PostGreSQL Database server by authenticating the server's identity through verifying the server CA certificate, set Verify CA flag to Yes. By default, this flag is set to No.
  12. CA Cert File - This parameter is applicable only if the target PostGreSQL Database is SSL-enabled.The certificate file is a public-key certificate following the x.509 standard. It contains information about the identity of the server, such as its name, geolocation, and public key. Each nodes of the target cluster can have individual certificate files or a single certificate can be used to access all the nodes in the cluster. Essentially, it’s a certificate that the server serves to the connecting users to prove that they are what they claim to be. Therefore, specify the full path to the server root certificate or certificate file that is signed by the CA in .crt file format for all/each node in the CA Cert File text box. For example, the location of this file may be: C:\app\eGurkha\JRE\lib\security\PostGreQL-test-ca.crt. By default, this parameter is set to none.
  13. Client Cert File - This parameter is applicable only if the target PostGreSQL Database is SSL-enabled. In order to collect metrics from the target MongoDB cluster, the eG agent requires client certificate in .p12 format. Hence, specify the full path to the Client certificate file in .p12 format in the Client Cert File text box. For example, the location of this file may be: C:\app\eGurkha\JRE\lib\security\test-client.p12.
  14. Client Key File - A client key file refers to a file containing the private key that corresponds to the public key used by a client. Provide full path of the file containing client key.
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.