PostgreSQL:您可能需要增加MAX_LOCKS_PER_TRANSACTION

作者: 汉斯·尤尔根·舍尔希(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 TABLE
CREATE TABLE
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  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;
BEGIN
test=# SELECT * FROM t_demo;
id
----
(0 rows)
test=# x
Expanded display is on.
test=# SELECT * FROM pg_locks WHERE relation = '232787';
-[ RECORD 1 ]------+----------------
locktype           | relation
database           | 187812
relation           | 232787
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 3/6633
pid                | 106174
mode               | AccessShareLock
granted            | t
fastpath           | 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_locks
WHERE   mode = 'AccessShareLock';
count
-------
1004
(1 row)

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

最后

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

( https://www.cybertec-postgres…

更多精彩内容,请关注以下平台、网站:

中国PostgreSQL分会官方公众号(技术文章、技术活动):

开源软件联盟PostgreSQL分会

中国PostgreSQL分会技术问答社区:

www.pgfans.cn

中国PostgreSQL分会官方网站:

www.postgresqlchina.com

SegmentFault博客
我还没有学会写个人说明!
上一篇

Mysql成神之路---B+树索引(超级干货)

下一篇

Mysql技术内幕之InnoDB锁探究

你也可能喜欢

评论已经被关闭。

插入图片