Documentation forDatabase Performance Analyzer

PostgreSQL metrics collected by DPA

The following sections list the metrics that DPA collects for PostgreSQL databases.

Learn how to view these metrics and change the thresholds.

Cache Eviction

Metric Description
Dirty Buffers Evicted by Background Writer The number of buffers written and freed (evicted) due to the PostgreSQL background writer.
Dirty Buffers Evicted by Background Writer Ratio The ratio of buffers written and freed (evicted) due to the PostgreSQL background writer to the total number of evictions.
Dirty Buffers Evicted by Checkpoints The number of buffers written and freed (evicted) due to a checkpoint execution. Higher values indicate an increased need for checkpoints.
Dirty Buffers Evicted by Checkpoints Ratio The ratio of buffers written and freed (evicted) due to a checkpoint execution to the total number of evictions. Higher values indicate an increased need for checkpoints.
Dirty Buffers Evicted by Client Backends The number of times client backends were delayed by being forced to write and free (evict) buffers themselves, instead of the buffers being evicted asynchronously by the background writer.
Dirty Buffers Evicted by Client Backends Ratio The ratio of buffers written and freed (evicted) by a client backend to the total number of evictions.
Total Dirty Buffers Evicted The total number of dirty buffers evicted by checkpoints, background writer, and client backends.

Checkpoints

Metric Description
Requested Checkpoints The number of unscheduled checkpoints requested by client statements because the WAL size has reached its threshold (max_wal_size). Requested checkpoints can cause client backend waits. Consider reconfiguration of checkpoint related settings (checkpoint_timeout, checkpoint_completion_target, and max_wal_size).
Requested Checkpoints Ratio The ratio of requested checkpoints to total checkpoints (requested and scheduled). The percentage should be low—optimally 0%.
Scheduled Checkpoints The number of scheduled checkpoints processed in the background, without affecting client statements. Scheduled checkpoints should not cause client backend waits.

Connections

Metric Description
Connected Devices The number of distinct client machines connected to this database instance, even if the connection is idle.
Connected Users The number of distinct users (that is, login names) connected to this database instance, even if the connection is idle.

CPU

To collect CPU metrics from a PostgreSQL instance, DPA requires the system_stats extension to be installed in the PostgreSQL instance.

Metric Description
Host CPU Utilization The percentage of CPU being used by the entire database server host. If this is high, compare this metric to the Instance CPU Utilization metric. If the instance is not using a significant portion of total CPU, review other programs running at this time.
Instance CPU Utilization The percentage of CPU being used by the database instance, which is a subset of the CPU used by the host. If this is high, use DPA Trends charts to review queries waiting on CPU.

Disk

Metric Description
Blocks Hit The number of times disk blocks were found already in the buffer cache, so that a read was not necessary. This includes only hits in the PostgreSQL buffer cache, not the operating system's file system cache.
Blocks Read The number of disk blocks read in this database.
Blocks Read Time The average amount of block read I/O during the specified time interval. If the track_io_timing parameter is off, the value of this metric is always 0. For more information, see this support article.
Blocks Write Time The average amount of block write I/O during the specified time interval. If the track_io_timing parameter is off, the value of this metric is always 0. For more information, see this support article.
Temp Bytes Written The total amount of data in kilobytes written to temporary files by queries in this database. All temporary files are counted, regardless of why the temporary file was created, and regardless of the log_temp_files setting.
Temp Files The number of temporary files created by queries in this database. All temporary files are counted, regardless of why the temporary file was created (for example, sorting or hashing), and regardless of the log_temp_files setting.
Write-ahead Log (WAL) Rate The rate of the Write-ahead Log creation as a result of database transaction activity in MB per second.

Memory

Metric Description
Buffer Cache Hit Ratio The rate at which PostgreSQL finds the data blocks it needs in memory rather than having to read from disk.

Network

Metric Description
DB Round-trip Time The round-trip time when running "select 1" (includes network time but not connect time) on this database.

Replication

Metric Description
Replication Lag The replication lag between the primary database and all replica databases. An increase in the replication lag indicates a growing number of transactions that are not yet replicated and at risk of not being replicated if the primary database fails.

Rows

Metric Description
Fetched vs. Returned Rows Of the total number of rows that were scanned (Rows Returned), the percentage that contained data needed to execute the query (Rows Fetched). High values indicate that the database is executing queries efficiently. Low values indicate that the database is performing extra work because it is scanning a large number of rows that aren't required to process the query. For example, 10% means that the database is scanning 10 rows to use 1 row. Low values could indicate inefficient queries or missing indexes.
Rows Operations The number of rows inserted, updated, and deleted by queries in this database instance.
Rows Deleted The number of rows deleted by queries in this database instance.
Rows Fetched

The subset of scanned rows (Rows Returned) that contained data needed to execute the query. For example, take the following query:

SELECT * FROM customers WHERE country = 'Spain';

The customers table has 10,000 rows, and country = 'Spain' in 100 rows. The column is not indexed, and so a full table scan is required. The Rows Returned value is 10,000, but the Rows Fetched value is only 100.

The Rows Fetched value is different than the number of rows returned to the client.

Rows Inserted The number of rows inserted by queries in this database instance.
Rows Returned

The total number of rows scanned by queries executed against this database instance.

This value indicates rows returned by the storage layer to be scanned, not rows returned to the client.

Rows Updated The number of rows updated by queries in this database instance.

Sessions

Metric Description
Active Sessions The number of transactions in the following state: The backend is executing a query.
All Sessions All sessions, regardless of state.
Blocked Sessions The number of sessions in a blocked state.
Deadlocks The number of deadlocks detected in this database instance.
Fastpath Function Call Sessions The number of sessions in which the backend is executing a fast-path function.
Idle Sessions The number of sessions in the following state: The backend is waiting for a new client command.
Idle in Transaction (Aborted) Sessions This state is similar to Idle in Transaction Sessions, except one of the statements in the transaction caused an error.
Idle in Transaction Sessions The number of sessions in the following state: The backend is in a transaction, but is not currently executing a query.
Percentage of Idle in Transaction The percentage of allowed connections that are in the idle-in-transaction state. Transactions that are in the idle-in-transaction state for a significant amount of time can cause the connection pool to fill to the limit, and they can cause other database operations such as VACUUM to fail to complete. You might also see SELECT waiting messages, which indicates that an HTTP request was made but the SELECT operation to get the data never completed (probably because the user waited so long that they abandoned the task).
Recovery Conflicts The number of queries canceled due to conflicts with recovery in this database instance. Conflicts occur only on standby servers.
Transaction Commit Rate The number of transactions being committed every second in this database instance.
Transaction Rate The number of transactions being executed every second in this database instance.
Transaction Rollbacks The number of transactions in this database that have been rolled back.
track_activities Disabled Sessions This state is reported if track_activities is disabled in this backend.

Vacuum

Metric Description
Autovacuum Worker Utilization An indication of how busy the set of vacuum worker processes are. The pg_stat_progress_vacuum view provides information about current vacuuming processes. If this value is consistently high, consider increasing the value of the autovacuum_max_workers parameter.
Multixact ID Space Taken The percentage of space available to store Multixact IDs (MXIDs) that is currently filled. this is the highest value across all databases in the database server.
Transaction ID Space Taken The percentage of space available to store Transaction IDs (XIDs) that is currently filled. this is the highest value across all databases in the database server.

Waits

Metric Description
Total Instance Wait Time The total wait time for the database instance.