Understanding and Configuring the "Potential Deadlock Issue" Rule in Foglight for PostgreSQL (4379678)

The "Potential Deadlock Issue" rule is designed to alert administrators when there are ungranted locks in the PostgreSQL database that have exceeded the configured deadlock_timeout threshold. This may indicate a potential deadlock situation that could impact database performance or availability.

PostgreSQL uses locks to manage concurrent access to resources. When a lock cannot be granted due to conflicting operations, it waits. If the wait time exceeds the deadlock_timeout setting, it may indicate a deadlock risk.

This rule evaluates:

• The number of ungranted locks (granted == false)
• The age of each lock compared to the deadlock_timeout (in milliseconds).

If any ungranted lock exceeds this timeout, the rule triggers a Critical alarm.

So where does scope.deadlock_timeout come from?

This value is retrieved from the PostgreSQL agent's monitored properties in Foglight. Specifically:

• scope refers to the PostgreSQL database instance being monitored.
• deadlock_timeout is a PostgreSQL configuration parameter that the Foglight agent collects from the database.
• The agent queries the database for its current runtime settings, including deadlock_timeout, and makes them available as part of the scope object.

In summary:

• timeoutMS is the current value of PostgreSQL’s deadlock_timeout setting, expressed in milliseconds.
• It is dynamically pulled from the monitored database instance by the Foglight agent.

Resolution 1: Adjust the deadlock_timeout Parameter in PostgreSQL

To reduce false positives or tune the sensitivity of this rule, adjust the deadlock_timeout setting in your PostgreSQL configuration:

1. Locate your PostgreSQL configuration file (typically postgresql.conf).
2. Find the deadlock_timeout parameter.
3. Set it to a value appropriate for your workload. For example:
   deadlock_timeout = '5s' -- sets timeout to 5 seconds<
4. Restart PostgreSQL or reload the configuration:
   SELECT pg_reload_conf();

Note: The default value is typically 1 second. Increasing this value may reduce the frequency of alarms but could delay deadlock detection.

The current deadlock timeout value configured in the PostgreSQL database can be viewed from the Configuration dashboard in the PostgreSQL agent  by searching for the word "deadlock" as shown in the image below.

Related article: PostrgreSQL Documentation: deadlock_timeout

Resolution 2: Customize the Rule Logic in the alarm

While not supported by Quest Support, advanced users may choose to customize the rule logic directly in Foglight on-premise systems. This is not available in Foglight Cloud.

1. Navigate to Administration > Rules & Notifications > Rules
2. Edit the Potential Deadlock Issue alarm
3. In the Critical condition, modify the timeout logic, for example
   def timeoutMS = 10000; // Use a fixed 10-second threshold

Disclaimer: Customizing rules is outside the scope of Quest Support. Proceed with caution and consult your Foglight administrator.