PostgreSQL 加索引系统OOM 怨我了--- 不怨你怨谁

PostgreSQL “我怎么就连个数据库都不会建？” --- 你还真不会！

咱们上期那位小哥，锲而不舍的问64怎么回事，我一直没说。这不生气了，不搭理我了，不过最近又联系我了，问他们那里的PostgreSQL OOM了，怎么办。还是ORACLE TO PG迁移过程中，他们先导入的数据，后续加的索引，但是只要一加索引就OOM。

错误信息

ERROR:  Out of memory
DETAIL:  Failed on request of size 4096 bytes.
HINT:    You may need to increase maintenance_work_mem parameter.

对话开始

DBA： 老师你说这怎么回事，我们这导入那么大的数据都没事，怎么一加索引就OOM，怎么回事，给看看呗！

我：你把你服务器的参数，服务器的配置说一下，还有把多大的表，怎么加的索引也说一下

DBA： 我们简单，参数没怎么动，我看了一个什么网课，上面人家说的听明白的，说做一些系统维护类的工作，尽量把maintenance_work_mem 调大，这样有利于操作。然后我别的没调，就把这个maintenance_work_mem 调大了。其他的服务器 32G内存，CPU 8核的，还有啥，哦那个数据量不小一个表不到1个亿，有那么10来张表都差不多，都大几千万。

对了这有啥关系，你不会是不会吧，问我这么多，不应该是一看报错，就都明白了，马上说结论了。

我： 你还没说怎么加的索引呢？ 怎么加的

DBA: 简单，不就加索引吗 create index idx_tj_name on count_info (systemname,systemstd)。

我：其他的参数你调整了吗？

DBA: 哦想起来了，我还调整了max_parallel_maintenance_workers 这个参数，好像有人说调整这个大了，加索引快。

我：挺好，爱学习哈，我问一句这个参数你调整到多大？

DBA： 我调整成4了，原来是2太小了。

我： 太小了？maintenance_work_mem 你调整到多大？ 别调整的太大。

DBA：4G，不多我们32G的内存，整体一共就给分配4G，所以我纳闷怎么就OOM了，4G，这不官方要求的吗，那英文官方文档你没看呀，8分之一，你不知道？

我： 啊，你不会以为是你建立索引的时候最多分配4G吧？ 你一次添加了多少索引？

DBA: 16个， 我开了16个PSQL登录建立的，你赶紧告诉我怎么回事，会吗，不会我找别人了，磨磨唧唧，问东问西的。

我： 我给你说，你maintenance_work_mem 设置的有点大了，你减少一下，我算一下哈，稍等 OK ，你把你的maintenance_work_mem 调整成300MB，你在试试应该不会在OOM。嗯，你现在也光是导数，没有业务对吧？也没有访问对吧？

DBA： 啥玩意，就这，我看你也是个半瓶子，说OOM 然后你你就把内存调小了，那我也会，为啥？

我：首先你对maintenance_work_mem的理解是有问题的，maintenance_work_mem不是说你添加索引的时候，一共分配多少内存，而是你添加索引时，每个进程可以开到的最大的内存。同时你表不小，还开了16个进程加索引，你这不出事，等什么呢？

DBA：不懂，不就加索引，有那么难，就几条命令，你这说的有根据吗？

我： 听我说

你加索引时采用的create index 命令，你PG应该是新版本吧，我假设你PG16，这个版本是支持并行添加索引的，且有参数可以进行设置，你又开了那么多“窗口”，来人工并行执行添加索引的命令，而你每个添加索引的命令本身也会产生多个子进程来进行工作，这就产生了叠加效应。

你自己看一下 16 * 4 * 4G = 256G ，如果其他的条件匹配的话，你会用到256G的内存，且你本身只有32G内存，你不OOM，这就没天理了。你想提速的想法是好的，但你没有弄明白这些参数组合在一起的后果，说明白了吗？

DBA：哦明白了，那我在问一个问题，有没有不调整内存的方法，然后尽量少出问题？

我： 有办法

DBA： 啥办法，还有上次那个64什么意思？ 你赶紧说，还有我记得官方文档上建议这个参数是总内存的8分之一，你这个对吗？

总结：“尽信书，则不如无书。” 且 “务要日日知非，日日改过；一日不知非，即一日安于自是；一日无过可改，即一日无步可进。”

PostgreSQL 数据库添加索引有关的参数：

max_worker_processes

max_parallel_maintenance_workers

maintenance_work_mem

这三个参数是与本次故障有关的原因之一，其中max_worker_processes 是PG后台工作进程最大数量，这个参数负责各种任务，包含的类型较多，如autovacuum,vacuum ,复制，逻辑复制，并行查询，添加索引等等一系列工作的进程数的池。

而max_parallel_maintenance_workers，是在进行维护性工作中，每个维护进程可以开的最大的子进程数。

maintennace_work_mem 设置的太大达到4GB，这里指的是每个进程（子进程）可以分配的内存数。在这样的情况下，如果max_worker_processes也调整的很大，且还不知节制的同时并行建立索引，那么必然会出现OOM的问题。

问题：

1 虽然系统没有业务在运行，但整体机器的性能并不能支持，进行16个索引的同时建立，且表的体积也较大的情况下。

2 部分参数调整的太大，尤其maintenance_work_mem，在这样的使用场景下。

最后建议 ：

调低 max_parallel_maintenance_workers = 2

maintenance_work_mem = （500MB ---- 1G）

一次不要同时建立16个索引，改为同时建立4-6个 （这里还未考虑磁盘是否支持这么高的IOPS的需求，假设支持），因为内存消耗过多而到时OOM得情况会得到缓解或不在出现。