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