作者简介

原文作者：Robert Haas，VP，首席架构师，Database Server @ EnterpriseDB，PostgreSQL主要贡献者和提交者
原文链接：https://rhaas.blogspot.com/2019/01/how-much-maintenanceworkmem-do-i-need.html

译者简介

陈河堆，中兴通讯数据库平台负责人，参与公司自研内存数据库和分布式数据库等系统的开发和设计，对开源数据库PostgreSQL和MySQL等亦有所了解

虽然我通常比较喜欢PostgreSQL官方文档，但在某些地方，它描述还不够具体，用户不知道需要怎么做。文档对maintenance_work_mem配置项的描述就是其中之一。我引述文中原话：“设置更大的值可能会提升空间清理和数据库转储恢复的性能”，但这句话并没有多大帮助，这是因为，如果它可能提高性能，也可能不会提高性能，那么，在决定增加这个配置值之前，你可能想知道究竟是属于哪一种情况，这样才不会浪费内存。长话短说，试试maintenance_work_mem = 1GB。继续阅读以获取更多详细建议。

maintenance_work_mem控制在两种不同场景下系统将分配的内存总量，这两种场景基本上毫不相干。首先，它控制系统在构建索引时将使用的最大内存量。为了构建一个B树索引，必须对输入的数据进行排序，如果要排序的数据在maintenance_work_mem设定的内存中放置不下，它将会溢出到磁盘中。起码在我的个人经验中，这不会成为一个大问题。索引构建很少发生在安装过程中，通常是手动发起的，因此，如果你确定增加该配置值有助于提升你所在环境的性能，那么，在执行CREATE INDEX命令之前，你可以在本地会话中使用SET命令增加该值。maintenance_work_mem所做的第二件事是控制vacuum进程将分配的内存量，以存储那些无效索引元组的TID。

简而言之，vacuum进程扫描表并收集所有无效元组的TID。然后它扫描索引，删除这些TID对应的所有索引项。如果它在扫描完整个表之前耗尽了存放无效元组TID所用的内存，那么它将停止表扫描，转而扫描索引，以丢弃堆积的TID列表，之后从它中断的位置继续扫描表。如果此后还遇到这样的情形，它将再次停止以扫描所有索引。对于一个大表，多次扫描索引的代价是非常昂贵的，特别是在表中有很多索引的情况下。如果你的maintenance_work_mem设置太低，甚至可能需要两次以上的索引扫描。那将非常让人讨厌。因此，需要将maintenance_work_mem设置得足够高，这样就不会导致多次索引扫描。

对于另一种场景，你也不想耗尽内存。注意，系统将同时运行autovacuum_max_workers所指定数目的autovacuum工作进程，每个工作进程将分配相当于maintenance_work_mem大小的内存。当前，它总是全额分配这么多内存，希望将来可以修复这个问题。因此，如果你使用缺省配置autovacuum_max_workers = 3，并且你设置了一个值，比如maintenance_work_mem = 10GB，你会而且经常会消耗30GB的内存专门用于自动空间清理，这还不包括你可能从前台发起的VACUUM或 CREATE INDEX操作所需的内存。这样，你会很容易把一个小系统的内存耗尽，即便是一个大系统，也可能存在诸多性能问题。

幸运的是，你可以粗略地估算实际可能需要多少内存。默认情况下，当无效元组的数量大约达到表大小的20%左右时，空间清理进程开始工作(参见autovacuum_vacuum_scale_factor参数)。每个无效元组将需要6个字节的maintenance_work_mem内存。因此，如果知道最大的表包含多少行记录，而你没有修改该自动空间清理比例因子，你可以把这个数乘以1.2，保险起见，可以再加上适当的值，把maintenance_work_mem设置成这个结果值大小的字节数。例如，如果最大的表包含1亿个元组，则1亿* 1.2 = 120MB，因此，为了安全起见，可以配置192MB甚至256MB。注意，对于分区表，每个分区将各自进行空间清理，所以只需关心那个拥有最多元组个数的分区，而不是整个分区表的元组总个数。

有时，基于最大表所占用的磁盘大小进行估算比基于它所包含的行数更为容易。要做到这一点，必须估计一下每8KB页面中有多少个元组。例如，如果估计平均每行大约占用磁盘空间128字节（一般来说，这个估计并非不合理，但显然这还取决于你在表中存储了什么数据），那么，每个8KB块大约有64个元组，由此可算出每1MB表大小大约有8192个元组。因此，如果最大的表是10GB，那么大约是10240 MB * 8192元组/MB * 1.2字节/元组 = ~96 MB的maintenance_work_mem；或许保守一点可以配置128MB左右。如你所见，只有一个相当大的表才要求配置超过我在第一段中所建议的1GB，希望这个解释能够清楚地说明为什么会这样。

请记住，这不是一门精确的科学。有时，即使该表触及到了20%的门槛，autovacuum进程可能也没有启动实例，比如由于它正在忙于处理其他一些表。而在其他时候，空间清理进程可能在该表触及20%门槛之前就开始运行了，比如由于该表需要回收空间用于循环使用，或者由于手动执行VACUUM命令。当然，你必须考虑这个估算值是否会耗尽你的系统内存。上述计算方式是为了给你一个大概的数字，而不是一个精确的值。

如果你确实有非常大的表，那么你可以做一件事情来减少所需的maintenance_work_mem内存数量，即将autovacuum_vacuum_scale_factor减少到一个更小的值。不一定要等到无效行数达到表大小的20%才清理该表的空间，相反，你可以在无效行数达到表大小的1%时就清理，甚至是表大小的0.1%。它可以分别针对每个表进行配置，这很好，因为你可能只想为非常大的表减少该配置。这似乎是一个奇怪的建议，因为，更频繁地清理表并每次只扫描一遍索引，与较少扫描表但每次扫描多遍索引相比，并不一定会节省任何I/O。然而，这忽略了对前台任务的影响考量。特别是对于非常大的表，频繁清理往往会显著提升查询性能。如果你有一个20MB大小的表，只在它有20MB * 20% = 4MB的无效元组时才清理，那没关系，因为4MB并不多。如果你有一个200GB大小的表，只在它有200GB * 20% = 40GB的无效元组时才清理，那就相当痛苦，因为对表的每次顺序扫描都必须读取和跳过40GB的额外内容。

除了恰当地设置autovacuum_work_mem之外，你也可以监视以查看是否得到期望的结果。我强烈建议将log_autovacuum_min_duration设置为非默认值，因为它将带给你很多深入了解autovacuum进程正在做什么的信息，这对故障诊断很有用，不仅对maintenance_work_mem问题，而且实际上对你可能遇到的任何autovacuum进程问题。我最喜欢把这个参数值设置为0，让它记录autovacuum进程执行的每个操作。不过，要是你有大量的表，那可能会生成数量惊人的日志条目。为了以防万一，可选择某些合适的值，使得只记录较大的表的自动清理操作，而不要让日志信息把你淹没。每次因该设置而记录一个自动清理操作时，日志消息将以“index scans: 1”之类的内容结束。有时你或许会看到“index scans: 0”。而如果你看到“index scans: 2”或任何更大的值，则表示maintenance_work_mem内存快用完了，应该考虑增加该配置值。

我有一个不太明确的观点，认为在这个方面PostgreSQL可以比它现在做得更智能。在上述计算示例中，所需的maintenenance_work_mem内存大小大约是最大表的1/100。对于绝大多数用户（尽管不是全部）来说，数据库活跃部分的大小不超过物理内存的数倍。如果autovacuum进程最初只分配少量内存，并在必要时增加内存以避免多次索引扫描，完全没有上限，那么这些用户将会非常高兴。尽管内存消耗没有上限，但它们永远不会真地耗尽内存，因为只会分配实际需要的内存量，而且可能不会太大。反过来说，在当前系统中，由于总是分配配置的内存数量，因此，要是把它设置为高于实际需要的值，很容易耗尽内存。另一方面，对于在普通硬件上运行真正大型PostgreSQL数据库的人来说，没有上限的分配策略将是非常糟糕的。对于这些用户，当前系统更好。所以，我并不完全清楚如何在我们现有的基础上进行改进。

CENTER_PostgreSQL_Community

## 作者简介

* 原文作者：Robert Haas，VP，首席架构师，Database Server @ EnterpriseDB，PostgreSQL主要贡献者和提交者
* 原文链接：https://rhaas.blogspot.com/2019/01/how-much-maintenanceworkmem-do-i-need.html

# 译者简介

陈河堆，中兴通讯数据库平台负责人，参与公司自研内存数据库和分布式数据库等系统的开发和设计，对开源数据库PostgreSQL和MySQL等亦有所了解

![](/images/news/2019/20190225_1540_01.jpg =600x485)

虽然我通常比较喜欢PostgreSQL官方文档，但在某些地方，它描述还不够具体，用户不知道需要怎么做。文档对`maintenance_work_mem`配置项的描述就是其中之一。我引述文中原话：“设置更大的值可能会提升空间清理和数据库转储恢复的性能”，但这句话并没有多大帮助，这是因为，如果它可能提高性能，也可能不会提高性能，那么，在决定增加这个配置值之前，你可能想知道究竟是属于哪一种情况，这样才不会浪费内存。长话短说，试试`maintenance_work_mem = 1GB`。继续阅读以获取更多详细建议。

`maintenance_work_mem`控制在两种不同场景下系统将分配的内存总量，这两种场景基本上毫不相干。首先，它控制系统在构建索引时将使用的最大内存量。为了构建一个B树索引，必须对输入的数据进行排序，如果要排序的数据在`maintenance_work_mem`设定的内存中放置不下，它将会溢出到磁盘中。起码在我的个人经验中，这不会成为一个大问题。索引构建很少发生在安装过程中，通常是手动发起的，因此，如果你确定增加该配置值有助于提升你所在环境的性能，那么，在执行CREATE INDEX命令之前，你可以在本地会话中使用SET命令增加该值。`maintenance_work_mem`所做的第二件事是控制vacuum进程将分配的内存量，以存储那些无效索引元组的TID。

简而言之，vacuum进程扫描表并收集所有无效元组的TID。然后它扫描索引，删除这些TID对应的所有索引项。如果它在扫描完整个表之前耗尽了存放无效元组TID所用的内存，那么它将停止表扫描，转而扫描索引，以丢弃堆积的TID列表，之后从它中断的位置继续扫描表。如果此后还遇到这样的情形，它将再次停止以扫描所有索引。对于一个大表，多次扫描索引的代价是非常昂贵的，特别是在表中有很多索引的情况下。如果你的`maintenance_work_mem`设置太低，甚至可能需要两次以上的索引扫描。那将非常让人讨厌。因此，需要将`maintenance_work_mem`设置得足够高，这样就不会导致多次索引扫描。

对于另一种场景，你也不想耗尽内存。注意，系统将同时运行`autovacuum_max_workers`所指定数目的`autovacuum`工作进程，每个工作进程将分配相当于`maintenance_work_mem`大小的内存。当前，它总是全额分配这么多内存，希望将来可以修复这个问题。因此，如果你使用缺省配置`autovacuum_max_workers = 3`，并且你设置了一个值，比如`maintenance_work_mem = 10GB`，你会而且经常会消耗30GB的内存专门用于自动空间清理，这还不包括你可能从前台发起的VACUUM或 CREATE INDEX操作所需的内存。这样，你会很容易把一个小系统的内存耗尽，即便是一个大系统，也可能存在诸多性能问题。

幸运的是，你可以粗略地估算实际可能需要多少内存。默认情况下，当无效元组的数量大约达到表大小的20%左右时，空间清理进程开始工作(参见`autovacuum_vacuum_scale_factor`参数)。每个无效元组将需要6个字节的`maintenance_work_mem`内存。因此，如果知道最大的表包含多少行记录，而你没有修改该自动空间清理比例因子，你可以把这个数乘以1.2，保险起见，可以再加上适当的值，把`maintenance_work_mem`设置成这个结果值大小的字节数。例如，如果最大的表包含1亿个元组，则1亿* 1.2 = 120MB，因此，为了安全起见，可以配置192MB甚至256MB。注意，对于分区表，每个分区将各自进行空间清理，所以只需关心那个拥有最多元组个数的分区，而不是整个分区表的元组总个数。

有时，基于最大表所占用的磁盘大小进行估算比基于它所包含的行数更为容易。要做到这一点，必须估计一下每8KB页面中有多少个元组。例如，如果估计平均每行大约占用磁盘空间128字节（一般来说，这个估计并非不合理，但显然这还取决于你在表中存储了什么数据），那么，每个8KB块大约有64个元组，由此可算出每1MB表大小大约有8192个元组。因此，如果最大的表是10GB，那么大约是10240 MB * 8192元组/MB * 1.2字节/元组 = ~96 MB的`maintenance_work_mem`；或许保守一点可以配置128MB左右。如你所见，只有一个相当大的表才要求配置超过我在第一段中所建议的1GB，希望这个解释能够清楚地说明为什么会这样。

如果你确实有非常大的表，那么你可以做一件事情来减少所需的`maintenance_work_mem`内存数量，即将`autovacuum_vacuum_scale_factor`减少到一个更小的值。不一定要等到无效行数达到表大小的20%才清理该表的空间，相反，你可以在无效行数达到表大小的1%时就清理，甚至是表大小的0.1%。它可以分别针对每个表进行配置，这很好，因为你可能只想为非常大的表减少该配置。这似乎是一个奇怪的建议，因为，更频繁地清理表并每次只扫描一遍索引，与较少扫描表但每次扫描多遍索引相比，并不一定会节省任何I/O。然而，这忽略了对前台任务的影响考量。特别是对于非常大的表，频繁清理往往会显著提升查询性能。如果你有一个20MB大小的表，只在它有20MB * 20% = 4MB的无效元组时才清理，那没关系，因为4MB并不多。如果你有一个200GB大小的表，只在它有200GB * 20% = 40GB的无效元组时才清理，那就相当痛苦，因为对表的每次顺序扫描都必须读取和跳过40GB的额外内容。

除了恰当地设置`autovacuum_work_mem`之外，你也可以监视以查看是否得到期望的结果。我强烈建议将`log_autovacuum_min_duration`设置为非默认值，因为它将带给你很多深入了解autovacuum进程正在做什么的信息，这对故障诊断很有用，不仅对`maintenance_work_mem`问题，而且实际上对你可能遇到的任何autovacuum进程问题。我最喜欢把这个参数值设置为0，让它记录autovacuum进程执行的每个操作。不过，要是你有大量的表，那可能会生成数量惊人的日志条目。为了以防万一，可选择某些合适的值，使得只记录较大的表的自动清理操作，而不要让日志信息把你淹没。每次因该设置而记录一个自动清理操作时，日志消息将以“index scans: 1”之类的内容结束。有时你或许会看到“index scans: 0”。而如果你看到“index scans: 2”或任何更大的值，则表示`maintenance_work_mem`内存快用完了，应该考虑增加该配置值。

我有一个不太明确的观点，认为在这个方面PostgreSQL可以比它现在做得更智能。在上述计算示例中，所需的`maintenenance_work_mem`内存大小大约是最大表的1/100。对于绝大多数用户（尽管不是全部）来说，数据库活跃部分的大小不超过物理内存的数倍。如果autovacuum进程最初只分配少量内存，并在必要时增加内存以避免多次索引扫描，完全没有上限，那么这些用户将会非常高兴。尽管内存消耗没有上限，但它们永远不会真地耗尽内存，因为只会分配实际需要的内存量，而且可能不会太大。反过来说，在当前系统中，由于总是分配配置的内存数量，因此，要是把它设置为高于实际需要的值，很容易耗尽内存。另一方面，对于在普通硬件上运行真正大型PostgreSQL数据库的人来说，没有上限的分配策略将是非常糟糕的。对于这些用户，当前系统更好。所以，我并不完全清楚如何在我们现有的基础上进行改进。

![CENTER_PostgreSQL_Community](/images/news/2016/pg_bot_banner.jpg)

如何估算maintenance_work_mem内存大小？

作者简介

译者简介