PostgreSQL：您可能需要增加MAX_LOCKS_PER_TRANSACTION

PostgreSQL：您可能需要增加MAX_LOCKS_PER_TRANSACTION

（本文阅读预计时间：11分钟）

作者：汉斯·尤尔根·舍尔希(Hans-JürgenSchönig)，从上世纪90年代就有使用PostgreSQL的经验，他是CYBERTEC公司的CEO与技术带头人，CYBERTEC是该领域的市场领导者之一，自2000年以来已为全球无数客户提供服务。他著有图书《Mastering PostgreSQL 9.6: A comprehensive guide for PostgreSQL 9.6 developers and administrators》和《Mastering PostgreSQL 11，Second Edition》，这两本英文图书均已经由武汉大学彭煜玮老师翻译完成并均已出版，中文书名分别为《由浅入深PostgreSQL》、《精通PostgreSQL 11第二版》。

译者：类延良，任职于瀚高基础软件股份有限公司，PostgreSQL数据库技术爱好者，10g & 11g OCM，OGG认证专家。

“out of shared memory”：你们中的有些人可能已经在PostgreSQL中看到了该错误消息。但是它的真正含义是什么，如何预防呢？实际上，问题并不像乍看起来那样晦涩难懂。max_locks_per_transaction是您需要用来避免麻烦的关键配置参数。

out of shared memory：何时发生

PostgreSQL使用的大多数共享内存都是固定大小的。对于I/O缓存（shared buffers）以及许多其他组件来说，这都是正确的。这些组件之一与锁定有关。如果您在事务中创建一个表，PostgreSQL必须跟踪您的活动以确保并发事务不能删除您创建的表。跟踪活动很重要，因为您要确保DROP TABLE（或某些其他DDL）必须等待所有读取事务已经终止。问题是，您必须将有关跟踪活动的信息存储在某个位置，而这正是您必须了解的内容。

让我们运行一个简单的脚本：

BEGIN;SELECT 'CREATE TABLE a' || id || ' (id int);' FROM generate_series(1, 20000) AS id;\gexec

该脚本的作用是启动一个事务并生成20000个CREATE TABLE语句。它只是生成SQL，然后自动执行该SQL（\gexec将前一条SQL语句的结果视为输入）。

让我们看看SELECT语句产生了什么……

BEGIN ?column?---------------------------- CREATE TABLE a1 (id int); CREATE TABLE a2 (id int); CREATE TABLE a3 (id int); CREATE TABLE a4 (id int); CREATE TABLE a5 (id int);...

现在让我们看看PosgreSQL做了什么：

...CREATE TABLECREATE TABLEERROR: out of shared memoryHINT: You might need to increase max_locks_per_transaction.ERROR: current transaction is aborted, commands ignored until end of transaction blockERROR: current transaction is aborted, commands ignored until end of transaction blockERROR: current transaction is aborted, commands ignored until end of transaction blockERROR: current transaction is aborted, commands ignored until end of transaction block...

在创建了几千张表之后，PostgreSQL会报错：“out of shared memory”。您可以看到我们在单个事务中创建了所有这些表。PostgreSQL必须锁定它们，最终耗尽内存。请记住:数据库使用固定大小的共享内存区域来保存这些锁。

一个逻辑问题是：这个内存区域的大小是多少？有两个参数起作用：

test=# SHOW max_connections; max_connections----------------- 100(1 row) test=# SHOW max_locks_per_transaction; max_locks_per_transaction--------------------------- 64(1 row)

我们可以在共享内存中保留的锁的数量为max_connections x max_locks_per_transaction。请记住，行级锁与此不相关。您可以轻松地执行下面的SQL语句

SELECT * FROM billions_of_rows FOR UPDATE;

上面的SQL语句不会用完内存，因为行锁存储在磁盘上，而不是RAM中。因此，锁的数量与表的数量是相关的–与行的数量无关。

检查pg_locks

您如何了解当前发生的情况？为了演示您可以做什么，我准备了一个小例子：

test=# CREATE TABLE t_demo (id int);CREATE TABLE

首先，您可以创建一个简单的表。

您可能知道，在PostgreSQL中，名称根本不相关。在内部，只有数字才重要。要获取简单表的对象ID，请尝试以下语句：

test=# SELECT oid, relkind, relname FROM pg_class WHERE relname = 't_demo'; oid | relkind | relname--------+---------+--------- 232787 | r | t_demo(1 row)

在我的示例中，对象ID为232787。让我们找出该数字在哪里弹出：

test=# BEGIN;BEGINtest=# SELECT * FROM t_demo; id----(0 rows) test=# \xExpanded display is on.test=# SELECT * FROM pg_locks WHERE relation = '232787';-[ RECORD 1 ]------+----------------locktype | relationdatabase | 187812relation | 232787page |tuple |virtualxid |transactionid |classid |objid |objsubid |virtualtransaction | 3/6633pid | 106174mode | AccessShareLockgranted | tfastpath | t

由于我们正在从表中读取数据，因此您可以看到PostgreSQL必须保留ACCESS SHARE LOCK，这能确保不会以损害并发SELECT语句的方式删除或修改表（= DDL）。

在一个事务中处理的表越多，pg_locks的条目就越多。在大量并发的情况下，多个条目可能会成为问题。

PostgreSQL分区及其与“out of shared memory”的关系

如果您正在运行典型的应用程序，则由于相关锁的总数通常很少，因此内存不足错误基本上很少发生。但是，如果您严重依赖过多的分区，则情况会有所不同。在PostgreSQL中，分区基本上是一个普通表，因此被视为普通表。因此，锁定可能成为问题。

让我们看下面的例子：

BEGIN; CREATE TABLE t_part (id int) PARTITION BY LIST (id); SELECT 'CREATE TABLE t_part_' || id || ' PARTITION OF t_part FOR VALUES IN (' || id || ');'FROM generate_series(1, 1000) AS id; \gexec SELECT count(*) FROM t_part;

首先，创建一个父表。然后，添加1000个分区。为了简单起见，每个分区仅允许仅容纳一行-但是暂时不要担心。然后，执行一个简单的SELECT语句-保证可以读取所有分区。

以下清单显示了脚本生成的用于创建分区的SQL：

?column? -------------------------------------------------------------------- CREATE TABLE t_part_1 PARTITION OF t_part FOR VALUES IN (1); CREATE TABLE t_part_2 PARTITION OF t_part FOR VALUES IN (2); CREATE TABLE t_part_3 PARTITION OF t_part FOR VALUES IN (3); CREATE TABLE t_part_4 PARTITION OF t_part FOR VALUES IN (4); CREATE TABLE t_part_5 PARTITION OF t_part FOR VALUES IN (5);...

运行后

SELECT count(*) FROM t_part

SELECT count(*)FROM pg_locksWHERE mode = 'AccessShareLock'; count------- 1004(1 row)

PostgreSQL已经需要超过1000个锁来执行此操作。因此，分区将增加此共享内存字段的使用率，并使“out of memory”的可能性更大。如果您正在使用大量分区，则可以更改max_locks_per_transaction。

最后

如果您对数据科学和机器学习感兴趣，可以查看Kevin Speyer在“强化学习”上的帖子，可以在这里找到。

(https://cybertec-postgresql.com/en/implementation-of-a-reinforcement-learning-algorithm-from-scratch/)

规模空前，再创历史 | 2020 PG亚洲大会圆满结束

PG ACE计划的正式发布

三期PostgreSQL国际线上沙龙活动的举办

六期PostgreSQL国内线上沙龙活动的举办

PGCM高级认证培训的正式开启

PostgreSQL 13.0 正式版发布通告

深度报告：开源协议那些事儿

从“非主流”到“潮流”，开源早已值得拥有

Oracle中国正在进行新一轮裁员，传 N+6 补偿

PostgreSQL与MySQL版权比较

PostgreSQL与Oracle：成本、易用性和功能上的差异

使用ora2pg完成从Oracle到Postgres的迁移

PostgreSQL活动篇

PostgreSQL培训认证篇

PostgreSQL技术干货

PostgreSQL热点文集

PostgreSQL新闻资讯

2020 PG亚洲大会珍藏

↓↓↓