---
id: b837bebd-2f50-480d-a820-bc5e0b91cd26
---

# Low Postgresql commit rate incident
---

The Low Postgresql commit rate incident is triggered when the number of transactions processed by Postgresql drops below a certain threshold, indicating a potential issue with the database's performance. This could be caused by a variety of factors, such as high load or inadequate resources. The incident requires investigation and resolution to ensure the database is functioning properly.

### Parameters
```shell
# Environment Variables

export DISK="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export DATABASE_PORT="PLACEHOLDER"

export DATABASE_USER="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_HOST="PLACEHOLDER"
```

## Debug

### Check if Postgresql service is running
```shell
systemctl status postgresql.service
```

### Check Postgresql logs for any errors
```shell
tail -n 50 /var/log/postgresql/postgresql-main.log
```

### Check the current connection count in Postgresql
```shell
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"
```

### Check the number of active transactions in Postgresql
```shell
sudo -u postgres psql -c "SELECT SUM(pg_stat_activity.xact_commit) FROM pg_stat_activity;"
```

### Check the Postgresql configuration for any anomalies
```shell
cat /etc/postgresql/main/postgresql.conf
```

### Check the system load and resource utilization
```shell
top
```

### Check disk usage and I/O performance
```shell
df -h

iostat -dx ${DISK}
```

### Check network connectivity to the Postgresql server
```shell
ping ${DATABASE_HOST}
```

### Check firewall rules for the Postgresql port
```shell
iptables -L -n | grep ${DATABASE_PORT}
```

### Check for any other running processes that may be causing contention
```shell
ps -ef | grep postgresql
```

### Check for any Postgresql updates
```shell
sudo -u postgres psql -c "SELECT version();"
```

## Repair

### Check if there are any long-running queries that may be causing a bottleneck. Identify the queries and tune them or optimize the database schema.
```shell


#!/bin/bash



# Set the database credentials

DB_HOST=${DATABASE_HOST}

DB_PORT=${DATABASE_PORT}

DB_NAME=${DATABASE_NAME}

DB_USER=${DATABASE_USER}

DB_PASSWORD=${DATABASE_PASSWORD}



# Connect to the database and check for long-running queries

LONG_RUNNING_QUERIES=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "SELECT query FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '5 minutes';")



# Check if there are any long-running queries

if [ -z "$LONG_RUNNING_QUERIES" ]; then

  echo "No long-running queries found"

else

  echo "Long-running queries found:"

  echo "$LONG_RUNNING_QUERIES"

  
fi


```

### Check if there are any configuration issues with the Postgresql server. Verify the configuration settings and make any necessary changes.
```shell


#!/bin/bash

PATH_TO_PG_CONFIG_FILE=/etc/postgresql/main/postgresql.conf

# Check if Postgresql is installed and running

if ! pgrep -x "postgres" > /dev/null

then

    echo "Postgresql is not running"

    exit 1

fi



# Check the configuration settings for Postgresql

if [ -f ${PATH_TO_PG_CONFIG_FILE} ]

then

    # Make a backup of the configuration file

    cp ${PATH_TO_PG_CONFIG_FILE} ${PATH_TO_PG_CONFIG_FILE}.bak

    

    # Check for any configuration issues and make changes if needed

    # Example: increase the cache size to improve performance

    sed -i 's/shared_buffers = 128MB/shared_buffers = 256MB/' ${PATH_TO_PG_CONFIG_FILE}

    

    # Restart Postgresql to apply the changes

    systemctl restart postgresql

else

    echo "Postgresql configuration file not found"

    exit 1

fi



echo "Postgresql configuration issues remediated successfully"

exit 0


```

The Low Postgresql commit rate incident is triggered when the number of transactions processed by Postgresql drops below a certain threshold, indicating a potential issue with the database's performance. This could be caused by a variety of factors, such as high load or inadequate resources. The incident requires investigation and resolution to ensure the database is functioning properly.


This incident type occurs when there are too many locks acquired on a Postgresql database instance. This can cause issues with database performance and functionality. It may be necessary to adjust the max\_locks\_per\_transaction setting in Postgresql to prevent this issue from occurring.


Postgresql too many locks acquired

This incident type refers to a situation where the Postgresql database has a high rollback rate, which means that a high percentage of transactions are being aborted compared to the committed ones. This can cause issues with data consistency and performance, and may require investigation and resolution by the responsible team. The incident details may include information about the affected database instance, the service or system impacted, the urgency level, and any related alerts or escalations.


Postgresql high rollback rate incident

This incident type refers to a scenario where there is a high rate of statement timeouts in a Postgresql database instance. This can lead to degraded performance and potentially impact the availability of the database. It is important to quickly identify and address the underlying cause of the timeouts to ensure the stability of the system.


Postgresql high rate statement timeout incident.

This incident type occurs when there is a failure in the authentication process for the Postgres user's password. This can happen due to a variety of reasons such as incorrect login credentials, expired passwords, or database configuration issues. It is important to address this type of incident promptly to ensure that the database remains secure and accessible only to authorized users.


Password Authentication Failure for Postgres User

This incident type occurs when the maximum number of connections allowed to a Postgres database has been reached. This prevents new connections from being established and can result in errors or downtime. It is important to monitor connection usage and adjust the maximum connection limit as needed to prevent this issue from occurring.


Postgres Connection Limit Reached

```shell
# Environment Variables

export DISK="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export DATABASE_PORT="PLACEHOLDER"

export DATABASE_USER="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_HOST="PLACEHOLDER"
```


### Check if Postgresql service is running

```shell
systemctl status postgresql.service
```

### Check Postgresql logs for any errors

```shell
tail -n 50 /var/log/postgresql/postgresql-main.log
```

### Check the current connection count in Postgresql

```shell
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"
```

### Check the number of active transactions in Postgresql

```shell
sudo -u postgres psql -c "SELECT SUM(pg_stat_activity.xact_commit) FROM pg_stat_activity;"
```

### Check the Postgresql configuration for any anomalies

```shell
cat /etc/postgresql/main/postgresql.conf
```

### Check the system load and resource utilization

```shell
top
```

### Check disk usage and I/O performance

```shell
df -h

iostat -dx ${DISK}
```

### Check network connectivity to the Postgresql server

```shell
ping ${DATABASE_HOST}
```

### Check firewall rules for the Postgresql port

```shell
iptables -L -n | grep ${DATABASE_PORT}
```

### Check for any other running processes that may be causing contention

```shell
ps -ef | grep postgresql
```

### Check for any Postgresql updates

```shell
sudo -u postgres psql -c "SELECT version();"
```


### Check if there are any long-running queries that may be causing a bottleneck. Identify the queries and tune them or optimize the database schema.

```shell


#!/bin/bash



# Set the database credentials

DB_HOST=${DATABASE_HOST}

DB_PORT=${DATABASE_PORT}

DB_NAME=${DATABASE_NAME}

DB_USER=${DATABASE_USER}

DB_PASSWORD=${DATABASE_PASSWORD}



# Connect to the database and check for long-running queries

LONG_RUNNING_QUERIES=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "SELECT query FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '5 minutes';")



# Check if there are any long-running queries

if [ -z "$LONG_RUNNING_QUERIES" ]; then

  echo "No long-running queries found"

else

  echo "Long-running queries found:"

  echo "$LONG_RUNNING_QUERIES"

  
fi


```

### Check if there are any configuration issues with the Postgresql server. Verify the configuration settings and make any necessary changes.

```shell


#!/bin/bash

PATH_TO_PG_CONFIG_FILE=/etc/postgresql/main/postgresql.conf

# Check if Postgresql is installed and running

if ! pgrep -x "postgres" > /dev/null

then

    echo "Postgresql is not running"

    exit 1

fi



# Check the configuration settings for Postgresql

if [ -f ${PATH_TO_PG_CONFIG_FILE} ]

then

    # Make a backup of the configuration file

    cp ${PATH_TO_PG_CONFIG_FILE} ${PATH_TO_PG_CONFIG_FILE}.bak

    

    # Check for any configuration issues and make changes if needed

    # Example: increase the cache size to improve performance

    sed -i 's/shared_buffers = 128MB/shared_buffers = 256MB/' ${PATH_TO_PG_CONFIG_FILE}

    

    # Restart Postgresql to apply the changes

    systemctl restart postgresql

else

    echo "Postgresql configuration file not found"

    exit 1

fi



echo "Postgresql configuration issues remediated successfully"

exit 0


```


Low Postgresql commit rate incident

Overview

Parameters

Debug

Check if Postgresql service is running

Check Postgresql logs for any errors

Check the current connection count in Postgresql

Check the number of active transactions in Postgresql

Check the Postgresql configuration for any anomalies

Check the system load and resource utilization

Check disk usage and I/O performance

Check network connectivity to the Postgresql server

Check firewall rules for the Postgresql port

Check for any other running processes that may be causing contention

Check for any Postgresql updates

Repair

Check if there are any long-running queries that may be causing a bottleneck. Identify the queries and tune them or optimize the database schema.

Check if there are any configuration issues with the Postgresql server. Verify the configuration settings and make any necessary changes.

Learn more

Related Runbooks

Postgresql too many locks acquired

Postgresql high rollback rate incident

Postgresql high rate statement timeout incident.

Password Authentication Failure for Postgres User

Support