为什么 PostgreSQL 耗尽了我所有宝贵的硬盘空间？

Question

我刚刚完成了尽可能多的有关维基百科（英语）的链接结构数据的传输。 基本上，我从维基百科的最新转储存储库下载了一堆 SQL 转储。 由于我使用 PostgreSQL 而不是 MySQL，因此我决定使用 管道 shell 将所有这些转储加载到我的数据库中命令。

无论如何，其中一个表有 2.95 亿行：pagelinks 表； 它包含所有维基内部的超链接。 我从我的笔记本电脑上使用 pgAdmin III 将以下命令发送到我的数据库服务器（另一台计算机）：

SELECT pl_namespace, COUNT(*) FROM pagelinks GROUP BY (pl_namespace);

它已经运行了一个小时左右。 问题是，邮政局长似乎越来越多地占用我非常有限的高清空间。 我认为到目前为止它已经消耗了大约 20 GB 的空间。 我之前曾尝试过 postgresql.conf 文件，以便为它提供更多的性能灵活性（即让它使用更多的资源），因为它使用 12 GB RAM 运行。 我想我基本上将这个文件的大多数字节和此类相关变量增加了四倍，认为它会使用更多的 RAM 来完成它的工作。

然而，数据库似乎并没有使用太多内存。 使用 Linux 系统监视器，我可以看到邮局管理员正在使用 1.6 GB 的共享内存 (RAM)。 不管怎样，我想知道你们是否可以帮助我更好地理解它在做什么，因为我似乎真的不明白PostgreSQL如何使用HD资源。

关于维基百科数据库的元结构，他们提供了一个很好的 架构 这可能对您有用，甚至您感兴趣。

请随时向我询问更多详细信息，谢谢。

Answer 1

可能是 GROUP BY 导致了问题。 为了进行分组，数据库必须对行进行排序以将重复的项目放在一起。 索引可能没有帮助。 粗略计算：

假设每行占用 100 字节的空间，即 29,500,000,000 字节，或大约 30GB 的存储空间。 它无法在内存中容纳所有内容，因此您的系统会出现抖动，从而使操作速度降低 1000 倍或更多。 如果您的硬盘空间使用交换文件，则它可能会消失在交换空间中。

如果您只需要执行一次此计算，请尝试将其分解为更小的数据子集。 假设 pl_namespace 是数字，范围为 1-2.95 亿，请尝试如下操作：

SELECT pl_namespace, COUNT(*)
FROM pagelinks
WHERE pl_namespace between 1 and 50000000
GROUP BY (pl_namespace);

然后对 50000001-100000000 执行相同操作，依此类推。 使用 UNION 将您的答案组合在一起，或者简单地使用外部程序将结果制成表格。 忘记我写的关于索引不能帮助 GROUP BY 的内容吧； 在这里，索引将有助于 WHERE 子句。

Answer 2

声称只占用 9.5MB RAM 的到底是什么？ 这对我来说听起来不太可能 - 共享内存几乎肯定是在不同 Postgres 进程之间共享的 RAM。 （据我所知，每个客户端最终都会成为一个单独的进程，尽管已经有一段时间了，所以我可能是非常错误的。）

您在 pl_namespace 列上有索引吗？ 如果有大量不同的结果，我可以想象该查询对于一个没有索引的 2.95 亿行表来说非常繁重。 话虽如此，10GB 实在是太难以承受了。 你知道它正在写入哪些文件吗？

Answer 3

好的，要点如下：

GROUP BY 子句使索引无效，因此 postmaster（postgresql 服务器进程）决定创建一堆位于目录 $PGDATA/base/ 中的表（23GB 表） 16384/pgsql_tmp。

修改 postgresql.conf 文件时，我授予 postgreSQL 使用 1.6 GB RAM 的权限（现在我将其加倍，因为它可以访问 11.7 GB RAM）； postmaster 进程确实使用了 1.6 GB RAM，但这还不够，因此需要 pgsql_tmp 目录。

正如 Barry Brown 所指出的，由于我只是执行此 SQL 命令来获取有关 pagelinks.namespaces 之间链接分布的一些统计信息，因此我可以查询 2.96 亿个链接的子集页面链接（这就是他们为调查所做的）。

当命令返回结果集时，所有临时表都被自动删除，就像什么也没发生一样。

谢谢你们的帮助！