Control: 6.2.6 Ensure that the 'log_lock_waits' database flag for Cloud SQL PostgreSQL instance is set to 'on'

Description

Enabling the log_lock_waits flag for a PostgreSQL instance creates a log for any session waits that take longer than the alloted deadlock_timeout time to acquire a lock. Default value for log_lock_waits flag is off.

The deadlock timeout defines the time to wait on a lock before checking for any conditions. Frequent run overs on deadlock timeout can be an indication of an underlying issue. Logging such waits on locks by enabling the log_lock_waits flag can be used to identify poor performance due to locking delays or if a specially-crafted SQL is attempting to starve resources through holding locks for excessive amounts of time.

Remediation

From Console:

1. Login to GCP console and navigate to Cloud SQL Instances.
2. Select the PostgreSQL instance for which the database flag needs to be enabled.
3. Click EDIT.
4. Scroll down to the Flags section.
5. Click ADD FLAG, choose the log_lock_waits from the drop-down menu, and set its to on.
6. Click SAVE.
7. Confirm the changes under Flags on the Overview page.

From Command Line:

1. List all Cloud SQL database instances using the following command

gcloud sql instances list

2. Configure the log_lock_waits database flag for every Cloud SQL PostgreSQL database instance using the below command

gcloud sql instances patch INSTANCE_NAME --database-flags log_lock_waits=on

Note

• This command will overwrite all previously set database flags. To keep those and add new ones, include the values for all flags to be set on the instance. Any flag not specifically included is set to its default value. For flags that do not take a value, specify the flag name followed by an equals sign ("=").
• Configuring the above flag does not require restarting the Cloud PostgreSQL instance.