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

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