Skip to main content
Monitoring of PostgreSQL cluster and databases for 1C
Last update:

Monitoring of PostgreSQL cluster and databases for 1C

In PostgreSQL cloud databases for 1C, you can monitor the state of the cluster.

To assess the overall state of the cluster check his status..

For more detailed analysis, some metrics can be viewed as graphs in the dashboard:

The full set of available metrics can be export in Prometheus format.

You can find out if your disk is about to run out of space by using disk fill notifications. Upon receipt of the notice disk cleanup or scale the cluster and select a configuration with a larger disk size.

View cluster status

  1. In control panels go to Cloud platformDatabases.
  2. In the cluster row, look at the status.
ACTIVEThe cluster is available
CREATINGA cluster is created
UPDATINGChanges are applied to the cluster
RESIZINGThe cluster is scalable
ERRORThere's been a mistake, file a ticket
DISK FULL

The disk is full and the cluster is read-only. To make the cluster read and write, disk cleanup or scale the cluster and select a configuration with a larger disk size

DEGRADEDSome nodes in the cluster are unavailable
DELETINGThe cluster is being deleted

View the status of the node cluster

  1. In control panels go to Cloud platformDatabases.
  2. Open the cluster page → tab Monitoring.
  3. In the block Cluster monitoring click Nodes of the cluster.
  4. In the field Server select the node whose metrics you want to view.
  5. See what's available node cluster metrics.

Cluster node metrics in the control panel

vCPUHow many percent of the node cluster cores are utilized
CPU iowaitHow much percent of the processor's time was spent waiting for I/O
MemoryMemory utilization excluding cache and operating system buffers in percent or gigabytes
DiskUsed disk space in percent or gigabytes. The file system reserves 4% of the disk space for service needs, so the space occupied is greater than the actual space used by this 4%
Network load

The number of bits or packets sent and received over the network interface

Disk, read and write speed

Data read speed in B/s and data write speed in KB/s

Disk, number of IOPS

Number of read and write operations per second

OOM

Number of processes that ended with an error Out of Memory due to lack of RAM

Load Average

The average value of system load over a period of time. Shows how many processes are processed by the cluster cores. The indicator is presented in the form of three values — for one minute, five minutes and 15 minutes. These values should not be greater than the number of cores on the node

View the status of the databases

  1. In control panels go to Cloud platformDatabases.
  2. Open the cluster page → tab Monitoring.
  3. In the block Cluster monitoring click Databases.
  4. In the field Server select the node whose database metrics you want to view.
  5. See what's available database metrics.

Database metrics in the dashboard

ConnectionsNumber of connections to each cluster database and total number of connections to all databases
Execution time of the longest queryExecution time of the longest query in each database of the cluster for a period of time
TransactionsNumber of transactions per second in each cluster database
Cache hitWhat percentage of the data in the query was read from the cache is a ratio of blks_hit to the sum of blks_hit and blks_read
LocksNumber of locks in each cluster database
DeadlocksNumber of mutual locks in each database
Database sizeTotal size of the selected database in megabytes
Statistics file sizeTotal size of the statistics file in kilobytes
Size of WAL filesTotal size of WAL files in megabytes
Temporary file sizeTotal size of temporary files in kilobytes
String operations

The number of operations in the selected database per second:

  • tup_deleted — number of rows deleted by queries in the database per second;
  • tup_fetched — the number of rows retrieved by queries in the database per second;
  • tup_inserted — the number of rows inserted by queries in the database per second;
  • tup_returned — the number of rows returned by queries in the database per second;
  • tup_updated — number of rows changed by queries in the database per second

View the status of the connection pooler

  1. In control panels go to Cloud platformDatabases.
  2. Open the cluster page → tab Monitoring.
  3. In the block Cluster monitoring click Connection Pooler.
  4. In the field Server select the node whose metrics you want to view.
  5. See what's available node cluster metrics.

Connection pooler metrics in the control panel

Customer connections to the pool

The number of client connections to the pool in the selected database:

  • pools_client_active_connections — the number of client connections associated with server connections or idle without requests;
  • pools_client_waiting_connections — number of client connections where the request has been sent, but there is no connection to the node yet
Active connections to the serverNumber of server connections associated with clients in the selected database
Waiting time for a response from the serverTime to wait for a response from a node in the selected database in seconds
Maximum waiting time of the client in the queueMaximum waiting time of the client in the queue in the selected database in seconds

Export metrics in Prometheus format

Historical information for clusters is not available — metrics are requested only in real time. The list of all metrics that are supported in cloud databases and their description can be seen in the following table Metrics in Prometheus format.

  1. Get a token.
  2. Get metrics in Prometheus format.

Get a token

The token gives access to the metrics of all clusters project in one bullet.

  1. In control panels go to Cloud platformDatabases.

  2. Open the cluster page → tab Monitoring.

  3. In the block Tokens for Prometheus click Add token. The token will be generated automatically.

  4. Copy the token. To do this, in the token row, click .

Get metrics in Prometheus format

  1. Add to the Prometheus configuration file:

    scrape_configs:
    - job_name: get-metrics-from-dbaas
    scrape_interval: 1m
    static_configs:
    - targets:
    - '<pool>.dbaas.selcloud.ru'
    scheme: https
    authorization:
    type: Bearer
    credentials: <monitoring_token>

    Specify:

    • <pool> — pool in which the token is valid, e.g. ru-3. The address (URL) depends on the region and pool, you can look in the URL list;
    • <monitoring_token> — a monitoring token that you received Previously.
  2. Open a page in your browser where Prometheus-formatted metrics will be available:

    http://<ip_address>:9090/targets

    Specify <ip_address> — IP address where Prometheus is installed.

  3. Independently configure monitoring and alerts for database clusters.

Metrics in Prometheus format

Metrics in Prometheus format are provided for all clusters. A specific cluster can be found by the database cluster identifier in the label ds_id.

dbaas_memory_percentMemory utilization excluding cache and operating system buffers (RAM) in percent
dbaas_memory_bytesOccupied memory excluding cache and operating system buffers (RAM) in bytes
dbaas_oom_countNumber of processes that ended with an error Out of Memory due to lack of RAM
dbaas_cpuPercent vCPU utilization on database cluster nodes
dbaas_cpu_iowaitI/O waiting time in percent
dbaas_disk_percentPercentage of disk space occupied. The file system reserves 4% of disk space for service needs, so the occupied space is larger than the actual space used by this 4%
dbaas_disk_bytesOccupied disk space in bytes. The file system reserves 4% of the disk space for service needs, so the occupied space is larger than the actual space used by this 4%
dbaas_disk_read_iopsNumber of read operations per second
dbaas_disk_write_iopsNumber of recording operations per second
dbaas_disk_read_bytesDisk read speed in bytes per second
dbaas_disk_write_bytesData write speed to disk in bytes per second
dbaas_node_load1The average value of system load in one minute. Shows how many processes are processed by the cluster cores
dbaas_node_load5The average system utilization over five minutes. Shows how many processes are processed by the cluster cores
dbaas_node_load15The average value of system utilization over 15 minutes. Shows how many processes are processed by the cluster cores
dbaas_network_receive_bytesNumber of bytes received through the network interface
dbaas_network_transmit_bytesNumber of bytes sent through the network interface
dbaas_network_receive_packetsNumber of packets received through the network interface per second
dbaas_network_transmit_packetsNumber of packets sent through the network interface per second
dbaas_role

Role of the node:

  • 0 — role unknown;
  • 1 — master;
  • 2 — replica

Disk fullness notifications

If the cluster disk is 80% full, a notification will appear in the dashboard and will be sent to the email of the Account Owner and those users subscribed to the the "Services and services" notification category.

If the cluster disk is 95% full or more, the cluster will go into status DISK_FULL and will be read-only. To make the cluster read-write, disk cleanup or scale the cluster and select a configuration with a larger disk size.

Clear the disk

Open a transaction transaction_read_only = no and delete unnecessary data using one of the queries:

  • DROP TABLE — deletes the structure (data, privileges, indexes, constraints, triggers). Use when completely deleting a table with data and structure:

    BEGIN;
    SET transaction_read_only = no;
    DROP TABLE table_name;
    COMMIT;
  • TRUNCATE TABLE — deletes the contents of the table, but the structure is preserved. Works faster DROP TABLE. Use when deleting all rows of a table while preserving the table structure:

    BEGIN;
    SET transaction_read_only = no;
    TRUNCATE TABLE table_name;
    COMMIT;
  • DELETE — use to delete specific strings.

For your information

We do not recommend using the query DELETE FROM table WHERE ... to clean up the disk. This query can create oversized samples on large tables and place them on disk. The remaining free disk space may run out completely, causing problems with PostgreSQL and the need to restore its operation manually.