PostgreSQL Tables Test

The real test of the performance of a database server lies in how quickly the database responds to queries. Whenever users complaint of slow execution of their queries, administrators need to know the reason for the delay - is it because the queries themselves are badly designed? or is it due to how the database server performs table scans and returns the requested result set to the queries?  The PostgreSQL Tables test helps with this root-cause analysis.

This test auto-discovers the tables in the configured databases and reports the number of times every table was scanned, the type of scanning (sequential or index) that was performed, and the rate at which the server reads data (via index and sequential scans) from each table. On the basis of this data, the test also indicates if any table is experiencing any query processing bottlenecks, and if so, how severe is the problem. In addition, the test also reveals how quickly critical database operations such as inserts, deletes, and updates, are performed on every table. Using this information, administrator can figure out whether/not the number and nature of scans performed on the tables are causing queries to the corresponding database to slowdown.

Target of the test : PostgreSQL server

Agent deploying the test: An internal/remote agent

Outputs of the test : One set of results for each table on every database configured for monitoring on 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 Descrption Measurement Unit Interpretation

Sequence scans count:

Indicates the number of sequential scans initiated on this table during the last measurement period.

Number

Sequential or Full table scan is a scan made on the database where each row of the table under scan is read in a sequential (serial) order and the columns encountered are checked for the validity of a condition. Full table scans are usually the slowest method of scanning a table due to the heavy amount of I/O reads and writes required from the disk which consists of multiple seeks as well as costly disk to memory transfers. Typically therefore, a low value is desired for this measure.

However, if a query returns more than approximately 5-10% of all rows in the table, then PostgreSQL prefers the sequential scan over the index scan. This is because an index scan requires several I/O operations for each row (look up the row in the index, then retrieve the row from the heap). Whereas a sequential scan only requires a single I/O for each row - or even less because a block (page) on the disk contains more than one row, so more than one row can be fetched with a single I/O operation.

Sequence reads row count:

Indicates the number of rows that are processed through sequential scan from this table during the last measurement period.

Number

 

Average reads per scan:

Indicates the rate at which rows from this table were processed through a sequential scan.

Fetches/Sec

A high value is desired for this measure. If the value is low or falls consistently, it indicates bottlenecks while performing sequential scans on the table.

Index scans:

Indicates the number of index scans initiated over all the indexes belonging to this table during the last measurement period.

Number

An index scan occurs when the database manager accesses an index for any of the following reasons:

  • To narrow the set of qualifying rows (by scanning the rows in a certain range of the index) before accessing the base table.
  • To order the output.
  • To retrieve the requested column data directly. If all of the requested data is in the index, the indexed table does not need to be accessed. This is known as an index-only access.

Typically, a high value of this measure is desired, as index scans are I/O-friendly operations.

However, if a query returns more than approximately 5-10% of all rows in the table, then PostgreSQL prefers the sequential scan over the index scan. This is because an index scan requires several I/O operations for each row (look up the row in the index, then retrieve the row from the heap). Whereas a sequential scan only requires a single I/O for each row - or even less because a block (page) on the disk contains more than one row, so more than one row can be fetched with a single I/O operation.

Average fetch per index:

Indicates the rate at which the rows are processed through an index scan on this table.

Fetches/Sec

A high value is desired for this measure. If the value is low or falls consistently, it indicates bottlenecks while performing index scans on the table.

Table scans:

Indicates the number of times this table was scanned during the last measurement period.

Number

A high value indicates that there are no proper indexes for this table. This may cause delays in query execution.

Inserts:

Indicates the rate at which the rows are inserted into this table.

Inserts/Sec

 

Deletes:

Indicates the rate at which the rows are deleted from this table.

Deletes/Sec

 

Updates:

Indicates the rate at which the rows are updated in this table.

Updates/Sec

 

Priority:

Indicates the type of problem that is currently experienced by this table while processing a query.

 

The difference between the Sequence scan count and the Index scan count measures determines the Priority of the problem experienced by a table. The various Priorities this measure reports and their numeric equivalents as shown in the table:

Numeric Value State
1

Minor Problem
2

Major Problem
3

Extreme Problem

Note:

By default, this measure reports the above-mentioned States while indicating the type of problem that is experienced while querying this database. However, the graph of this measure will be represented using the corresponding numeric equivalents of the states as mentioned in the table above.

If the severity of this measure is high, it indicates that the query used may be inefficient or there may be a problem with the indexing of the column or there may be a possibility of fragmentation of the table or index of this database.