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 allotted 'deadlock_timeout' time to acquire a lock.

Rationale:

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. This recommendation is applicable to PostgreSQL database instances.

From Console:

1. Go to the Cloud SQL Instances page in the Google Cloud Console by visiting https://console.cloud.google.com/sql/instances.
2. Select the PostgreSQL instance where the database flag needs to be enabled.
3. Click 'Edit'.
4. Scroll down to the 'Flags' section.
5. To set a flag that has not been set on the instance before, click 'Add item', choose the flag 'log_lock_waits' from the drop-down menu and set the value as '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 PosgreSQL database instance using the below command:

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

Note: This command will overwrite all database flags previously set. 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 ("=").
.