Service Instance Metrics
This page describes the metrics used to monitor state of an a9s PostgreSQL. Service Instance. For further information on how to monitor an a9s Service Instance, see the Set up Monitoring section of the Application Developer's documentation.
Metric groups
| Custom parameter name | Suggested (interval in seconds) | Default value (interval in seconds) | Description |
|---|---|---|---|
metric_pg_database_size | 300 | 300 | This group lets you see how quickly your database is growing. |
metric_pg_stat_all_indexes | 30 | 0 | This group provides status information on all indexes. |
metric_pg_statio_all_indexes | 60 | 0 | This group provides information on index usage for all indexes. |
metric_pg_statio_all_tables | 30 | 0 | This group provides information on table usage for all tables. |
metric_pg_stat_all_tables | 120 | 0 | This group provides the status of all tables. |
metric_pg_stat_archiver_table | 300 | 0 | This group provides the archival status. Useful with continuous archiving enabled. |
metric_pg_stat_database | 120 | 0 | This group provides the general status of each database. |
metric_pg_stat_database_conflicts | 300 | 0 | This group provides insights on database conflicts. |
metric_pg_replication | 10 | 10 | This group provides the status of replication. Useful when the Service Instance is a replica set. |
metric_pg_node_status | 10 | 10 | This group provides metrics based on node state |
Metric group patterns
Each metric group has a set of patterns that describe the metrics contained in each group.
These metrics are dynamic between the PostgreSQL versions, therefore some of them might differ based on the PostgreSQL version that is currently running on the Service Instance. In order to be sure, search into the documentation link provided in the metric item and confirm the information based on PostgreSQL metric and PostgreSQL version.
metric_pg_database_size
| Metric Id | Type | Description |
|---|---|---|
pg_database_size | Bigint (Bytes) | Database size. |
*.*.postgres.*.*.*.*.database.*.pg_database_size
metric_pg_stat_all_indexes
The pg_stat_all_indexes view will contain one row for each index in the current database, showing statistics about
accesses to that specific index. The pg_stat_user_indexes and pg_stat_sys_indexes views contain the same
information, but filtered to only show user and system indexes respectively.
| Metric Id | Type | Description |
|---|---|---|
idx_scan | Bigint | Number of index scans initiated on this index. documentation |
idx_tup_read | Bigint | Number of index entries returned by scans on this index. documentation |
idx_tup_fetch | Bigint | Number of live table rows fetched by simple index scans using this index. documentation |
Metrics
*.*.postgres.*.*.*.*.database.*.table.*.*.index.*.*.*.idx_scan
*.*.postgres.*.*.*.*.database.*.table.*.*.index.*.*.*.idx_tup_read
*.*.postgres.*.*.*.*.database.*.table.*.*.index.*.*.*.idx_tup_fetch
metric_pg_statio_all_indexes
The pg_statio_all_indexes view will contain one row for each index in the current database, showing statistics about
I/O on that specific index. The pg_statio_user_indexes and pg_statio_sys_indexes views contain the same information,
but filtered to only show user and system indexes respectively.
| Metric Id | type | Description |
|---|---|---|
idx_blks_read | Bigint | Number of disk blocks read from all indexes on this table. documentation |
idx_blks_hit | Bigint | Number of buffer hits in all indexes on this table. documentation |
Metrics
*.*.postgres.*.*.*.*.database.*.table.*.*.index.*.*.*.idx_blks_read
*.*.postgres.*.*.*.*.database.*.table.*.*.index.*.*.*.idx_blks_hit
metric_pg_statio_all_tables
The pg_statio_all_tables view will contain one row for each table in the current database (including TOAST tables),
showing statistics about I/O on that specific table. The pg_statio_user_tables and pg_statio_sys_tables views
contain the same information, but filtered to only show user and system tables respectively.
| Metric Id | type | Description |
|---|---|---|
heap_blks_read | Bigint | Number of disk blocks read from this table.documentation |
heap_blks_hit | Bigint | Number of buffer hits in this table. documentation |
idx_blks_read | Bigint | Number of disk blocks read from all indexes on this table. documentation |
idx_blks_hit | Bigint | Number of buffer hits in all indexes on this table. documentation |
toast_blks_read | Bigint | Number of disk blocks read from this table's TOAST table (if any). documentation |
toast_blks_hit | Bigint | Number of buffer hits in this table's TOAST table (if any). documentation |
tidx_blks_read | Bigint | Number of disk blocks read from this table's TOAST table indexes (if any). documentation |
tidx_blks_hit | Bigint | Number of buffer hits in this table's TOAST table indexes (if any). documentation |
Metrics
*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.heap_blks_read
*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.heap_blks_hit
*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.idx_blks_read
*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.idx_blks_hit
*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.toast_blks_read
*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.toast_blks_hit
*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.tidx_blks_read
*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.tidx_blks_hit
metric_pg_stat_all_tables
The pg_stat_all_tables view will contain one row for each table in the current database (including TOAST tables),
showing statistics about accesses to that specific table. The pg_stat_user_tables and pg_stat_sys_tables views
contain the same information, but filtered to only show user and system tables respectively.
| Metric Id | type | Description |
|---|---|---|
seq_scan | Bigint | Number of sequential scans initiated on this table. documentation |
seq_tup_read | Bigint | Number of live rows fetched by sequential scans. documentation |
idx_scan | Bigint | Number of index scans initiated on this table. documentation |
idx_tup_fetch | Bigint | Number of live rows fetched by index scans. documentation |
n_tup_ins | Bigint | Number of rows inserted. documentation |
n_tup_upd | Bigint | Number of rows updated (includes HOT updated rows for < 17 and includes row updates counted in n_tup_hot_upd and n_tup_newpage_upd, and remaining non-HOT updates for ≥ 17). documentation |
n_tup_del | Bigint | Number of rows deleted. documentation |
n_tup_hot_upd | Bigint | Number of rows HOT updated (i.e., with no separate index update required). documentation |
n_live_tup | Bigint | Estimated number of live rows. documentation |
n_dead_tup | Bigint | Estimated number of dead rows. documentation |
last_vacuum | Timestamp With Time Zone | Last time at which this table was manually vacuumed (not counting VACUUM FULL). documentation |
last_autovacuum | Timestamp With Time Zone | Last time at which this table was vacuumed by the autovacuum daemon. documentation |
last_analyze | Timestamp With Time Zone | Last time at which this table was manually analyzed. documentation |
last_autoanalyze | Timestamp With Time Zone | Last time at which this table was analyzed by the autovacuum daemon. documentation |
vacuum_count | Bigint | Number of times this table has been manually vacuumed (not counting VACUUM FULL). documentation |
autovacuum_count | Bigint | Number of times this table has been vacuumed by the autovacuum daemon. documentation |
analyze_count | Bigint | Number of times this table has been manually analyzed. documentation |
autoanalyze_count | Bigint | Number of times this table has been analyzed by the autovacuum daemon. documentation |
Metrics
*.*.postgres.*.*.*.*.database.*.table.*.*.*.seq_scan
*.*.postgres.*.*.*.*.database.*.table.*.*.*.seq_tup_read
*.*.postgres.*.*.*.*.database.*.table.*.*.*.idx_scan
*.*.postgres.*.*.*.*.database.*.table.*.*.*.idx_tup_fetch
*.*.postgres.*.*.*.*.database.*.table.*.*.*.n_tup_ins
*.*.postgres.*.*.*.*.database.*.table.*.*.*.n_tup_upd
*.*.postgres.*.*.*.*.database.*.table.*.*.*.n_tup_del
*.*.postgres.*.*.*.*.database.*.table.*.*.*.n_tup_hot_upd
*.*.postgres.*.*.*.*.database.*.table.*.*.*.n_live_tup
*.*.postgres.*.*.*.*.database.*.table.*.*.*.n_dead_tup
*.*.postgres.*.*.*.*.database.*.table.*.*.*.last_vacuum
*.*.postgres.*.*.*.*.database.*.table.*.*.*.last_autovacuum
*.*.postgres.*.*.*.*.database.*.table.*.*.*.last_analyze
*.*.postgres.*.*.*.*.database.*.table.*.*.*.last_autoanalyze
*.*.postgres.*.*.*.*.database.*.table.*.*.*.vacuum_count
*.*.postgres.*.*.*.*.database.*.table.*.*.*.autovacuum_count
*.*.postgres.*.*.*.*.database.*.table.*.*.*.analyze_count
*.*.postgres.*.*.*.*.database.*.table.*.*.*.autoanalyze_count
metric_pg_stat_archiver_table
The pg_stat_archiver view will always have a single row, containing data about the archiver process of the cluster.
| Metric Id | type | Description |
|---|---|---|
failed_count | Bigint | Number of failed attempts for archiving WAL files. documentation |
archived_count | Text | Number of WAL files that have been successfully archived. documentation |
last_archived_time_in_seconds | Integer (seconds) | Time of the last successful archive operation. documentation |
wal_files_count | Integer | Amount of WAL files. |
last_archive_successful | Boolean (0 = success/1 = failed)` | Indicate if the last archive operation was successful. |
Metrics
*.*.postgres.*.*.*.*.stat_archiver.failed_count
*.*.postgres.*.*.*.*.stat_archiver.archived_count
*.*.postgres.*.*.*.*.stat_archiver.last_archived_time_in_seconds
*.*.postgres.*.*.*.*.stat_archiver.wal_files_count
*.*.postgres.*.*.*.*.stat_archiver.last_archive_successful
metric_pg_stat_database
The pg_stat_database view will contain one row for each database in the cluster, showing database-wide statistics.
| Metric Id | type | Description | Versions |
|---|---|---|---|
numbackends | Integer | Number of backends currently connected to this database. This is the only column in this view that returns a value reflecting current state; all other columns return the accumulated values since the last reset. documentation | all |
xact_commit | Bigint | Number of transactions in this database that have been committed. documentation | all |
xact_rollback | Bigint | Number of transactions in this database that have been rolled back. documentation | all |
blks_read | Bigint | Number of disk blocks read in this database. documentation | all |
blks_hit | Bigint | Number of times disk blocks were found already in the buffer cache, so that a read was not necessary (this only includes hits in the PostgreSQL buffer cache, not the operating system's file system cache). documentation | all |
tup_returned | Bigint | Number of rows returned by queries in this database. documentation | all |
tup_fetched | Bigint | Number of rows fetched by queries in this database. documentation | all |
tup_inserted | Bigint | Number of rows inserted by queries in this database. documentation | all |
tup_updated | Bigint | Number of rows updated by queries in this database. documentation | all |
tup_deleted | Bigint | Number of rows deleted by queries in this database. documentation | all |
conflicts | Bigint | Number of queries canceled due to conflicts with recovery in this database. (Conflicts occur only on standby servers; see pg_stat_database_conflicts for details.) documentation | all |
temp_files | Bigint | Number of temporary files created by queries in this database. All temporary files are counted, regardless of why the temporary file was created (e.g., sorting or hashing), and regardless of the log_temp_files setting. documentation | all |
temp_bytes | Bigint | Total amount of data 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. documentation | all |
deadlocks | Bigint | Number of deadlocks detected in this database. documentation | all |
blk_read_time | Double Precision | Time spent reading data file blocks by backends in this database, in milliseconds. documentation | all |
blk_write_time | Double Precision | Time spent writing data file blocks by backends in this database, in milliseconds. documentation | all |
session_time | Double Precision | Time spent by database sessions in this database, in milliseconds (note that statistics are only updated when the state of a session changes, so if sessions have been idle for a long time, this idle time won't be included). | documentation |
active_time | Double Precision | Time spent executing SQL statements in this database, in milliseconds (this corresponds to the states active and fastpath function call in pg_stat_activity). | documentation |
idle_in_transaction_time | Double Precision | Time spent idling while in a transaction in this database, in milliseconds (this corresponds to the states idle in transaction and idle in transaction (aborted) in pg_stat_activity). | documentation |
sessions | Bigint | Total number of sessions established to this database. | documentation |
sessions_abandoned | Bigint | Number of database sessions to this database that were terminated because connection to the client was lost. | documentation |
sessions_fatal | Bigint | Number of database sessions to this database that were terminated by fatal errors. | documentation |
sessions_killed | Bigint | Number of database sessions to this database that were terminated by operator intervention. | documentation |
stats_reset | Timestamp With Time Zone | Time at which these statistics were last reset. documentation | ≥ 15 |
Metrics
*.*.postgres.*.*.*.*.database.*.*.numbackends
*.*.postgres.*.*.*.*.database.*.*.xact_commit
*.*.postgres.*.*.*.*.database.*.*.xact_rollback
*.*.postgres.*.*.*.*.database.*.*.blks_read
*.*.postgres.*.*.*.*.database.*.*.blks_hit
*.*.postgres.*.*.*.*.database.*.*.tup_returned
*.*.postgres.*.*.*.*.database.*.*.tup_fetched
*.*.postgres.*.*.*.*.database.*.*.tup_inserted
*.*.postgres.*.*.*.*.database.*.*.tup_updated
*.*.postgres.*.*.*.*.database.*.*.tup_deleted
*.*.postgres.*.*.*.*.database.*.*.conflicts
*.*.postgres.*.*.*.*.database.*.*.temp_files
*.*.postgres.*.*.*.*.database.*.*.temp_bytes
*.*.postgres.*.*.*.*.database.*.*.deadlocks
*.*.postgres.*.*.*.*.database.*.*.blk_read_time
*.*.postgres.*.*.*.*.database.*.*.blk_write_time
*.*.postgres.*.*.*.*.database.*.*.session_time
*.*.postgres.*.*.*.*.database.*.*.active_time
*.*.postgres.*.*.*.*.database.*.*.idle_in_transaction_time
*.*.postgres.*.*.*.*.database.*.*.sessions
*.*.postgres.*.*.*.*.database.*.*.sessions_abandoned
*.*.postgres.*.*.*.*.database.*.*.sessions_fatal
*.*.postgres.*.*.*.*.database.*.*.sessions_killed
*.*.postgres.*.*.*.*.database.*.*.stats_reset
metric_pg_stat_database_conflicts
The pg_stat_database_conflicts view will contain one row per database, showing database-wide statistics
about query cancels occurring due to conflicts with recovery on standby servers. This view will only contain
information on standby servers, since conflicts do not occur on master servers.
| Metric Id | type | Description |
|---|---|---|
confl_tablespace | Bigint | Number of queries in this database that have been canceled due to dropped tablespaces. documentation |
confl_lock | Bigint | Number of queries in this database that have been canceled due to lock timeouts. documentation |
confl_snapshot | Bigint | Number of queries in this database that have been canceled due to old snapshots. documentation |
confl_bufferpin | Bigint | Number of queries in this database that have been canceled due to pinned buffers. documentation |
confl_deadlock | Bigint | Number of queries in this database that have been canceled due to deadlocks. documentation |
Metrics
*.*.postgres.*.*.*.*.conflicts.*.*.confl_tablespace
*.*.postgres.*.*.*.*.conflicts.*.*.confl_lock
*.*.postgres.*.*.*.*.conflicts.*.*.confl_snapshot
*.*.postgres.*.*.*.*.conflicts.*.*.confl_bufferpin
*.*.postgres.*.*.*.*.conflicts.*.*.confl_deadlock
metric_pg_replication
| Metric Id | type | Description |
|---|---|---|
replication.wal_receiver_count | Integer | Amount WAL receiver process running the master what sends data to the slave. |
replication.wal_sender_count | Integer | Amount of WAL sender process that run at the primary node. Each replication has its own. |
replication.replication_lag | Integer (seconds) | Amount of time since the pg_last_xact_replay_timestamp, that is the last transaction replayed during recovery. |
replication.replication_slots_count | Integer | The same as pg_replication_slots PostgreSQL property that this view provides a listing of all replication slots that currently exist on the database cluster. documentation |
replication.inactive_replication_slots_count | Integer | The amount of replication_slots_count in inactive state. |
replication.cluster_replication_slots_count | Integer | The amount of replication_slots_count that marches with the regex /^repmgr_slot_[0-9]+$/. |
ha.master | Integer (1 = true/0 = false) | If it is a master node. |
ha.standby | Integer (1 = true/0 = false) | If it is a slave node. |
ha.wal_dir_size | Integer (Bytes) | Size of the WAL directory. |
Metrics
*.*.postgres.*.*.*.*.replication.wal_receiver_count
*.*.postgres.*.*.*.*.replication.wal_sender_count
*.*.postgres.*.*.*.*.replication.replication_lag
*.*.postgres.*.*.*.*.replication.replication_slots_count
*.*.postgres.*.*.*.*.replication.inactive_replication_slots_count
*.*.postgres.*.*.*.*.replication.cluster_replication_slots_count
*.*.postgres.*.*.*.*.ha.master
*.*.postgres.*.*.*.*.ha.standby
*.*.postgres.*.*.*.*.ha.wal_dir_size
metric_pg_node_status
*.*.postgres.*.*.*.*.postgresql_info_status.blocked
Description of Metrics:
postgresql_info_status.blocked indicates if any of the nodes has blocked
status that means that this node doesn't participate in cluster life.
This metric can be equal:
1- blocked0- unblocked
Configuring metrics databases
By setting the metric_databases custom parameter on a Service Instance it is
possible to control which database metrics are generated. The
metric_databases parameter expects an array of database names. If the
parameter is not set or the array is set to empty, then metrics will be collected
for all databases.
PostgreSQL Extensions
Create or Drop Extensions
It is possible to install PostgreSQL extensions with the Cloud Foundry CLI and additional configuration parameters:
Commands
cf create-service a9s-postgresql PLAN_NAME INSTANCE_NAME -c '{"install_plugins": ["<plugin_name>"]}'
cf update-service INSTANCE_NAME -c '{"install_plugins": ["<plugin_name>"]}'
cf update-service INSTANCE_NAME -c '{"delete_plugins": ["<plugin_name>"]}'
Available Extensions
The following PostgreSQL extensions are available:
postgisuuid-osspltreepgcryptocitextpg_stat_statementsfuzzystrmatchpg_trgmhstorevector
Default Extensions
There are PostgreSQL extensions that are always installed and it's also not possible to delete them. The following PostgreSQL extensions are set as default extensions:
pgcryptocitext
These two extensions are needed to have a functional replication.
PostgreSQL statistics collector
You can enable support for the statistics collector feature (please note that this feature is PostgreSQL version specific). This feature allows you to retrieve information about the current status of the PostgreSQL cluster. It includes information about currently active queries and other useful informations. For each statistics collector view another one is created by a PostgreSQL super user (with the super users' access privileges) that has the same name with a prefix prepended.
Example: For pg_stat_activity the associated view is called ds_pg_stat_activity.
You can set the value of the prefix as you prefer. Read-only access to the views created by this feature is given to every user
allowed to login on the cluster via the public schema.
You need to connect to the postgres database with your service
bindings in order to use those views.
This feature is disabled by default. You can ask your Platform Operator to enable this feature for your Service Instance.
Enable AppDynamics support
Enable AppDynamics support to create a view called pg_stat_activity_allusers which is accessible to all users as described here.
This feature is disabled by default. You can ask your Platform Operator to enable this feature for your Service Instance.