PostgreSQL 教程: 记录锁等待和临时文件

十二月 16, 2023

摘要:在本教程中,您将学习如何在 PostgreSQL 中记录锁等待和临时文件。

为了满足 ACID 属性中的并发性,每个关系数据库都实现了锁定。该锁定可能会导致一个或多个会话等待,直到持有锁定的会话释放锁定。通过查询pg_locks视图可以实时查看大部分这方面的信息,但它并没有包含锁等待相关的历史信息。为此,我们可以使用 PostgreSQL 中提供的工具,来记录由于另一个 SQL 获取了锁而等待的所有 SQL 语句。与锁类似,我们也可以记录死锁场景中涉及的多个会话的情况。在本教程中,我们将讨论在 PostgreSQL 中记录由于锁定和死锁而导致的等待的日志,所涉及的的步骤。

准备工作

对于等待时间超过deadlock_timeout的 SQL,将会记录锁等待。 默认情况下deadlock_timeout设置为 1 秒。将此值增加到更大的数量,可能会使查询在等待至此持续时间时的死锁检查成本更低。 但是,将deadlock_timeout设置为较高的值,可能不会记录太多有关锁等待的信息。这是因为只有当等待时间超过此持续时间时,才会记录锁等待。

操作步骤

让我们从以下步骤开始:

1. 设置log_lock_waits为 ON,以在会话等待时间超过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. 设置log_temp_files为一个值,在临时数据达到此大小时记录包含语句的消息。

以下命令设置,记录生成超过 100KB 的临时数据的所有 SQL 的日志:

$ psql -c "ALTER SYSTEM SET log_temp_files to '100KB'"
$ psql -c "select pg_reload_conf()"

以下命令设置,记录生成超过 100MB 的临时数据的所有 SQL 的日志:

$ psql -c "ALTER SYSTEM SET log_temp_files to '100MB'"
$ psql -c "select pg_reload_conf()"

怎么做到的…

为了开始记录由于锁定而导致的等待,我们可以使用步骤 1 中显示的命令。启用log_lock_waits后,在重新加载时,将记录由于锁定而等待超过deadlock_timeout时间的每个查询。

同样,要记录生成超过一定数量的临时数据的所有查询,我们需要按照步骤 2 中所示,设置log_temp_files。在此示例中,我们看到可以设置log_temp_files为 100KB 和 100MB 的命令。这意味着,如果查询生成 100KB 或 100MB 的临时数据,则该语句将被记录到日志文件中。