ANBOB™

在使用 PostgreSQL 数据库时,可能会看到过该错误消息，最近在highGoDB（postgresq）一个大的业务库做drop database时有遇到,也可能查询pgp_dump或查看表对象大小(如几千个分区)时，会提示提示错误” ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.

PostgreSQL 使用的大多数共享内存都是固定大小的。对于 I/O 缓存（共享缓冲区）和许多其他组件也是如此。其中一个组件与锁定有关。如果您在事务内接触表，PostgreSQL 必须跟踪您的活动以确保并发事务不会删除您将要接触的表。跟踪活动很重要，因为您要确保 DROP TABLE（或其他 DDL）必须等到所有读取事务都终止。问题是，您必须将有关跟踪活动的信息存储在某处——这一点正是您必须理解的。

在 PostgreSQL 中，shared lock table tracks locks 在对象（例如表）上的锁跟踪锁最大允许值为参数 max_locks_per_transaction*（max_connections+max_prepared_transactions）。这意味着不能同时锁定超过这么多不同的对象。

如果您的数据库存储大量表和模式，任何需要锁定超过 max_locks_per_transaction*（max_connections+max_prepared_transactions）的查询都会引发错误：

ERROR: out of shared memory
 HINT: You might need to increase max_locks_per_transaction

模拟该问题

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

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

在创建了几千个表之后，PostgreSQL 会报错：“共享内存不足”。你可以看到，我们在单个事务中创建了所有这些表。PostgreSQL 必须锁定它们，最终耗尽内存。行级锁在这里不相关，因为行锁存储在磁盘上而不是 RAM 中。因此，表的数量很重要，而不是行的数量。请记住：数据库使用固定大小的共享内存字段来存储这些锁。这个内存字段的大小是多少？有3个参数发挥作用max_locks_per_transaction、max_connections 和 max_prepared_transactions：

• max_locks_per_transaction is set to PostgreSQL’s default of 64
• max_prepared_transactions is set to PostgreSQL’s default of 0
• max_connections  is set to PostgreSQL’s   default of 100

虽然这些设置无法直接修改，但使用具有更大连接限制 (max_connections) ，将有效增加共享锁表限制并有助于临时解决当前的错误。
该错误通常与 PostgreSQL 数据库中的大量表和模式有关，这也可能导致 PostgreSQL 工具（例如 pg_dump，它在捕获备份时检查所有数据库对象）的性能问题。对于这种情况以及数据库和应用程序的进一步可扩展性，我们建议考虑数据库分片并将模式拆分为多个数据库。

如通过pg_locks.relation 匹配pg_class.oid可以查询对象lock记录，事务接触的表越多，pg_locks 的条目就越多。在并发性较强的情况下，多个条目可能会成为问题。

如是是pg_dump备份时触发，您可以使用 pg_dump 及其 –schema / –table 选项为独立的模式/表或一组模式/表独立创建备份，而不是一次捕获整个数据库的备份。

如果您运行的是典型应用程序，内存不足错误基本上很少发生，因为相关锁的总数通常很低。但是，如果您严重依赖过度分区，情况就不同了。在 PostgreSQL 中，分区基本上是一个普通表——并且被如此对待。因此，锁定可能成为一个问题。

让我们看看下面的例子：

而做SELECT count(*) FROM t_part时，需要防止在SELECT过程中发现DDL变更，增加AccessShareLock锁，通过pg_locks中可以发现, PostgreSQL 已经需要超过 1000 个锁才能完成此操作。因此，分区将增加此共享内存字段的使用率，并使“内存不足”的可能性更大。如果您大量使用分区，则更改 max_locks_per_transaction 是有意义的。

同事在pg12中，逐渐增大max_locks_per_transaction，观察多分区的lock个数

[postgres@mydb1a pg12_7]$ psql -p 5431 -d test
psql (12.7)
Type "help" for help.
test=# begin;
BEGIN
test=# select 'select count(*) from test_rel_part_'||generate_series(1,5000)||';select count(*) from pg_locks ;' \gexec

参数修改方法

systemctl stop postgresql
echo max_locks_per_transaction = 128 | sudo tee -a /etc/postgresql/xx/main/postgresql.conf
systemctl start postgresql
sudo -u postgres psql -c 'SHOW max_locks_per_transaction;'

或使用 sed 编辑 “postgresql.conf” 文件更新 “max_locks_per_transaction” parameter:

sed -i 's/^max_locks_per_transaction = .*/max_locks_per_transaction = new_value/' postgresql.conf

打赏

微信扫一扫，打赏作者吧～