December 16, 2023
Summary: in this tutorial, you will learn how to log lock waits and temp files in PostgreSQL.
Table of Contents
Introduction
In order to satisfy concurrency in the ACID properties, every relational database implements locking. This locking may cause one or more sessions to wait until the lock has been released by a session holding the lock. Much of this information may be available to see in real time by querying the pg_locks view, but it doesn’t contain historical information about lock waits. For this purpose, we could use the facility available within PostgreSQL to log all the SQL statements that are waiting due to a lock acquired by another SQL. Similar to locks, we could also log when multiple sessions are involved in a deadlock. In this tutorial, we shall discuss the steps to enable logging of such waits due to locking and deadlocks in PostgreSQL.
Getting ready
Locks are logged for SQL that has been waiting for more than deadlock_timeout amount of time. deadlock_timeout is set to 1 second by default. Increasing this to larger amounts may make the deadlock checks less expensive for a query when it waits for this duration.
However, having a higher value set for deadlock_timeout may not log much information about lock waits. This is because lock waits are only logged when they are waiting for longer than this duration.
How to do it…
Let’s get started with the following steps:
1. Set log_lock_waits to ON to log a message when a session waits for a longer duration than deadlock_timeout:
$ psql -c "ALTER SYSTEM SET log_lock_waits to 'ON'"
-- reload to get the change into effect.
$ psql -c "select pg_reload_conf()"
2. Set log_temp_files to log a message including the statement when temp reaches this size.
The following command enables the logging of all SQLs generating more than 100KB of temp:
$ psql -c "ALTER SYSTEM SET log_temp_files to '100KB'"
$ psql -c "select pg_reload_conf()"
The following command enables the logging of all SQLs generating more than 100MB of temp:
$ psql -c "ALTER SYSTEM SET log_temp_files to '100MB'"
$ psql -c "select pg_reload_conf()"
How it works…
In order to start logging waits due to locks, we can use the command seen in step 1. When log_lock_waits is enabled, upon a reload, every query that is waiting due to locks for more than deadlock_timeout amount of time is logged.
Similarly, to log all queries generating more than a certain amount of temp, we need to set log_temp_files as seen in step 2. In the example, we see the commands that can set log_temp_files to 100KB and 100MB. What this means is that if a query generates 100KB or 100MB of temp, the statement will be logged to the log file.