VK Cloud logo

Setting up notifications about PostgreSQL database metric changes

Set up monitoring of PostgreSQL database metrics and notifications about their changes to maintain stable operation of cloud applications.

Preparation steps

  1. Go to the VK Cloud management console.
  2. Select a project.
  3. Create a PostgreSQL DB instance, if this has not been done yet.

1. View monitoring data on the DB page

  1. View the monitoring data on the DB instance page. Pay attention to the following metrics:

    RAM Used

    Percentage of used memory relative to the total memory on the node

    Current CPU

    Percentage of time during which the node's processor ensures the operation of the DB instance and its related infrastructure

    Current IOWait

    Percentage of the node's total CPU resources spent waiting for I/O operations to complete

    Free connections

    Percentage of available connections out of the total number

    Disk used

    Percentage of used disk space relative to the total volume on the node

  2. Add the monitoring data to your dashboards for quick access to them.

2. Configure alerts

  1. Create one or more notification channels.

  2. Create triggers with the following parameters:

    Metric name

    Threshold value

    Description

    mem_used_percent

    > 85-90%

    High memory load

    cpu_usage_user

    > 90%

    The application is overloaded

    cpu_usage_system

    > 30%

    Possible issues with system calls

    cpu_usage_iowait

    > 80%

    High percentage of waiting for disk operations

    postgresql_free_connections_percent

    < 10-20%

    Few free DB connections remain

    disk_used_percent

    > 85-90%

    Little space remains on the disk

When the threshold value is reached, an incident will be created and a message will be sent to the specified notification channel.

3. Use the monitoring data

High CPU utilization (Current CPU) and RAM usage (RAM Used) values, disk subsystem load (Disk used), as well as intensive or uneven load on the database (Current IOWait) indicate a high load on the nodes or suboptimal indexes and queries.

Use the built-in PostgreSQL performance diagnostic tools as the database administrator, for example:

  • Run queries against the system table pg_stat_activity to collect statistics on the queries being executed.

    Pay attention to the queries that take longer than all the others to execute.

  • Use the EXPLAIN command to find bottlenecks in such slow queries.

    Pay attention to queries that do not use indexes (a large number of rows in Seq Scan). Create or update the necessary indexes.

A low percentage of free connections (Free connections) may indicate either a large number of simultaneously connected clients or the presence of long transactions that hold an open connection for too long.

Optimize resource usage:

  • Increase the value of the max_connections parameter.
  • Optimize queries so that there are no long transactions.
  • Use PgBouncer to reduce the load on the server and optimize resource usage.

Delete unused resources

The deployed DB instance is billed and consumes computing resources. If you no longer need it, delete it.

Was this article helpful?