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
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. 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 Specify the name of this user in the Username text box. |
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. |
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:
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:
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. |