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
Parameter	Description
Test period	How often should the test be executed
Host	The IP address of the host for which this test is to be configured.
Port	The port on which the server is listening. The default port is 5432.
Username	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?.
Password	The password associated with the above Username (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.
DB Name	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 in the Include DB text box.
Exclude DB	Specify a comma-separated list of databases that need to be excluded from monitoring in the Exclude DB text box. By default, this is set to rdsadmin. Note: If you are monitoring a PostgreSQL server hosted on AWS cloud, then make sure that you do not remove 'rdsadmin' from the Exclude DB list.
SSL	If the PostgreSQL server being monitored is an SSL-enabled server, then set the SSL flag to Yes. If not, then set the SSL flag to No.
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.
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. This parameter specification differs according to the type of cluster and configuration: If the certificate file is available for each node of the PostGreSQL Cluster then, provide a comma-seperated list of full path to the certificates in CA Cert File text box: For example:C:\app\eGurkha\JRE\lib\security\postgresql-test-ca.crt,C:\app\eGurkha\JRE\lib\security\postgresql-test-ca2.crt,C:\app\eGurkha\JRE\lib\security\postgresql-test-ca3.crt Specify the full path to the certificate file of the target PostGreSQL Database if a single certificate is used to access all nodes. For example: C:\app\eGurkha\JRE\lib\security\postgresql-test-ca.crt
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.
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
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.